Andrew Welch · Insights · #craftcms #database #devops

Published , updated · 5 min read ·


Please consider 🎗 sponsoring me 🎗 to keep writing articles like this.

Connecting to an External Database in Craft CMS

Learn how to con­nect to an exter­nal data­base using Craft CMS by lever­ag­ing the built-in flex­i­bil­i­ty of appli­ca­tion components

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):

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

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: 

<?php
/**
 * Yii Application Config
 *
 * Edit this file at your own risk!
 *
 * The array returned by this file will get merged with
 * vendor/craftcms/cms/src/config/app.php and app.[web|console].php, when
 * Craft's bootstrap script is defining the configuration for the entire
 * application.
 *
 * You can define custom modules and system components, and even override the
 * built-in system components.
 *
 * If you want to modify the application config for *only* web requests or
 * *only* console requests, create an app.web.php or app.console.php file in
 * your config/ folder, alongside this one.
 * 
 * Read more about application configuration:
 * https://craftcms.com/docs/4.x/config/app.html
 */

use craft\helpers\App;
use craft\db\Connection;

return [
    'id' => App::env('CRAFT_APP_ID') ?: 'CraftCMS',
    'components' => [
        'externalDb' => [
            'class' => Connection::class,
            'dsn' => App::env('EXTERNAL_DB_DSN').'',
            'username' => App::env('EXTERNAL_DB_USER'),
            'password' => App::env('EXTERNAL_DB_PASSWORD'),
        ],
    ],
];

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:

EXTERNAL_DB_DSN=mysql:host=127.0.0.1;dbname=demo;port=3306
EXTERNAL_DB_USER=root
EXTERNAL_DB_PASSWORD=ry4nzG0b4g

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:

{% set dbQuery = create('craft\\db\\Query') %}

{% set results = dbQuery
    .from('books')
    .select('id', 'author')
    .where({genre: 'fiction'})
    .all(craft.app.externalDb)
%}

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:

{% set results = create({
        class: 'craft\\db\\Query',
    })
    .from('books')
    .select('id', 'author')
    .where({genre: 'fiction'})
    .all(craft.app.externalDb)
%}

Or:

{% set results = create('craft\\db\\Query')
    .from('books')
    .select('id', 'author')
    .where({genre: 'fiction'})
    .all(craft.app.externalDb)
%}

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

$results = new craft\db\Query
    ->from('books')
    ->select('id', 'author')
    ->where(['genre' => 'fiction'])
    ->all(Craft::$app->externalDb);

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: 

    "yiisoft/db-sqlite": "^1.2"

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

<?php
/**
 * Yii Application Config
 *
 * Edit this file at your own risk!
 *
 * The array returned by this file will get merged with
 * vendor/craftcms/cms/src/config/app.php and app.[web|console].php, when
 * Craft's bootstrap script is defining the configuration for the entire
 * application.
 *
 * You can define custom modules and system components, and even override the
 * built-in system components.
 *
 * If you want to modify the application config for *only* web requests or
 * *only* console requests, create an app.web.php or app.console.php file in
 * your config/ folder, alongside this one.
 *
 * Read more about application configuration:
 * https://craftcms.com/docs/4.x/config/app.html
 */

use craft\helpers\App;
use Yiisoft\Db\Sqlite\Dsn;
use yii\db\Connection;

/**
 * Database is from https://www.sqlitetutorial.net/sqlite-sample-database/
 */
const SQLITE_DB_PATH = '/sqlite-db/chinook.db';

$sqliteDsn = (new Dsn('sqlite', dirname(__DIR__, 1) . SQLITE_DB_PATH))->__toString();

return [
    'id' => App::env('CRAFT_APP_ID') ?: 'CraftCMS',
    'components' => [
        'sqliteDb' => [
            'class' => Connection::class,
            'dsn' => $sqliteDsn,
        ],
    ],
];

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:

{% set artists = create({
    class: 'craft\\db\\Query',
})
    .from('artists')
    .all©aft.app.sqliteDb) %}

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.