Express multi-tenant API with RLS

This is the simplest path to a multi-tenant API. Express handles HTTP, pg handles queries, and PostgreSQL Row-Level Security enforces tenant isolation at the database level. No ORM required.

What you get

• Tenant resolution from the tenant_id claim of a verified JWT
• Every SQL query automatically scoped to the current tenant
• No manual WHERE tenant_id = ? clauses
• Config and permissions resolved per-tenant from the Stratum control plane

Project setup

1. Initialize

mkdir my-api && cd my-api
npm init -y
npm install express pg jsonwebtoken @stratum-hq/lib @stratum-hq/db-adapters
npm install -D typescript @types/express @types/pg @types/jsonwebtoken tsx

2. Start PostgreSQL

Docker Compose

docker-compose.yml

services:
  db:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: stratum
      POSTGRES_PASSWORD: stratum_dev
      POSTGRES_DB: stratum
    ports:
      - "5432:5432"
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

docker compose up -d

Plain Docker

docker run -d --name stratum-db \
  -e POSTGRES_USER=stratum \
  -e POSTGRES_PASSWORD=stratum_dev \
  -e POSTGRES_DB=stratum \
  -v "$PWD/init.sql:/docker-entrypoint-initdb.d/init.sql" \
  -p 5432:5432 postgres:16-alpine

Write init.sql (the next step) before the first start. The image runs it only when it creates the data volume.

3. Create tables with RLS

-- init.sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "ltree";

-- Application table
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  tenant_id UUID NOT NULL,
  product TEXT NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 1,
  total NUMERIC(10,2) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Enable RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- Tenant isolation policy
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);

-- Performance index
CREATE INDEX idx_orders_tenant ON orders (tenant_id, created_at DESC);

-- The application's login. POSTGRES_USER (stratum) is a superuser, and a
-- superuser or a BYPASSRLS role skips every row-level security policy,
-- FORCE included. The app connects as stratum_app, which has neither.
CREATE ROLE stratum_app LOGIN PASSWORD 'stratum_app_dev' NOSUPERUSER NOBYPASSRLS;
GRANT CONNECT ON DATABASE stratum TO stratum_app;
GRANT USAGE ON SCHEMA public TO stratum_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO stratum_app;

Caution

Always include FORCE ROW LEVEL SECURITY. Without it, the table owner bypasses RLS policies. Stratum’s enableRLS() helper sets this automatically.

Connect as a role that RLS applies to

The Docker POSTGRES_USER is a superuser. A superuser, or any role with BYPASSRLS, skips every row-level security policy, so an app that connects as stratum sees every tenant’s rows. Keep that login for bootstrap and migrations, and connect the app as stratum_app. For production, give Stratum its own admin login that is not a superuser either, and limit the application login as described in Hardening: separate admin and app roles.

The application

Tenant middleware

The middleware reads the tenant ID from the tenant_id claim of a verified JWT. Then it creates a tenant-scoped connection pool for the request:

src/middleware.ts

import { Pool } from "pg";
import jwt from "jsonwebtoken";
import { createTenantPool } from "@stratum-hq/db-adapters";
import { Request, Response, NextFunction } from "express";

export function tenantMiddleware(pool: Pool, jwtSecret: string) {
  return (req: Request, res: Response, next: NextFunction) => {
    const authorization = req.headers.authorization;
    if (!authorization?.startsWith("Bearer ")) {
      res.status(401).json({ error: "Missing bearer token" });
      return;
    }

    // A token that does not verify is rejected, never ignored.
    let claims: string | jwt.JwtPayload;
    try {
      claims = jwt.verify(authorization.slice("Bearer ".length), jwtSecret, {
        algorithms: ["HS256"],
      });
    } catch {
      res.status(401).json({ error: "Invalid bearer token" });
      return;
    }

    const tenantId = typeof claims === "object" ? claims.tenant_id : undefined;
    if (typeof tenantId !== "string") {
      res.status(401).json({ error: "Token has no tenant_id claim" });
      return;
    }

    // Create a tenant-scoped pool for this request
    req.tenantPool = createTenantPool(pool, () => tenantId);
    req.tenantId = tenantId;
    next();
  };
}

