Multi-Tenant SaaS Development Guide: Database Patterns & Code Recipes

Designing a Software-as-a-Service (SaaS) application requires a fundamental architectural decision: How do you isolate client data? In SaaS, multiple clients (tenants) access the same core application. If your data isolation is weak, a bug in your code could accidentally expose sensitive customer files or financial records to another tenant.

This guide compares multi-tenant database patterns and provides code recipes for implementing Row-Level Security (RLS) in PostgreSQL, the standard for modern SaaS development.

1. Comparing Multi-Tenant Database Patterns

There are three common approaches to separating tenant data in a relational database:

A. Isolated Databases (Database-per-Tenant)

Each tenant gets a completely separate database server.

• Pros: Maximum security, easy data backups per client.
• Cons: Very expensive, high maintenance overhead (running 100 Postgres instances), difficult to execute global schema migrations.

B. Isolated Schemas (Schema-per-Tenant)

All tenants share one database, but each gets a separate namespace (schema) containing duplicate tables.

• Pros: Better resource utilization than database-per-tenant.
• Cons: Hard to run analytical queries across tenants, schema migrations must be repeated for each schema, connection pooling becomes complex.

C. Shared Database, Shared Schema (Recommended)

All tenants share the same database tables. Rows are separated using a tenant_id column.

• Pros: Extremely cost-effective, easy to scale, database migrations are run once.
• Cons: High risk of data leakage if the application layer fails to filter queries properly.

Trustoryx recommendation: Use the Shared Database, Shared Schema pattern, but harden it using PostgreSQL Row-Level Security (RLS). This shifts the responsibility of data isolation from your application code directly to the database engine.

2. Implementing Postgres RLS for SaaS (Code Recipe)

Let's walk through how to configure a secure, multi-tenant database using Postgres RLS.

Step 1: Create the Tables

Define your tenants and client data tables, enforcing a foreign key constraint: `sql CREATE TABLE tenants ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() );

CREATE TABLE client_leads ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE, name TEXT NOT NULL, email TEXT NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() ); `

Step 2: Enable Row-Level Security

Enable security on the table. By default, this blocks all access for non-superuser connections: `sql ALTER TABLE client_leads ENABLE ROW LEVEL SECURITY; `

Step 3: Define the Isolation Policy

Create a policy that inspects the current tenant context. We can set a local config parameter during the database session: `sql CREATE POLICY tenant_isolation_policy ON client_leads FOR ALL USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::UUID); `

Step 4: Applying in Your Application Code (Node/Next.js)

When handling an incoming request in your server, execute a transaction that sets the local session parameter before running queries: `typescript import { createClient } from '@supabase/supabase-js';

export async function getClientLeads(tenantId: string) { const pgClient = await pool.connect(); try { await pgClient.query('BEGIN'); // Set the tenant context for this connection session await pgClient.query('SELECT set_config($1, $2, true)', ['app.current_tenant_id', tenantId]);

// Run the query - RLS will automatically filter by tenantId const res = await pgClient.query('SELECT * FROM client_leads'); await pgClient.query('COMMIT'); return res.rows; } catch (err) { await pgClient.query('ROLLBACK'); throw err; } finally { pgClient.release(); } } `

3. Next.js Dynamic Subdomain Routing

To provide tenant-specific branding (e.g., clientA.yourdomain.com vs clientB.yourdomain.com), configure Next.js Middleware to extract the subdomain and rewrite the request path:

`typescript import { NextResponse } from 'next/server'; import type { NextRequest } from 'next/server';

export function middleware(request: NextRequest) { const url = request.nextUrl; const hostname = request.headers.get('host') || '';

// Extract subdomain (excluding www) const subdomain = hostname.split('.')[0]; if (subdomain && subdomain !== 'www' && subdomain !== 'localhost:3000') { // Rewrite requests to the dynamic tenant directory return NextResponse.rewrite(new URL(/tenant/${subdomain}${url.pathname}, request.url)); }

return NextResponse.next(); } `

Build Scalable SaaS with Trustoryx

At Trustoryx, we build high-performance, secure multi-tenant SaaS products from scratch. Our database experts configure robust PostgreSQL RLS rules, optimize query plans, and implement subdomain routing pipelines to deliver enterprise-grade software.

Contact us today to speak with a systems architect about your SaaS database design.