Database cross-schema query API

Synopsis

This modules provides an API for database cross-schema queries. It allows to query schema/database outside Drupal's schema (in the case of PostgreSQL or database in the case of MySQL) using the same database API provided by Drupal.

For instance, you have a Drupal site running on a PostgreSQL database "mydb". Drupal is installed in PostgreSQL "public" schema. With this module, you can use the same connection to query between multiple schemas at the same time (ie. in a same query) if you use other schemas "myschema1", "some_custom_data_schema_2", etc.

It also works with mysql at the difference that you would use the same connection to query multiple mySQL databases (ie. the same server, port and credential would work for them all).

Furthermore, it is also possible to run Drupal on PostgreSQL and query a MySQL database as long as its credentials are set in the site's settings.php file.

Note: you can only cross-query schemas/databases when they use the same connection (ie. same server+port+credentials).

Current state: beta version.
Database supported:
-PostgreSQL (tested functional)
-MySQL (tested functional)

Version 1.0.x was released for Drupal 8 and 9 and is obsolete now. Version 2.0.x works with both Drupal 9.4+ and Drupal 10 and does not support per-table prefixing (even with Drupal 9.4+ while it was just deprecated but supported by Drupal).

Requirements

Once the main module is enabled, you should also enable at least one external database driver (dbxschema_mysql or dbxschema_pgsql, provided with this module).

Recommended modules

• External Entities Database storage

Roadmap

• Better document the API
• Add automated tests

Credits

The Alliance Bioversity - CIAT.

Restrictions

• No more support for per-table prefixing used by Drupal 9 and older. The support was deprecated in Drupal 9 and new database driver module were introduced in 9.4 which lead to a rewrite of some parts of this module that removed the support for per-table prefix in "settings.php". If you really need per-table prefixing, send me a message to see what could be done.
• Not working with very old versions of PostgreSQL and MySQL that don't support cross-schema/database queries.