Overview

Using multiple databases in Laravel is straightforward and can be achieved by configuring database connections in config/database.php and using them in models, queries, and migrations. Here’s how:

Step 1: Configure Database Connections

Open config/database.php and add multiple database connections under the 'connections' array.

return [
    'default' => env('DB_CONNECTION', 'mysql'),
    'connections' => [
        'mysql' => [
                // Primary Database
                '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' => '',
                'strict' => true,
                'engine' => null,
            ],
        'secondary_db' => [
            // Secondary Database
            'driver' => 'mysql',
            'host' => env('DB2_HOST', '127.0.0.1'),
            'port' => env('DB2_PORT', '3306'),
            'database' => env('DB2_DATABASE', 'secondary_db'),
            'username' => env('DB2_USERNAME', 'root'),
            'password' => env('DB2_PASSWORD', ''),
                'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],
    ],
];

Now, update your .env file:

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

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

Step 2: Create Models for Each Database

To manage data from multiple databases, set the $connection property inside each model.

User Model (Primary DB)

namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class User extends Model {
    protected $connection = 'mysql'; // Primary DB
    protected $table = 'users';
}

Property Model (Secondary DB)

namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Property extends Model {
    protected $connection = 'secondary_db'; // Secondary DB
    protected $table = 'properties';
}

Step 3: Running Migrations for Each Database

To run migrations on different databases, specify the connection inside the migration file.

Migration for Users (Primary DB)

use Illuminate\Database\Migrations\Migration; 
use Illuminate\Database\Schema\Blueprint; 
use Illuminate\Support\Facades\Schema;  
class CreateUsersTable extends Migration {

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

    public function down()
    {
        Schema::connection('mysql')->dropIfExists('users');
    }
}

Migration for Properties (Secondary DB)

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint; 
use Illuminate\Support\Facades\Schema;
class CreatePropertiesTable extends Migration {
    public function up()
    {
        Schema::connection('secondary_db')->create('properties', function (Blueprint $table) {
            $table->id();
            $table->string('title');
            $table->text('description');
            $table->decimal('price', 10, 2);
            $table->timestamps();
        });
    }
    
    
    public function down()
    {
        Schema::connection('secondary_db')->dropIfExists('properties');
    }
}

Run migrations separately:

php artisan migrate --database=mysql 
php artisan migrate --database=secondary_db

Step 4: Querying Data from Multiple Databases

Now you can query from different databases easily.

Fetching Users (Primary DB)

$users = User::all();

Fetching Properties (Secondary DB)

$properties = Property::all();

Using Query Builder

// Uses default connection 
$users = DB::table('users')->get();

// Uses secondary database
$properties = DB::connection('secondary_db')->table('properties')->get();

Step 5: Using Multiple Databases in Transactions

If you want to insert data into both databases in a transaction:

use Illuminate\Support\Facades\DB;
DB::transaction(function () {
    DB::table('users')->insert([
        'name' => 'John Doe',
        'email' => 'john@example.com'
    ]);

    DB::connection('secondary_db')->table('properties')->insert([
        'title' => 'Luxury Villa',
        'description' => 'A beautiful villa in California',
        'price' => 500000
    ]);
});

Step 6: Set Connection Dynamically (Optional)

If you want to change the database connection dynamically in runtime:

$property = new Property(); 
$property->setConnection('secondary_db'); 
$property->title = "Modern Apartment"; 
$property->description = "Located in downtown"; 
$property->price = 200000; 
$property->save();

Conclusion

Laravel provides excellent support for multiple databases. You can configure them in config/database.php and use them in Eloquent, Query Builder, Migrations, and Transactions.

Now question comes, how to perform joining and setting up models relationships. Here is how you will do.

Table Joining across multiple databases

Joining tables across multiple databases in Laravel can be tricky because Laravel’s Eloquent ORM and Query Builder don’t support direct joins between different database connections. However, you can achieve this in a few different ways:

Method 1: Using Raw SQL Queries (Best Performance)

If both databases are on the same MySQL server, you can use fully qualified table names in raw SQL.

$results = DB::select("     
SELECT users.name, properties.title, properties.price     
FROM primary_db.users      
JOIN secondary_db.properties ON users.id = properties.user_id 
");

If you want to execute this using Laravel’s Query Builder:

$results = DB::table('users')
->join('secondary_db.properties', 'users.id', '=', 'properties.user_id')
->select('users.name', 'properties.title', 'properties.price')
->get();

✅ Pros: Fast, simple, efficient
❌ Cons: Works only if both databases are on the same MySQL server.

Method 2: Fetch Data Separately and Merge in PHP

If the databases are on different servers or Laravel connections, you can fetch data separately and merge them in PHP.

$users = DB::connection('mysql')->table('users')->get();
$properties = DB::connection('secondary_db')->table('properties')->get();

$mergedData = $users->map(function ($user) use ($properties) {
    $user->properties = $properties->where('user_id', $user->id);
    return $user;
});

return $mergedData;

✅ Pros: Works across different database servers
❌ Cons: Can be slower for large datasets

Method 3: Using Laravel Collections to Manually Join Data

If the datasets are not too large, you can use Laravel collections.

$users = collect(DB::connection('mysql')->table('users')->get());
$properties = collect(DB::connection('secondary_db')->table('properties')->get());

$mergedData = $users->map(function ($user) use ($properties) {
    $user->property = $properties->firstWhere('user_id', $user->id);
    return $user;
});

return $mergedData;

✅ Pros: Clean, Laravel-way of handling data
❌ Cons: Performance issues with large datasets

If you control the MySQL database, you can create a database link to allow cross-database queries.

  1. Ensure Both Databases Are on the Same Server

  2. Create a Database Link (MySQL Example):

    CREATE VIEW joined_data AS SELECT u.name, p.title, p.price 
FROM primary_db.users u 
JOIN secondary_db.properties p 
ON u.id = p.user_id;

  3. Query it in Laravel:

    $results = DB::table('joined_data')->get();

✅ Pros: Efficient, fast
❌ Cons: Requires database admin access, setup overhead

Which Method Should You Use?

  • ✅ Same MySQL Server? → Use Method 1 (Raw SQL)
  • ✅ Different Servers? → Use Method 2 (Fetch Separately)
  • ✅ Smaller Data? → Use Method 3 (Collections)
  • ✅ Performance Critical? → Use Method 4 (Database Link)

Eloquent Modal relationships accross diffrent database

Since Laravel Eloquent relationships do not support direct relationships across different database connections, you cannot use hasOne, hasMany, or belongsTo directly between models in different databases. However, there are workarounds that allow you to link data between models manually. Here’s how you can implement relationships between User (in mysql database) and Property (in secondary_db database).

Solution 1: Manual Relationship Using a Custom Method

Since Eloquent relationships do not work across multiple databases, you need to define a custom method in the User model to fetch related properties.

Step 1: Define Models

User Model (App\Models\User.php)

namespace App\Models;
use Illuminate\Database\Eloquent\Model; 
use Illuminate\Support\Facades\DB;
class User extends Model {
    protected $connection = 'mysql'; // Primary DB
    protected $table = 'users';
    
    // Custom relationship function to fetch properties from secondary DB

    public function properties()     
    {
        return DB::connection('secondary_db')
            ->table('properties')
            ->where('user_id', $this->id)
            ->get();
    }
}

Property Model (App\Models\Property.php)

namespace App\Models;  
use Illuminate\Database\Eloquent\Model;  
class Property extends Model {     
    protected $connection = 'secondary_db'; // Secondary DB     
    protected $table = 'properties';      
    public function user()     
    {         
        return DB::connection('mysql')             
            ->table('users')             
            ->where('id', $this->user_id)             
            ->first();    
    }
}

Step 2: Fetch Data Using Custom Relationship

Now, you can use the relationship like this:

// Fetch a user with related properties 
$user = User::find(1); 
$properties = $user->properties(); // Custom function to fetch related properties  
// Fetch a property with the related user 
$property = Property::find(1); 
$user = $property->user();

✅ Pros: Works seamlessly, no complex SQL required
❌ Cons: No automatic Eloquent relationship features (lazy loading, eager loading)

Solution 2: Fetch Data and Merge with Collections

Another way is to fetch users and properties separately and merge the data using Laravel collections.

$users = User::all(); 
$properties = Property::all();  

// Merge users with their properties 
$usersWithProperties = $users->map(function ($user) use ($properties) {
    $user->properties = $properties->where('user_id', $user->id);
        return $user; 
});  
return $usersWithProperties;

✅ Pros: Uses Laravel collections, simple
❌ Cons: Less efficient for large datasets

Solution 3: Using a Manual Join with Query Builder

If both databases are on the same MySQL server, you can use a raw SQL join.

$results = DB::select("
    SELECT users.name, properties.title, properties.price
    FROM primary_db.users
    JOIN secondary_db.properties ON users.id = properties.user_id ");
return $results;

✅ Pros: Fastest way if databases are on the same server
❌ Cons: Won’t work if databases are on different servers

Which Solution Should You Use?

  • ✅ Different Database Servers? → Use Solution 1 (Custom Method)
  • ✅ Laravel Way, Small Data? → Use Solution 2 (Collections)
  • ✅ Same MySQL Server? → Use Solution 3 (Raw SQL Join)