Laravel: multi-DB not respecting the HOST attribute

Question

I'm reposting this question because the previous one was closed as being answered by a related post here but it wasn't.

That post is saying how to access the servers using raw commands. My question has to do with how LARAVEL handles it - because I use Eloquent and Laravel relationships to handle all the pivots, etc.

---

So I have a multi-DB setup which I thought was working (well it does, but the caveat is the title).

I'm currently using two databases, one for the actual app we're developing, and another DB specifically for user accounts.

Here's the snippet on the ENV side:

DB_CONNECTION=mysql
DB_HOST=APP_SERVER_IP
DB_PORT=APP_SERVER_PORT
DB_DATABASE=APP_SERVER_DB
DB_USERNAME=APP_SERVER_USER
DB_PASSWORD=APP_SERVER_PASS

DB_CONNECTION_SH=mysql
DB_HOST_SH=ACCOUNTS_SERVER_IP
DB_PORT_SH=ACCOUNTS_SERVER_PORT
DB_DATABASE_SH=ACCOUNTS_SERVER_DB
DB_USERNAME_SH=ACCOUNTS_SERVER_USER
DB_PASSWORD_SH=ACCOUNTS_SERVER_PASS

Then on the database.php I have:

'mysql'  => [
    'driver'         => 'mysql',
    'host'           => env('DB_HOST', '127.0.0.1'),
    'port'           => env('DB_PORT', '3306'),
    'database'       => env('DB_DATABASE', 'forge'),
    'username'       => env('DB_USERNAME', 'forge'),
    'password'       => env('DB_PASSWORD', ''),
    ...
],
'sh'     => [
    'driver'   => env('DB_CONNECTION_SH'),
    'host'     => env('DB_HOST_SH'),
    'port'     => env('DB_PORT_SH'),
    'database' => env('DB_DATABASE_SH'),
    'username' => env('DB_USERNAME_SH'),
    'password' => env('DB_PASSWORD_SH'),
    ...
],
...

Then I use this method of setting the connection dynamically on the model.

public function __construct(array $attributes = array()) { 
    parent::__construct($attributes);
    $this->setConnection($this->switchableDB());
}

switchableDB is just a method on a trait; and it just returns a string of either 'mysql' or 'sh'. This is set ONCE in the env (and taken by the config) btw - so it's not as complicated as you may think.

public function switchableDB() {
    // simply return the connection based on what's set on the config/env
    return config('sso.enabled') ? 'sh' : 'mysql';
}

Which WORKS as far as selecting the DB goes.

What I WANT to do is

1. Have the app server instance on one AWS instance
2. Have its database on a remote DB instance
3. Have the accounts server on ANOTHER remote DB (so that other apps can connect to it like this app)

But I observed that multi-DB access only seems to work if the databases are on the same server/IP.

You CAN have the app server on a different IP from the DBs no problem, but when I tried moving the two databases to two different IPs, suddenly things broke with a SQLSTATE[42000]: Syntax error or access violation: 1049 Unknown database 'DB_NAME') error.

But the database certainly exists, just not in the same IP. This suggests the DBs at least have to be located in one server. Reinforced when I cloned the "missing db" to the same server and set the host for both DBs as the same.

Which leads me to believe that while it CAN "look for" set the connection (via switchableDB), for some reason the connection's HOST (and possibly PORT) settings aren't recognized.

I must be doing something wrong because why even have the option to define the host on multiple DBs if it's just going to assume they're on the same server?

Hopefully someone can shed light on what I can do to basically use TWO different DBs on TWO different server IPs.

---

Update: clarification as Requested by user Dont Panic

So the "accounts" (sh) is a kind of "global" database. And there's a setting in the .env that can tell the webapp whether it should be using it or not.

The reason for this env "setting/switch" is if you wanted to (re)deploy the app in a self-contained environment where it just uses its local DBs for everything (i.e. an airgapped intranet) or whether it uses the "global/cloud" db.

So there are some tables common (users is one of them) on BOTH databases, but it never uses them at the same time - it's always one or the other (determined by what was set on the env)

So for a use case, I use it as a toggle whether or not to enable single-sign-on functionality. If SSO is enabled, it will use the users table of accounts (sh) db. But if it's disabled, it just uses the users table of the "local" db (mysql)

The latter will allow the user to just have a self-contained deployment where they don't even need the internet - they could just setup a local webserver on their machine and logins will work.

The former on the other hand, will assume you're online and should use the accounts db for user login.

Now I know this may not be the best implementation of SSO functionality, but that really isn't my question/concern. My concern is simply given the fact that switching connections is already working (as it DOES try to access the DBs as intended judging from the errors)... it's just that for some reason it assumes - regardless of what you set as the host of the nominated connection - that the they're all on the same IP (or localhost). Which begs the question: why even have a "host" setting for the other connection if it's not going to respect it?

Answer 1

I understand what you're looking for. Unfortunately, you cannot set a dynamic $connection property on a model without having to write the functionality yourself (like you did on your __construct method. However, you can do it when you query the model.

App\Models\User::on('sh')->get();    // Query User model using sh connection
App\Models\User::on('mysql')->get(); // Query User model using mysql connection

This is equivalent to

(new App\Models\User)->setConnection('sh')->get();
(new App\Models\User)->setConnection('mysql')->get();