How to use multiple databases in Laravel

In this tutorial we will learn about how we can define and use different database connection using laravel framework. Laravel is so flexible it allows you to switch between different databases or connections easily.

How/Where to define multiple database connections in laravel?

In laravel to define database connections you can go to config/database.php file and add your new connections. Let say that you have a blog website and you have two different databases:

  • blog
  • reporting

You want to switch between your databases efficiently in Laravel. First you need to define all your db connections in config/database.php file as seen below code: 

<?php

return [
    'default' => 'blog',
    
    // ....

    'connections' => [
        'blog_db' => [
            'database'  => 'blog',
            'driver'    => 'mysql',
            'host'      => env('DB_HOST'),
            'port'      => env('DB_PORT'),
            'username'  => env('DB_USERNAME'),
            'password'  => env('DB_PASSWORD'),
        ],
        
        'reporting_db' => [
            'driver'    => 'mysql',
            'database'  => 'reporting',
            'host'      => env('DB_HOST'),
            'port'      => env('DB_PORT'),
            'username'  => env('DB_USERNAME'),
            'password'  => env('DB_PASSWORD'),
        ],
    ]
];​

Now, all we did here is that we are using same server but different databases so we defined two connections in above code. Then we changed default connection to be blog.

Next, we need to add our DB configs in .env file as shown below: 

DB_PORT=3306
DB_USERNAME=root
DB_HOST=127.0.0.1
DB_PASSWORD=secret​

We added server related credentials in our .env file notice we did not set DB_CONNECTION variable here this is because we already set default connection to blog_db in our config/database.php file.

Now, we are all set with our database connections all of your eloquent queries and models will use default connection blog_db as defined in config/database.php file.

You can switch to any db connection using following methods:

  • Defining $connection variable in model so that your model use specific db connection
  • You can change db connection on-fly

Switching DB connection in Laravel Model

To use different database connection than default connection you can define $connection variable in your eloquent model as show below: 

<?php

class Blog extends Eloquent {

    // change default model connection to reporting
    protected $connection = 'reporting_db';
}​

Switching DB Connection on-fly in Laravel? 

// run select query from reporting_db connection
DB::connection('reporting_db')->select(...);

// get default connection
$pdo = DB::connection()->getPdo(); 

// get reporting connection
$pdo = DB::connection('reporting_db')->getPdo();​

How to define read/write connections in Laravel?

In some cases you might have different database servers and you want to use read/write connection for your web app.

Sometimes you may wish to use one database connection for SELECT statements, and another for INSERT, UPDATE, and DELETE statements.

To define read/write connection for your application open your config/database.php file and change it to match following code: 

<?php

return [
    'default' => 'blog',
    
    // ....    

    'mysql' => [
        'read' => [
            'host' => [
                '192.168.1.1',
                '196.168.1.2',
            ],
        ],
        'write' => [
            'host' => [
                '196.168.1.3',
            ],
        ],

        'sticky'    => true,
        'host'      => env('DB_HOST'),
        'port'      => env('DB_PORT'),
        'username'  => env('DB_USERNAME'),
        'password'  => env('DB_PASSWORD'),
        'driver'    => env('DB_CONNECTION'),

        // other keys goes here
    ],
];​

If the sticky option is enabled and a "write" operation has been performed against the database during the current request cycle, any further "read" operations will use the "write" connection. This ensures that any data written during the request cycle can be immediately read back from the database during that same request. It is up to you to decide if this is the desired behavior for your application. Laravel

Hope this tutorial is useful for those seeking to play with different database connection. Please share and like the tutorial keep me motivated to write awesome laravel tutorials.

↑