Connecting to an External Database in Craft CMS

There may be times you are work­ing on a web­site using Craft CMS when you find the need to con­nect to an exter­nal data­base in addi­tion to the data­base that Craft itself uses.

Perhaps you need to display some information from a legacy database or integrate with existing systems.

In fact, I had this very need when work­ing on a client project, so I cre­at­ed the Con­nect plu­g­in that allowed you to do so.

How­ev­er, in eval­u­at­ing port­ing the Con­nect plu­g­in to Craft CMS 5, I real­ized it was­n’t need­ed at all, so I am dep­re­cat­ing it. 

Let’s get rid of plugins we don’t need!

In this arti­cle, I’ll show you how to con­nect to an exter­nal data­base using noth­ing but Twig and a lit­tle config.

This tech­nique described in this arti­cle works in Craft 3, Craft 4, Craft 5, and like­ly beyond because it is inte­gral to how Craft works.

Link Application Components

You may have seen the app.php file in your config/ direc­to­ry whilst work­ing on your website. 

This seem­ing­ly innocu­ous file is actu­al­ly a very pow­er­ful appli­ca­tion con­fig­u­ra­tion that lets you over­ride or add to the con­fig­u­ra­tion of the Craft CMS web application.

This con­fig­u­ra­tion file is com­bined with the default Craft app.php file when Craft boot­straps itself.

Most of the things you access via craft.app. in your Twig tem­plates or Craft::$app-> in PHP are com­po­nents that are defined in this con­fig file.

This means that we can replace any Craft com­po­nent via this config/app.php and we can also add new components.

Link Database Connections

The way Craft con­nects to its own data­base is via a Con­nec­tion com­po­nent that is acces­si­ble via craft.app.getDb() in Twig or Craft::$app->getDb() in PHP.

This all works trans­par­ent­ly for you, to the point where you may not even have been aware that such a Con­nec­tion com­po­nent exists. Craft cre­ates this com­po­nent using the con­fig­u­ra­tion you have in your config/db.php and/​or the CRAFT_DB_ envi­ron­ment vari­ables, and away you go.

As it turns out, The Yii2 frame­work that Craft uses is very flex­i­ble out of the box, and allows you to con­nect to a vari­ety of data­bas­es (even those not sup­port­ed by Craft itself):

• MySQL
• Mari­aDB
• SQLite
• Post­greSQL: ver­sion 8.4 or higher
• CUBRID: ver­sion 9.3 or higher.
• Ora­cle
• MSSQL: ver­sion 2008 or higher.

If you need to work with data­bas­es that aren’t bun­dled with Yii2, exten­sions are avail­able for more databases:

• Informix
• IBM DB2
• Fire­bird
• Mon­goDB

Let’s see how we can use this to our advantage!

Link Making a Connection

With a lit­tle bit of con­fig in config/app.php and a lit­tle bit of Twig, we can do exact­ly what my old Con­nect plu­g­in did, but native­ly in Craft.

First, in our config/app.php file let’s add our new Con­nec­tion component: 

Here we’re defin­ing a new com­po­nent named externalDb which is of the class Con­nec­tion, and pass­ing in the dsn, username, and password prop­er­ties to con­fig­ure it from the respec­tive EXTERNAL_DB_DSN, EXTERNAL_DB_USER, and EXTERNAL_DB_PASSWORD envi­ron­ment vari­ables in our .env file.

Now that we’ve done this, our new Con­nec­tion com­po­nent will mag­i­cal­ly become avail­able via craft.app.externalDb in Twig, and Craft::$app->externalDb in PHP.

Here’s what our .env file might look like:

It’s like­ly that every­thing except the dsn prop­er­ty looks famil­iar. dsn stands for Data Source Name, and is a string that defines how PHP can con­nect to a data­base using the PHP Data Objects (PDO) abstrac­tion layer.

Just think of it as the cre­den­tials need­ed to con­nect to an arbi­trary database.

You might be used to spec­i­fy­ing things like the data­base host, data­base name, port, etc. sep­a­rate­ly. A dsn is just all of that mashed togeth­er into a sin­gle string.

The for­mat is: {database_driver}:host={database_host};dbname={database_name};port={database_port}

Now that the set­up is out of the way, let’s see how we can access our database!

Link Accessing the External Database

To access the exter­nal data­base, we’ll need to use the create() Twig func­tion to cre­ate a Query object:

We’re cre­at­ing Query object that we defined in our con­fig file, and then we’re con­struct­ing the query we want to use, and return­ing the results via .all()

The chained syn­tax you use for a Query should look famil­iar to you since Craft Ele­ment Queries are just Query objects cus­tomized for query­ing Craft Ele­ments.

The only dif­fer­ence here is that we tell it what data­base Con­nec­tion to use in the .all() method that exe­cutes the Query.

All of the action meth­ods that exe­cute a Query, such as .all(), .one(), .collect(), .column(), etc. take a data­base Con­nec­tion as an argument.

You can also chain it all togeth­er and elim­i­nate the inter­me­di­ate dbQuery vari­able if you like:

Or:

For com­plete­ness, the equiv­a­lent of the above in PHP would be:

Link Performance Considerations

It’s worth not­ing that if you’re con­nect­ing to an exter­nal data­base, if the data­base is host­ed on a sep­a­rate serv­er, there can be per­for­mance implications.

It would be best for per­for­mance if the data­base is host­ed on the same serv­er as your Craft CMS install, or at least on the same sub­net. This will result in much low­er laten­cy con­nec­tions to the data­base, result­ing in bet­ter performance.

Link SQLite real-world example

For a real-world exam­ple, check out the CraftQuest SQLite repos­i­to­ry that was cre­at­ed for a LiveStream demo on CraftQuest​.io.

It shows how to con­nect to an SQLite data­base via Craft using the tech­niques described in this arti­cle. You can eas­i­ly run the project in your web brows­er or clone it down and run it locally!

First, you need to ensure that your VPS or Dock­er con­tain­er has the sqlite bina­ry installed.

Next, we need to add the db-sqlite PHP pack­age to our composer.json file: 

Then here is the config/app.php for con­nect­ing to SQLite:

The doc­u­men­ta­tion for con­nect­ing to SQLite appears to be out­dat­ed (it looks like it’s for Yii 1.x), so use the exam­ple above instead.

We’re just spec­i­fy­ing the path to the SQLite data­base that it should use (in our case, a sam­ple data­base), and then away you go: craft.app.sqliteDb will be a Connection to the SQLite database.

You can access it as described pre­vi­ous­ly in this article:

Link Getting Querious

That’s all there is to it! You can lever­age the famil­iar Query syn­tax you’re used to from Craft while access­ing an exter­nal database.

And you can do it all with­out the need for any kind of plu­g­in or cus­tom module.

That’s the exten­si­ble pow­er of Craft CMS and the under­ly­ing Yii2 framework.