0

What is the best way to connect to a remote database through drupal? Would the correct method be to use the settings.php with the database in there, and use the built in db_select, db_query() etc functions to access the remote database?

I have a system that has a bunch of entities, which are different 'servers'. Each server may or may not have a different database for up to 4 different services. Currently, I am storing the database connection details inside of the entity fields, and calling them using entity_load() as needed. In doing this, I am having to write my own SQL library and queries to retrieve data from the external databases.

I am wondering; if instead of writing the values to the entity fields, if I wrote them to maybe an external .php file (maybe dbconfig.php) or something, if there was a way I could still use the built in database functions and methods to access it, or if Drupal only reads from the settings.php for database connections.

I also question if using it on a table that doesn't use the same structure as drupal would even work. (It's a completely different service with it's own tables and structures).

What's the best way to handle this type of situation? I really would like to get away from writing my own library to use for remote connections, as being able to do pagination and all that using the built in methods is extremely helpful and already been tested and hardened.

Just trying to find the best way to get access to the drupal database methods/functions and still be able to update/edit/add new databases when they are needed without risking security, and without having to manually change permissions on the settings.php before making new changes. (The servers currently are automatically added as new entities by scanning a single remote db for new entries then using entity_create() to add them).

Is it possible for me to put an include('databases.settings.php'); inside of settings.php, and use $databases['database_name']['default'] = $info_array; inside of that file (and keep it writable) to store the new database values?

Improve this question

1 Answer 1

Reset to default
0

You don't have to re-invent the database abstraction layer to access external databases. You can switch the database connection dynamically.

Using Database::addConnectionInfo() you can add your database connection information dynamically. Then use Database::setActiveConnection() and Database::getConnection() with your respective target database identifiers for doing your queries with.

Entity (field) queries won't work obviously, if the external database doesn't have entity tables. But static and dynamic queries will. The latter allow for including the core pagination, if desired.

After you queried your external database, use Database::setActiveConnection() without parameters to reset the connection to the defaults. This is important for Drupal to being able to access its own tables after your queries.

Check the Database API documentation and the database class for more in-depth information. (This will also work in Drupal 7 and its database class.)

Improve this answer
5
  • Yeah with doing this though, it doesn't allow me to do asynchronous database calls and I won't be able to access the Drupal database and the external one at the same time. It would be nice to not have to switch databases. I can use Database::getconnection and pass the external databases array to it without setting it as active. Also, saving the information somewhere would be helpful instead of having to grab it from the entity fields every time. If I could just pass the array key to a function that does the query it would be helpful.
    – Ex0r
    Commented Mar 22, 2019 at 23:42
  • You can switch the active connection multiple times during the same request, e.g. to your external DB just before you query it and back again to query the Drupal DB. So I don't actually get the problem here?
    – Mario Steinitz
    Commented Mar 23, 2019 at 0:06
  • Oh, yeah that makes sense. I was thinking once I set the active database away from the Drupal one, that the entire Drupal core would be using that one, so for if example it was in the middle of a query and somebody did something on the website (for example updated a node), it wouldn't be able to update the record since it wasn't pointing to the default Drupal database. I guess the setActiveConnection is only for the call/method instance it's being triggered on. Does the addConnectionInfo method persist or do you need to call it every time you make an external connection?
    – Ex0r
    Commented Mar 23, 2019 at 0:29
  • Call it just before your external query (building/execution) to set the connection to the external DB and just after your query execution to set it back to the Drupal DB. It is persistent during your request. So you should set it back immediately after your query for Drupal to work when evaluating your data, caching results, processing templates and so on.
    – Mario Steinitz
    Commented Mar 23, 2019 at 0:41
  • Okay, got it. Was thinking it was persistent across the board not just in the single request, meaning it would break the entire Drupal site if it wasn't set to default when another request elsewhere was made.
    – Ex0r
    Commented Mar 23, 2019 at 0:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.