Express + Knex setup

Wire fail-closed multi-tenancy into an Express 5 + Knex app with database-enforced Postgres RLS - a copy-pasteable setup-agent prompt, plus the manual walkthrough.

tenancy init doesn't scaffold Express + Knex yet, so this stack is a manual wire-up - a few files. This page gives you two ways to do it: hand the setup-agent prompt to your AI assistant, or follow the manual walkthrough. Either way, finish by running the two-tenant test - with fail-closed isolation you verify, you don't trust.

This is row-level on PostgreSQL, and Knex is a callback facade backed by forced RLS. The facade scopes every query; the database enforces isolation independently via a FORCE ROW LEVEL SECURITY policy under a non-privileged runtime role. That backstop is the point - set it up (Step 4) or you're running on the facade alone. Keep the base Knex private; only expose tenancy.run.

Setup-agent prompt

Paste this into an assistant that can edit your repo and run commands (Claude Code, Cursor, …). It executes in order and verifies each step. It's pinned to the real API, so it won't invent factory names.

# TenancyJS Setup Agent - Express 5 + Knex (row-level, PostgreSQL)

You are a setup agent. Add fail-closed multi-tenancy to this Express + Knex project with TenancyJS.
Execute every step in order. After each step, verify it applied before continuing. If a step does not
apply, say so and continue. Do NOT invent API names - if unsure, fetch the linked doc.

Source of truth (fetch if a detail is missing, do not guess):
- https://tenancyjs.pages.dev/docs/adapters/knex
- https://tenancyjs.pages.dev/docs/integrations/express
- https://tenancyjs.pages.dev/docs/concepts/limitations
- https://tenancyjs.pages.dev/docs/guides/testing-isolation

## Pre-flight
- Run `node -v`; abort if it is not >= 24.
- Confirm `express` (v5) and `knex` are in package.json. Abort if this is not an Express + Knex project.
- Confirm the database is PostgreSQL. Knex tenancy is PostgreSQL-only - abort otherwise.

## Step 1 - Install
Run:
```sh
npm install tenancyjs-core@beta tenancyjs-adapter-knex@beta tenancyjs-integration-express@beta tenancyjs-identifiers@beta knex pg
```
Verify all six appear in package.json dependencies.

## Step 2 - Schema
Ensure a `tenants` table (`id text primary key`, `status text not null default 'active'`) and at least
one tenant-scoped table with a non-null `tenant_id` column (e.g. `posts`). Add `tenant_id` to every
tenant-owned table. Index `tenant_id`.

## Step 3 - tenancy.ts (the single source of truth)
Create `src/tenancy.ts`. It must:
- Construct ONE `TenancyManager<Tenant>` and export it.
- Construct the BASE Knex with `Knex({ client: "pg", connection: process.env.DATABASE_URL })` using the
  NON-owner RUNTIME role, and keep it PRIVATE to this module.
- Create the facade: `createKnexTenancy({ manager, knex: baseKnex, tenantTables: { posts: { tenantColumn: "tenant_id", policyName: "posts_tenant_isolation" } } })`.
  List every tenant table. Export it as `tenancy`. Queries run ONLY through `tenancy.run((db) => …)`.
- Build a `TenantResolutionChain` with `new HeaderTenantResolver()` and a store whose `find(identifier)`
  looks the tenant up and returns `[{ tenant: { id }, status }]` (or `[]`).
- Export `manager`, `resolver`, `tenancy`. Do NOT export the base Knex for app use.

## Step 4 - Forced RLS migration (the database backstop) - DO NOT SKIP
Write a migration, run under a MIGRATION/OWNER role, that for each tenant table:
- Adds a non-null `tenant_id`.
- `ALTER TABLE posts ENABLE ROW LEVEL SECURITY;` then `ALTER TABLE posts FORCE ROW LEVEL SECURITY;`
  (FORCE so even the table owner is subject to the policy).
- Creates a policy named exactly `posts_tenant_isolation` whose USING and WITH CHECK read the request
  GUCs: allow the row when `tenancyjs.is_central` is true, else `tenant_id = current_setting('tenancyjs.tenant_id', true)`.
Confirm the RUNTIME role the app connects as is NOT the table owner, is NOT superuser, and does NOT have
BYPASSRLS. This is what makes the isolation database-enforced, not facade-only.

## Step 5 - Validate at startup
Before `app.listen`, `await tenancy.validate()`. If it rejects (RLS missing, policy wrong, or the role
can bypass), log the error and REFUSE to serve - do not fall back to facade-only.

## Step 6 - Middleware
In `server.ts`, register `app.use(createExpressTenancyMiddleware({ manager, resolver }))` EARLY, before
routes. The resolver is the `TenantResolutionChain` object - NOT a `(req) => …` function.

## Step 7 - Scope the routes
For every handler that touches tenant data, query through `tenancy.run((db) => db.table("posts")…)`.
REMOVE any manual `where("tenant_id", …)` filters - the facade injects the scope and the policy enforces
it. Leave non-tenant/public routes alone.

## Step 8 - Central (cross-tenant) work
For admin/cross-tenant handlers, wrap the work in
`manager.runInCentralContext(() => tenancy.run((db) => …))` explicitly. Unscoped tenant access must throw.

## Step 9 - Error handler
Add a final Express error handler that maps `ExpressTenancyResolutionError` (it carries `.statusCode`) to
400/404, and anything else to 500. Otherwise resolution failures surface as an unhandled 500.

