Multiple Database Connections In Laravel

Sometimes, our application needs multiple database connections. By default, Laravel is configured with the default database connection. In this tutorial, you’re going to learn how to use multiple database connections in laravel. There are a lot of ways to do that.

Firstly, you need an installed laravel application. I’m using two MySQL databases, but you can use any connections like SQLite, PostgreSQL, etc. Also, you can use all the different connections too. Learn more.

# Configure Database Connections

Before using multiple database connections you need to configure them. To do that, open config > database.php. Next, in the connections array put your database connections as shown. You just need to copy and paste the connection you want.

/* config > database.php */

<?php

...

'connections' => [

	'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            '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' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

    'mysql2' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB2_HOST', '127.0.0.1'),
            'port' => env('DB2_PORT', '3306'),
            'database' => env('DB2_DATABASE', 'forge'),
            'username' => env('DB2_USERNAME', 'forge'),
            'password' => env('DB2_PASSWORD', ''),
            'unix_socket' => env('DB2_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
    	],

],

...

After configuring the database connections provide its credentials into the .env file. Of course, you can do that directly into the config/database.php file. But I’ll do that in the .env file.

/* .env */

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database1
DB_USERNAME=root
DB_PASSWORD=

DB2_HOST=127.0.0.1
DB2_PORT=3306
DB2_DATABASE=database2
DB2_USERNAME=root
DB2_PASSWORD=

Now, all the configurations for the multiple database connections are done. Keep in mind that if you want to use laravel eloquent relationship beween multiple databases then you must keep your foreign key accordingly. For example, there is users table with an id column from database1 and you want to fetch posts from database2, then the posts table must have a user_id column.

After that, you can use these configurations in lots of ways.

# Using Custom Connection In Eloquent Models

To clarify, laravel use a default database connection for eloquent models. You can change its default behavior using the $connection property like below. By using this property the eloquent model will use a defined database connection instead of a default connection. From now whenever you are using eloquent the model will use a defined connection.

/* app > Models > User.php */

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;
use Illuminate\Notifications\Notifiable;

class User extends Authenticatable
{
    use HasFactory, Notifiable;

    protected $connection = "mysql2";
}

# Using Custom Connection In Migration

On the other hand, you can also use a custom connection during database migration, when creating a schema or in artisan migrate command. Defining a connection during creating a schema will create a table into a defined database. In the same vein, defining a connection into artisan command will do the same. For example:

/* database > migrations > *_create_users_table.php */

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateUsersTable extends Migration
{
    public function up()
    {
        Schema::connection('mysql2')->create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }
    
    public function down()
    {
        Schema::dropIfExists('users');
    }
}

php artisan migrate --database=mysql2

# Using Custom Connection In Controller

Moreover, you can set a database connection in controller methods. Suppose you have posts table in database2 then you can get posts like below:

/* app > Http > Controllers */

<?php

namespace App\Http\Controllers;

use App\Models\Post;
use Illuminate\Http\Request;

class PostController extends Controller
{
    public function index()
    {
        $post = new Post;
        $post->setConnection('mysql2');
        $post->find(1);
    }
}

Similarly, you can create DB queries with a specific database connection. To do that, you can use the following syntax.

/* app > Http > Controllers */

<?php

namespace App\Http\Controllers;


use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class PostController extends Controller
{
    public function index()
    {
        $posts = DB::connection('mysql2')->table('posts')->get();
        return $posts;
    }
}

That’s it for this tutorial! You learned how to use multiple database connections in laravel. Please share and comment on your feedback. Thank you!