Configure database connections on the fly in Laravel

Question

In Laravel 8, I am trying to set dynamic connections in two scenarios:

After login, depending on the user I set a custom database connection.

In this case, after login, in a middleware I call a method:

public static function changeConnection($customerId)
{
    $database = Database::where('customer_id', '=', $customerId)->first();

    if (empty($database)) {
        return null;
    }

    $connectionName = 'customer';

    $config = Config::get('database.connections.' . $connectionName);
    $config = [
        'driver' => 'mysql',
        'host' => $database->database_host,
        'port' => $database->database_port,
        'database' => $database->database_name,
        'username' => $database->database_username,
        'password' => $database->database_password
    ];
    config()->set('database.connections.' . $connectionName, $config);
    DB::purge($connectionName);

    return $connectionName;
}

This works perfect, and the connection works perfectly.

I need to run some processes in a job. So I need to access each user database, I tried to do the same process, but I keep getting an error:

local.ERROR: SQLSTATE[HY000] [2002] No such file or directory (SQL: select ... is null) {"exception":"[object] (Illuminate\Database\QueryException(code: 2002): SQLSTATE[HY000] [2002] No such file or directory (SQL: select ... is null) at /app/vendor/laravel/framework/src/Illuminate/Database/Connection.php:671)

Any ideas?, I guess inside a job, since it runs in console, the procedure is different?

You'll need to update anywhere you're using eloquent to select the database. For example: `$users = DB::connection('mysql2')->select(...);` — Lewis Smith, Jan 26 '21 at 23:38
@LewisSmith I am using a different way: (new Customer)->setConnection($this->customerConnection)->select... — Eduardo, Jan 27 '21 at 01:17
I've not personally come across that method before, The above way is how I'd achieve the solution you require. You'll still need to set up your connections in the config, then you can just build some helper functions on your models to pick one similar to how you're currently doing it — Lewis Smith, Jan 27 '21 at 11:55
@LewisSmith In my case connections are dynamic so I can just set a connection with no user, password, database which should be updated by config()->set(...), but it is not working. — Eduardo, Jan 31 '21 at 20:23

score 1 · Answer 1 · answered May 12 '21 at 17:37

1

Laravel one domain, multiple database detected by Session

I had the same problem few years ago, this was my solution, plus if you need to switch connection on the fly:

config(
[
    'database.connections.tenant' => 
    [
        'driver'    => 'mysql',
        'host'      => 'localhost',
        'port'      => 3306,
        'database'  => 'the_database_name',
        'username'  => env('DB_USERNAME'),
        'password'  => env('DB_PASSWORD'),
        'charset'   => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
    ],
]);

$data = DB::connection('tenant')->select('Your sql');

answered May 12 '21 at 17:37

Linesofcode

5,327
13
62
116

What if you have multiple migration files and you want to set the specific db connection in every file? Then, why not using `env(''DB_CONN)` but the problem is `Expression is not allowed as field default value` ? – Pathros May 03 '22 at 19:46
@Pathros I don't use migration files, so I don't know how that works. What I did was to instead of going a DB one-by-one to add SQL, I created a feature in the backoffice that would allow me to input the SQL and run in all DB's automatically through a loop, dunno if this helps you. – Linesofcode May 03 '22 at 20:12

Configure database connections on the fly in Laravel

1 Answers1

Linked