Improve Drupal's Database Abstraction Layer Extensibility and Capabilities

This is very far from my area of expertise, so some general questions:

- Is this potentially an all-or-nothing rework of a big subsystem or are there ways towards meaningfull incremental changes?
- If the latter: what are the main pain points to tackle?
- What do we need to know to be able to identify where to start?

It would be good to hear from more people who run into limitations.

@yoroy, @mradcliffe,
I believe this could be done, and it certainly seems that Doctrine/DBAL is well thought out. The DBTNG is not bad at all, but does indeed have its faults.

I think the issues mentioned in the motivation section would, however, need to be addressed separately; use of D/DBAL is not going to fix them all even if it's fully implemented.

My experience to date is still mostly D7, but the major points from that are:
- over-reliance on weakly typed variables and poor design/use of Interfaces. I expect this is a lot better in D8.
- Lowest common denominator approach, especially not using stored procedures and views to better effect (because it is usually hard to predict what to do in advance). I would like to see more granular approach taken.

As for test frameworks, new driver discovery: well almost by definition that's going to be hard. Even if you can discover that a new driver is available for the test, normally the database will not be available on the test machine, and doing that in any automatic way is not something I would want to see done on the testbots because of the security issues.

It is hard to see whether e.g Doctrine/DBAL can be implemented progressively, but I would suggest that if it's not it will be a very hard sell. Perhaps a compatibility layer can be set up to enable db_query, db_select et al into DBAL? Perhaps DBTNG can be extended with a Doctrine driver? Then those wishing to use and expose Doctrine functionality can do so.

Possibly a dupe of #1817778: Consider using Doctrine ORM for Entities

+1 for doctrine as dbal, so Drupal can use more db engine

Removed the abbreviation in the title (full words are more clear) and changed the abbrivation (DBTNG) to DBAL.

This issue is all the more pressing with MySQL and MariaDB diverging.

The problem

The situation that we now have is that there are 3 by core supported databases with for each a driver directory. The idea was that for each supported database a driver directory would be enough. The problem only that that is not the case. There are a number of exceptions where code for a specific database is in core and not in the driver directory. Contrib database drivers also have those exceptions and core does not want to have the contrib database code in core. For an example see #2009238: CONCAT_WS is not compatible with all database drivers. The views subsystem maintainer (@lendude) does not want code for contrib databases in "his" views module. And if I were the views subsystem maintainer I would not want that either. That makes creating a contrib database driver with only the driver directory impossible. So how can we solve the problem? We should give all database drivers module capabilities. The ones supported by core and the contrib ones. It would create a solid base to solve all the problems mentioned in the issue summery.

The plan

1. We make the database drivers first class citizens just like modules and themes are.
2. We place the by core supported database drivers in [root]/core/databases and the contrib ones in [root]/databases.
3. We should also move all code in core for a specific database to the module for the database driver. Show contrib drivers how it should be done.
4. The driver name and the module name should be the same. Or maybe just let the driver method return the module name.
5. The namespaces for the by core supported database drivers should not be changed. It is not strictly necessary and if we would change it, it would cause a lot of havoc. By keeping the namespaces the same, no interruption should occur for sites owners. For them there are no changes.
6. Install the database drivers module as the first module. It can then override anything it needs to.
7. On drupal.org create a new section next to contrib modules and contrib themes for contrib database drivers.
8. Add composer support for contrib database drivers.
9. Allow contrib database driver to have custom testbot(s) for testing their driver. Maybe it would a good idea that certain amount of work on the driver has been done before granting them a custom testbot. Those custom testbots should only be run from their contrib database driver module. Not being able to run those testbots from other contrib modules/themes guarantees that the monetary costs for running those extra testbots stays reasonable. Contrib database drivers with testbot coverage give the site owner/builder a lot more confidence that core works with that contrib driver. See my "Statuses for contrib database drivers" for more details.
10. It all needs to be documented.

Statuses for contrib database drivers

1. Default: the testbot will run with the contrib driver the core tests and the contrib driver tests with one of the by core supported databases.
2. Single custom testbot: the testbot will run with the contrib driver the core tests and the contrib driver tests with not by core supported database (special testbot). For this status the driver must pass about 50 - 80% of all the test of core. The actual percentage is open for discussion.
3. Multiple custom testbots: same as single custom testbot, but with multiple special testbots (different versions of the non-core database and/or different PHP versions). This status can only be granted by a core product maintainer.
4. Semi supported by core: same as multiple custom testbots, but with any BC break by core for the contrib driver, the maintainer of the contrib driver must be consulted, but has no blocking ability. One or more testbots for this driver are available for other modules and themes to test against. This status can only be granted by a core product maintainer.

