Multi-Tenant SaaS with Next.js, Prisma & PostgreSQL (Practical Guide)

Multi-tenancy quietly decides whether your SaaS scales cleanly or becomes a security incident. Get it right early - you barely think about it again. Get it wrong — you're rewriting your data layer at the worst time.

👉 Full architecture guide here

The 3 isolation strategies

1. Shared schema + tenant_id column ← recommended default One database, every table has orgId. Simplest to build, works for 100–10,000 tenants comfortably.

2. Schema-per-tenant

Separate PostgreSQL schema per tenant. Good for mid-market with customization needs, but pooling gets complicated.

3. Database-per-tenant

Maximum isolation. Enterprise only - operationally the heaviest.

Start with shared schema. Graduate specific tenants later.

The rule you never break

Every Prisma query must include the tenant filter — no exceptions:

const projects = await prisma.project.findMany({
  where: { orgId: currentOrgId }, // always
  orderBy: { createdAt: "desc" },
});

One forgotten where clause leaks another tenant's data.

Add RLS as a safety net

Row-Level Security makes PostgreSQL itself enforce isolation:

ALTER TABLE "Project" ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON "Project"
  USING (org_id = current_setting('app.current_tenant_id'));

Now even if app code forgets a filter, the database won't hand over wrong tenant's rows.

---

The production gotcha nobody warns about

PgBouncer in transaction mode resets session variables between transactions - your SET app.current_tenant_id disappears before the query runs.

Fix: use session mode, or set tenant context inside the same prisma.$transaction as your query.

---

Full guide with Prisma schema, middleware tenant resolver, RLS setup, and FAQ:

👉 osamahabib.com — Multi-Tenant SaaS Guide

Comments

[Aldo]

That observation about multi-tenancy quietly deciding scale and security really resonates. We initially went down the shared database, shared schema with tenant_id column path for most of our services, primarily for operational simplicity and cost savings compared to separate databases. The biggest challenge there, and where PostgreSQL's Row Level Security becomes absolutely critical, is ensuring that every single query respects the tenant context. It's too easy for a developer to forget a WHERE clause, and that's exactly where security incidents brew.

Integrating RLS with an ORM like Prisma can be a bit nuanced. While Prisma doesn't directly expose RLS functions in its API, setting a session_variable or a custom function that Prisma then implicitly uses via RLS policies is a robust pattern. We found that wrapping our Prisma client with a tenant-aware service layer, which explicitly sets the tenant context derived from Next.js middleware, was essential. This creates a clear boundary and prevents accidental cross-tenant data access, especially with server components or API routes where the tenant context needs to be consistent across the request lifecycle.

The other major consideration is when compliance or specific client requirements push you towards stronger isolation, like separate schemas or even separate databases. While initially more complex to set up and manage, it offers a cleaner data separation boundary. The trade-off is always between initial development speed and long-term operational overhead, and it's a decision that often evolves as the product matures and new tenants come on board with varying demands.

[Endo Hayashi]

This is the kind of content I love—less theory, more real production experience. The PgBouncer note especially isn't something you see mentioned often. Looking forward to reading more of your posts!