How to connect multiple databases in Laravel?

Learn how to seamlessly connect multiple databases within your Laravel application. This tutorial provides comprehensive insights into configuring Laravel to interact with multiple databases. Whether you're utilizing Laravel version 6 or higher, this guide is applicable. For demonstration purposes, Laravel 10 will be used in this tutorial.

Let's get started.

Step 1: Install Laravel

This step is not compulsory if you are going to use your existing laravel project. But, if you may have to install a new installation, then you have to go ahead with the following command.

composer create-project laravel/laravel example-app

Step 2: Create Databases

Next, create two new databases to connect with laravel application. If you are using xampp as your local development server, then you can create your databases at localhost/phpmyadmin. I have created two new databases with the names laravel_database1 and laravel_database2.

Step 3: Configure Databases

Now, we need to configure our newly created databases in config/database.php and .env file. By default, Laravel provides several database connections, such as SQLite, mysql, pgsql and sqlsrv. In this tutorial, we will use mysql databases, so we have to add a new connection to the mysql database in config/database.php file just like the following.

'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'),
    ]) : [],
],
'mysql2' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST_2', '127.0.0.1'),
    'port' => env('DB_PORT_2', '3306'),
    'database' => env('DB_DATABASE_2', 'forge'),
    'username' => env('DB_USERNAME_2', 'forge'),
    'password' => env('DB_PASSWORD_2', ''),
    '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'),
    ]) : [],
],

Update .env file with the following code. 

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel_multiple_database1
DB_USERNAME=root
DB_PASSWORD=

DB_CONNECTION_2=mysql
DB_HOST_2=127.0.0.1
DB_PORT_2=3306
DB_DATABASE_2=laravel_multiple_database2
DB_USERNAME_2=root
DB_PASSWORD_2=

Step 4: Create Model & Migration files

In this step, We will create a model and migration files for the second database (laravel_database2) and for the first one (laravel_database1) we will use default migrations which are given by laravel from the start. Just run the following command to create the model and migration file for Post table.

php artisan make:model Post -m

Now open the newly created model file and add the following line of code to define that the Post model is connected to the second database (mysql2) or (laravel_database2).

protected $connection = 'mysql2';

Next, update the post migration file with the following code to create a post table in the second database.

public function up()
{
   Schema::connection('mysql2')->create('posts', function (Blueprint $table) {
       $table->id();
       $table->string('title');
       $table->timestamps();
   });
}

public function down()
{
   Schema::connection('mysql2')->dropIfExists('posts');
}

Now you can run the migration command to create the tables in databases.

php artisan migrate

Step 5: Create Dummy users and posts with tinker

OK, now let's create some dummy data to populate the users table in database laravel_database1 and posts table in laravel_database2. Laravel provides a default factory file to generate dummy users, so we can use this default factory to generate dummy users. Just run the following commands step by step.

php artisan tinker
User::factory()->count(10)->create()

Now, we need to generate data for the posts table. For this, first we have to create a factor file to generate dummy post data. So, run the following command to create a factory file for posts.

php artisan make:factory PostFactory --model=Post

Now open the newly created factory file for posts (database/factories/PostFactory.php) and update the following code. 

public function definition()
{
   return [
       'title' => $this->faker->text()
   ];
}

Now you can run the following tinker command to generate dummy posts for the posts table in the second database. At this point, we have created dummy users and posts in different databases, namely laravel_database1 and laravel_database2.

php artisan tinker
Post::factory()->count(10)->create()

At this point, we have added dummy data to both tables from two different databases.

Next, let's display the data from the users and post tables which, of course, also come from two different databases.

Step 6: Show data in tables

I have created a simple table to show data from the two different databases.

<h2 class="fs-5 fw-bold my-3">Users Data from First Database </h2>
<table class="table">
    <thead>
        <tr>
            <th scope="col">#</th>
            <th scope="col">Name</th>
            <th scope="col">Email</th>
        </tr>
    </thead>
    <tbody>
        @foreach ($users as $key => $item)
            <tr>
                <th scope="row">{{ ++$key }}</th>
                <td>{{ $item->name }}</td>
                <td>{{ $item->email }}</td>
            </tr>
        @endforeach
    </tbody>
</table>

<h2 class="fs-5 fw-bold my-3">Posts Data from Second Database </h2>
<table class="table">
    <thead>
        <tr>
            <th scope="col">#</th>
            <th scope="col">Title</th>
        </tr>
    </thead>
    <tbody>
        @foreach ($posts as $key => $item)
            <tr>
                <th scope="row">{{ ++$key }}</th>
                <td>{{ $item->title }}</td>
            </tr>
        @endforeach
    </tbody>
</table>

At the end of this step, we have displayed data from two different databases in two tables separately. What if we want to add a relationship between users and post table? Let's check how to add relationships between two tables from two different databases.

Add Relationship

We have to add a new column to the posts table (second database) namely the user_id that will be associated with the id from the user table (first database). Run the following command to create a file after we edit it to add a new field in the post table.

php artisan make:migration add_user_id_to_posts_table

Let's open and edit the newly created file in the database/migrations/ folder, namely xxxx_xx_xx_xxxxxx_add_user_id_to_posts_table.php. Just update the file with the following code. 

public function up()
{
   Schema::connection('mysql2')->table('posts', function (Blueprint $table) {
      $table->foreignId('user_id')->default(1);
   });
}
public function down()
{
   Schema::table('posts', function (Blueprint $table) {
       $table->dropColumn('user_id');
   });
}

Now open the Posts.php model file and add the following line of code. It will create a relationship or connect a post to a user's table with the mysql setconnection. setConnection('mysql') means, we connect posts table  from database laravel_database2 to a user's table with a database connection to database laravel_database1.

public function user()
{
   return $this->setConnection('mysql')->belongsTo(User::class);
}

And now we can display user name based on the user_id in the post table by updating a little bit in the post table view in our view file. Add User Name in table head and add the {{$item->user->name}} in table body.

<tr>
    <th scope="col">#</th>
    <th scope="col">Title</th>
    <th>User Name</th>
</tr>
...
@foreach ($posts as $key => $item)
    <tr>
        <th scope="row">{{ ++$key }}</th>
        <td>{{ $item->title }}</td>
        <td>{{ $item->user->name }}</td>
    </tr>
@endforeach

Hope this tutorial will be helpful.
Happy Coding :)