How to Run Laravel Using Multiple Database Connections on the Fly

-

Recently I’ve found myself in a scenario where I had to run multiple database connections in a single Laravel application. Pretty easy right? There are tons of tutorials online and Eloquent makes it so elegant anyway. Well not so much in my case.

Imagine this scenario. There is one main database, with a table inside called “clients”. Each row represents a client and each client has it’s own database with identical structure as all the other clients’ databases. So, in my Laravel application there are models that have a table in the clients database, but they have no connection defined.

With the power of Laravel’s Service Containers and Facades let me show you how easy and elegant it is.

TL;DR

Too lazy to read the whole article? Jump straight into:

Install Laravel

I’m using Laravel 7.0 in this tutorial so make sure your environment meets it’s requirements. Make sure you have it installed and connected to the main database. If you have any issues in setting up Laravel refer to it’s official Documentation Page.

First Things First

Let’s create a model named Client with it’s migration.

php artisan make:model Client --migration

Pretty straightforward, an empty model.

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Client extends Model
{
    //
}

In it’s migrations I have created a column for the client’s name, and then the MySQL credentials for his database, hence host, username, password and database. You may have to add a column for the database port as well but since I’m running on localhost I’m not gonna bother with it.

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateClientsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('clients', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('host');
            $table->string('username');
            $table->string('password');
            $table->string('database')->nullable();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('clients');
    }
}

Service Container

My idea was basically, whenever I’m calling a model that has it’s table inside the client’s database I don’t wanna write raw queries, I don’t wanna use the on() method on Eloquent every time I’m trying to retrieve a row, and most important of all I don’t wanna have credentials for every database stored inside my config file.

I needed a singleton class to store the current current connection. So I’ve written this:

<?php

namespace App\Services;

use App\Client;
use Illuminate\Support\Facades\Config;

class SlaveConnection
{
    /**
     * @var Client
     */
    private $client;

    /**
     * SlaveConnection constructor.
     * @param Client $client
     */
    public function __construct(Client $client)
    {
        $this->client = $client;
    }

    /**
     * @param int $id
     */
    public function setConnection(int $id)
    {
        $this->client = $this->client->findOrFail($id);

        Config::set('database.connections.' . $this->client->database, [
            'driver'   => 'mysql',
            'host'     => $this->client->host,
            'port'     => 3306,
            'database' => $this->client->database,
            'username' => $this->client->username,
            'password' => $this->client->password,
        ]);
    }

    /**
     * @return string
     */
    public function getConnectionName()
    {
        return $this->client->database;
    }
}

and instantiated it inside the AppServiceProvider.

...
    public function register()
    {
        $this->app->singleton(SlaveConnection::class);
    }
...

What happens here is inside the Service Provider an singleton is instantiated and using Laravel’s Dependency Injection Container an instance of SlaveConnection is created.

Then, we can use setConnection() method to get the client by his id and store the MySQL connection inside the config.

getConnectionName() simply returns the name of the key that holds the connection’s credentials. It must be a unique string, I have chosen the database name, you can choose anything else, maybe even make a column that holds some unique value.

I wanted to get more fancy so I created a Facade for SlaveConnection just to have cleaner and more elegant code.

<?php

namespace App\Facades;

use Illuminate\Support\Facades\Facade;

/**
 * @method static void setConnection(int $id)
 * @method static string getConnectionName()
 *
 * @see \App\Services\SlaveConnection
 */
class Slave extends Facade
{
    /**
     * Get the registered name of the component.
     *
     * @return string
     */
    protected static function getFacadeAccessor()
    {
        return \App\Services\SlaveConnection::class;
    }
}

Make sure you have commented SlaveConnection‘s methods above the class so your code editor recognizes them.

Clients’ Models

All the models that connect to the client’s databases should extend one base model which I named SlaveModel.

php artisan make:model SlaveModel

In SlaveModel we only define the database connection in it’s constructor. Finally, we get to use the fancy Facade we have created.

<?php

namespace App;

use App\Facades\Slave;
use Illuminate\Database\Eloquent\Model;

class SlaveModel extends Model
{
    public function __construct(array $attributes = [])
    {
        parent::__construct($attributes);

        $this->setConnection(Slave::getConnectionName());
    }
}

Now, let’s create a model that’s gonna extend SlaveModel with it’s migrations.

php artisan make:model SlaveModels/Order --migration

Move the Order migration file inside a sub-folder, /database/migrations/clients so we can run the migrations separately.

<?php

namespace App\SlaveModels;

use App\SlaveModel;

class Order extends SlaveModel
{
    //
}

Migrations

In the migration file, we get all the clients then we run an each loop on the Collection. Inside the loop we set the connection using the Slave Facade and create a table on the connection we have just set. I am creating a simple table structure with just the id, name and timestamps.

<?php

use App\Client;
use App\Facades\Slave;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateOrdersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        $clients = Client::all();

        $clients->each(function ($client) {
            Slave::setConnection($client->id);

            Schema::connection(Slave::getConnectionName())->create('orders', function (Blueprint $table) {
                $table->id();
                $table->string('name');
                $table->timestamps();
            });
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        $clients = Client::all();

        $clients->each(function ($client) {
            Slave::setConnection($client->id);

            Schema::connection(Slave::getConnectionName())->dropIfExists('orders');
        });
    }
}

Factories & Seeders

Using Faker let’s create some dummy data for clients and orders.

