Support for secondary non-MySQL databases

AlexHoebart-ICPDR commented 2 years ago

Description of the bug

I understood that Backdrop core only works on MySQL, but the documentation suggests that it is still possible to "connect to non-MySQL databases for specialized applications, such as using PostgreSQL for spatial searches or querying a corporate MS SQL server" (see https://docs.backdropcms.org/documentation/database-configuration#other-databases). But it does not work.

Steps To Reproduce

In settings.php: define your external database as in the documentation: e.g.
```
$databases['other_server']['default'] = array(
'driver' => 'pgsql',
'database' => 'database_name',
'username' => 'user',
'password' => 'pass',
'host' => 'localhost',
);
```
Note that you also have to define the Backdrop database in the same way (as array in $databases, not in $database without 's');
Ignore the db_query example given in above referenced documentation as that only specifies a target which is a secondary/slave database as mentioned in the previous configuration example. The following statement defines the target 'default' and key 'other_server' to query the external database (as in Drupal 7):

$result = Database::getConnection('default', 'other_server')->query("SELECT * FROM custom_table");

This works as expected for a mysql database, but not for pgsql (and obviously any other) databases. Backdrop is looking for a pgsql database driver file it does not have and dies on a white screen. The following warning can be found in watchdog:

Warning: require_once(...backdrop/core/includes/database/pgsql/database.inc): failed to open stream: No such file or directory in require_once() (line 1796 of ...backdrop/core/includes/database/database.inc).

Actual behavior

Backdrop does not include any other database driver than mysql.

Expected behavior

As in the documentation, Backdrop should at least support to connect and query other external database such as PostgreSQL.

Additional information

I experimented by adding the includes/database/pgsql folder from Drupal 7 into /core/includes/database of Backdrop as I saw that the mysql driver files haven't changed so much from Drupal. After two small mock-changes (adding function createDatabase and removing line 69 in pgsql/database.inc), the basic db_query example worked.

Therefore, I assume that it would be not impossible to adapt the non-mysql Drupal 7 database drivers and add them into Backdrop. I'm not an expert for this, but I would be willing to help for PostgreSQL, at least with testing, as I am interested in upgrading Drupal 7 sites to Backdrop, but querying PostgreSQL is a must-have for this.

laryn commented 2 years ago

This isn't a complete answer, but just a quick note to say that you may wish to also investigate https://github.com/silkscreencms/silkscreen

klonos commented 2 years ago

Yeah, AFAIK the database abstraction layer was removed from Backdrop, and we only support MySQL/MariaDB. If you need support for other databases, then Silkscreen is the way to go.

If there's any place in our documentation that says that other databases are supported, then we should fix (remove) that.

klonos commented 2 years ago

@AlexHoebart-ICPDR https://docs.backdropcms.org/documentation/database-configuration#other-databases means to say that you can connect to additional non-MySQL databases for other purposes - NOT as the main database for a Backdrop site to be installed on. Sorry if that was not clear.

AlexHoebart-ICPDR commented 2 years ago

@klonos it seems you did not read my post completely: I did not say I want to achieve non mysql database as main Backdrop database.

So, is the documentation wrong or not?

Related to Silkscreen: why is this done only in a fork of the fork? why not directly in Backdrop? Anyway, I have no problem with installing Backdrop in MySQL or MariaDB - it can easily live together with PostgreSQL.

I can also fully understand why Backdrop dropped non mysql support from core, because I also know from experience with Drupal that not many contrib developers are willing and able to support different database types. But complete disconnection from non mysql databases would be a big loss in usage options for Backdrop, I think. I would assume, that it's not too hard to allow Backdrop to connect to additional other database and issuing static SQL statements.

klonos commented 2 years ago

Related to Silkscreen: why is this done only in a fork of the fork? why not directly in Backdrop?

This was a decision that was made early on. I think that the main reasons were:

not 80% use cases
simplification of code
performance

I'll try to find more information about it and post here.

@klonos it seems you did not read my post completely...

Sorry, you are right. I think that our documentation needs some fixing. Not sure re re-introducing or fixing support for non-MySQL databases though - I'll leave that up to our @backdrop/core-committers to reply.

ghost commented 2 years ago

See:

AlexHoebart-ICPDR commented 2 years ago

I just want to emphasize that I do not question the dropping of support of installation of Backdrop in non MySQL databases. However, I think it is not very wise to completely cut the possibility to connect to other external databases in contrib or custom modules. I looked into Silkscreen and after some digging found their pgsql driver (well hidden), dropped it into Backdrop and voilá: I could connect and do the basic example query as stated in the initial post. So the functionality is available, but not in Backdrop as it should be... (and as documented) I made a test port of views_database_connector and it works like a charm after struggling with some Views compatibility issue (#5512).

herbdool commented 2 years ago

Looks like we need to either 1) update the documentation with an example that actually works, 2) update the documentation to say it isn't supported, or 3) include a driver to make it work.

