How to Create Multiple Database Connection in Laravel

When working on larger Laravel applications, you might find the need to interact with multiple databases. Laravel provides a straightforward way to manage multiple database connections. In this article, we'll explore how to set up and use multiple database connections in a Laravel application.

Defining Connections in Laravel

To access multiple database connections, you can use the connection method on the DB facade. The name you pass to the connection method should correspond to the connections defined in your config/database.php configuration file.

Using .env File (Laravel 5.0 and higher)

Firstly, define your database connections in the .env file:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=mysql_database
DB_USERNAME=root
DB_PASSWORD=secret

DB_CONNECTION_PGSQL=pgsql
DB_HOST_PGSQL=127.0.0.1
DB_PORT_PGSQL=5432
DB_DATABASE_PGSQL=pgsql_database
DB_USERNAME_PGSQL=root
DB_PASSWORD_PGSQL=secret

Then, access the connections using the DB facade:

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

Using config/database.php

Alternatively, you can define connections directly in the config/database.php file:

'mysql' => [
    'driver'    => env('DB_CONNECTION'),
    'host'      => env('DB_HOST'),
    'port'      => env('DB_PORT'),
    'database'  => env('DB_DATABASE'),
    'username'  => env('DB_USERNAME'),
    'password'  => env('DB_PASSWORD'),
],

'pgsql' => [
    'driver'    => env('DB_CONNECTION_PGSQL'),
    'host'      => env('DB_HOST_PGSQL'),
    'port'      => env('DB_PORT_PGSQL'),
    'database'  => env('DB_DATABASE_PGSQL'),
    'username'  => env('DB_USERNAME_PGSQL'),
    'password'  => env('DB_PASSWORD_PGSQL'),
],

Note: In pgsql, if DB_USERNAME_PGSQL and DB_PASSWORD_PGSQL are the same in your environmental file(.env), then you can use env('DB_USERNAME_PGSQL') to void redundancy and simplifies your configuration.

Using Connections in Your Application

Once your connections are defined, you can use them in various parts of your application, such as migrations, query builders, models, and transactions.

Schema and Migration

When you are running migrations, specify the connection using the connection() method:

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

Or, you can define the connection at the top.

protected $connection = 'pgsql';

Query Builder

For query builder operations with the specific connection you need to specify the connection by using the connection() method as shown below:

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

Models

In models, set the $connection property to define the specific database connection that you are going to use:

class ModelName extends Model {
    protected $connection = 'pgsql';
}

Transaction Mode

You can perform transactions across multiple connections as well:

DB::transaction(function () {
    DB::connection('mysql')->table('users')->update(['name' => 'John']);
    DB::connection('pgsql')->table('orders')->update(['status' => 'shipped']);
});

or

DB::connection('mysql')->beginTransaction();
try {
    DB::connection('mysql')->table('users')->update(['name' => 'John']);
    DB::connection('pgsql')->beginTransaction();
    DB::connection('pgsql')->table('orders')->update(['status' => 'shipped']);
    DB::connection('pgsql')->commit();
    DB::connection('mysql')->commit();
} catch (\Exception $e) {
    DB::connection('mysql')->rollBack();
    DB::connection('pgsql')->rollBack();
    throw $e;
}

Dynamic Connection at Runtime

You can also dynamically change the connection at runtime using the setConnection method or the on static method:

class ExampleController extends BaseController {
    public function exampleMethod()
    {
        $exampleModel = new ExampleModel;
        $exampleModel->setConnection('pgsql'); // Non-static method
        $example = $exampleModel->find(1);
        $example = ExampleModel::on('pgsql')->find(1); // Static method
        return $example;
    }
}

Note: When building relationships with tables from different databases, exercise caution as it can have complications depending on your database and settings.

Managing multiple database connections in Laravel is a powerful feature that allowing developers to build robust applications that interact with diverse of data sources. By following the above guidelines provided in this article, you can effectively set up and utilize multiple database connections within your Laravel project, enhancing the flexibility and scalability of your applications.