Examples

1. PostgreSQL with JSON
   If you now want to create contrib PostgreSQL driver with JSON capabilities you:
   - create a new contrib database driver;
   - extend all the classes from the core PostgreSQL driver;
   - add the JSON functionality.
   And you are done. If we add the contrib driver testbot we can also test if it all works.
   Maybe a custom testbot is needed for testing against a newer version of PostgreSQL.
2. MariaDB
   If you now want to create contrib MariaDB driver you:
   - create a new contrib database driver;
   - extend all the classes from the core MySQL driver;
   - add the specific MariaDB code.
   And you are done. If we add a custom contrib driver testbot with a MariaDB database we can test if it all works.
3. Doctrine ORM for Entities
   If you want to convince that core can work with Doctrine ORM for entities, you:
   - create a new contrib database driver;
   - extend all the classes from the core MySQL driver;
   - add the specific Doctrine ORM code.
   And you are done. If we add the contrib driver testbot we can also test if it all works.
   We can then more easily see and discuss what needs to be changed for the move to Doctrine ORM for entities.

That makes creating a contrib database driver with only the driver directory impossible.

Which is why backend_overridable tagged services exist, see #2302617: Define a standard mechaism for backend-aware service overrides and the relevant change notice linked from there.

@chx: Those backend_overridable services are great. Thank you and @Crell for creating them. Maybe I am missing your point, but I will still need an mongodb.services.yml for my aliases and a location for my file that holds the class implementation. And the driver directory is for me the wrong directory, it is just not part of the driver.

Second: What I also want is that site builders can easily use composer to get my MongoDB for Drupal.

composer create-project drupal-composer/drupal-project:8.x-dev my_site_name_dir --no-interaction
composer require daffie/mongodb
composer install

At the moment that is not possible.

Third: I need to override plugin classes. For that I will need to implement a hook. And for that I will need to have a mongodb.module file. See #2009238: CONCAT_WS is not compatible with all database drivers. By your suggestion by the way @chx :-).

Making database drivers a module with the driver directory inside it, would fix those problems. And also the other problems noted in the issue summery. If you think that I am wrong or you see a better solution, then please explain.

@lendude, the subsystem maintainer for the views module said in #2009238-46: CONCAT_WS is not compatible with all database drivers:

@daffie #44 sounds like a great solution.

+++ b/core/modules/views/src/Plugin/views/filter/Combine.php
@@ -84,8 +122,15 @@ public function query() {
+      if ($this->connection->driver() == 'sqlsrv') {

We ideally don't want switches like this in core anyway, let alone for a database backend we don't officially support. So either do something DB agnostic (like #23), or go for #44.

The solution that @lendude wants in "his" views module requires that the contrib database driver has module like capabilities.

@alexpott said in #3098426-10: EntityQueryTest::testToString fails with non-core db drivers:

+++ b/core/tests/Drupal/KernelTests/Core/Entity/EntityQueryTest.php
@@ -1205,6 +1206,10 @@ public function testConditionOnRevisionMetadataKeys() {
+    if (!in_array(Database::getConnection()->driver(), ['mysql', 'sqlite', 'pgsql'])) {
+      $this->markTestSkipped("This test only runs for Drupal core database drivers");
+    }

So i'm not really a fan of this. It feels fragile. Imagine we add a mariadb specific driver to core.

I think we need to back to the drawing board and come up with a less fragile way of producing a test suite for contrib / custom db drivers.

A typical solution for this will result in a system with hooks or plugins. Both require that the custom database driver has module like capabilities.

+1 for going in the Doctrine/DBAL direction, if we can. That unlocks the ability to use more of the Symfony eco-system in Drupal and makes it easier to abstract interactions with the database than DBTNG is currently capable of. D8's (and, by exension, D9's) current approach to DB storage provides driver-level abstraction but the fields represented in entities are pretty much as they appear in the DB.

While we're at it, if we go the DBAL way, shouldn't we consider adopting Doctrine migrations, which :

• do not rely on magic naming like hook_update_N()
• support both up and down migrations
• only apply to the DB schema, unlike hook_update_N()

The Ideas project is being deprecated. This issue is moved to the Drupal project. Check that the selected component is correct. Also, add the relevant tags, especially any 'needs manager review' tags.