Use several databases within your Laravel project
Tags: laravel, sql, php, tutorial
Published: 2023-09-04 10:46:49

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

&nbsp;
&nbsp;

You can find a Laravel Project example on our [Github Repository](https://github.com/capsulescodes/articles).

&nbsp;
&nbsp;

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.

&nbsp;
&nbsp;

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

&nbsp;

`.env`

```php
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=
```

&nbsp;
&nbsp;

The default `.env` file informations is reflected in the `database.php` configuration file.

&nbsp;

`config/database.php`

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

&nbsp;
&nbsp;

We'll duplicate this connection information as many times as there are connections.

&nbsp;
&nbsp;

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

&nbsp;
&nbsp;

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.

&nbsp;
&nbsp;

`2023_08_31_000000_create_foos_table.php`

```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' );
    }
};
```

&nbsp;
&nbsp;


`2023_08_31_000001_create_bars_table.php`

```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' );
    }
};
```

&nbsp;
&nbsp;

Next, the models related to the migrations need to be modified to indicate their connection with the database via the `$connection` attribute.

&nbsp;

`App\Models\Foo.php`

```php
 <?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Foo extends Model
{
	 protected $connection = 'one';
}
```

&nbsp;
&nbsp;

`App\Models\Bar.php`

 

```php
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Bar extends Model
{
	 protected $connection = 'two';
}
```

&nbsp;
&nbsp;

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

&nbsp;
&nbsp;

In order to test the functionality, we can quickly implement an anonymous function when calling the main route.

&nbsp;

`web.php`

```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 );
});
```

&nbsp;
&nbsp;

The values are then created in the respective databases and visible in the browser.

&nbsp;
&nbsp;

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.

&nbsp;
&nbsp;

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

&nbsp;
&nbsp;

You can also develop your own command that would automate the various tasks needed to clean your database.

&nbsp;
&nbsp;

Glad this helped.

&nbsp;
&nbsp;

Find out more on [Capsules](https://capsules.codes) or [X](https://x.com/capsulescodes)