Laravel dynamic database connections

Question

I going to use dynamic database connections in backend.

        Config::set('database.connections._pgsql.host', $connection->host);
        Config::set('database.connections._pgsql.port', $connection->port);
        Config::set('database.connections._pgsql.username', $connection->account_name);
        Config::set('database.connections._pgsql.password', $connection->password);
        Config::set('database.connections._pgsql.database', $connection->initial_db);

        DB::purge('_pgsql');
        DB::reconnect('_pgsql');

As you see, I have to change db config every request to use dynamic db connections. If there are concurrent requests with different db connections then how can I handle these requests and connections?

Please help me.

Config::set is a per-request action so it should still work with concurrent requests. — apokryfos
– apokryfos, Commented Feb 6, 2018 at 19:12
This is directly answered in the official Laravel documentation: laravel.com/docs/5.5/… — 0kay
– 0kay, Commented Feb 6, 2018 at 19:12
Wow. really? Config::set only works per request? Where can I find documentation for this? Thanks. — John Doe
– John Doe, Commented Feb 6, 2018 at 19:14

score 3 · Accepted Answer · 2018-02-07 04:36:11Z

3

The documentation is good for a lot of situations, however, I find it to be more convenient in a lot of situations to set the connection in middleware. This is more tailored for multi-tenancy type apps but it may be useful for you:

public function handle($request, Closure $next)
{
    // if a user belongs to an organization, company, team, etc.
    // and each having their own database with a DSN stored in the database.

    if (auth()->check() && auth()->user()->company->active) {
        $uuid = $request->user()->company->db_uuid;
        $host = $request->user()->company->db_host;

        // here you can merge additional connection info in, such
        // as a master password from .env, for example
        $password = config('database.customer.master');

        // then set the default connection or assign a new one
        config(['database.connections.customer' => [
            'host' => $host,
            'password' => $password,
            'database' => $uuid,
            'username' => 'customer_master'
        ]]);

        return $next($request);
    }

    abort(403, 'Unauthorized');
}

Every query from there on in the request lifecycle will use that connection, without the need to set the connection each time.

Most of the models were specific to customer needs so we set the database connection in a base model and inherited where needed.

class BaseModel extends Model
{
    public function __construct($attributes = []) {
          parent::__construct($attributes);

          $this->connection = config('database.connections.customer');
     }
}

edited Feb 7, 2018 at 4:36

answered Feb 6, 2018 at 19:49

user320487

Sign up to request clarification or add additional context in comments.

9 Comments

John Doe Over a year ago

Thanks for your reply. But not sure how to use dsn and would you guide me how to do it? Thanks.

user320487 Over a year ago

DSN is just an acronym for datasource name. I probably should have used like organization id instead. The point being you might do a query to look up the database connection info. It could be as simple as the database name you want to connect to.

John Doe Over a year ago

yeah. we can use organization id instead of dsn. To use database connection, it must be registered in confg/database.php I guess. Is it possible to use connection of organization id without registering it?

user320487 Over a year ago

Yeah that's what I'm getting at. You'd store the connection info in a "master" database, so to speak, and when a user authenticates, it reads the necessary connection info and then the middleware sets the connection on every subsequent request from there on out. If you do go this route, make sure you're not storing sensitive data as plain text. I built a crm system that stored a UUID on the organizations table and that was what we used to identify data base connections. Worked really well with 1000+ users/databases.

John Doe Over a year ago

Amazing!!! But I'm worrying about the confliction from concurrent requests. Would you show me part of sample code? Thanks.

|

Lance Pioch · Accepted Answer · 2018-02-06 20:42:44Z

0

The configuration for your application is static unless changed via the files itself or .env.

If you wish to separate the database per user, you can store the connection name in the session instead.

An example:

$users = DB::connection(session('databaseName'))->select(...);

In your login action, you should set the session: session(['databaseName' => 'secondDatabase']);

Make sure that in your database.php that you have a connection called 'secondDatabase'.

answered Feb 6, 2018 at 20:42

Lance Pioch

1,1676 silver badges19 bronze badges

3 Comments

John Doe Over a year ago

How about in case of multiple databases not only secondDatabase?

Lance Pioch Over a year ago

You can provide as many database connections as you want in the config's array. If it's not a static number, then you'll want to create a custom system to handle this or figure out a different solution.

John Doe Over a year ago

Well.. I don't think so. There must be solution for it I guess.

Collectives™ on Stack Overflow

Laravel dynamic database connections

2 Answers 2

9 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related