Working with a database-per-tenant model in Laravel

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:

• HTTP Requests.
• Console Commands.
• 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.