Laravel Multiple Database Connections

7 9,378

In this article, we will discuss the “Laravel Multiple Database Connections”. In some conditions, we need to connect multiple databases to the Laravel application. Let’s see how we implement multiple database connections in Laravel 5.7.

Prerequisites

We need a fresh Laravel setup where we implement our example.

Database Configuration without .env

In Laravel, database configuration file is located at “config/database.php”. Here we can define more than one database connection of any type. If your application use data from two different MySql database then you can define them easily.

<?php
return => [
    'connections' => [
        // Default database connection
        'mysql' => [
            'driver' => 'mysql',
            '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' => '',
            'strict' => true,
            'engine' => null,
        ],

        // Custom database connection
        'mysql2' => [
            'driver' => 'mysql',
            'host' => 'localhost',
            'port' => '3306',
            'database' => 'lara_multiple',
            'username' => 'root',
            'password' => '',
            'unix_socket' => '',
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],
    ]
]

Database Configuration with .env

Update your “config/database.php” with following code snippet.

<?php

return => [
    'connections' => [
        // Custom database connection
        'mysql2' => [
            'driver'    => env('DB_CONNECTION_SECOND'),
            'host'      => env('DB_HOST_SECOND'),
            'port'      => env('DB_PORT_SECOND'),
            'database'  => env('DB_DATABASE_SECOND'),
            'username'  => env('DB_USERNAME_SECOND'),
            'password'  => env('DB_PASSWORD_SECOND'),
            ...
        ],
    ]
]

After updating the You need to add following code snippet in your “.env” file.

DB_CONNECTION_SECOND=mysql
DB_HOST_SECOND=127.0.0.1
DB_PORT_SECOND=3306
DB_DATABASE_SECOND=multi_lara
DB_USERNAME_SECOND=root
DB_PASSWORD_SECOND=

Custom Connection in Migration

You can use the following example for how to use the custom connection in your migration.

<?php

...

public function up()
{
    Schema::connection('mysql2')->create('multipost', function (Blueprint $table) {
        $table->increments('id');
        $table->string('post_title');
        $table->string('post_content');
        $table->timestamps();
    });
}

...

After creating your migration time to execute our migrations for the specific database connection.

php artisan migrate --database=mysql2

Sometimes we face error when we execute the migrate command. Such as:

Laravel Multiple Database Connections

You can fix this kind of issues via using “config:cache” artisan command.

php artisan config:cache

Custom connection with Model

Set the “$connection” variable in your model.

<?php

class SomeModel extends Eloquent {

    protected $connection = 'mysql2';

}

Custom connection with Controller

You can also define the connection in your controller using “setConnection” method.

<?php

class SomeController extends BaseController
{
    public function someMethod()
    {
        $someModel = new SomeModel;
        $someModel->setConnection('mysql2');
        $something = $someModel->find(1);
        return $something;
    }
}

Custom connection with Query Builder

You can also define a connection on the query builder.

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

Conclusion

In this article, we will discuss Laravel Multiple Database Connections. Thanks for reading this article. Please feel free to add the comment or submit your feedback 🙂


You may like:

Laravel 5.7 New Feature Dump Server

LaRecipe: Create Beautiful Documentation with Markdown

Difference Between Agile and Scrum Methodology

7 Comments
  1. Praveen says

    Nice article but i seen this article some other links as well but it is not clear in the statement below what is … means DB::connection(‘mysql2’)->select(…)->(…);

    1. Pankaj Sood says

      Hi Praveen,

      It means you can write your query using a DB instance with a specific connection if required.

  2. Swapnil Nath says

    Hi,

    my query is can we use multiple env file at laravel project?

    Thanks,
    Swapnil Nath

  3. Merle says

    Hello, thanks for the helpful tutorial!
    Is it possible to select the database connection on a per-request basis?
    We have a database per tenant to keep data from one tenant from other tenants (only having a few tenants).
    A request comes in from tenant A so we want to use tenant A’s database to get all the data. If a request from tenant C comes in we want to use C’s connection. The application (models, etc) is the same. We are using Eloquent ORM.
    Could you describe that scenario for me?
    appreciating your response and thanks for all your effort!

    1. Pankaj Sood says

      Hi Merle, You can make your own custom logic to play with connections for multi-tenancy. For example, please check Laravel Multi-Tenancy.

      1. Merle says

        Thanks for your quick response!
        However it looks like Tenancy doesn’t work with Lumen which was used to build our whole API.
        Do you know how one can set the default connection used by Eloquent ORM per request?

        1. Pankaj Sood says

          You have to make your own logic. Something like dynamically changes the connection as per tenant.

          I will try to make an example of this in the upcoming weekend.

Leave A Reply

Your email address will not be published.