How to Set Up a Multi-Tenant PostgreSQL Database with Prisma

In today’s software development landscape, building applications that can serve multiple clients or tenants efficiently is crucial. Multi-tenancy allows a single instance of an application to serve multiple clients, sharing resources but keeping data isolated. This article will guide you through setting up a multi-tenant PostgreSQL database using Prisma, a powerful ORM for Node.js and TypeScript, known for its developer-friendly syntax and powerful features.

What is Multi-Tenancy?

Multi-tenancy is an architecture where a single instance of software serves multiple customers, known as tenants. Each tenant's data is isolated and remains invisible to others. This approach is cost-effective and resource-efficient, making it a popular choice in SaaS applications.

Use Cases for Multi-Tenancy

• SaaS Products: Applications like CRM or project management tools where different companies use the same software.
• Shared Hosting: Environments where multiple websites use a single server.
• Enterprise Applications: Large organizations with various departments needing access to a single software solution.

Why Choose PostgreSQL with Prisma?

PostgreSQL is an advanced, open-source relational database that offers robustness, performance, and extensive support for concurrent transactions. Prisma enhances the developer experience with features such as:

• Type Safety: Autocompletion and validation based on your database schema.
• Migrations: Easy database schema migrations.
• Data Modeling: Simple and intuitive data modeling.

Setting Up the Environment

Before diving into coding, ensure you have the following installed:

• Node.js: Version 14 or later.
• PostgreSQL: Version 10 or later.
• Prisma CLI: Install it globally using npm.

npm install -g prisma

Step 1: Initialize Your Prisma Project

Create a new directory for your project and initialize a new Node.js project:

mkdir multi-tenant-postgres
cd multi-tenant-postgres
npm init -y

Then, install Prisma and the PostgreSQL driver:

npm install @prisma/client prisma

Step 2: Configure Your PostgreSQL Database

Create a PostgreSQL database. You can use a client like psql or a GUI like pgAdmin. For example, to create a database named multi_tenant_db, run:

CREATE DATABASE multi_tenant_db;

Next, create a .env file in your project root to store the database connection URL:

DATABASE_URL="postgresql://user:password@localhost:5432/multi_tenant_db"

Replace user and password with your PostgreSQL credentials.

Step 3: Define Your Schema

Create a new Prisma schema file at prisma/schema.prisma. In a multi-tenant application, you can use a single schema with a tenant ID to differentiate records. Here's a basic example:

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())
  name      String
  email     String @unique
  tenantId  Int
  tenant    Tenant @relation(fields: [tenantId], references: [id])
}

Step 4: Run Database Migrations

After defining your schema, you need to run the migrations to create the tables in your PostgreSQL database:

npx prisma migrate dev --name init

This command generates SQL migration files and applies them to your database.

Step 5: Seed Your Database

Seeding your database with initial data is important for testing. Create a prisma/seed.js file:

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

async function main() {
  const tenantA = await prisma.tenant.create({
    data: {
      name: 'Tenant A',
      users: {
        create: [
          { name: 'User 1', email: 'user1@tenantA.com' },
          { name: 'User 2', email: 'user2@tenantA.com' },
        ],
      },
    },
  });

  console.log({ tenantA });
}

main()
  .catch(e => console.error(e))
  .finally(async () => await prisma.$disconnect());

Run the seed script:

node prisma/seed.js

Step 6: Querying Multi-Tenant Data

To fetch data specific to a tenant, you can write a function in your application. Here’s an example using Express.js:

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

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

app.listen(3000, () => {
  console.log('Server running on http://localhost:3000');
});

Step 7: Troubleshooting Tips

• Connection Issues: Ensure your PostgreSQL server is running and that your connection string in .env is correct.
• Data Isolation: Always filter data by tenantId when querying to prevent data leaks between tenants.
• Performance Optimization: Consider indexing tenantId for faster queries, especially with large datasets.

Conclusion

Setting up a multi-tenant PostgreSQL database with Prisma is a powerful way to create efficient, scalable applications. By following the steps outlined in this article, you can ensure data isolation while leveraging the advantages of a single codebase. Whether you're building a SaaS product or an enterprise application, understanding multi-tenancy and implementing it effectively will set you apart in the competitive software development landscape. Happy coding!