Multi-Tenancy with PostgreSQL Schemas in Laravel

Introduction

When building multi-tenant applications, choosing the right architecture is critical. This post explores how to leverage PostgreSQL schemas within a Laravel application to achieve tenant isolation, enhance security, and improve scalability.

The Multi-Tenant Challenge

Traditional multi-tenant approaches often involve sharing a single database and distinguishing tenants by a tenant ID column in each table. This can lead to performance bottlenecks, security risks, and difficulties in managing tenant-specific data. PostgreSQL schemas offer an alternative: creating separate, isolated environments within the same database instance.

PostgreSQL Schemas for Tenant Isolation

PostgreSQL schemas provide a namespace mechanism within a database. Each tenant gets its own schema, effectively isolating its data and database objects (tables, views, functions). This approach offers several advantages:

• Data Isolation: Tenant data is physically separated, reducing the risk of accidental data leakage.
• Security: Each tenant operates within its own secure namespace, limiting access to its data.
• Scalability: Tenant-specific queries can be optimized without affecting other tenants.
• Simplified Management: Tenant schemas can be easily backed up, restored, or migrated independently.

Implementing Tenant Context in Laravel

To use PostgreSQL schemas in a Laravel application, we need a mechanism to dynamically switch the database connection to the correct schema for each tenant. A service or middleware can handle this:

<?php

namespace App\Services;

use Illuminate\Support\Facades\DB;

class TenantContext
{
    public function setTenant(string $tenantId):
    {
        DB::statement("SET search_path TO '$tenantId', public;");
    }
}

This TenantContext class sets the search_path for the current database connection. The search_path determines the order in which schemas are searched when resolving unqualified object names. Setting it to the tenant ID ensures that the tenant's schema is searched first. The public schema is included as a fallback for shared data or extensions.

Laravel Eloquent and Tenant Tables

To automatically resolve table names to the correct tenant schema, a trait can be used by Eloquent models:

<?php

namespace App\Concerns;

use Illuminate\Support\Facades\DB;

Trait HasTenantTable
{
    protected static function bootHasTenantTable()
    {
        static::creating(function ($model) {
            $model->setTable(DB::getConfig('prefix') . '.' . $model->getTable());
        });
    }
}

This HasTenantTable trait automatically prefixes the table name with the tenant ID when creating new records. You may need to adjust the table setting logic based on your application needs.

Jobs and Schema Context

When queuing jobs, it's crucial to maintain the tenant context. The TenantContext should be applied within the job's handle method to ensure that database operations are performed in the correct schema.

Conclusion

PostgreSQL schemas offer a robust and scalable solution for building multi-tenant Laravel applications. By isolating tenant data at the database level, you can enhance security, improve performance, and simplify management. Consider exploring PostgreSQL schemas for your next multi-tenant project to leverage these benefits.