I'd be interested in 1) if there's any other standard way to query a Postgres database. And would that mean using PDO directly and setting up a connection in a different way?

stpaultim commented 2 years ago

I flagged this as a possible topic for discussion at next DEV meeting. https://forum.backdropcms.org/forum/feb-17th-weekly-meeting-agenda-items#comment-3814

quicksketch commented 2 years ago

Thanks @AlexHoebart-ICPDR for the report and summary.

Looking at Database::openConnection() in database.inc, I can't fathom how the documentation would have been correct. It's possible that the original documentation was written when we decided to support only MySQL in policy but before we removed the PGSQL and SQLite implementations.

I don't think there's any way in core currently to make a "generic" database connection as the documentation describes. Even using Pressflow I don't think you'd be able to connect to MSSQL or Oracle, at least not using a settings.php definition and then through db_query().

For the time being, it seems the immediate thing we should do is update the documentation. Since Backdrop does not apparently have any support for generic database connections as described. And then we should either make that a new feature request or possibly request it as a feature for Pressflow.

klonos commented 2 years ago

...and by "Pressflow", I'm sure that @quicksketch meant "Silkscreen". I actually think that we should consider updating our official documentation and link to Silkscreen if non-MySQL connections is what people are after. Silkscreen is a "friendly" in-place replacement/fork, so we should be fine 😉

herbdool commented 2 years ago

@klonos I agree. That will resolve this issue. The bug is actually the documentation. @AlexHoebart-ICPDR I know this is not your preferred resolution but if you'd like Backdrop to support other DBs instead of using Silkscreen I recommend opening a new feature request issue.

AlexHoebart-ICPDR commented 2 years ago

Related to the documentation, if I may suggest, you could replace the current section "Non MySQL Databases" with two sections:

1. External MySQL Databases This still works in Backdrop as in Drupal 7, nicely described e.g. here: https://www.webomelette.com/how-connect-external-databases-your-drupal-7-site

Please consider that being able to connect to external databases opens a lot of use cases for integration or migration of content/data from other sources, this might be relevant for any "website". These connections can be used in custom modules, but there are also two modules which integrate external tables or views into Views: https://www.drupal.org/project/views_database_connector https://www.drupal.org/project/view_custom_table This is a very powerful and easy way to integrate data. I'm considering to port one of those to Backdrop.

2. External Non MySQL Databases Here you probably want to only refer to Silkscreen. I would not like this approach, but given that nobody noticed that the documentation was wrong about this so far, makes it clear to me that I'm in a small minority. Please consider linking also directly to the database drivers at https://github.com/silkscreencms-contrib, as this is not documented on their web site and I missed their availability when I reviewed it first.

