Multi-tenancy in Laravel: working with a database-per-tenant model.

Updated: Apr 28, 2020 — 3 min Read#databases #multi-tenancy

In this article, we're going to explore one of the most confusing aspects of multi-tenancy in laravel applications; communicating with multiple databases.

The majority of applications just communicate with a single database. However, a considerable portion of laravel applications communicates with multiple databases. There are some neat packages that help with managing multiple connections, but it'd be useful if we understand how database connections work in Laravel, so let's dive in.

Establishing a connection with the Database

When you run a query in Laravel, the Illuminate\Database\DatabaseManager takes care of configuring a database connection to run the query on. Each connection has a unique name and you get to choose a default connection to be used when no connection name is explicitly provided:

// Uses the default connection.
DB::table('users')->all();

// Uses the "tenant" connection.
DB::connection('tenant')->table('users')->all();

The connection is created once and then re-used every time you need to run a query during the application life cycle.

The PHP Data Objects (PDO)

The PDO is a standard interface for accessing databases in PHP, Laravel uses the PDO to run all kinds of queries. However, you can configure a connection to use a separate read & write PDO objects for read/write operations. You can find more details on that subject in the official docs.

Most multi-tenancy apps that use a separate database for each tenant has a central database where details on all tenants are stored. So basically in a single application you might have a system connection and a tenant connection.

'tenant' => [
  'driver' => 'mysql',
  'host' => env('DB_HOST', '127.0.0.1'),
  'port' => env('DB_PORT', '3306'),
  // ...
],

'system' => [
  'driver' => 'mysql',
  'host' => env('DB_HOST', '127.0.0.1'),
  'port' => env('DB_PORT', '3306'),
  // ...
],

The system connection always connects to the same database, so the configuration set in the config file can always be used, and queries to this connection are pretty easy to do:

DB::connection('system')->table('tenants')->all();

It gets interesting though for when you want to run a query on the tenant connection. Since the tenant connection configurations are based on who is the current tenant, we can't put the configuration in the config/database.php file, instead we want to configure the connection on the fly.

config(['database.connections.tenant.database' => 'tenant1']);

This line of code will set the tenant connection configuration to read from the "tenant1" database, you can change any connection parameters in the same manner; like the username, password, read/write connections, anything.

Now when the DatabaseManager tries to build the tenant connection, it will use the configurations you set on the fly in the step above. However, if the tenant connection was already resolved before any changes to the config file won't take effect since the connection is cached first time it's created and Laravel won't create a new instance of the connection.

To overcome this, you need to make sure no connection is resolved before you can set the new configuration:

config(['database.connections.tenant.database' => 'tenant1']);

DB::purge('tenant');

DB::reconnect('tenant');

Using purge() and reconnect() will ensure any query that runs in the future on the tenant connection will use the configuration from above.

Where to put this piece of code

There are several entry points to a laravel application:

  1. HTTP Requests.
  2. Console Commands.
  3. Queued Jobs.

Let's create a new TenancyProvider and add it to our config/app.php file. In the register method of this provider we can configure the current tenant like this:

public function register(){
    if($this->app->runningInConsole()){
        return;
    }

    if($request->getHttpHost() == 'tenant1.app.com'){
      config(['database.connections.tenant.database' => 'tenant1']);
    
      DB::purge('tenant');
    
      DB::reconnect('tenant');
     }
}

We check the HTTP host of the current request and then assign the configuration to respect the current tenant.

As for queued jobs, we can store the tenant_id in the payload of all jobs, and while precessing the job we set the current tenant configuration same as above.

So in our service provider, we add this line:

$this->app['queue']->createPayloadUsing(function () {
      return Tenant::get() ? [
              'tenant_id' => Tenant::get()->id
             ] : [];
});

To simplify things I use Tenant::get() that should be holding the logic that identifies who is the current tenant.

Now the payload of each job will include a tenant_id if a tenant is discovered, now we can listen to the JobProcessing event and configure our database for the current tenant if one exists:

$this->app['events']->listen(\Illuminate\Queue\Events\JobProcessing::class, function($event){
    if (isset($event->job->payload()['tenant_id'])) {
        Tenant::set($event->job->payload()['tenant_id']);
    }
});

As for Console Commands; you will need to specify who is the tenant to run the code against. So each time you run a command you need to send details about which tenant to be used, and inside the command code you configure the tenant database connection as seen above.

For more ideas on how to deal with migrations, seeding, and testing. Check my video on the topic:


Hey! 👋 If you want to receive updates on what I'm up to, I host a newsletter on my website themsaid.com and would love to have you.

You can also follow me on Twitter, I regularly post about all things Laravel including my latest video tutorials and blog posts.

By Mohamed Said

Hello! I'm a full-stack web developer working at Laravel. In this publication, I share everything I know about Laravel's core, packages, and tools.

You can find me on Twitter and Github.

This site was built using Wink. Follow the RSS Feed.