How to Use Multiple Databases in Laravel

Laravel makes web development easy and efficient, but what if your application needs to interact with more than one database? Maybe you want to use different databases for different models or integrate data from an external source. Whatever your need, Laravel has you covered. In this guide, we’ll walk through how to use multiple databases in Laravel.

Let’s dive in!

Why Use Multiple Databases in Laravel?

There are various scenarios where using multiple databases becomes crucial:

• Separation of Data: Large applications often need separate databases for various modules.
• Scalability: Handling a high volume of data may require splitting databases for better performance.
• External Integrations: Sometimes, you might need to connect to external or legacy databases.

In such cases, Laravel allows you to seamlessly manage multiple database connections.

Setting Up Multiple Databases in Laravel

To use multiple databases in Laravel, you need to define different database connections in your config/database.php file. Here’s a step-by-step guide:

Step 1: Add Database Connections

Laravel supports multiple database drivers like MySQL, SQLite, and PostgreSQL. In the config/database.php file, you’ll find a section where you can define the database connections.

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'database' => env('DB_DATABASE', 'main_db'),
    'username' => env('DB_USERNAME', 'root'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

'second_db' => [
    'driver' => 'mysql',
    'host' => env('DB_SECOND_HOST', '127.0.0.1'),
    'database' => env('DB_SECOND_DATABASE', 'second_db'),
    'username' => env('DB_SECOND_USERNAME', 'root'),
    'password' => env('DB_SECOND_PASSWORD', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

Here, we are setting up two MySQL databases: main_db and second_db.

Step 2: Update the Environment File

You need to define the environment variables for both databases in your .env file:

DB_HOST=127.0.0.1
DB_DATABASE=main_db
DB_USERNAME=root
DB_PASSWORD=

DB_SECOND_HOST=127.0.0.1
DB_SECOND_DATABASE=second_db
DB_SECOND_USERNAME=root
DB_SECOND_PASSWORD=

This ensures that Laravel knows how to connect to both databases.

Step 3: Use Multiple Connections in Models

In Laravel, each model is associated with a default database connection. If you want a model to use a different connection, you can specify it inside the model like this:

class User extends Model
{
    protected $connection = 'second_db';
}

Here, the User model will use the second_db connection instead of the default one.

Step 4: Running Migrations on Multiple Databases

You can also run migrations for different databases. To do this, you can specify the connection when running the migration command:

php artisan migrate --database=second_db

This will run migrations on the second_db database.

Step 5: Querying Multiple Databases

Laravel’s query builder allows you to specify which database connection to use for each query. Here’s an example of querying from the second_db:

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

This ensures that the query is run on the second_db connection.

Tips for Working with Multiple Databases

1. Consistency: Keep your database configurations consistent across environments to avoid confusion.
2. Performance: Monitor the performance of your queries across databases and optimize as needed.
3. Security: Secure database credentials using Laravel’s environment variables.

Conclusion

Using multiple databases in Laravel opens up a wide range of possibilities for building scalable and robust applications. Whether you need to separate data or interact with external systems, Laravel’s flexible database configuration makes it all possible.

By following this guide, you can now confidently work with multiple databases in Laravel without any hassle. Happy coding!