How to Use Multiple Database Connections in Laravel 8

Admin・ 6 Oktober 2021

13 min read ・ 49843 views

Laravel Multiple Database Connections - In some cases or conditions, we may need to use multiple databases in one laravel project. Well, in this article I will share about how to use multiple databases in one Laravel project or commonly called multiple database connections. For this experiment later, we will implement in the latest version of laravel (currently version 8).

Okay. The steps that we will do in this experiment, among others; install laravel, create two databases, database configuration, create dummy data, setup view to display data from the two databases we created, and others.

Step 1: Install Laravel

//via Laravel Installer
composer global require laravel/installer
laravel new laravel-multiple-database

//via Composer
composer create-project laravel/laravel laravel-multiple-database

In this first step, we need to install the latest version of laravel (currently version 8) which we will try to implement or use multiple databases in one laravel project (Multiple Database Connections). To install laravel, you can use the laravel installer or use composer like the example above.

Please choose one method you want to use for laravel installation. From the two examples of laravel installation commands above, they will both generate or generate a laravel project with the name laravel-multiple-database.

Wait until the installation process is complete and when it's finished, don't forget to go to the project directory using the command cd laravel-multiple-database.

Step 2: Create Database

Next, create two new databases to store sample data that we will use in this experiment. If you are using xampp as local development, please create a new database at localhost/phpmyadmin. Here I give an example, I created two new databases with the names laravel_multiple_database1 and laravel_multiple_database2. Then we will configure the config/app.php and .env files.

Step 3: Configuration

Then we need to add the database configuration in the config/database.php and .env files.

Step 3.1: config/database.php

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

By default, Laravel already provides several database connections such as sqlite, mysql, pgsql and sqlsrv. Because in this article we will try to create multiple database connections in laravel using mysql, so we need to add a mysql connection like the code above. For example, here I add a database connection with the name mysql2.

Step 3.2: Setup .ENV

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=

Then also add it in the .env file as in the example above.

Step 4: Create Model & Migration

Next, we need to create a new model and migration which will be used for the laravel_multiple_database2 database. For the laravel_multiple_database1 database, we will use the model and user migration files that have been provided by laravel from the start.

php artisan make:model Post -m

Run the artisan command as above to create the Post model and migration files.

protected $connection = 'mysql2';

Then open the app/Models/Post.php file and add the code as above to define that the Post model is connected to mysql2 or to the laravel_multiple_database2 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');
}

If you have run the php artisan make:model Post -m command and the Post model and migration files have been generated, now please open the generated migration file in database/migrations/xxxx_xx_xx_xxxxxx_create_posts_table.php. In the up method, we add the title and connection strings to mysql2. The down method is the same, we add a connection to mysql2.

php artisan migrate

Run artisan command as above to migrate migration file to database connection mysql2 or in database laravel_multiple_database2.

Step 5: Create Dummy Data

Ok, now we will create dummy data to populate the users table in database laravel_multiple_database1 (mysql connection) and table posts in database laravel_multiple_database2 (mysql2 connection).

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

First, we will create dummy data for the users table. Because laravel has provided a User factory, so we use the existing one. We're going to run a factory from Tinker. To do this, run the php artisan tinker command, then run the command User::factory()->count(10)->create().

That way, we have dummy data for the users table in database laravel_multiple_database1.

php artisan make:factory PostFactory --model=Post

Second, we will create dummy data for table posts in database laravel_multiple_database2. But first, we need to generate a new factory file for posts. Run the artisan command as above to generate the PostFactory file.

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

Then if you have generated PostFactory, now open the file in database/factories/PostFactory.php. In the file or rather in the method definition, change it to be as above. Here we just need to create dummy data with faker->text in the title field.

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

Next, we execute the PostFactory.php file from tinker by running the two commands above sequentially.

Okay, so far we have succeeded in creating dummy data in different databases, namely laravel_multiple_database1 (mysql connection) and laravel_multiple_database2 (mysql2 connection).

Next we will display the data from the users and posts table which of course also comes from two different databases (multiple databases).

Step 6: Setup Route

