Multi-tenancy with Drizzle ORM

Drizzle ORM does not have built-in multi-tenancy support. The standard approach is to add .where(eq(orders.tenantId, currentTenantId)) to every query. This is error-prone: miss one query and you have a cross-tenant data leak.

Stratum’s drizzleWithTenant() wrapper for Drizzle takes a different approach. It wraps every execute() and transaction() call so that PostgreSQL’s Row-Level Security handles tenant isolation at the database level. No manual .where() filters needed.

The problem

A typical Drizzle multi-tenancy setup looks like this:

// The manual way: must remember for every query
const orders = await db
  .select()
  .from(ordersTable)
  .where(eq(ordersTable.tenantId, currentTenantId));

const stats = await db
  .select({ total: sum(ordersTable.total) })
  .from(ordersTable)
  .where(eq(ordersTable.tenantId, currentTenantId)); // easy to forget

// Joins are worse: both tables need filtering
const result = await db
  .select()
  .from(ordersTable)
  .innerJoin(productsTable, eq(ordersTable.productId, productsTable.id))
  .where(
    and(
      eq(ordersTable.tenantId, currentTenantId),
      eq(productsTable.tenantId, currentTenantId), // miss this = data leak
    )
  );

Every query needs the filter. Every join needs it on both sides. Aggregations, subqueries, CTEs… all need manual scoping. One missed filter in a midnight deploy and tenant data leaks.

The Stratum approach

Stratum’s Drizzle adapter wraps execute() and transaction() to inject a set_config('app.current_tenant_id', tenantId, true) call before every operation. Combined with PostgreSQL RLS policies on your tables, the database enforces tenant isolation regardless of what queries you write.

import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import { drizzleWithTenant } from "@stratum-hq/db-adapters";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle(pool);

// Wrap the Drizzle instance for tenant-scoped queries
const tenantDb = drizzleWithTenant(db, () => currentTenantId, pool);

// No .where() needed; RLS handles isolation
const orders = await tenantDb.execute(
  db.select().from(ordersTable)
);

Note

The drizzleWithTenant function was previously named drizzleWithTenantScope. The old name still works as a deprecated alias, but new code should use drizzleWithTenant for consistency with the Prisma adapter (prismaWithTenant).

Full walkthrough

1. 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

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.

2. Create tables with RLS policies

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

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 Row-Level Security
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- RLS policy: only show rows matching the session tenant
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);

-- Index for performance
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

FORCE ROW LEVEL SECURITY prevents the table owner from bypassing policies. Always include it. Stratum’s enableRLS() helper sets this flag 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.

3. Drizzle schema

src/schema.ts

import { pgTable, uuid, text, integer, numeric, timestamp } from "drizzle-orm/pg-core";

export const orders = pgTable("orders", {
  id: uuid("id").primaryKey().defaultRandom(),
  tenantId: uuid("tenant_id").notNull(),
  product: text("product").notNull(),
  quantity: integer("quantity").notNull().default(1),
  total: numeric("total", { precision: 10, scale: 2 }).notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow(),
});

4. Install dependencies

npm install @stratum-hq/lib @stratum-hq/db-adapters pg drizzle-orm jsonwebtoken
npm install -D drizzle-kit @types/pg @types/jsonwebtoken

5. Express app with tenant middleware

src/index.ts

import express from "express";
import { Pool } from "pg";
import jwt from "jsonwebtoken";
import { drizzle } from "drizzle-orm/node-postgres";
import { Stratum } from "@stratum-hq/lib";
import { drizzleWithTenant } from "@stratum-hq/db-adapters";
import { orders } from "./schema.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 db = drizzle(pool);
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.");
}

