In our last episode, we talked about the various ways of storing data in Drupal 8. One important point we noted was that "in the database" is not an option. All of Drupal's storage systems are abstractions above the actual data store. In fact, I will go as far as saying that if you ever write an SQL query yourself in Drupal 8, you're probably doing it wrong.

There are two key reasons for that stance. One, there's no reason that any of those storage systems, conceptually, need to be in SQL. In fact, for Configuration, Key/Value, and the Cache, it's not even the best tool available. Any of them could be backed by MongoDB, Cassandra or Redis, instead. In fact, many sites will use one of those tools instead of SQL for some (but not all) data storage systems. If your module is hard-coded to SQL, you've now hard-coded all of your users to SQL only. And there's a good chance you've also hard-coded a specific SQL server (generally MySQL) without even intending to.

The second reason is that as a module developer, you should be thinking at a higher level than rows and columns. Most of those systems offer a lot of automation and abstraction tools that provide more power with an easier syntax than SQL, and if you write your own SQL you are bypassing all of that. Most especially, if you have any module configuration not stored in the Configuration system it will not work with any staging and deployment tools. Don't do that to your users.

If for some reason you must write a custom query, say for performance, there is a supported, flexible way to do so. First, ensure that your query is contained within a service, and that service conforms to a declared interface. (You should be doing that anyway, but it's especially important here.) For example, let's say we're creating a service that finds nodes by some highly complex logic that normal Entity Queries don't support. We'll call the class DatabaseComplexNodeFinder, with a ComplexNodeFinderInterface. When we register that service in the container, we should also tag it as one that allows its backend to be overridden, like so:

mymodule.services.yml:

services:<br> mymodule.nodefinder:<br>&nbsp; class: Drupal\mymodule\DatabaseComplexNodeFinder<br>&nbsp; arguments: ['@database']<br>&nbsp; tags:<br>&nbsp;&nbsp; - { name: backend_overridable }

And then we use that service wherever we need to use that logic. The "backend_overridable" tag tells Drupal that there may be alternate implementations it should look for. By default, the class should be written to use generic, non-engine-specific SQL. (That is, no MySQL or PostgreSQL specific features.) It doesn't have to be fast, just work.

Now comes the fun part. We can also define another service named mysql.mymodule.nodefinder, which has the same interface but is very specific to MySQL. Similarly, we can have a service named pgsql.mymodule.nodefinder or mongodb.mymodule.nodefinder, which would be specific to PostgreSQL or MongoDB, respectively. Just registering those services in the container has virtually no cost if they're not used. Those alternate services can have whatever code in them they want, and any set of dependencies they want, as long as they follow ComplexNodeFinderInterface.

Now, in the sites/default/services.yml file, a site owner can specify an alternate default backend:

parameters:<br> default_backend: mysql

The default is mysql, which means that if Drupal finds a mysql version of any "backend_overridable" service, it will use that instead of the generic one. If it doesn't, it just uses whatever is registered by default. (Often times an SQL-database-specific version will be unnecessary, but the capability is there if you need it.) If your site is running on PostgreSQL, change that default_backend to "pgsql". If on MongoDB, set it to "mongodb". And so on.

What if we want to use something other than the default? For instance, we're on a mostly-MySQL-based site but we want to use MongoDB for the State system? That's another simple toggle in the services.yml file. To change the backend for our nodefinder service, we would simply add this to the site-specific services.yml file:

services:<br> mymodule.nodefinder:<br>&nbsp; alias: mongodb.mymodule.nodefinder

That tells the container to use the MongoDB-specific version of that service instead of whatever it was going to use.
There are two big advantages of this design:
1) As a module developer, you can optimize your module for MySQL, PostgreSQL, or MongoDB at the same time. Even if you don't, any other module is free to provide an alternate backend by just registering a service with the correct name.
2) As a site owner, you can mix and match what backend services you want to use. Want a mostly-MySQL-based site, but with Redis for the lock and caching systems? Go for it. Want to store your entities in SQL but everything else in MongoDB? You can do that. Any well-written module will keep on working just fine, because it's either using Drupal's higher-level abstractions or using swappable backends. And if the backend you're looking for isn't available for that service, there's only one class that needs to be written to make it available.
That's the power of dependency injection.