I’ve been building SaaS applications for over a decade, and one question keeps resurfacing: how do you securely isolate customer data while maintaining performance at scale? This challenge led me to explore database-level security patterns, and today I want to share a practical approach using NestJS, Prisma, and PostgreSQL Row-Level Security.

Have you ever considered what happens when a customer requests to export all their data? With proper multi-tenancy, this becomes straightforward rather than a security nightmare.

Let me walk you through building a production-ready multi-tenant API. We’ll start with the foundation – setting up our project with the right dependencies. I prefer using NestJS because its modular architecture naturally fits multi-tenant patterns.

npm i -g @nestjs/cli
nest new multi-tenant-saas
cd multi-tenant-saas
npm install @prisma/client prisma @nestjs/config

What if you could ensure data isolation at the database level rather than relying solely on application logic? That’s where PostgreSQL Row-Level Security becomes invaluable. Let me show you how to design a schema that supports this approach.

Here’s a simplified version of our Prisma schema focusing on tenant isolation:

model Tenant {
  id   String @id @default(cuid())
  name String @unique
  users User[]
}

model User {
  id       String @id @default(cuid())
  email    String
  tenantId String
  tenant   Tenant @relation(fields: [tenantId], references: [id])
  @@unique([email, tenantId])
}

Notice the composite unique constraint on email and tenantId? This allows the same email to exist across different tenants while maintaining uniqueness within each tenant’s context.

Now, here’s where the magic happens. We enable Row-Level Security in our database migrations:

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

This policy ensures that users can only access records where the tenant_id matches their current context. But how do we set this context securely?

That brings us to the Prisma setup. We need to extend the Prisma client to handle tenant context automatically:

@Injectable()
export class PrismaService extends PrismaClient {
  async setTenantContext(tenantId: string) {
    await this.$executeRaw`SELECT set_config('app.current_tenant_id', ${tenantId}, true)`;
  }
}

In a real application, you’d call setTenantContext at the start of each request after authenticating the user. This approach ensures that every database query automatically respects tenant boundaries.

Have you thought about how authentication fits into this picture? We need JWT tokens that include tenant information. Here’s a basic implementation:

@Injectable()
export class AuthService {
  async login(email: string, password: string, tenantId: string) {
    const user = await this.prisma.user.findFirst({
      where: { email, tenantId }
    });
    // Validate password and return JWT with tenant context
    return this.jwtService.sign({ userId: user.id, tenantId });
  }
}

What happens when you need to handle tenant onboarding dynamically? This is where many systems become complex, but it doesn’t have to be. We can create a simple tenant service:

@Injectable()
export class TenantService {
  async createTenant(name: string, adminEmail: string) {
    const tenant = await this.prisma.tenant.create({
      data: { name }
    });
    
    await this.prisma.user.create({
      data: {
        email: adminEmail,
        tenantId: tenant.id,
        role: 'ADMIN'
      }
    });
    
    return tenant;
  }
}

Performance optimization becomes crucial as you scale. Did you know that proper indexing can make or break your multi-tenant application? Always index tenant_id columns and consider partial indexes for common query patterns.

Here’s a common pitfall I’ve encountered: forgetting to handle edge cases like super admins who need cross-tenant access. We solve this with careful policy design:

CREATE POLICY admin_override ON users
  USING (
    tenant_id = current_setting('app.current_tenant_id') OR
    current_setting('app.is_super_admin') = 'true'
  );

Testing multi-tenant applications requires special consideration. How do you ensure data doesn’t leak between test tenants? I recommend creating isolated test suites that reset tenant contexts between tests.

describe('UserService', () => {
  beforeEach(async () => {
    await prisma.setTenantContext('test-tenant-1');
  });
  
  it('should only access users from current tenant', async () => {
    const users = await userService.findAll();
    expect(users.every(user => user.tenantId === 'test-tenant-1')).toBe(true);
  });
});

As your application grows, you might consider alternative approaches like schema-per-tenant or database-per-tenant. Each has trade-offs in complexity versus isolation. Row-Level Security strikes a good balance for most SaaS applications.

Building multi-tenant systems taught me that security shouldn’t be an afterthought. By baking data isolation into your database layer, you create a foundation that’s both secure and maintainable.

I’d love to hear about your experiences with multi-tenancy! What challenges have you faced? Share your thoughts in the comments below, and if this guide helped you, consider liking and sharing it with other developers who might benefit.