I want to combine multiple databases in my system. Most of the time the database is MySQL; but it may differ in future i.e. Admin can generate such a reports which is use source of heterogeneous database system.
So my question is does Laravel provide any Facade to deal with such situations? Or any other framework have more suitable capabilities for problem is?
From Laravel Docs: You may access each connection via the connection method on the DB
facade when using multiple connections. The name passed to the connection method should correspond to one of the connections listed in your config/database.php
configuration file:
$users = DB::connection('foo')->select(...);
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'),
],
Note: In
pgsql
, ifDB_username
andDB_password
are the same, then you can useenv('DB_USERNAME')
, which is mentioned in.env
first few lines.
Without .env
<= 4.0 (or lower)
app/config/database.php
return array(
'default' => 'mysql',
'connections' => array(
# Primary/Default database connection
'mysql' => array(
'driver' => 'mysql',
'host' => '127.0.0.1',
'database' => 'mysql_database',
'username' => 'root',
'password' => 'secret'
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
),
# Secondary database connection
'pgsql' => [
'driver' => 'pgsql',
'host' => 'localhost',
'port' => '5432',
'database' => 'pgsql_database',
'username' => 'root',
'password' => 'secret',
'charset' => 'utf8',
'prefix' => '',
'schema' => 'public',
]
),
);
Run the connection()
method to specify which connection to use.
Schema::connection('pgsql')->create('some_table', function($table)
{
$table->increments('id'):
});
Or, at the top, define a connection.
protected $connection = 'pgsql';
$users = DB::connection('pgsql')->select(...);
(In Laravel >= 5.0 (or higher))
Set the $connection
variable in your model
class ModelName extends Model { // extend changed
protected $connection = 'pgsql';
}
(In Laravel <= 4.0 (or lower))
Set the $connection
variable in your model
class SomeModel extends Eloquent {
protected $connection = 'pgsql';
}
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;
}
You can also define the connection at runtime via 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;
}
}
Note: Be careful about building relationships with tables across databases! It is possible to do, but it can come with caveats depending on your database and settings.
Tested versions (Updated)
Version | Tested (Yes/No) |
---|---|
4.2 | No |
5 | Yes (5.5) |
6 | No |
7 | No |
8 | Yes (8.4) |
9 | Yes (9.2) |
Useful Links