Multiple database connections in a single Laravel project

2406
multiple database connection

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.

Read More Articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.