Multi-Tenancy Done Right: Tenant-Specific Database Tables

When building a multi-tenant application with Laravel and PostgreSQL, handling data isolation is crucial. One effective approach is to use separate database schemas for each tenant. This provides a high level of isolation, ensuring that data from different tenants remains completely separate. Let's explore how this works in practice within the Reimpact platform.

The Problem: Shared Tables

A common pitfall in multi-tenant applications is using a single database table to store data for all tenants, differentiating records by a tenant_id column. While seemingly simple, this approach can lead to several issues:

• Data Security: Risk of accidental data leakage or cross-tenant access.
• Performance: Queries become slower as the table grows with data from multiple tenants.
• Complexity: Increased complexity in queries and application logic to filter data by tenant.

The Solution: Tenant-Specific Tables

A more robust solution is to create separate tables for each tenant within their respective database schemas. In the Reimpact platform, this means that instead of a single public.rates table, each tenant has its own packaging_rates table within their schema. This approach offers several advantages:

• Data Isolation: Data is physically separated, eliminating the risk of cross-tenant access.
• Performance: Queries are faster since they only operate on a single tenant's data.
• Simplicity: Simplified queries and application logic, as tenant filtering is no longer required.

Implementing Tenant-Specific Tables

To implement tenant-specific tables, you can leverage PostgreSQL's schema feature. Each tenant gets its own schema, and tables are created within that schema. Laravel's database connection can then be configured to use the appropriate schema based on the current tenant.

Here's an example of how you might structure your Laravel application to work with tenant-specific schemas:

<?php

namespace App\Services;

use Illuminate\Support\Facades\DB;

class TenantService
{
    public function setTenantSchema(string $tenantId):
    {
        DB::statement('SET search_path TO ' . $tenantId);
    }
}

This TenantService class sets the search_path for the database connection to the tenant's schema. This ensures that all subsequent database queries will operate within that schema.

Dashboard Considerations

When querying across tenants for dashboard purposes, ensure your SQL functions correctly reference the tenant-specific tables. Instead of directly querying public.rates, dynamically construct the table name based on the tenant context, such as querying packaging_rates within the tenant's schema.

Key Takeaways

Using tenant-specific database tables provides a robust and scalable solution for multi-tenancy. It enhances data isolation, improves performance, and simplifies application logic. When designing your multi-tenant application, consider the benefits of tenant-specific tables to ensure a secure and efficient system.