Route::get('/', function () {
    $users = \App\Models\User::get();
    $posts = \App\Models\Post::get();

    return view('welcome', compact('users','posts'));
});

We turn to the routes/web.php file. In this file, we will change the route to be like the code above. In this root route, we will display data from table users in database laravel_multiple_database1 and data from table posts in database laravel_multiple_database2 with connection mysql2.

Step 7: Setup View

<!doctype html>
<html lang="en">
  <head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <!-- Bootstrap CSS -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-F3w7mX95PdgyTmZZMECAngseQB83DfGTowi0iMjiWaeVhAn4FJkqJByhZMI3AhiU" crossorigin="anonymous">

    <title>Laravel Multiple Database Connections</title>
  </head>
  <body>
    <div class="container my-5">
        <h1 class="fs-5 fw-bold my-3 text-center">Laravel Multiple Database Connections</h1>
        <div class="row">
            <h2 class="fs-5 fw-bold my-3">Database 1: laravel_multiple_database1</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>
        </div>

        <div class="row">
            <h2 class="fs-5 fw-bold my-3">Database 2: laravel_multiple_database2</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>
        </div>
    </div>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.1/dist/js/bootstrap.bundle.min.js" integrity="sha384-/bQdsTh/da6pkI1MST/rWKFNjaCP5gBSY4sEBT38Q/9RBh9AH40zEOg7Hlq2THRZ" crossorigin="anonymous"></script>

  </body>
</html>

Then, open the welcome.blade.php file and change the existing code to be like the code above. From the code as above, we use the starter template from bootstrap 5 and add a table to display the data from the users and posts table as we have setup in the routes/web.php file in the previous step.

Step 8: Testing

We have gone through the steps starting from installing laravel, creating two databases, configuring, to setup routes and changing the welcome.blade.php file to display data from the users and posts table. Now we have come to the final step which is testing. To see the results, whether the data from the two databases has been successfully displayed in one laravel project, please run the project by running the php artisan serve command, then open the laravel project in the browser. Then it will display the data from the users and posts table. That means, up to this step, we have successfully implemented or used two databases in one laravel project (laravel multiple database connections).

Add Relationship

What if we add a relationship ? like adding one to many relationship in laravel multiple database connections.

Okay, let's try to implement it by adding a new field in the posts table (in the database laravel_multiple_database2), namely the user_id field which we will associate with the users table in the laravel_multiple_database1 database.

php artisan make:migration add_user_id_to_posts_table

We create a new migration file with the artisan command as above.

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

Then open the database/migrations/xxxx_xx_xx_xxxxxx_add_user_id_to_posts_table.php file that you just generated. In the up and down method, change the code to be as above by adding a user_id field with a default value of 1 and a connection to mysql2 or to the laravel_multiple_database2 database.

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

Next, open the app/Models/Post.php file and add the code as above. Pay attention to the code above, with that code we create a relationship or connect Post to User with mysql setconnection. setConnection('mysql'), means we connect table posts from database laravel_multiple_database2 to table users with database connection to mysql or to database laravel_multiple_database1.

<div class="row">
    <h2 class="fs-5 fw-bold my-3">Database 2: laravel_multiple_database2</h2>
    <table class="table">
        <thead>
            <tr>
                <th scope="col">#</th>
                <th scope="col">Title</th>
                <th scope="col">User</th>
            </tr>
        </thead>
        <tbody>
            @foreach ($posts as $key => $item)
            <tr>
                <th scope="row">{{ ++$key }}</th>
                <td>{{ Str::limit($item->title, 100) }}</td>
                <td>{{ $item->user->name }}</td>
            </tr>
            @endforeach
        </tbody>
    </table>
</div>

We update a bit for the welcome.blade.php file. In the code to display posts data in table form, add  <th scope="col">User</th> and <td>{{$item->user->name}}</td>.

And now if we check again, the posts table view has succeeded in displaying the user name based on the user_id (which we have set default(1)).

That's enough articles this time, if you have another way than what has been conveyed in this article, you can share it in the comment form below. Hopefully this article can be useful and see you in the next article. 👋 🚀 👨‍🚀