Managing Multiple Databases in Laravel

When working with Laravel applications, it's common to encounter situations where multiple databases are required. In such cases, it's crucial to understand how to effectively manage and interact with these databases within your Laravel application.

Laravel's Database Facade

Laravel provides the DB facade, which serves as a central point for accessing different database connections. By utilizing the connection method provided by the DB facade, developers can establish and retrieve connections to multiple databases. The connection method expects a parameter corresponding to the connection name defined in the config/database.php configuration file.

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

Configuring Database Connections

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

Using config/database.php:

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

Without .env (Laravel <= 4.0):

return array(
    'default' => 'mysql',
    'connections' => array(
        'mysql' => array(
            'driver' => 'mysql',
            'host' => '127.0.0.1',
            'database' => 'mysql_database',
            'username' => 'root',
            'password' => 'secret',
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
        ),

        'pgsql' => [
            'driver' => 'pgsql',
            'host' => 'localhost',
            'port' => '5432',
            'database' => 'pgsql_database',
            'username' => 'root',
            'password' => 'secret',
            'charset' => 'utf8',
            'prefix' => '',
            'schema' => 'public',
        ]
    ),
);<h3>Queries and Model Manipulation</h3>
<p><strong>Schema</strong>:</p>
<p>The connection method can be applied to specify the database for schema operations:</p>
<pre class="brush:php;toolbar:false">Schema::connection('pgsql')->create('some_table', function($table){ ... });

Query Builder:

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

Models:

The connection can be explicitly defined in the model class:

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

Eloquent:

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

Transactions

Transactions can also be managed across multiple databases:

DB::transaction(function () {
    DB::connection('mysql')->table('users')->update(...);
    DB::connection('pgsql')->table('orders')->update(...);
});

Runtime Connection Management

Connections can be set dynamically:

$someModel = new SomeModel;
$someModel->setConnection('pgsql'); // non-static
$something = $someModel->find(1);
$something = SomeModel::on('pgsql')->find(1); // static

Relationships and Caveats

When dealing with relationships across different databases, exercise caution. It is possible but may require careful consideration of database setup and settings.

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