Laravel 6 Dynamic database connection

By | October 19, 2019

Hello Coders !! This article is about the Laravel 6 Dynamic database connection. Sometimes we need multiple databases in our system to work with and laravel is capable of doing this.

Laravel 6 Dynamic database connection

With no more complexity, this article is pretty easy to implement you will find the exact changes you want to make in your system.

Let’s start implementing Laravel 6 Dynamic database connection.

Please follow steps to integrate the laravel multilevel database connection.

Step 1 : Install latest Laravel 6

We will start with fresh and latest laravel version 6.

Run this command for laravel installation

composer create-project --prefer-dist laravel/laravel multiple-database

If you want to learn basic steps for the whole installation process of laravel. visit this link: Laravel Installation

This command will install a new laravel in your system.

Step 2 : .env file changes

In your project’s root folder, you will find the “.env” file and in that file, you will find a database connectivity params like this.

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

Now, we will add one more database connection parameter the same as the default one with different parameter names.

Now, we can identify and use this parameter differently.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel
DB_USERNAME=root
DB_PASSWORD=pass

DB_CONNECTION_TWO=mysql
DB_HOST_TWO=127.0.0.1
DB_PORT_TWO=3306
DB_DATABASE_TWO=laravel2
DB_USERNAME_TWO=root
DB_PASSWORD_TWO=pass

Here we have the two different database parameters for two different database connections.

Step 3 : Database config file

File path “config/database.php”

Open this file and you will find this below code.

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

I have to rewrite the above code and now this code will look like this.

So I have created to alias one is “mysql” and another is “mysql2” and passed the different values according to required database values.

'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST'),
            'port' => env('DB_PORT'),
            'database' => env('DB_DATABASE'),
            'username' => env('DB_USERNAME'),
            '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_TWO'),
            'port' => env('DB_PORT_TWO'),
            'database' => env('DB_DATABASE_TWO'),
            'username' => env('DB_USERNAME_TWO'),
            'password' => env('DB_PASSWORD_TWO'),
            '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'),
            ]) : [],
        ],

Step 4 : How to use

Schema

To specify which connection to use you just need to simply run the connection() method.

Schema::connection('mysql2')->create('table_name', function($table)
{
    $table->increments('id'):
});

Query Builder

$users = DB::connection('mysql2')->select(...);

Eloquent

You can set the $connection variable in your model.

class SomeModel extends Eloquent {
    protected $connection = 'mysql2';
}

Here is an example:

You can use “setConnection” method for the database connection.

$someModel = new ModelName;
$someModel->setConnection('mysql2'); // non-static method

And if you want to use “on” the static method.

 $something = ModelName::on('mysql2')->find(1); // static method

These are some examples and ways to implement the Laravel 6 Dynamic database connection in a single system.

Thank you for reading this article let us know if you have any query regarding this.

Happy Coding !!

Leave a Reply

Your email address will not be published. Required fields are marked *