Use several databases within your Laravel project

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:

  • one2023_08_31_000000_create_foos_table.php
  • two2023_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