How to create multiple database connections in Lumen.
Last week I was working on a project in which I need multiple Database connections. I need one separate database connection for reporting (Only for a read operation) and another for the rest of the work (Read & write both).
How to Create Multiple Database Connections in Lumen
Let’s first put the configuration of your both databases in a .env file. In .env file just make sure you enter the host, port, database, username and password for both the Databases.
|
1 2 3 4 5 6 7 8 9 10 11 |
DB_HOST=localhost DB_PORT=3306 DB_DATABASE=example1 DB_USERNAME=root DB_PASSWORD= DB_HOST_READ=localhost DB_PORT_READ=3306 DB_DATABASE_READ=example2 DB_USERNAME_READ=readroot DB_PASSWORD_READ= |
In config/database.php add a configuration array for your new database. In connections array, you’ll find MySQL and below that, you can add similar array.
For illustration purpose check this example.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
'connections' => [ //Default configuration 'mysql' => [ 'driver' => 'mysql', 'host' => env('DB_HOST', 'localhost'), 'port' => env('DB_PORT', 3306), 'database' => env('DB_DATABASE', 'forge'), 'username' => env('DB_USERNAME', 'forge'), 'password' => env('DB_PASSWORD', ''), 'charset' => env('DB_CHARSET', 'utf8'), 'collation' => env('DB_COLLATION', 'utf8_unicode_ci'), 'prefix' => env('DB_PREFIX', ''), 'timezone' => env('DB_TIMEZONE', '+00:00'), 'strict' => env('DB_STRICT_MODE', false), ], /** * Another DB connection config */ 'read-replica' => [ 'driver' => 'mysql', 'host' => env('DB_HOST_READ', 'localhost'), 'port' => env('DB_PORT_READ', 3306), 'database' => env('DB_DATABASE_READ', 'forge'), 'username' => env('DB_USERNAME_READ', 'forge'), 'password' => env('DB_PASSWORD_READ', ''), 'charset' => env('DB_CHARSET', 'utf8'), 'collation' => env('DB_COLLATION', 'utf8_unicode_ci'), 'prefix' => env('DB_PREFIX', ''), 'timezone' => env('DB_TIMEZONE', '+00:00'), 'strict' => env('DB_STRICT_MODE', false), ], ] |
Now you have added connection configurations, you can access them by using either DB facade or app() helper.
How to debug SQL queries in Laravel & Lumen
NOTE – For using different databases in your code, You need to define which database connection you need by passing in the name of the connection.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
// Use default connection app('db')->connection()->select('SELECT * FROM employees'); DB::table($this->table) ->select(DB::raw($this->_employeeAttributes)) ->where('employee_id',$employeeId) ->first() // Use read-replica connection app('db')->connection('read-replica')->select('SELECT * FROM employees'); DB::connection('read-replica') ->table($this->table) ->select(DB::raw($this->_employeeAttributes)) ->where("employee_id", $employeeId) ->get(); |
How to Create Read & Write Connections in Lumen & Laravel
If you want to setup separate DB connections for a read and write database. I mean one database connection only for select statement and another one for insert, update and delete statements.
Laravel & Lumen provides this functionality out of the box. You just need to configure this setting that’s it. Your master and slave DB connections are ready to use.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
'mysql' => [ 'read' => [ 'host' => '192.168.1.1', ], 'write' => [ 'host' => '196.168.1.2' ], 'driver' => 'mysql', 'database' => 'database', 'username' => 'root', 'password' => '', 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', 'prefix' => '', ], |
