Integrating External Database Connections

A client of ours recently wanted to make a change to their till systems. Previously these tills were pulling in data from the website using xml feeds which were hosted on the web server. The change they wanted to make was to move away from these xml feeds and use a Microsoft Azure database instead. The tills could then communicate directly with this database, instead of having to periodically pull in the entire xml feed. This xml feed was introducing a lag of around 5 minutes for the tills which meant they didn’t have the most recent data.

Microsoft Azure has been identified as the solution the client wishes to implement, so our next step is to refactor the codebase to write to Azure instead of an xml file. Connection details have been provided and we can connect successfully but I want to talk about how we can use this connection in a number of different ways within CraftCMS and the Yii framework which it is built upon.

We already have a custom plugin in place handling our xml feed so I’m not going to explain too much around how this was set up but you can find details on plugin development in our previous article: Getting Technical: Building Complex Craft Plugins.

Initial Connection

First of all we want to add our database connection to our plugin in such a way that we can access it from anywhere. To start us off I’ve created a connectionDetails function in one of our service files. Not shown in the screenshot are the use cases at the top of the file which need to define Craft, yii\db\Connection and yii\db\Exception. The connection function can be seen below:

We’ve specified our connection details in our .env file to keep them secure in the same way that we would with our default craft database. This is considered best practice and if you aren’t already doing this for additional database connections then you really should consider it. A side note to get Microsoft Azure working is that we had to install the SQLSRV php extension onto our server for the connection to work.

To make this connection available to our code form anywhere I then added the following to our plugins init function in it’s main file:

Our plugin is called BookingAdmin so we can now call adigital\bookingadmin\BookingAdmin::$plugin->azureDb from any php file across our plugins to get our connection details.

As mentioned earlier, there are a number of ways we can now use this database connection. I’m going to run through each method we’ve used in our tests on this particular site and explain why we chose to either use or avoid them.

Query Method 1

With our first method we are going to run a basic query to select 5 rows from our database table, but all of our logic is going to stay within our plugin function. This means that when we call it in our template we aren’t able to set any further query parameters. You can see an example of our template code below and I’ve included the query in plain text above it.

Of course we can alter this to pass through parameters such as a limit, or a where clause, but all of the logic still sits within the plugin. This is because our “->one()” and “->all()” methods on craft\db\Query need to have the database connection passed into them so that they don’t use the default database connection from Craft. You can see an example of this code below.

When we output each row of our results within the loop using a dump() tag, we get an array where the database table columns are the keys.

Query Method 2

In our second method we are moving the database connection into our template. To do this we have created a variable within or plugin which we can call to return the connection details to be used in twig (the templating language of craft cms). By doing this we can now define all of our query parameters within the template. We are also calling getRawSql() to output our query instead of typing it out as we were in the first method. An example of our updated template code can be seen below:

Now that we’ve moved our logic into the template, our plugins function shouldn’t return a query result any longer. We need to alter our plugin to return a fully featured query object, which we can then perform “where” and “orderBy” methods against. We can do this by using the code below:

The result of these changes still gives us the same formatting as the first method. We get an array for each row within our results loop.

Query Method 3

Our third and final method is a little different to the first two. Instead of moving logic between the template and the plugin functions, we are actually going to ditch the craft/db/Query object entirely. In this example we will be using a record within our plugin.

Typically records can be used for custom plugin tables but we discovered in the yii documentation it’s actually possible to override the database connection within a record. We can do this using a getDb() function which then calls our database connection settings. This means we no longer need to pass a connection into the “all()” and “one()” methods. You can see an example of our plugins record below:

Now that we’ve added our azure table as a record, we can query it as if it was a custom plugin table held in the default database. We can also add eager loading to our record if needed so we can query using the “with()” parameter. Our plugin function now only needs to return an active query object using the records “find()” method and this can be seen below:

By having a record we can have all of our querying logic in the template and there is no need to define a database connection any longer. This means that although the table exists in an external database, you wouldn’t be able to tell if you were looking at the template code in isolation. You can see an example of this template code below:

By using a record, we are no longer returning an array for each row within our results loop. Instead each row is a record object which has attributes and custom functions which we can define. We can also populate a model from this instead of returning a record.

This final approach of using a record gives us the closest experience to a regular craft entry. It allows us to keep the templating code much more consistent between entries, custom plugin tables, and our external database. This is great because if another member of the team needs to make any changes, they don’t need to ask why the queries are done differently. Also if another page needs to be created with these external queries there is less chance that a setting such as the database connection will be missed out.

Another benefit of using a record means that we don’t have to write any insert or update queries, instead we can just populate a model/record and then call the “->save()” method. This will handle many of the validation rules and security best practices for us so we aren’t having to reinvent the wheel each time we want to insert data into our external database. We can use the record and leverage our frameworks benefits correctly whilst also minimising the amount of code we need to write.

Closing Thoughts

Craft is a very capable system which allows us to perform actions in a number of ways. Because of this it is also possible to introduce suboptimal methods for querying data. As we’ve seen in the 3 examples above, we can make the template handle all of the query parameters, none of the query parameters, or even set which database it is connecting to. Personally I think that we need to have some level of logic in the templates so we aren’t creating a new plugin function for each different query, but defining the database in the template is a step too far. Database definitions should remain within the plugin to reduce any confusion when templating.

As developers, it is always good to try to maintain consistency with the frameworks features. In this case I’m referring to how we would query an entries section within a template. If we can replicate that experience using our external databases then that is the best outcome for maintainability of the templates in the future and by other team members. On top of this it also gives us access to eager loading and many other features which records can use. Using a record also makes it a lot easier for us to insert, update, and delete rows in our external database without having to write out the queries. This also keeps consistency with our other custom plugin tables code for our default database.

Just because you’ve connected to an external database and run a query successfully, doesn’t ever mean you should stop there. Keep going to see if you can improve that integration and enable more of your frameworks features. This applies to all frameworks and not just CraftCMS and yii.

Don’t settle for simple.