Use several databases within your Laravel project

TL;DR: How to use multiple databases within your Laravel project and manage database separated records.

You will find the source code via this Github Repository. Find out more on Capsules, X or Bluesky.

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 MySQL databases: one and two. There will also be a connection to one to make this database the default one.

.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= 

In case of SQLite databases stored in the database directory :

DB_CONNECTION=one DB_ONE_DATABASE=one DB_TWO_DATABASE=two 

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'), ]) : [], ], ... ] 

In case of SQLite databases stored in the database directory :

'connections' => [ 'one' => [ 'driver' => 'sqlite', 'url' => env('DATABASE_URL'),         'database' => database_path(env('DB_ONE_DATABASE', 'database').'.sqlite'), 'prefix' => '', 'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true), ], 'two' => [ 'driver' => 'sqlite', 'url' => env('DATABASE_URL'),         'database' => database_path(env('DB_TWO_DATABASE', 'database').'.sqlite'), 'prefix' => '', 'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true), ], ... ] 

Then, it is necessary to instruct the migrations to migrate to the different databases created:

• one → 2023_08_31_000000_create_foos_table.php
• two → 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.