## Step 10 - Prove it
Create a two-tenant test (Vitest or Jest): create tenant A and tenant B, write a row under EACH with the
SAME primary key, assert querying as A never returns B's row. THEN, as the runtime role with no tenant
GUC set, run a raw `select * from posts` and assert it returns 0 rows - that proves the RLS backstop, not
just the facade. Follow https://tenancyjs.pages.dev/docs/guides/testing-isolation.

## Step 11 - Verify
- `npm run build` (if TypeScript) - report errors.
- Boot the app (`npm run start`) and confirm `tenancy.validate()` passed and it serves.
- Run the two-tenant test and report the result.

Report a summary of every file created/changed and any issue found.

AI wiring is a starting point, not proof. The only thing that demonstrates isolation is Step 10 - the two-tenant colliding-id test and the raw-query-as-runtime-role check that proves the RLS backstop. Run both before you trust the result.

Manual walkthrough

Prefer to wire it yourself? The same steps, with the code.

Install

npm install tenancyjs-core@beta tenancyjs-adapter-knex@beta tenancyjs-integration-express@beta tenancyjs-identifiers@beta knex pg

One `tenancy.ts`

The manager, the Knex facade, and the resolver all live here - one source of truth. The base Knex stays private; the app only ever gets a scoped db through tenancy.run.

src/tenancy.ts

import { TenancyManager, type TenantRecord } from "tenancyjs-core";
import { createKnexTenancy } from "tenancyjs-adapter-knex";
import { HeaderTenantResolver, TenantResolutionChain } from "tenancyjs-identifiers";
import Knex from "knex";

interface Tenant extends TenantRecord {
  readonly id: string;
}

export const manager = new TenancyManager<Tenant>();

// Connect as the NON-owner runtime role (no BYPASSRLS). Keep it private to this module.
const baseKnex = Knex({ client: "pg", connection: process.env.DATABASE_URL });

// The ONLY way the app touches the DB. List every tenant table.
export const tenancy = createKnexTenancy({
  manager,
  knex: baseKnex,
  tenantTables: {
    posts: { tenantColumn: "tenant_id", policyName: "posts_tenant_isolation" },
  },
});

export const resolver = new TenantResolutionChain<Tenant>({
  resolvers: [new HeaderTenantResolver({ headerName: "x-tenant-id" })],
  store: {
    async find(identifier) {
      const rows = await baseKnex("tenants").where({ id: identifier.value });
      return rows[0] ? [{ tenant: { id: rows[0].id }, status: rows[0].status }] : [];
    },
  },
});

The forced RLS migration (the database backstop)

Run this under the owner/migration role. The app connects as a separate runtime role that is not the owner, not superuser, and lacks BYPASSRLS - so FORCE ROW LEVEL SECURITY binds it to the policy.

migrations/001_posts_rls.ts

import type { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  await knex.raw(`
    ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
    ALTER TABLE posts FORCE ROW LEVEL SECURITY;

    CREATE POLICY posts_tenant_isolation ON posts
      USING (
        current_setting('tenancyjs.is_central', true) = 'true'
        OR tenant_id = current_setting('tenancyjs.tenant_id', true)
      )
      WITH CHECK (
        current_setting('tenancyjs.is_central', true) = 'true'
        OR tenant_id = current_setting('tenancyjs.tenant_id', true)
      );
  `);
}

export async function down(knex: Knex): Promise<void> {
  await knex.raw(`DROP POLICY IF EXISTS posts_tenant_isolation ON posts;`);
}

tenancy.run sets tenancyjs.tenant_id / tenancyjs.is_central per transaction; the policy reads them.

Validate, then wire the middleware

Call tenancy.validate() at startup and refuse to serve if RLS is missing or the role can bypass it. Register the middleware early - the resolver is the TenantResolutionChain object, not a function.

src/server.ts

import express from "express";
import {
  createExpressTenancyMiddleware,
  ExpressTenancyResolutionError,
} from "tenancyjs-integration-express";
import { manager, resolver, tenancy } from "./tenancy";

const app = express();

app.use(createExpressTenancyMiddleware({ manager, resolver }));

app.get("/posts", async (_req, res) => {
  const posts = await tenancy.run((db) =>
    db.table("posts").where("published", true).select("id", "title"),
  );
  res.json(posts); // only the current tenant's rows
});

// Map resolution failures to 400/404; everything else to 500.
app.use((err, _req, res, next) => {
  if (err instanceof ExpressTenancyResolutionError)
    res.status(err.statusCode ?? 400).json({ error: err.message });
  else next(err);
});

await tenancy.validate(); // throws if the RLS backstop is not in force
app.listen(3000);

Central (cross-tenant) work

Admin routes don't run unscoped - the policy would hide every row. Open an explicit central scope; the policy's is_central branch lets the query through.

src/admin.ts

export function countAllPosts() {
  return manager.runInCentralContext(() =>
    tenancy.run((db) => db.table("posts").count("* as n")),
  );
}

Prove isolation

Write the two-tenant colliding-id test: the same primary key under A and B, assert A never reads B's row. Then, connected as the runtime role with no tenant GUC set, run a raw select * from posts and assert it returns 0 rows - that proves the forced-RLS backstop holds even if the facade were bypassed.

Two independent guards: the facade scopes queries, and forced RLS enforces isolation in the database under a non-privileged role. Validate the second at startup - facade-only is not the design.

Full lifecycle and the security boundary: Express integration · Knex adapter · Limitations.

Express + Knex setup

On this page