Laravel Dynamic Database Connection

In most cases, we use a single database for our application. But in some cases, we use 2 or 3 databases. In such cases, multiple databases set up in Laravel really help to connect multiple databases. But there are some scenarios we need to use more database connections depending on the users of the application.

Let's take an example of a centralised online shop system where each shop uses different databases and uses single application. In such cases, the database needs to connect dynamically either depending on the shop or based on users. In this scenario, we can use a simple technique to create a dynamic database connection.

In the below example, the system uses one main database which stores the database details of each shop.

Let's first set up a separate MySQL database connection in the database.php config file. This will help in the later stage to set up the proper database connection for the shop.

'mysql' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    ........
],
'mysql_shop' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_SHOP_HOST', '127.0.0.1'),
    'port' => env('DB_SHOP_PORT', '3306'),
    'database' => env('DB_SHOP_DATABASE', 'forge'),
    'username' => env('DB_SHOP_USERNAME', 'forge'),
    'password' => env('DB_SHOP_PASSWORD', ''),
    .....
],

For the sake of the tutorial omitted some config values from the configuration.

So in the table and other places where we will use multiple databases, we can use mysql_shop as a database connection. But actually, it will be using a dynamic db connection from the main database.

Code for setting Dynamic DB connection

Let's assume we are storing the database details in a table named shop

$shop = Shop::FindOrFail($id);

$dbConnection = array_merge(
    config('database.connections.mysql_shop'),
    [
        "host" => $shop->db_host,
        "database" => $shop->db_database,
        "username" => $shop->db_username,
        "password" => $shop->db_password
    ]
);
Config::set("database.connections.mysql_shop", $dbConnection);

It is not recommended to store database password directly in a table. If there is a data breach the attackers will get access to all databases. To improve the security of the system we can use any encryption mechanism to store the database password in the table where its key stored in the .env file of the main application.

It is better to use this setup in a Middleware. Lets see an example using database connection in middleware.

Example Use Case in Middleware

<?php 
namespace App\Http\Middleware;

use Closure;
use Illuminate\Contracts\Auth\Guard;
use Illuminate\Support\Facades\Config;

class DatabaseSwitcher {

    /**
     * The Guard implementation.
     *
     * @var Guard
     */
    protected $auth;

    /**
     * Create a new filter instance.
     *
     * @param  Guard  $auth
     * @return void
     */
    public function __construct(Guard $auth)
    {
        $this->auth = $auth;
    }

    /**
     * Handle an incoming request.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  \Closure  $next
     * @return mixed
     */
    public function handle($request, Closure $next)
    {
        if ( !$this->auth->guest() )
        {
            $user = $this->auth->user();
            $shop = $user->shop;
            // database connection code goes here
            return $next($request);
        } else {
            return response()->json(['Access Denied'], 413);
        }
    }

}

In similar way we can use these database connection in migrations as well as in seeds for modifies tables.