How to Use Multiple Databases in Laravel

Managing multiple database connections in Laravel can help you organize data more efficiently, improve scalability, and separate concerns in your application. In this guide, we’ll walk you through setting up and using multiple databases in Laravel with clear, step-by-step instructions.

Prerequisites

Before starting, ensure you have:

• Laravel installed
• MySQL (or another database system) set up
• Basic knowledge of Laravel migrations and models

Step 1: Create a Laravel Project

First, create a new Laravel project using Composer:

composer create-project laravel/laravel MultiDatabaseApp

Navigate into your project directory:

cd MultiDatabaseApp

Step 2: Configure Database Connections in .env

Update your .env file to include multiple database configurations:

# Primary Database (Default)
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=primary_db
DB_USERNAME=root
DB_PASSWORD=

# Secondary Database
DB_CONNECTION_SECOND=mysql
DB_HOST_SECOND=127.0.0.1
DB_PORT_SECOND=3306
DB_DATABASE_SECOND=secondary_db
DB_USERNAME_SECOND=root
DB_PASSWORD_SECOND=

# Third Database (Optional)
DB_CONNECTION_THIRD=mysql
DB_HOST_THIRD=127.0.0.1
DB_PORT_THIRD=3306
DB_DATABASE_THIRD=third_db
DB_USERNAME_THIRD=root
DB_PASSWORD_THIRD=

Step 3: Set Up Database Connections in config/database.php

Define the connections in config/database.php:

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'primary_db'),
    'username' => env('DB_USERNAME', 'root'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
],

'mysql2' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST_SECOND', '127.0.0.1'),
    'port' => env('DB_PORT_SECOND', '3306'),
    'database' => env('DB_DATABASE_SECOND', 'secondary_db'),
    'username' => env('DB_USERNAME_SECOND', 'root'),
    'password' => env('DB_PASSWORD_SECOND', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
],

'mysql3' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST_THIRD', '127.0.0.1'),
    'port' => env('DB_PORT_THIRD', '3306'),
    'database' => env('DB_DATABASE_THIRD', 'third_db'),
    'username' => env('DB_USERNAME_THIRD', 'root'),
    'password' => env('DB_PASSWORD_THIRD', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
],

Step 4: Create the Databases in MySQL

Run these SQL commands in your MySQL console or GUI tool (like phpMyAdmin):

CREATE DATABASE primary_db;
CREATE DATABASE secondary_db;
CREATE DATABASE third_db;

Step 5: Create Migrations for Each Database

To create tables in different databases, specify the connection in migrations:

Example: Creating a users Table in the Primary Database

Schema::connection('mysql')->create('users', function ($table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamps();
});

Example: Creating a products Table in the Secondary Database

Schema::connection('mysql2')->create('products', function ($table) {
    $table->id();
    $table->string('name');
    $table->decimal('price');
    $table->timestamps();
});

Example: Creating a orders Table in the Third Database

Schema::connection('mysql3')->create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->references('id')->on('primary_db.users');
    $table->foreignId('product_id')->references('id')->on('secondary_db.products');
    $table->string('name');
    $table->integer('total_amount');
    $table->timestamps();
});

Run Migrations

• For the default database:

  php artisan migrate

• For a specific database:

  php artisan migrate --database=mysql2

Step 6: Define Models with Database Connections

Specify the database connection in your models:

User Model (Primary DB)

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    protected $connection = 'mysql';
    protected $table = 'users';
}

Product Model (Secondary DB)

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    protected $connection = 'mysql2';
    protected $table = 'products';
}

Order Model (Third DB) with Relationships

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Order extends Model
{
    protected $connection = 'mysql3';
    protected $table = 'orders';

    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function product()
    {
        return $this->belongsTo(Product::class);
    }
}

Step 7: Query Data from Multiple Databases

Fetching Users (Primary DB)

use App\Models\User;

public function getUsers()
{
    return User::all(); // Fetches from 'mysql' connection
}

Fetching Products (Secondary DB)

use App\Models\Product;

public function getProducts()
{
    return Product::all(); // Fetches from 'mysql2' connection
}

Fetching Orders with Relationships (Cross-Database)

use App\Models\Order;

public function getOrders()
{
    return Order::with(['user', 'product'])->get();
}

Conclusion

By following these steps, you can efficiently manage multiple databases in Laravel. This setup allows you to:

• Separate data logically (e.g., users, products, orders)
• Improve performance by distributing database load
• Maintain cleaner code with well-defined models and connections

Now you’re ready to build scalable Laravel applications with multiple databases! 

Happy Coding! 🚀