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?
Actually, DB::connection('name')->select(..)
doesnt work for me, because 'name' has to be in double quotes: "name"
Still, the select query is executed on my default connection. Still trying to figure out, how to convince Laravel to work the way it is intended: change the connection.
Edit: I figured it out. After debugging Laravels DatabaseManager it turned out my database.php (config file) (inside $this->app) was wrong. In the section "connections" I had stuff like "database" with values of the one i copied it from. In clear terms, instead of
env('DB_DATABASE', 'name')
I needed to place something like
'myNewName'
since all connections were listed with the same values for the database, username, password, etc. which of course makes little sense if I want to access at least another database name
Therefore, every time I wanted to select something from another database I always ended up in my default database
Also you can use postgres fdw system
https://www.postgresql.org/docs/9.5/postgres-fdw.html
You will be able to connect different db in postgres. After that, in one query, you can access tables that are in different databases.
Laravel has inbuilt support for multiple database systems, you need to provide connection details in config/database.php file
return [
'default' => env('DB_CONNECTION', 'mysql'),
'connections' => [
'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
'engine' => null,
],
'mysqlOne' => [
'driver' => 'mysql',
'host' => env('DB_HOST_ONE', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE_ONE', 'forge'),
'username' => env('DB_USERNAME_ONE', 'forge'),
'password' => env('DB_PASSWORD_ONE', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
'engine' => null,
],
];
Once you have this you can create two base model class for each connection and define the connection name in those models
//BaseModel.php
protected $connection = 'mysql';
//BaseModelOne.php
protected $connection = 'mysqlOne';
You can extend these models to create more models for tables in each DB.
In Laravel 5.1, you specify the connection:
$users = DB::connection('foo')->select(...);
Default, Laravel uses the default connection. It is simple, isn't it?
Read more here: http://laravel.com/docs/5.1/database#accessing-connections
Source: Stackoverflow.com