Blog / Use several databases within your Laravel project
Use several databases within your Laravel project
4 min - 01 Sep 2023
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. Learn more on Capsules or X.
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.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.