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
'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
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 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
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.
Cover image credit: lavarmsg