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
Top comments (4)
Hi i am new in php and i am very interested in this solution. Can you share it in more detail ?
Sure thing, what do you need to know so I can help?
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.
full series of how to implement this approach