Caution

Do not read the tenant from a request header such as x-tenant-id. Any client can send that header and select another tenant. Read a header only when a gateway you control sets it and removes any copy that the client sent. The SDK middleware applies the same rule: with jwtSecret set, it reads the header only with trustTenantHeader: true.

createTenantPool returns a pool wrapper that automatically calls set_config('app.current_tenant_id', tenantId, true) inside a transaction before every query. RLS policies do the rest.

Express app

src/index.ts

import express from "express";
import { Pool } from "pg";
import { Stratum } from "@stratum-hq/lib";
import { tenantMiddleware } from "./middleware.js";

const app = express();
app.use(express.json());

// The application login: row-level security applies to it.
const pool = new Pool({
  connectionString: process.env.DATABASE_URL
    || "postgres://stratum_app:stratum_app_dev@localhost:5432/stratum",
});

// Stratum's own login, which runs its migrations. The local superuser is fine
// for development; see the hardening guide for production.
const adminPool = new Pool({
  connectionString: process.env.DATABASE_ADMIN_URL
    || "postgres://stratum:stratum_dev@localhost:5432/stratum",
});

const stratum = new Stratum({ adminPool, pool, autoMigrate: true });

// The tenant binding comes from a signed token, so the server does not start
// without the key that verifies it.
const jwtSecret = process.env.JWT_SECRET;
if (!jwtSecret) {
  throw new Error("JWT_SECRET is required to verify bearer tokens.");
}

// Apply tenant middleware to all /api routes
app.use("/api", tenantMiddleware(pool, jwtSecret));

// List orders (automatically filtered by RLS)
app.get("/api/orders", async (req, res) => {
  const result = await req.tenantPool.query(
    "SELECT * FROM orders ORDER BY created_at DESC"
  );
  res.json(result.rows);
});

// Create an order
app.post("/api/orders", async (req, res) => {
  const { product, quantity, total } = req.body;
  const result = await req.tenantPool.query(
    "INSERT INTO orders (tenant_id, product, quantity, total) VALUES ($1, $2, $3, $4) RETURNING *",
    [req.tenantId, product, quantity, total]
  );
  res.status(201).json(result.rows[0]);
});

// Get order by ID (RLS ensures it belongs to the current tenant)
app.get("/api/orders/:id", async (req, res) => {
  const result = await req.tenantPool.query(
    "SELECT * FROM orders WHERE id = $1",
    [req.params.id]
  );
  if (result.rows.length === 0) {
    res.status(404).json({ error: "Order not found" });
    return;
  }
  res.json(result.rows[0]);
});

// Delete an order (RLS prevents deleting another tenant's order)
app.delete("/api/orders/:id", async (req, res) => {
  const result = await req.tenantPool.query(
    "DELETE FROM orders WHERE id = $1 RETURNING *",
    [req.params.id]
  );
  if (result.rows.length === 0) {
    res.status(404).json({ error: "Order not found" });
    return;
  }
  res.json({ deleted: result.rows[0] });
});

// Health check (no tenant required)
app.get("/health", (req, res) => {
  res.json({ status: "ok" });
});

async function main() {
  await stratum.initialize();
  app.listen(3000, () => console.log("Listening on :3000"));
}

main();

TypeScript declarations

Add type declarations for the request properties:

src/types.d.ts

import { TenantPool } from "@stratum-hq/db-adapters";

declare global {
  namespace Express {
    interface Request {
      tenantPool: TenantPool;
      tenantId: string;
    }
  }
}

Running the app

export JWT_SECRET=$(openssl rand -hex 32)
npx tsx src/index.ts

Testing isolation

