Multi-Tenant Database
Patterns for implementing multi-tenancy in database applications.
Overview#
Multi-tenancy enables serving multiple customers from a single application:
- Data isolation between tenants
- Shared infrastructure efficiency
- Scalable architecture
- Flexible deployment options
Prerequisites:
- Prisma ORM setup
- Understanding of tenant isolation strategies
Implementation#
Schema-Based Multi-Tenancy#
1// prisma/schema.prisma
2model Organization {
3 id String @id @default(cuid())
4 name String
5 slug String @unique
6 createdAt DateTime @default(now())
7
8 users User[]
9 projects Project[]
10 settings OrganizationSettings?
11}
12
13model User {
14 id String @id @default(cuid())
15 email String
16 name String?
17 organizationId String
18 organization Organization @relation(fields: [organizationId], references: [id])
19 role Role @default(MEMBER)
20
21 @@unique([email, organizationId])
22 @@index([organizationId])
23}
24
25model Project {
26 id String @id @default(cuid())
27 name String
28 organizationId String
29 organization Organization @relation(fields: [organizationId], references: [id])
30
31 @@index([organizationId])
32}
33
34enum Role {
35 OWNER
36 ADMIN
37 MEMBER
38}Tenant Context#
1// lib/tenant.ts
2import { AsyncLocalStorage } from 'async_hooks'
3import { prisma } from '@/lib/db'
4
5interface TenantContext {
6 organizationId: string
7 userId: string
8 role: string
9}
10
11const tenantStorage = new AsyncLocalStorage<TenantContext>()
12
13export function getTenant(): TenantContext {
14 const tenant = tenantStorage.getStore()
15 if (!tenant) {
16 throw new Error('No tenant context')
17 }
18 return tenant
19}
20
21export function withTenant<T>(
22 context: TenantContext,
23 fn: () => Promise<T>
24): Promise<T> {
25 return tenantStorage.run(context, fn)
26}
27
28// Middleware to set tenant context
29export async function setTenantContext(request: Request) {
30 const session = await auth()
31 if (!session?.user) return null
32
33 const user = await prisma.user.findUnique({
34 where: { id: session.user.id },
35 include: { organization: true }
36 })
37
38 if (!user) return null
39
40 return {
41 organizationId: user.organizationId,
42 userId: user.id,
43 role: user.role
44 }
45}Tenant-Scoped Queries#
1// lib/db/tenant.ts
2import { prisma } from '@/lib/db'
3import { getTenant } from '@/lib/tenant'
4
5// Automatically scope queries to tenant
6export const tenantPrisma = {
7 project: {
8 findMany: async (args?: any) => {
9 const { organizationId } = getTenant()
10 return prisma.project.findMany({
11 ...args,
12 where: {
13 ...args?.where,
14 organizationId
15 }
16 })
17 },
18
19 findUnique: async (args: any) => {
20 const { organizationId } = getTenant()
21 const project = await prisma.project.findUnique(args)
22
23 if (project?.organizationId !== organizationId) {
24 return null
25 }
26
27 return project
28 },
29
30 create: async (args: any) => {
31 const { organizationId } = getTenant()
32 return prisma.project.create({
33 ...args,
34 data: {
35 ...args.data,
36 organizationId
37 }
38 })
39 },
40
41 update: async (args: any) => {
42 const { organizationId } = getTenant()
43
44 // Verify ownership first
45 const existing = await prisma.project.findFirst({
46 where: {
47 id: args.where.id,
48 organizationId
49 }
50 })
51
52 if (!existing) {
53 throw new Error('Not found')
54 }
55
56 return prisma.project.update(args)
57 },
58
59 delete: async (args: any) => {
60 const { organizationId } = getTenant()
61
62 // Verify ownership first
63 const existing = await prisma.project.findFirst({
64 where: {
65 id: args.where.id,
66 organizationId
67 }
68 })
69
70 if (!existing) {
71 throw new Error('Not found')
72 }
73
74 return prisma.project.delete(args)
75 }
76 }
77}Prisma Extension for Multi-Tenancy#
1// lib/db/tenant-extension.ts
2import { PrismaClient, Prisma } from '@prisma/client'
3import { getTenant } from '@/lib/tenant'
4
5const TENANT_MODELS = ['Project', 'Task', 'Document']
6
7export function createTenantPrisma() {
8 const prisma = new PrismaClient()
9
10 return prisma.$extends({
11 query: {
12 $allModels: {
13 async findMany({ model, args, query }) {
14 if (TENANT_MODELS.includes(model)) {
15 const { organizationId } = getTenant()
16 args.where = { ...args.where, organizationId }
17 }
18 return query(args)
19 },
20
21 async findFirst({ model, args, query }) {
22 if (TENANT_MODELS.includes(model)) {
23 const { organizationId } = getTenant()
24 args.where = { ...args.where, organizationId }
25 }
26 return query(args)
27 },
28
29 async create({ model, args, query }) {
30 if (TENANT_MODELS.includes(model)) {
31 const { organizationId } = getTenant()
32 args.data = { ...args.data, organizationId }
33 }
34 return query(args)
35 },
36
37 async update({ model, args, query }) {
38 if (TENANT_MODELS.includes(model)) {
39 const { organizationId } = getTenant()
40 // Add tenant check to where clause
41 args.where = { ...args.where, organizationId }
42 }
43 return query(args)
44 },
45
46 async delete({ model, args, query }) {
47 if (TENANT_MODELS.includes(model)) {
48 const { organizationId } = getTenant()
49 args.where = { ...args.where, organizationId }
50 }
51 return query(args)
52 }
53 }
54 }
55 })
56}Row-Level Security (PostgreSQL)#
1-- migrations/enable_rls.sql
2
3-- Enable RLS on tables
4ALTER TABLE "Project" ENABLE ROW LEVEL SECURITY;
5
6-- Create policy for select
7CREATE POLICY tenant_isolation_select ON "Project"
8 FOR SELECT
9 USING ("organizationId" = current_setting('app.current_org_id'));
10
11-- Create policy for insert
12CREATE POLICY tenant_isolation_insert ON "Project"
13 FOR INSERT
14 WITH CHECK ("organizationId" = current_setting('app.current_org_id'));
15
16-- Create policy for update
17CREATE POLICY tenant_isolation_update ON "Project"
18 FOR UPDATE
19 USING ("organizationId" = current_setting('app.current_org_id'));
20
21-- Create policy for delete
22CREATE POLICY tenant_isolation_delete ON "Project"
23 FOR DELETE
24 USING ("organizationId" = current_setting('app.current_org_id'));1// lib/db/rls.ts
2import { prisma } from '@/lib/db'
3
4export async function withTenantRLS<T>(
5 organizationId: string,
6 operation: () => Promise<T>
7): Promise<T> {
8 // Set the organization ID for RLS
9 await prisma.$executeRaw`
10 SELECT set_config('app.current_org_id', ${organizationId}, true)
11 `
12
13 return operation()
14}Middleware Integration#
1// middleware.ts
2import { NextResponse } from 'next/server'
3import type { NextRequest } from 'next/server'
4import { auth } from '@/auth'
5
6export async function middleware(request: NextRequest) {
7 const session = await auth()
8
9 if (!session?.user) {
10 return NextResponse.redirect(new URL('/login', request.url))
11 }
12
13 // Add tenant info to headers
14 const requestHeaders = new Headers(request.headers)
15 requestHeaders.set('x-organization-id', session.user.organizationId)
16 requestHeaders.set('x-user-id', session.user.id)
17
18 return NextResponse.next({
19 request: { headers: requestHeaders }
20 })
21}Organization Switching#
1// lib/organization.ts
2import { prisma } from '@/lib/db'
3import { cookies } from 'next/headers'
4
5export async function switchOrganization(userId: string, organizationId: string) {
6 // Verify user has access to organization
7 const membership = await prisma.user.findFirst({
8 where: {
9 id: userId,
10 organizationId
11 }
12 })
13
14 if (!membership) {
15 throw new Error('No access to organization')
16 }
17
18 // Set active organization in cookie
19 cookies().set('active-org', organizationId, {
20 httpOnly: true,
21 secure: process.env.NODE_ENV === 'production',
22 sameSite: 'lax',
23 maxAge: 60 * 60 * 24 * 365 // 1 year
24 })
25
26 return membership
27}
28
29export async function getActiveOrganization(userId: string) {
30 const activeOrgId = cookies().get('active-org')?.value
31
32 if (activeOrgId) {
33 const membership = await prisma.user.findFirst({
34 where: { id: userId, organizationId: activeOrgId },
35 include: { organization: true }
36 })
37
38 if (membership) {
39 return membership.organization
40 }
41 }
42
43 // Fall back to first organization
44 const user = await prisma.user.findFirst({
45 where: { id: userId },
46 include: { organization: true }
47 })
48
49 return user?.organization
50}Tenant-Aware API Route#
1// app/api/projects/route.ts
2import { NextResponse } from 'next/server'
3import { auth } from '@/auth'
4import { withTenant, setTenantContext } from '@/lib/tenant'
5import { tenantPrisma } from '@/lib/db/tenant'
6
7export async function GET() {
8 const session = await auth()
9 if (!session?.user) {
10 return NextResponse.json({ error: 'Unauthorized' }, { status: 401 })
11 }
12
13 const context = await setTenantContext(session)
14 if (!context) {
15 return NextResponse.json({ error: 'No tenant' }, { status: 400 })
16 }
17
18 const projects = await withTenant(context, () =>
19 tenantPrisma.project.findMany({
20 orderBy: { createdAt: 'desc' }
21 })
22 )
23
24 return NextResponse.json(projects)
25}Best Practices#
- Always scope queries - Never expose data across tenants
- Use database-level isolation - RLS provides additional security
- Validate tenant access - Check permissions on every request
- Consider schema per tenant - For strict isolation requirements
- Monitor per-tenant usage - Track resources and quotas
Related Patterns#
- Prisma - Prisma setup and basics
- RBAC - Role-based access control
- Session Management - Session handling