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 a link to a CodeSandbox Demo or 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.