Implementing Multi-Tenancy in PostgreSQL with Prisma ORM

In today’s cloud-driven world, multi-tenancy has become a vital architectural pattern for applications serving multiple customers (tenants) from a single instance of software. This approach maximizes resource utilization while providing an isolated experience for each tenant. PostgreSQL, a powerful open-source relational database, paired with Prisma ORM, a popular database toolkit, makes implementing multi-tenancy straightforward and efficient. In this article, we’ll explore how to set up multi-tenancy in PostgreSQL using Prisma ORM, providing you with actionable insights and code examples along the way.

What is Multi-Tenancy?

Multi-tenancy refers to a software architecture where a single instance of an application serves multiple tenants. Each tenant's data is isolated, ensuring privacy and security. The primary types of multi-tenancy include:

• Database-per-tenant: Each tenant has its own database.
• Schema-per-tenant: Each tenant has its own schema within a shared database.
• Row-level security: A single database and schema are used, with data partitioned by tenant identifiers.

In this article, we will focus on the row-level security approach, which is cost-effective and efficient for many applications.

Why Choose Prisma ORM?

Prisma ORM simplifies database interactions with type safety and an intuitive API. Its features include:

• Type Safety: Automatic type inference based on your database schema.
• Migration Management: Simple database migrations with prisma migrate.
• Easy Querying: A powerful query engine for CRUD operations.

These features make Prisma a great choice for building applications with multi-tenancy.

Setting Up Your Environment

Before diving into the implementation, ensure you have the following prerequisites installed:

• Node.js (v12 or later)
• PostgreSQL
• Prisma CLI (install via npm with npm install prisma --save-dev)

Once you have these tools ready, create a new project and initialize Prisma:

mkdir multi-tenant-app
cd multi-tenant-app
npm init -y
npm install @prisma/client
npx prisma init

This command creates a new Prisma setup with the necessary configuration files.

Configuring PostgreSQL for Multi-Tenancy

Step 1: Database Schema

Let’s define a simple data model for our tenants. Open the schema.prisma file and modify it as follows:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model Tenant {
  id      Int     @id @default(autoincrement())
  name    String
  users   User[]
}

model User {
  id       Int     @id @default(autoincrement())
  username String
  tenantId Int
  Tenant   Tenant   @relation(fields: [tenantId], references: [id])
}

In this schema, we have two models: Tenant and User. Each user belongs to a specific tenant, identified by tenantId.

Step 2: Database Migration

Now, let’s apply the schema to your PostgreSQL database. Make sure your DATABASE_URL is set in the .env file. Then run the following commands:

npx prisma migrate dev --name init
npx prisma generate

These commands will create the necessary tables in your PostgreSQL database.

Implementing Multi-Tenancy Logic

Step 3: Middleware for Tenant Identification

To ensure that users can only access their data, we need middleware that identifies the tenant based on the incoming request. Here’s how to set it up in an Express.js application.

const express = require('express');
const { PrismaClient } = require('@prisma/client');

const app = express();
const prisma = new PrismaClient();

app.use(express.json());

// Middleware to extract tenant ID from request
app.use((req, res, next) => {
  const tenantId = req.headers['x-tenant-id']; // Assuming tenant ID is passed in headers
  if (!tenantId) {
    return res.status(400).send('Tenant ID is required');
  }
  req.tenantId = tenantId;
  next();
});

Step 4: CRUD Operations with Tenant Context

Now, let’s implement the CRUD operations that respect the tenant context.

Create a User

app.post('/users', async (req, res) => {
  const { username } = req.body;
  const user = await prisma.user.create({
    data: {
      username,
      tenantId: req.tenantId,
    },
  });
  res.json(user);
});

Read Users

app.get('/users', async (req, res) => {
  const users = await prisma.user.findMany({
    where: { tenantId: req.tenantId },
  });
  res.json(users);
});

Update a User

app.put('/users/:id', async (req, res) => {
  const { id } = req.params;
  const { username } = req.body;

  const user = await prisma.user.updateMany({
    where: { id: Number(id), tenantId: req.tenantId },
    data: { username },
  });

  if (user.count === 0) {
    return res.status(404).send('User not found');
  }
  res.send('User updated successfully');
});

Delete a User

app.delete('/users/:id', async (req, res) => {
  const { id } = req.params;

  const user = await prisma.user.deleteMany({
    where: { id: Number(id), tenantId: req.tenantId },
  });

  if (user.count === 0) {
    return res.status(404).send('User not found');
  }
  res.send('User deleted successfully');
});

Step 5: Testing the Multi-Tenancy

To test your multi-tenancy implementation, use tools like Postman or cURL to perform requests to your API, passing the x-tenant-id header with each request to ensure operations are scoped to the correct tenant.

Conclusion

Implementing multi-tenancy in PostgreSQL using Prisma ORM provides a robust and scalable solution for applications that serve multiple clients. By following the steps outlined in this article, you can ensure data isolation while optimizing resource utilization.

With Prisma’s intuitive API and PostgreSQL’s powerful capabilities, you’re well-equipped to build multi-tenant applications efficiently. Start integrating these concepts into your projects, and watch your application scale elegantly. Happy coding!