Blog / Use several databases within your Laravel project

Image used for article Use several databases within your Laravel project

Use several databases within your Laravel project




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




A sample Laravel project can be found on this 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.




Find out more on Capsules or X.







v1.0.5

X IconGithub Icon