Framework: Running seed for another connection won't work with dynamic config

[bobbybouwmann]

Hi,

I have a unusual case with seeding my database. I'm aware that I can run the seed using the following command (within a custom command class)

$this->call('db:seed', [
    '--class' => 'TenantDatabaseSeeder',
    '--database' => 'tenant',
]);

However I need to set the config for the database dynamically since each tenant user has its' own database. So I can simply set the database like so

$config = Config::get('database.connections.tenant');

$config['database'] = $tenant->database_name;
$config['username'] = $tenant->database_username;
$config['password'] = $tenant->database_password;

Config::set('database.connections.tenant', $config);
Config::set('database.default', 'tenant');

This works perfectly fine for setting the correct database connection for the application. It goes wrong at the point that the db:seed command is fired. It seems like it's setting up a new application instance and therefor it resets the database config as well.

So this is my code for the current command I'm running.

$this->setDatabaseConfig($tenant);

$this->call('db:seed', [
    '--class' => 'TenantDatabaseSeeder',
    '--database' => 'tenant',
]);

Any help would be appreciated. Thanks in advance.

[bobbybouwmann]

One more note: setting the database config and then running migrate does work! So it seems that this is a db:seed bug

[GrahamCampbell]

Not a bug. I think you've got the idea of the config incorrect. We read it once at boot, and that's it. Any modifications you make will be ignored.

[philbates35]

In order to be able to make Laravel application multi-tenant with one database per tenant, you do have set the database config and the purge and reconnect at run time. I'm not aware of any other way to do this currently.

[GrahamCampbell]

You shouldn't be doing that. You need to instead setup multiple database connections.

[philbates35]

Yep that's fine for a small number of tenants. However our app has close to 300 tenants, each with their own database. I don't want to add 300 different database connections to the database config file. Additional tenants are added all the time, and having to add the make commits to add the additional database connections would be annoying.

[philbates35]

As you say we should not be setting the config at run time (which I completely agree with in all scenarios exception this specific case), do you have any suggestions how we make a Laravel app multi-tenant with a database per tenant?

[GrahamCampbell]

It's definitely possible in a clean way. I've done it before. This discussion belongs on the forums though. This is not a bug.

[philbates35]

I appreciate that usually this sort of thing is more suited to the forums, but I done an awful lot of research regarding this exact problem, and I've not yet come across a multi-tenant multi-database solution that doesn't involve setting the tenant database config at runtime. I'm certain if I asked on the forums the answer I get would be to dynamically set the config at run time, either myself or by using a library.

For example, these packages all dynamically set the database config in one way or another at runtime:

• hyn/multitenant
• orchestral/tenanti
• leemason/tenantable

I am, and I'm sure others would be, incredibly curious as to see what your clean solution to this was that avoided having to set config at runtime.

[bobbybouwmann]

@GrahamCampbell It would be nice if you could provide a small example on the subject :D

[GrahamCampbell]

Those example package don't overwrite an existing connection. They create a new connection. That's the key difference, and that's what I'd recommend doing. Doing it via config modification isn't very clean, and that's not how I'd do it, but it is a way I see people doing a fair bit.

[crynobone]

Those example package don't overwrite an existing connection. They create a new connection.

As the developer of orchestra/tenanti, this argument is true.

I've not yet come across a multi-tenant multi-database solution that doesn't involve setting the tenant database config at runtime.

This is also true. However, we setup unique custom database connection at runtime (not replacing a template such as database.connections.tenant). As an example, if the template is database.connections.tenant, then the actual connection for users with id=1 is database.connections.tenant_1.

[philbates35]

Let's say you have a connection called tenant.

Are you saying that at run time if I override the tenant connection config in the following way (taken from the OP):

$config = Config::get('database.connections.tenant');

$config['database'] = $tenant->database_name;
$config['username'] = $tenant->database_username;
$config['password'] = $tenant->database_password;

Config::set('database.connections.tenant', $config);
Config::set('database.default', 'tenant');

Then that is bad and will cause problems, as identified in this issue.

However, if at run time I created a brand new new connection like this:

$config['database'] = $tenant->database_name;
$config['username'] = $tenant->database_username;
$config['password'] = $tenant->database_password;

Config::set('database.connections.tenant_'.$tenant->id, $config);
Config::set('database.default', 'tenant_'.$tenant->id);

That that is fine, and in fact is actually the recommended way of solving this problem?

[philbates35]

In other words, you still have to set config dynamically at run time (which is the part I thought @GrahamCampbell was saying should be avoided), but the only differences is to creating a new connection rather than setting the config for an existing connection is the right thing to do.

Is that right?

[philbates35]

Actually reading @GrahamCampbell's message again he says he would create a new connection for the tenant, but wouldn't do it via Config::set().

I guess then that you're interacting with the DB class directly then.

[bobbybouwmann]

Thanks all for thinking with me!

