Recently, while working on a project, I came across a situation where I have to work on multiple databases. As I was working on a hotel API, they gave me a static database file containing general information about hotels. So, I created a separate database for static information database and use accordingly.
In this article, we are going to connect to multiple databases and retrieve values from them. So, let’s get started.
Database Set Up
First of all, we need to define our external database. We will define it in our app/database.php
file.
// app/database.php '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', ''), ... ], 'mysql_2' => [ 'driver' => 'mysql', 'host' => env('DB_HOST_SECOND', '127.0.0.1'), 'port' => env('DB_PORT_SECOND', '3306'), 'database' => env('DB_DATABASE_SECOND', 'forge'), 'username' => env('DB_USERNAME_SECOND', 'forge'), 'password' => env('DB_PASSWORD_SECOND', ''), ... ], .... ],
We can directly define these database settings in database.php
file, but it’s nice to have those settings in .env
file as the .env
file is not versioned and won’t be pushed on servers. We will add our second database credentials on .env
file which will looks like below:
// .env DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=kodementor DB_USERNAME=myusername DB_PASSWORD=secret DB_HOST_SECOND=127.0.0.1 DB_PORT_SECOND=3306 DB_DATABASE_SECOND=kodementor_static DB_USERNAME_SECOND=myusername DB_PASSWORD_SECOND=secret
Ok, now we have set up our 2 databases in a single project. Now, let’s test it if it works.
Migration
While using migration, we have never specified our database. This is because it will automatically get from the settings. But, we have 2 databases. This time we need to specify which database is to be used for creating tables from migration. We can specify this database as below. This will create a table on the second database.
// migration public function up() { Schema::connection('mysql_2')->create('posts', function($table) { $table->increments('id'); $table->string('title'); $table->text('body'); $table->string('submitted_by'); $table->string('published_by'); $table->string('status'); $table->string('image')->nullable(); $table->string('thumbnail')->nullable(); $table->timestamps(); }); }
Query Builder
We can define our connection while building a query as in the below example.
$images = DB::connection('mysql2')->select(...);
Eloquent
Similar to query builder, we can also define our connection in model. For this, we should use $connection
variable where we need to specify which database to use.
// model class Post extends Eloquent { protected $connection = 'mysql_2'; }
So, these are the ways to define multiple database connections in laravel. We can also define a different driver while defining multiple databases.
'mysql_2' => [ 'driver' => 'pgsql', 'host' => env('DB_HOST_SECOND', '127.0.0.1'), 'port' => env('DB_PORT_SECOND', '5432'), 'database' => env('DB_DATABASE_SECOND', 'forge'), 'username' => env('DB_USERNAME_SECOND', 'forge'), 'password' => env('DB_PASSWORD_SECOND', ''), ... ],
Quick Tip:
We can back up our laravel project or database only regularly using an open source package by spatie. If you want to know how you can follow this article how to backup laravel app and database.
Did you know, we can view our actual SQL queries while running migration in laravel. For more information please refer to the article view actual SQL queries before migration in laravel.
This is all about using multiple databases connections in laravel. If you have any comment or feedback, drop in the comment section below.