Connect Multiple Databases in Laravel Efficiently

Introduction

Modern applications often require access to multiple databases to manage diverse data sources. Laravel simplifies this process by providing a range of features to seamlessly integrate multiple databases into your application.

Utilizing the DB Facade

To establish connections to different databases in Laravel, leverage the DB facade's connection method. Each connection you define corresponds to a configuration entry in your config/database.php file.

Defining Database Connections

Configure database connections using .env or the config/database.php file. Here's an example using .env:

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

In config/database.php, you can define connections as follows:

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

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

Working with Multiple Databases

After defining your connections, you can access them using the connection method, as in the following examples:

// Database operations on 'mysql' database
$users = DB::connection('mysql')->select(...);

// Database operations on 'pgsql' database
$orders = DB::connection('pgsql')->select(...);

Schema, Migration, and Query Builder

To perform schema, migration, or query builder operations on a specific database, specify the connection using the connection method:

// Schema operations on 'pgsql' database
Schema::connection('pgsql')->create('some_table', function($table) {});

// Migrations on 'mysql' database
Artisan::call('migrate', ['--connection' => 'mysql']);

// Query builder operations on 'pgsql' database
$results = DB::connection('pgsql')->select(...);

Model and Eloquent

To use a specific database for your model or Eloquent operations, set the $connection property in your model:

// Model using 'pgsql' database
class User extends Model {
    protected $connection = 'pgsql';
}

Transaction Management

To execute transactions across multiple databases, use the transaction method:

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

Runtime Connection Selection

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

// Runtime connection change
$someModel = new SomeModel;
$someModel->setConnection('pgsql');

// Static connection change
$something = SomeModel::on('pgsql')->find(1);

Note: Be cautious when establishing relationships between tables across databases. It's possible but may introduce complexities.

The above is the detailed content of How to Efficiently Connect and Manage Multiple Databases in Laravel?. For more information, please follow other related articles on the PHP Chinese website!