Create two tenants and verify they cannot see each other’s data. Your identity provider issues the tokens in a real application. For local tests, sign one token for each tenant with the same JWT_SECRET as the server:

# Create tenants via Stratum control plane
TENANT_A="aaaaaaaa-0000-0000-0000-000000000001"
TENANT_B="bbbbbbbb-0000-0000-0000-000000000002"

# Sign a token for each tenant
TOKEN_A=$(node -e 'console.log(require("jsonwebtoken").sign({ tenant_id: process.argv[1] }, process.env.JWT_SECRET, { algorithm: "HS256", expiresIn: "1h" }))' "$TENANT_A")
TOKEN_B=$(node -e 'console.log(require("jsonwebtoken").sign({ tenant_id: process.argv[1] }, process.env.JWT_SECRET, { algorithm: "HS256", expiresIn: "1h" }))' "$TENANT_B")

# Insert orders for Tenant A
curl http://localhost:3000/api/orders -X POST \
  -H "Authorization: Bearer $TOKEN_A" \
  -H "Content-Type: application/json" \
  -d '{"product": "Widget", "quantity": 5, "total": "49.95"}'

curl http://localhost:3000/api/orders -X POST \
  -H "Authorization: Bearer $TOKEN_A" \
  -H "Content-Type: application/json" \
  -d '{"product": "Sprocket", "quantity": 10, "total": "99.90"}'

# Insert orders for Tenant B
curl http://localhost:3000/api/orders -X POST \
  -H "Authorization: Bearer $TOKEN_B" \
  -H "Content-Type: application/json" \
  -d '{"product": "Gadget", "quantity": 2, "total": "29.90"}'

# Tenant A sees 2 orders (Widget + Sprocket)
curl http://localhost:3000/api/orders -H "Authorization: Bearer $TOKEN_A"

# Tenant B sees 1 order (Gadget)
curl http://localhost:3000/api/orders -H "Authorization: Bearer $TOKEN_B"

# Tenant B cannot delete Tenant A's order (returns 404, not 403)
ORDER_A_ID=$(curl -s http://localhost:3000/api/orders \
  -H "Authorization: Bearer $TOKEN_A" | jq -r '.[0].id')

curl -X DELETE "http://localhost:3000/api/orders/$ORDER_A_ID" \
  -H "Authorization: Bearer $TOKEN_B"
# {"error": "Order not found"}

The last test is the important one. Tenant B tries to delete Tenant A’s order by ID. RLS filters the DELETE query, so no row matches, and the API returns 404. The tenant never knows the order exists.

Adding more tables

Use Stratum’s migration helpers to add RLS to new tables:

import { migrateTable } from "@stratum-hq/db-adapters";

const client = await pool.connect();
try {
  await client.query("BEGIN");
  await migrateTable(client, "invoices");
  await migrateTable(client, "products");
  await client.query("COMMIT");
} finally {
  client.release();
}

Or use the CLI:

npx @stratum-hq/cli migrate invoices products

Using an ORM instead

If you prefer an ORM over raw SQL:

• Prisma: See Multi-Tenancy with Prisma and PostgreSQL RLS
• Drizzle: See Multi-Tenancy with Drizzle ORM

Both guides use the same RLS setup. Only the query layer changes.

Scaffold with Stratum CLI

Skip the manual setup entirely:

npx @stratum-hq/create my-api
cd my-api
npx @stratum-hq/cli init
npx @stratum-hq/cli scaffold express

This generates the middleware, route templates, and environment configuration. The scaffolded Express middleware uses the Stratum SDK client for tenant resolution with JWT support.

Generate a ready-to-run project

Skip the manual setup. The Stack Wizard configures your database, ORM, and framework, then gives you a single command to scaffold the whole project.

Next steps

• Learn about isolation strategies to choose between RLS, schema-per-tenant, and database-per-tenant
• Build a Next.js multi-tenant app with subdomain routing
• Add config inheritance for per-tenant feature flags and limits
• Set up ABAC policies for fine-grained permission control