Multi-tenancy with Laravel (dynamic databases)

Like many developers before me when it comes to a web project, PHP with Laravel is my go to technology for it is not only making my life as a developer a lot easier but fun as well.

However when my team and I have been working on a project for one of our clients that requires dynamic database creation we found ourselves between doing it the Laravel way or doing our way.

I will take the chance in this flash article to explain the problem with doing it the traditional Laravel way and doing it your own way and I will be expecting the experts amongst you to share in the comments section other ways they would use to tackle this challenge.

Say we have a web-based app whose master will be required to create a new instance of the app for every client. All of these instances are being hosted on the same server which means every instance should have its own database hence multi-tenant.

If you wish to do it the Laravel way you need to manually add something like this for every database in config/database.php:

        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => 'database1',
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

Then you need to store the name of the database somewhere on your main database to call every time this client requests its instance via ModelName::connection($databaseName).

This process will result in having one big giant config/database.php if you have for example more than 100 clients. And it is kind of tedious to repeat every time you have a new client.

You could make a script that automatically adds the client's database required config in config/database.php but this won't make your file less big.

The solution we came out with is simple and straightforward. All you need to do is create a table on your main database where you will define every client and their database name. Create a script either using PHP and a web interface or use a custom Artisan command.

The custom Artisan command will store the client in the main database (which is the only database defined on config/database.php), create the client database and finally migrate all migrations. All of that using only one simple artisan command line. Something like php artisan client:make Client1. The name of the database will be Client1 in this case.

Then instead of calling ModelName::connection($clientDatabaseName), you create a Trait where you will define the following:

$clientDatabaseName = MainDatabase::where('client_name', $request->client_name)->value('database_name');

config(['database.connections.mysql.database' => $clientDatabaseName]);

\DB::purge('mysql');

\DB::reconnect('mysql');

To use this Trait either you call it on your models or you use it inside AppServiceProvider.

Using this simple approach you won't need to store everything inside config/database.php and you won't have to use connection($db) every time you need to instantiate a Model. Only with a simple artisan command line, you take care of creating the database and its migrations and seeders if you have any.

I hope this flash article helps, I would like to know in the comments how would you solve this.

If you'd like me to create a full series of how to implement this approach, leave that in the comments section.

Happy coding!

Cover image credit: lavarmsg