Multi-tenancy with Laravel (dynamic databases)
Tags: laravel, database, performance
Published: 2021-07-18 18:19:19

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](https://www.vecteezy.com/members/lavarmsg)