In Laravel, you can configure multiple database connections and switch between them dynamically during runtime. Here’s a step-by-step guide on how to set this up:

Step 1: Configure Multiple Database Connections

In your config/database.php file, you can define multiple database connections by adding entries to the connections array. For example, you could have two MySQL connections like this:

‘connections’ => [
‘mysql’ => [
‘driver’ => ‘mysql’,
‘host’ => env(‘DB_HOST’, ‘127.0.0.1’),
‘port’ => env(‘DB_PORT’, ‘3306’),
‘database’ => env(‘DB_DATABASE’, ‘default_db’),
‘username’ => env(‘DB_USERNAME’, ‘root’),
‘password’ => env(‘DB_PASSWORD’, ”),
‘charset’ => ‘utf8mb4’,
‘collation’ => ‘utf8mb4_unicode_ci’,
‘prefix’ => ”,
‘strict’ => true,
‘engine’ => null,
],

‘mysql2’ => [
‘driver’ => ‘mysql’,
‘host’ => env(‘DB_HOST_SECOND’, ‘127.0.0.1’),
‘port’ => env(‘DB_PORT_SECOND’, ‘3306’),
‘database’ => env(‘DB_DATABASE_SECOND’, ‘second_db’),
‘username’ => env(‘DB_USERNAME_SECOND’, ‘root’),
‘password’ => env(‘DB_PASSWORD_SECOND’, ”),
‘charset’ => ‘utf8mb4’,
‘collation’ => ‘utf8mb4_unicode_ci’,
‘prefix’ => ”,
‘strict’ => true,
‘engine’ => null,
],
],


Here, we have two connections defined:

  • mysql: the default connection.
  • mysql2: a second connection that points to a different database.

Step 2: Set Up Your .env File

In your .env file, you should define the variables for the two connections. For example:

DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=default_db
DB_USERNAME=root
DB_PASSWORD=

DB_HOST_SECOND=127.0.0.1
DB_PORT_SECOND=3306
DB_DATABASE_SECOND=second_db
DB_USERNAME_SECOND=root
DB_PASSWORD_SECOND=


Step 3: Use Multiple Database Connections

You can specify which database connection to use when querying models or running raw queries.

Using Eloquent Models with Different Connections

In your Eloquent models, you can specify the connection for a model by setting the $connection property:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
protected $connection = ‘mysql’; // Use the default connection
}

If you want to use the second database connection:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class UserFromSecondDb extends Model
{
protected $connection = ‘mysql2’; // Use the second database connection
}

Running Raw Queries with Different Connections

You can use the DB::connection() method to run raw queries on a specific connection:

use Illuminate\Support\Facades\DB;

// Using the default connection
$users = DB::connection(‘mysql’)->select(‘SELECT * FROM users’);

// Using the second connection
$usersFromSecondDb = DB::connection(‘mysql2’)->select(‘SELECT * FROM users’);

Step 4: Dynamically Switch Database Connections at Runtime

If you need to switch the database connection dynamically (e.g., based on user input or runtime conditions), you can do so by setting the connection on the fly. Here are two ways to achieve this:

Option 1: Dynamically Change the Database Connection in Eloquent Models

You can change the database connection dynamically within a model like this:

use App\Models\User;

class UserService
{
public function getUsersFromDifferentDatabase($connectionName)
{
$userModel = new User();
$userModel->setConnection($connectionName); // Set the connection dynamically

return $userModel->all(); // Query the new database
}
}

Now, if you call:

$usersFromSecondDb = (new UserService())->getUsersFromDifferentDatabase(‘mysql2’);

It will switch the connection dynamically and fetch data from the second database.

Option 2: Change the Database Connection Globally

If you need to change the connection for all queries dynamically (e.g., in a multi-tenant application), you can update the default database connection in the config at runtime.

In a controller or service:

use Illuminate\Support\Facades\Config;

class DatabaseSwitcher
{
public function switchDatabase($dbName, $dbUsername, $dbPassword)
{
Config::set(‘database.connections.mysql.database’, $dbName);
Config::set(‘database.connections.mysql.username’, $dbUsername);
Config::set(‘database.connections.mysql.password’, $dbPassword);

// Reconnect to apply the new settings
\DB::purge(‘mysql’);
\DB::reconnect(‘mysql’);
}
}

To use this:

$databaseSwitcher = new DatabaseSwitcher();
$databaseSwitcher->switchDatabase(‘new_database_name’, ‘new_user’, ‘new_password’);

This approach changes the default database dynamically by updating the config settings.

Step 5: Reconnect the Database

After changing the database configuration dynamically, you might need to reconnect the database to apply the changes. Use the DB::purge() and DB::reconnect() methods to reset the connection:

DB::purge(‘mysql’); // Purge the current connection
DB::reconnect(‘mysql’); // Reconnect with the new settings

Conclusion

By setting up multiple database connections in Laravel and using the setConnection() method or updating the configuration at runtime, you can easily switch between different databases dynamically. This flexibility is especially useful in multi-tenant systems or situations where you need to work with multiple databases within the same application.

Share!

Shares