How to use multiple databases in Laravel

Question

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

User · Answer

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.

User · Answer

Using  env    5 0  tested on 5 5   In  env  DB CONNECTION mysql DB HOST 127 0 0 1 DB PORT 3306 DB DATABASE database1 DB USERNAME root DB PASSWORD secret  DB CONNECTION SECOND mysql DB HOST SECOND 127 0 0 1 DB PORT SECOND 3306 DB DATABASE SECOND database2 DB USERNAME SECOND root DB PASSWORD SECOND secret   In config database php   mysql    gt         driver       gt  env  DB CONNECTION         host         gt  env  DB HOST         port         gt  env  DB PORT         database     gt  env  DB DATABASE         username     gt  env  DB USERNAME         password     gt  env  DB PASSWORD         mysql2    gt         driver       gt  env  DB CONNECTION SECOND         host         gt  env  DB HOST SECOND         port         gt  env  DB PORT SECOND         database     gt  env  DB DATABASE SECOND         username     gt  env  DB USERNAME SECOND         password     gt  env  DB PASSWORD SECOND            Note  In mysql2 if DB username and DB password is same  then you can use env  DB USERNAME   which is metioned in  env first few lines    Without  env  lt 5 0  Define Connections     app config database php   return array        default    gt   mysql         connections    gt  array             Primary Default database connection          mysql    gt  array               driver       gt   mysql                host         gt   127 0 0 1                database     gt   database1                username     gt   root                password     gt   secret               charset      gt   utf8                collation    gt   utf8 unicode ci                prefix       gt                            Secondary database connection          mysql2    gt  array               driver       gt   mysql                host         gt   127 0 0 1                database     gt   database2                username     gt   root                password     gt   secret               charset      gt   utf8                collation    gt   utf8 unicode ci                prefix       gt                               Schema  To specify which connection to use  simply run the connection   method  Schema  connection  mysql2  - gt create  some table   function  table         table- gt increments  id          Query Builder   users   DB  connection  mysql2  - gt select         Eloquent  Set the  connection variable in your model  class SomeModel extends Eloquent        protected  connection    mysql2        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- gt setConnection  mysql2       non-static method           something    someModel- gt find 1             something   SomeModel  on  mysql2  - gt find 1      static method          return  something                Note Be careful about attempting to build relationships with tables across databases  It is possible to do  but it can come with some caveats and depends on what database and or database settings you have      From Laravel Docs  Using Multiple Database Connections  When using multiple connections  you may access each connection via the connection method on the DB facade  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  - gt select         You may also access the raw  underlying PDO instance using the getPdo method on a connection instance    pdo   DB  connection  - gt getPdo        Useful Links   Laravel 5 multiple database connection FROM laracasts com Connect multiple databases in laravel FROM tutsnare com Multiple DB Connections in Laravel FROM fideloper com

User · Answer

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

User · Answer

Actually  DB  connection  name  - gt 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

User · Answer

In Laravel 5 1  you specify the connection    users   DB  connection  foo  - gt 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

[php] How to use multiple databases in Laravel

Examples related to php

Examples related to mysql

Examples related to database

Examples related to laravel