php artisan make:factory ClientFactory
php artisan make:factory OrderFactory
<?php

/** @var Factory $factory */

use App\Client;
use Faker\Generator as Faker;
use Illuminate\Database\Eloquent\Factory;

$factory->define(Client::class, function (Faker $faker) {
    return [
        'name' => $faker->firstName . ' ' . $faker->lastName,
        'host' => 'localhost',
        'username' => 'root',
        'password' => '',
    ];
});
<?php

/** @var Factory $factory */

use App\SlaveModels\Order;
use Faker\Generator as Faker;
use Illuminate\Database\Eloquent\Factory;

$factory->define(Order::class, function (Faker $faker) {
    return [
        'name' => $faker->sentence(4),
    ];
});

Then let’s make Seeders for them.

php artisan make:seeder ClientSeeder
php artisan make:seeder OrderSeeder
<?php

use App\Client;
use Illuminate\Database\Seeder;

class ClientSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        factory(Client::class, 5)->create()->each(function ($client, $key) {
            $client->database = 'client_' . $client->id;
            $client->save();
        });
    }
}
<?php

use App\Client;
use App\Facades\Slave;
use App\SlaveModels\Order;
use Illuminate\Database\Seeder;

class OrderSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $clients = Client::all();

        $clients->each(function ($client) {
            Slave::setConnection($client->id);

            factory(Order::class, 100)->connection(Slave::getConnectionName())->create();
        });
    }
}

Inside the run() of DatabaseSeeder class call the seeder for the clients table

...
	public function run()
    {
       	$this->call(ClientSeeder::class);
    }
...

Finally, create 5 databases named client_1, client_2, client_3, client_4 and client_5 and run the migrations and seeders.

Note that migrations won’t run on orders since we put the file inside a sub-folder.

php artisan migrate
php artisan db:seed

Then run the migrations from the clients sub-folder with --path option and seed the orders by specifying the Seeder class with --class option.

php artisan migrate --path=database/migrations/clients
php artisan db:seed --class=OrderSeeder

Controllers, Routes and Views

I’m not gonna go into depth about Controllers, Routes and Views, refer to the official Laravel Documentation for assistance.

Just like in the migration files we set the connection first before getting the orders with Order::all(). Connection is already defined in SlaveModel so we don’t have to worry about that.

<?php

namespace App\Http\Controllers;

use App\Client;
use App\Facades\Slave;
use App\SlaveModels\Order;

class ClientController extends Controller
{
    public function index()
    {
        $clients = Client::all();

        return view('clients', compact('clients'));
    }

    public function orders($id)
    {
        Slave::setConnection($id);

        $client = Client::findOrFail($id);
        $orders = Order::all();

        return view('orders', compact(['client', 'orders']));
    }
}
Route::get('/', 'ClientController@index');
Route::get('/clients/{id}/orders', 'ClientController@orders')->name('client.orders');
@extends('layouts.app')

@section('content')

    <table>
        <thead>
            <tr>
                <th>#</th>
                <th>Name</th>
            </tr>
        </thead>
        <tbody>
            @foreach($clients as $client)
                <tr>
                    <td>{{ $client->id }}</td>
                    <td>
                        <a href="{{ route('client.orders', $client->id) }}">
                            {{ $client->name }}
                        </a>
                    </td>
                </tr>
            @endforeach
        </tbody>
    </table>

@endsection
@extends('layouts.app')

@section('content')

    <h3>Order for Client: <strong>{{ $client->name }}</strong></h3>

    <table>
        <thead>
        <tr>
            <th>#</th>
            <th>Name</th>
        </tr>
        </thead>
        <tbody>
        @foreach($orders as $order)
            <tr>
                <td>{{ $order->id }}</td>
                <td>{{ $order->name }}</td>
            </tr>
        @endforeach
        </tbody>
    </table>

@endsection

Demonstration

If you have followed the tutorial carefully, and haven’t missed any steps you are ready to go. Serve the page on your local server.

php artisan serve

And voila!

http://127.0.0.1:8000

And when you click on each of these clients it shows the orders from each of their databases.

http://127.0.0.1:8000/clients/1/orders
http://127.0.0.1:8000/clients/2/orders

…and so on.

Recap

This is just a simple demonstration, there is so much room for improvement. Perhaps, you may want to throw a custom exception if the connection is used before it’s set, or check if client row exists before you try to pull data, etc.

Once more, you can find the complete demo project here:

Feel free to comment below, clone, fork the repo or submit a pull request.

Share this article

Recent posts

How to debug Artisan with Xdebug?

Xdebug 2 If you are using Xdebug 2 type this command in the terminal: php...

Extract Untranslated Strings from Laravel Projects using Localizator

Localizator is a small tool for Laravel that gives you the ability to extract untranslated strings from project files. It works using...

Use Laravel’s default translation strings in JavaScript

There are many great Laravel packages that offer ways to use Larave's translation strings in front-end, like mariuzzo/laravel-js-localization and thepinecode/i18n, but what...

How to Run Laravel Using Multiple Database Connections on the Fly

Recently I've found myself in a scenario where I had to run multiple database connections in a single Laravel application. Pretty easy...

Popular categories

4 COMMENTS

  1. Great resource! Now, I can freely switch DB based on login now. Thanks a lot. Also, is it possible to create databases and run migrations on the fly? Like, without cmd?

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Recent comments