/** Return the tenant_id claim of a bearer token that verifies, or null. */
function verifiedTenantId(authorization: string | undefined, secret: string): string | null {
  if (!authorization?.startsWith("Bearer ")) return null;
  try {
    const claims = jwt.verify(authorization.slice("Bearer ".length), secret, {
      algorithms: ["HS256"],
    });
    return typeof claims === "object" && typeof claims.tenant_id === "string"
      ? claims.tenant_id
      : null;
  } catch {
    return null;
  }
}

// Tenant middleware: resolve the tenant from a verified JWT
app.use((req, res, next) => {
  const tenantId = verifiedTenantId(req.headers.authorization, jwtSecret);
  if (!tenantId) {
    res.status(401).json({ error: "Missing or invalid bearer token" });
    return;
  }

  req.tenantDb = drizzleWithTenant(db, () => tenantId, pool);
  req.tenantId = tenantId;
  next();
});

// Routes
app.get("/orders", async (req, res) => {
  const result = await req.tenantDb.execute(
    db.select().from(orders)
  );
  res.json(result);
});

app.post("/orders", async (req, res) => {
  const result = await req.tenantDb.execute(
    db.insert(orders).values({
      tenantId: req.tenantId,
      product: req.body.product,
      quantity: req.body.quantity,
      total: req.body.total,
    }).returning()
  );
  res.status(201).json(result[0]);
});

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

main();

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.

6. Using transactions

The wrapped Drizzle instance supports transactions. The tenant context is automatically injected before your callback runs:

await req.tenantDb.transaction(async (tx) => {
  // Both operations run with the tenant context set
  await tx.execute(
    db.insert(orders).values({
      tenantId: req.tenantId,
      product: "Widget",
      quantity: 1,
      total: "9.99",
    })
  );

  await tx.execute(
    db.insert(orders).values({
      tenantId: req.tenantId,
      product: "Gadget",
      quantity: 2,
      total: "19.98",
    })
  );
});

7. Test isolation

Start the server with JWT_SECRET set, for example to the output of openssl rand -hex 32. TENANT_A and TENANT_B hold the IDs of two tenants.

# Sign a token for each tenant with the same JWT_SECRET as the server
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")

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

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

# Tenant A only sees their orders
curl http://localhost:3000/orders -H "Authorization: Bearer $TOKEN_A"

# Tenant B only sees their orders
curl http://localhost:3000/orders -H "Authorization: Bearer $TOKEN_B"

How the adapter works

The Drizzle adapter wraps two methods on the Drizzle instance:

execute(query): Wraps the call in a transaction() that sets the tenant context first, then runs the query. This guarantees the set_config and your statement execute on the same connection.

transaction(fn): Injects a set_config call before running your callback. The tenant context applies to all operations within the transaction.

When contextFn returns an empty string, the adapter throws an error rather than silently running without tenant scope. Use the unwrapped Drizzle instance for admin or system operations that intentionally bypass tenant isolation.

Error handling

The adapter throws a clear error if no tenant context is available:

const tenantDb = drizzleWithTenant(db, () => "", pool);

// Throws: "Tenant context is required for database operations.
//          Use the unwrapped instance for system/admin operations."
await tenantDb.execute(db.select().from(orders));

This prevents accidental unscoped queries. For system operations (migrations, admin dashboards), use the original db instance directly.

Comparison: manual filters vs RLS

Aspect	Manual .where()	Stratum + RLS
Every query needs filter	Yes	No
Joins need double filter	Yes	No
Aggregations	Must filter manually	Automatically scoped
Raw SQL	Must add WHERE clause	Filtered by database
Missed filter = data leak	Yes	No, database enforces
Performance	JS-level filtering	Native PostgreSQL

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

• Scaffold a complete project: npx @stratum-hq/create my-app then stratum init
• Learn about isolation strategies to choose between RLS, schema-per-tenant, and database-per-tenant
• See the Prisma + RLS guide for the Prisma equivalent
• Build a Next.js multi-tenant app with subdomain routing
• Build an Express API with RLS for a simpler backend setup
• Compare Stratum with plain Drizzle, feature by feature