https://capsules.codes/storage/canvas/images/himICQw4vdilQhOi08bKqtpSTwFVabBiYq6NyevB.jpg
TL;DR: How to use multiple databases within your Laravel project and manage database separated records.
You can find a sample Laravel Project on our Github Repository.
In an effort to maintain clarity for each of my projects, I separate my databases based on the role they play. This blog, for instance, includes several databases: one specifically for the blog and another for analytics. This article explains how to go about it.
A new Laravel project already contains, in its .env file, information related to the database, including the default mysql connection. We’ll be working with two databases: one and two. There will also be a connection to one [ optional ].
.env
Before DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=<database-name> DB_USERNAME= DB_PASSWORD= After DB_CONNECTION=one DB_ONE_HOST=127.0.0.1 DB_ONE_PORT=3306 DB_ONE_DATABASE=one DB_ONE_USERNAME= DB_ONE_PASSWORD= DB_TWO_HOST=127.0.0.1 DB_TWO_PORT=3306 DB_TWO_DATABASE=two DB_TWO_USERNAME= DB_TWO_PASSWORD=
The default .env file informations is reflected in the database.php configuration file.
config/database.php
'connections' => [ 'mysql' => [ 'driver' => 'mysql', 'url' => env('DATABASE_URL'), 'host' => env('DB_HOST', '127.0.0.1'), 'port' => env('DB_PORT', '3306'), 'database' => env('DB_DATABASE', 'forge'), 'username' => env('DB_USERNAME', 'forge'), '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'), ]) : [], ], ... ]
We’ll duplicate this connection information as many times as there are connections.
'connections' => [ 'one' => [ 'driver' => 'mysql', 'url' => env('DATABASE_URL'), 'host' => env('DB_ONE_HOST', '127.0.0.1'), 'port' => env('DB_ONE_PORT', '3306'), 'database' => env('DB_ONE_DATABASE', 'forge'), 'username' => env('DB_ONE_USERNAME', 'forge'), 'password' => env('DB_ONE_PASSWORD', ''), 'unix_socket' => env('DB_ONE_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'), ]) : [], ], 'two' => [ 'driver' => 'mysql', 'url' => env('DATABASE_URL'), 'host' => env('DB_TWO_HOST', '127.0.0.1'), 'port' => env('DB_TWO_PORT', '3306'), 'database' => env('DB_TWO_DATABASE', 'forge'), 'username' => env('DB_TWO_USERNAME', 'forge'), 'password' => env('DB_TWO_PASSWORD', ''), 'unix_socket' => env('DB_TWO_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, it is necessary to instruct the migrations to migrate to the different databases created:
one→2023_08_31_000000_create_foos_table.phptwo→2023_08_31_000001_create_bars_table.php
The static function connection('<connection-name>') of the Schema Facade allows for this, which we add in the up() and down() functions.
2023_08_31_000000_create_foos_table.php
<?php use Illuminate\Database\Migrations\Migration; use Illuminate\Database\Schema\Blueprint; use Illuminate\Support\Facades\Schema; return new class extends Migration { public function up() : void { Schema::connection( 'one' )->create( 'foos', function( Blueprint $table ) { $table->id(); $table->timestamps(); }); } public function down() : void { Schema::connection( 'one' )->dropIfExists( 'foos' ); } };
2023_08_31_000001_create_bars_table.php
<?php use Illuminate\Database\Migrations\Migration; use Illuminate\Database\Schema\Blueprint; use Illuminate\Support\Facades\Schema; return new class extends Migration { public function up() : void { Schema::connection( 'two' )->create( 'bars', function( Blueprint $table ) { $table->id(); $table->timestamps(); }); } public function down() : void { Schema::connection( 'two' )->dropIfExists( 'bars' ); } };
Next, the models related to the migrations need to be modified to indicate their connection with the database via the $connection attribute.
App\Models\Foo.php
<?php namespace App\Models; use Illuminate\Database\Eloquent\Model; class Foo extends Model { protected $connection = 'one'; }
App\Models\Bar.php
<?php namespace App\Models; use Illuminate\Database\Eloquent\Model; class Bar extends Model { protected $connection = 'two'; }
We can now launch the migration php artisan migrate. By default, this command uses the value given by DB_CONNECTION. If it’s not defined in the .env file, then it has to be indicated in the command php artisan migrate --database=one.
In order to test the functionality, we can quickly implement an anonymous function when calling the main route.
web.php
<?php use Illuminate\Support\Facades\Route; use App\Models\Foo; use App\Models\Bar; Route::get( '/', function() { $foo = Foo::create(); $bar = Bar::create(); dd( $foo, $bar ); });
The values are then created in the respective databases and visible in the browser.
In case a database refresh is needed using the command php artisan migrate:fresh, it’s worth noting that only the default database, i.e. the one specified by DB_CONNECTION, will be refreshed. Unfortunately, Laravel does not yet support the refreshing of multiple databases at the same time.
To refresh a database that is not the default one, it is necessary to use the command php artisan db:wipe --database=<database-name>. This command can be repeated for each additional database. Once all databases have been properly wiped with db:wipe, you can then proceed without errors with php artisan migrate:fresh.
You can also develop your own command that would automate the various tasks needed to clean your database.
Glad this helped.
Laravel News Links