Laravel one domain, multiple database detected by Session

Question

I have read several posts/topics (like this, this and this) about the subject SaaS, Multi-Tenant, etc. and I reached the conclusion that most of them does not fit my requirements:

I don't need a multi-tenant as i'm only going to use the main-domain
I can't write in .env nor in database.config all my MySQL connections as they will all be dynamic (at least the name of the database)

Workflow i need:

The subscriptions (SaaS) contains the database name
Whenever the user login it is assigned to his session the database name
Run all the queries to the user database

Example:

- project_admin <- Main database 
--- subscriptions <- Table
------ id | db_name
------ 1  | project_child_one
------ 2  | project_child_two
--- users <- Table
------ id | subscription_id 
------ 1  | 1 
------ 2  | 2

- project_child_one <- Child database
--- customers <- table

- project_child_two <- Child database
--- customers <- table

When the user 1 login, the data retrieved from customers should be from database project_child_one.
When the user 2 login, the data retrieved from customers should be from database project_child_two.

I want the database name to be saved in the session so I don't need to always query the project_admin in order to know in which database the user should connect to. This is the easy part.

**Don’t** store database connection settings in a session. Instead, consider using middleware to dynamically set the connection based on the authenticated user if you must have multiple databases. — Martin Bean, Apr 29 '19 at 20:58
@MartinBean i'm not gonna store the connection settings in session, only the database name. I have not figure out yet how do i use a middleware with this approach? — Linesofcode, Apr 29 '19 at 20:59

score 1 · Answer 1 · answered Apr 29 '19 at 18:42

Well I am not sure whether this is what you meant . But if you want db name to be dynamic you can easily do it in the laravel as follows . The connections part of config should be like above . However you can change the database credential at the controller in the runtime . If we take the above example for the configuration then . You can do like this on the controller .

    $config= [
            'database'=>'Dynamic db name',
    ];
    Config::set("database.connections.mysql2",$config);
    DB::purge('mysql');
    DB::setDefaultConnection('mysql2');

And suppose you want to switch another db after transaction you can do like this

     $config= [
            'database'=>'Dynamic db name',
    ];
    Config::set("database.connections.mysql",$config);
    DB::purge('mysql2');
    DB::setDefaultConnection('mysql2');

I hope it helps

More information can be found [here](https://divinglaravel.com/understanding-how-laravel-configures-database-connections). — Thomas Van der Veen, Apr 29 '19 at 18:47
I don't want to do that in the controller. I need it to be in the model or inject somehow in a middleware. — Linesofcode, Apr 29 '19 at 20:28

score 1 · Accepted Answer · answered Apr 29 '19 at 21:04

If you really need to have a “tenant” database connection, then you can configure it on the fly in a middleware class:

class ConfigureTenantConnection
{
    public function handle($request, Closure $next)
    {
        if ($user = $request->user()) {
            // Create a tenant database connection if there is an authenticated user
            config([
                'database.connections.tenant' => [
                    'driver' => 'mysql',
                    // I don’t know what column names you use, but…
                    'host' => $user->database_host,
                    'port' => $user->database_port,
                    'database' => $user->database_name,
                    'username' => $user->database_username,
                    'password' => $user->database_password,
                ],
            ]);
        }

        return $next($request);
    }
}

You can then use this tenant connection in database queries and models:

abstract class TenantModel extends Model
{
    protected $connection = 'tenant';
}

class Widget extends TenantModel
{
    protected $table = 'widgets';
}

Hi Martin, can you take a look at a related problem I'm facing? https://stackoverflow.com/questions/56791809/laravel-request-validation-with-custom-tenant-connection-on-the-fly — Linesofcode, Jun 27 '19 at 13:12
@Linesofcode You’ve already accepted an answer. Not sure what you’re wanting me to add? — Martin Bean, Jun 27 '19 at 15:42

Laravel one domain, multiple database detected by Session

2 Answers2

Linked