DEV Community

Cover image for Multi-tenancy with Laravel (dynamic databases)
WAIYL KARIM
WAIYL KARIM

Posted on

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'),
            ]) : [],
        ],
Enter fullscreen mode Exit fullscreen mode

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');
Enter fullscreen mode Exit fullscreen mode

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

Latest comments (3)

Collapse
 
nguyen1998pr profile image
nguyenvd-3

Hi i am new in php and i am very interested in this solution. Can you share it in more detail ?

Collapse
 
waiylkarim profile image
WAIYL KARIM

Sure thing, what do you need to know so I can help?

Collapse
 
nguyen1998pr profile image
nguyenvd-3

I see at the end of this article you have a notice that you will create a full series of how to implement this approach, but I can't find anything related to that.