How to Run Laravel Using Multiple Database Connections

Recently I was researching about Laravel using multiple database connection. I was curious how Eloquent handle relationship if another model is using different connection or how query builder handle join operation if another table is in other database.

Well, let’s get started.

TL;DR

Do not like to read a bunch of stories? Just jump to the code:

didikz/laravel-multidb

Install Laravel

Installing Laravel is easy, just make sure you have internet connection and your machine has minimal requirements for installing Laravel.

Installation - Laravel - The PHP Framework For Web Artisans

Wait until progress completed and we’re ready to setup our database.

Database Preparation & Configuration

I will use 3 different databases and 2 different database engines using MySQL & SQL Server for researching is it possible or not to run query or relationship model across different database or even database engine.

1. laramultidb database with connection name mysql is using MySQL engine.
2. laramultidb2 database with connection name mysql2 is using MySQL engine.
3. laravelmultidb database with connection name sqlsrv is using SQL Server Engine.

If you don’t have SQL Server, you may use another database engine such as PostgreSQL or Sqlite.

Go to config/database.php to create new database connection configuration. In this case, this is my setup

'connections' => [

    'sqlite' => [
        'driver' => 'sqlite',
        'database' => env('DB_DATABASE', database_path('database.sqlite')),
        'prefix' => '',
    ],

    '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', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],

    'mysql2' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE2', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],
    'sqlsrv' => [
        'driver' => 'sqlsrv',
        'host' => env('DB_HOST_SQLSRV', 'localhost'),
        'port' => env('DB_PORT_SQLSRV', '1433'),
        'database' => env('DB_DATABASE_SQLSRV', 'forge'),
        'username' => env('DB_USERNAME_SQLSRV', 'forge'),
        'password' => env('DB_PASSWORD_SQLSRV', ''),
        'charset' => 'utf8',
        'prefix' => '',
    ],

],

Since mysql2 is using same database engine and same host as mysql, I will let other mysql configuration is same as mysql connection except for DB_DATABASE2 because I used different database name.

Then I need to adjust env parameters for sqlsrv configuration using new key so I can fill with my SQL Server credential in my .env file later.

Database connection is ready, now modify .env file in database section.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laramultidb
DB_USERNAME=yourusername
DB_PASSWORD=secret

DB_DATABASE2=laramultidb2

DB_HOST_SQLSRV=127.0.0.1
DB_PORT_SQLSRV=1433
DB_DATABASE_SQLSRV=laravelmultidb
DB_USERNAME_SQLSRV=sa
DB_PASSWORD_SQLSRV=secret

Note that in my .env file, DB_CONNECTION=mysql is default connection.

Migration & Model

How do we exactly create migration based on connection we want? Simple, define connection name on your Schema. Let’s take a look to my migration that using non default database connection:

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::connection('mysql2')->create('types', function (Blueprint $table) {
        $table->increments('id');
        $table->string('code')->index();
        $table->string('name');
        $table->timestamps();
    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::connection('mysql2')->drop('types');
}

In above case, when we run migration, types table will be migrated to mysql2 connection that using laravmultidb2 as database name. If we’re not define specified connection name, then laravel will use default connection.

How about Eloquent as model? Simply define connection name in your Eloquent Model to override default connection.

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Type extends Model
{
    protected $connection = 'mysql2';
}

Relationship Multiple Databases

Now we are already knows that we can define connection in Eloquent model. The magic of Eloquent is we can write relationships between model that has different connection.

I’ve wrote three models that has different connection:

Type (connection: mysql2) has many Post (connection: mysql)

Type(connection: mysql2) has many Mail (connection: sqlsrv)

Please take a note, although each model is using different connection, make sure your data in foreign key column has same value as parent key or model relationship won’t work.

Here is example of our Type model:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Type extends Model
{
    protected $connection = 'mysql2';

    /**
     * one to many relationship with Post (mysql connection)
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function posts()
    {
        return $this->hasMany(Post::class, 'type_code', 'code');
    }

    /**
     * one to many relationship with Mail (sqlsrv connection)
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function mails()
    {
        return $this->hasMany(Mail::class, 'type_code', 'code');
    }
}

Then this is our Mail model that has code similarity with Post model:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Mail extends Model
{
    protected $connection = 'sqlsrv';

    /**
     * override date format since SQL server has own date format for timestamp fields
     * @var string 
     */
    protected $dateFormat = 'Y-m-d H:i:s';

    /**
     * one to many inversion relationship with Type (mysql2 connection)
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function type()
    {
        return $this->belongsTo(Type::class, 'type_code', 'code');
    }
}

Now, since we’re already seeded our data dummy into tables, then we could test our relationship using tinker

check multiple connection relationship using tinker

Now we see Type model that connect to mysql2 connection is able to get relationship with Post which connect to mysql and Mail which connect to sqlsrv.

Same rule when belongsTo relationship method is also working with multiple connection.

multiple connection using belongsTo relationship method

Join Operation

Join query is also support multiple database connection, but we have to define database name for other tables. Open tinker and try to run query using Eloquent model that join from Post andType .

join query with connection mysql & mysql2

But if we don’t specify database name for types table, error occured:

error table not found

Instead using Eloquent Model, Query Builder is also can run join operation

join using query builder

Or we can specify connection from query builder:

query builder join with specified connection name

Next, we want to check join query from Mail which has sqlsrv connection (SQL Server) and Type which has mysql2 connection (MySQL).

Whoops, it seems we can not run join query across different database engine. We could run join from posts table to type table because their database is still hosted in same engine. They are basically has same connection but only different in database name.

Conclusion

1. Laravel has ability to run in multiple database connection even in across different database engine.
2. We can create migration based on connection we made before.
3. Eloquent ORM has ability to run relationship with different connection but we need to override connection attribute to define which connection is used by model.
4. Query Builder is support join query in different connection but limited with same host / database engine. I couldn’t find how to join across different database engine yet.
5. In real case, although Eloquent support relationship with different database engine, I think the problem may occur is about compatibility in your query since there are some different approach when we run query between database engine.
6. I’ve wrapped this topic with simple project in github. Feel free to clone or modify as you need.

didikz/laravel-multidb

References

Multiple DB Connections in Laravel

Eloquent: Relationships - Laravel - The PHP Framework For Web Artisans

Database: Query Builder - Laravel - The PHP Framework For Web Artisans

Happy coding folks :)