As I sad in my second reply, swapping the config does work for php artisan migrate but not for php artisan db:seed. So it sounds like this should work just fine, it only doesn't work for the seeder classes.

@philbates35 I know there is a function called makeConnection on the DatabaseManager class, but that one works with the config as well

[crynobone]

in fact is actually the recommended way of solving this problem?

This could only solve problem if config was added too late on the request/app booting process. So the next question is how and when do you resolve the connection during app booting, and how would you know that you need to resolve tenant with id=1 instead of only passing php artisan db:seed --database=tenant.

[bobbybouwmann]

@crynobone I set the config based on the tenant. I retrieve that from the database before I set a new connection. I have a command that is fired with an argument that holds the tenant identifier. From there I can retrieve the database stuff and assign that to the config. I then know that my tenant connection has the correct database credentials

[philbates35]

@bobbybouwmann How do you identify what tenant you are in artisan commands? And at what point do you hook in and reconnect to the database?

For me, these are the two important points that make the multi-database thing difficult. There obviously are solutions, but both feel "hacky" IMO. Clarification on exactly how we should be handling this in a clean way would be very much appreciated.

[crynobone]

How do you identify what tenant you are in artisan commands? And at what point do you hook in and reconnect to the database?

We don't actually use php artisan migrate, It's either php artisan tenanti:migrate {driver} which would loop through every tenant (it would be better to actually run it as php artisan tenanti:queue {driver} migrate where each tenant would run migration on separated queue).

Having to use our own command allows us to dynamically set the database connection as need.

There obviously are solutions, but both feel "hacky" IMO.

Using php artisan migrate and php artisan db:seed for multi-tenant could also be said as hacky, since it wasn't actually designed for that in mind, other than it does support supplying a database connection name.

[bobbybouwmann]

@philbates35 So I have a command called php artisan tenant:seed {tenant}. The tenant is the identifier I need to set the correct database configuration. This command will simply do two things. Set the correct database settings based on the given tenant and then call db:seed

public function handle()
{
    $tenant = $this->argument('tenant');

    $this->setDatabaseConfig($tenant);

    $this->call('db:seed', [
        '--class' => 'TenantDatabaseSeeder',
        '--database' => 'tenant',
    ]);
}

[bobbybouwmann]

So I found a way to solve this. I can simply call DB::reconnect('tenant'); afterI have set the config. This way the new config is applied to the new database connection.

The problem occurred when there was already a call to the database before the config was set. So you would get the same class instance, with the already applied settings.

Thanks all for thinking with me!

@GrahamCampbell It would still be nice too see an example solution from you ;)

[tomschlick]

@bobbybouwmann a db reconnect is how I do it as well

[bobbybouwmann]

@tomschlick I first did a reconnect but that was not always working with an already going connection. So I decided to do what Graham said and created a complete database config on the fly. So instead of modifying the tenant connection I just copied the tenant connection and created a new one with the name of the tenant. That kinda solved all my problems ;)

I do find your point about migrations on the internals page really frightening. There is no way of doing a batch update on the database. Would be nice if we can find a good solution for that ;)

[mairechnejib]

how i make a loop to see if the commit is already in the my database thx :)

[Qh0stM4N]

@bobbybouwmann your issue title gave hope but comments were a bad surprise. I'am generate dynamical connections on each users in appServiceProvider.php

 public function boot(Factory $cache)
    {
        $cnn= $cache->remember('db_connections', 240, function () {
            $users = [];
            try {
                $users = \App\User::all();
            } catch (\Exception $e) {
                // TODO log critical
            }
            // get old connections value.
            $connections = config("database.connections");
            $queue = config("queue.connections");

            foreach ($users as $user) {
                $connections[config("app.dbPrefix") . $user->id] = [// prefix_1
                    'database' => config("app.dbPrefix") . $user->id,
                   /*.. another configurations ..*/
                ];

                $queue[config("app.dbPrefix") . $user->id] = [ // prefix_1
                    'connection' => config("app.entegrePrefix") . $user->id,
                    /*.. another configurations ..*/
                ];
            }
            return ["connections" => $connections, "queue" => $queue];
        });

        config()->set('database.connections', $cnn["connections"]);
        config()->set('queue.connections', $cnn["queue"]);
    }

I can't code generation but AppServiceProvider at run before every console command. I think adding dynamical connections in here work all project.

php artisan migrate --database prefix_1 --path database/migrations/multiDatabases -->looks like good
php artisan queue:work prefix_1 --> perfect
php artisan db:seed --database prefix_1 --> ...

Database [prefix_0] not configured.

@GrahamCampbell think this is not a bug.
But I think the settings have been reset. Because this is only in the seeder process.
If the seed is working as it should, migrations does not work properly.

Well that is so, put seeding code directly into the migration file.
And run command
php artisan migrate --database prefix_1 --path database/migrations/seederMigration --> at least it works