Just some general notes: MySQL is fine for websites. But for anyone seriously working with databases (and not having resources for Oracle), PostgreSQL is the standard. And I also see SQLite useful on the other side of the spectrum. @quicksketch it is possible in Drupal to connect to even more databases, see https://www.drupal.org/project/sqlsrv or https://www.drupal.org/project/oracle. I guess with the specific knowledge, it would not be hard to convert those to Silkscreen/Backdrop, but probably there is not much interest from this market end. MySQL-only for core and contrib is acceptable, because Migration from Drupal 7 installed on non MySQL to Backdrop on MySQL is still manageable. But migration of any external non-MySQL database to MySQL is not possible because of lack of features and/or other external systems depending on it. Connecting to different database backends in one request is a very powerful feature. I dropped in the SilkScreen pgsql driver into Backdrop and it just works and this keeps me in for the moment (although I have not invested more time in further testing). The connectivity to external data sources defines the flexibility of use of a CMS. There are still >500.000 potential migrations from Drupal 7, please consider them and don't shut all doors which were available in Drupal 7.

indigoxela commented 2 years ago

MySQL is fine for websites. But for anyone seriously working with databases ... PostgreSQL is the standard.

I agree with that.

Re Silkscreen and https://github.com/silkscreencms-contrib/database_pgsql

Wow, I wasn't actually aware of that, either. I wonder what @jlfranklin thinks about providing it (also) in backdrop-contrib? PostgreSQL may not be an 80% use-case in Backdrop (core), but having it available in contrib might be a big win.

indigoxela commented 2 years ago

Re additional drivers via contrib projects: There's one problem: it's currently not possible because of the hard-coded driver inclusion path. (Only that path is considered, but adding things there means to "hack core".)

If there were a way to allow additional driver paths for inclusion outside the "core" directory - still inside the Backdrop root dir, we could easily get (back) additional database drivers. Note that this does not automatically mean that Backdrop core would work with it. But additional databases would.

Not sure, how much interest is there in the Backdrop community or within the core committer team to enable contribs to provide additional drivers without hacking core. Again: it's currently not possible but might be made possible with small core changes.

laryn commented 2 years ago

If it's a small core change that doesn't increase core maintenance, with no effect on the standard use of Backdrop, and we have folks interested in this added functionality for contrib, I'd be for it.

ghost commented 2 years ago

I don't see this as a bug.

indigoxela commented 2 years ago

I don't see this as a bug.

@BWPanda it's a documentation bug. The example given is simply wrong and won't work, which causes frustration. We should fix documentation. OR provide the ability to actually use other db types without hacking core. The latter can be seen as feature request (with some BC impact). In that case we also need to update documentation to provide a working example.

ghost commented 2 years ago

We should fix documentation.

Sure. That's why I left the type - documentation tag and removed the type - bug report one.

jlfranklin commented 2 years ago

@AlexHoebart-ICPDR says:

I looked into Silkscreen and after some digging found their pgsql driver (well hidden), dropped it into Backdrop and voilá: I could connect and do the basic example query as stated in the initial post. So the functionality is available, but not in Backdrop as it should be... (and as documented)

That's a fair criticism. The Silkscreen documentation can and should be updated to make it easier to deploy a site that talks to PostgreSQL, either as the primary or as a secondary database.

stpaultim commented 2 years ago

@BWPanda it's a documentation bug.

I tend to agree that a if the documentation is actually telling people something that is wrong, that is a bug in our system. Simply marking something as documentation does not convey any sense of urgency. If we have information that is incorrect and frustrating users, I think it should be tagged in a way that conveys that urgency.

AlexHoebart-ICPDR commented 2 years ago

Thanks for all your considerations. Just one more note on this:

There's one problem: it's currently not possible because of the hard-coded driver inclusion path. (Only that path is considered, but adding things there means to "hack core".)

The driver path in Backdrop code still includes the driver name, so e.g. pgsql driver needs to be in core/includes/database/pgsql - that's still the same logic as in Drupal7, it still works in Backdrop. Installation of a driver might not be possible with the installer - it's not a module (although there might be an accompanying module for e.g. settings). But manually downloading a driver, extracting and putting it in the right place is not a big hurdle (for those who need it and if it is documented).

I don't know about further limitations, but maybe @jlfranklin knows this better (and sorry for the criticism on Silkscreen, I highly appreciate this effort - my concern is only that hidden things don't get any attention and therefore get lost once upon a time, e.g. when Backdrop core would be changed to NOT include the driver in the path)

backdrop / backdrop-issues