How to Use Two or More Databases in One Laravel Project

Laravel
17 Nov 2024 01:59 AM

In modern applications, you may encounter scenarios where you need to connect to multiple databases within the same Laravel project. For instance, one database might handle user authentication, while another manages transactional data. Laravel makes this task seamless by allowing multiple database configurations and switching connections dynamically. Here’s a step-by-step guide:

1. Configure Multiple Database Connections

Laravel stores its database connection configurations in the config/database.php file. To connect to multiple databases, follow these steps:

Step 1.1: Add New Connections

Open the config/database.php file and add new connections under the connections array. Here's an example with three databases:

'connections' => [

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

    // Second database connection
    'second_mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_SECOND_HOST', '127.0.0.1'),
        'port' => env('DB_SECOND_PORT', '3306'),
        'database' => env('DB_SECOND_DATABASE', 'database2'),
        'username' => env('DB_SECOND_USERNAME', 'root'),
        'password' => env('DB_SECOND_PASSWORD', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
    ],

    // Third database connection (if needed)
    'third_mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_THIRD_HOST', '127.0.0.1'),
        'port' => env('DB_THIRD_PORT', '3306'),
        'database' => env('DB_THIRD_DATABASE', 'database3'),
        'username' => env('DB_THIRD_USERNAME', 'root'),
        'password' => env('DB_THIRD_PASSWORD', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
    ],
],

Step 1.2: Update .env File

Add the corresponding environment variables for each database connection:

# Default Database
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=database1
DB_USERNAME=root
DB_PASSWORD=

# Second Database
DB_SECOND_HOST=127.0.0.1
DB_SECOND_PORT=3306
DB_SECOND_DATABASE=database2
DB_SECOND_USERNAME=root
DB_SECOND_PASSWORD=

# Third Database
DB_THIRD_HOST=127.0.0.1
DB_THIRD_PORT=3306
DB_THIRD_DATABASE=database3
DB_THIRD_USERNAME=root
DB_THIRD_PASSWORD=

2. Querying Multiple Databases

Once you’ve configured the connections, Laravel allows you to specify which connection to use for queries.

Step 2.1: Using DB Facade

The DB facade allows you to query different databases by specifying the connection name:

use Illuminate\Support\Facades\DB;

// Query default database
$users = DB::table('users')->get();

// Query second database
$orders = DB::connection('second_mysql')->table('orders')->get();

// Query third database
$products = DB::connection('third_mysql')->table('products')->get();

Step 2.2: Using Models

You can also specify the database connection for individual Eloquent models by overriding the $connection property:

<?php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    // Default connection
    protected $connection = 'mysql';
}

class Order extends Model
{
    // Second database connection
    protected $connection = 'second_mysql';
}

class Product extends Model
{
    // Third database connection
    protected $connection = 'third_mysql';
}

Now you can use these models as usual:

$users = User::all();         // Fetch users from the default database
$orders = Order::all();       // Fetch orders from the second database
$products = Product::all();   // Fetch products from the third database

3. Running Migrations on Multiple Databases

To run migrations for different databases, you can use the --database option with the artisan migrate command.

Step 3.1: Migrate Specific Databases

For the default database:

php artisan migrate

For the second database:

php artisan migrate --database=second_mysql

For the third database:

php artisan migrate --database=third_mysql

4. Using Transactions Across Databases

If you need to maintain consistency across multiple databases during a transaction, you can use the DB facade with multiple connections:

use Illuminate\Support\Facades\DB;

DB::transaction(function () {
    DB::connection('mysql')->table('users')->insert([
        'name' => 'John Doe',
        'email' => 'john@example.com',
    ]);

    DB::connection('second_mysql')->table('orders')->insert([
        'user_id' => 1,
        'product' => 'Laptop',
    ]);
});

5. Example Use Case: Synchronizing Data Across Databases

You may want to fetch data from one database and store it in another. Here's an example:

use Illuminate\Support\Facades\DB;

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

foreach ($users as $user) {
    DB::connection('second_mysql')->table('archived_users')->insert([
        'name' => $user->name,
        'email' => $user->email,
    ]);
}

Conclusion

Using multiple databases in Laravel is straightforward and flexible. Whether through the DB facade or Eloquent models, Laravel provides powerful tools to manage and query multiple databases seamlessly. By following this guide, you can easily configure, query, and maintain multiple databases within a single Laravel project.

 

 

 

 

4
22