Connecting Multiple Databases with Laravel

To manage multiple databases within a Laravel system, Laravel offers a versatile solution through its database facade.

Using Database Connections

When utilizing multiple database connections, you can access each connection using the connection method on the DB facade. The name provided to the connection method aligns with the names defined in the config/database.php configuration file:

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

Definition of Connections

Database connections can be configured using the .env file or the config/database.php file:

Using 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

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

Schema and Migrations

To specify the connection to use for schema and migrations, employ the connection() method:

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

Alternatively, you can define a connection at the top of the class:

protected $connection = 'pgsql';

Query Builder

To utilize the Query Builder for a specific connection:

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

Model

In Laravel 5.0 and above, you can set the $connection variable in your model:

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

Eloquent

In Laravel 4.0 and below, you can define the $connection variable within your model:

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

Transaction Mode

To manage transactions across multiple databases:

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;
}

Runtime Connection Management

You can also establish connections at runtime using the setConnection method or the on static method:

class SomeController extends BaseController {
    public function someMethod()
    {
        $someModel = new SomeModel;
        $someModel->setConnection('pgsql'); // non-static method
        $something = $someModel->find(1);
        $something = SomeModel::on('pgsql')->find(1); // static method
        return $something;
    }
}

Caution

When establishing relationships with tables across different databases, it's crucial to exercise caution due to potential caveats based on the database and settings you employ.

The above is the detailed content of How Can I Manage Multiple Database Connections in Laravel?. For more information, please follow other related articles on the PHP Chinese website!