Skip to content

Multi-tenancy and row-level security (RLS) in TypeScript

When all your customers share the same tables, one line is all that keeps their data apart:

PostgreSQL
WHERE "tenantId" = $1

You write it in every endpoint. Then someone, or an agent, adds a CSV export and leaves it out there. The tests pass, because they seed a single tenant, and the first report comes from a customer with a screenshot of another company’s invoices.

Most apps hold the rule in one of these places, and each misses something:

Where the rule lives Holds writes Holds raw SQL Can be skipped Compiler-checked
By hand, in every query if you remember no by forgetting it no
A middleware rewriting queries partly no often no
A global filter or scope no no per call partly
Hand-written CREATE POLICY yes yes no no
UQL policies yes in native mode no yes

Hand-written row-level security holds everything, but it lives in SQL strings in an old migration, invisible from the model, and a set_config you have to bind on every request yourself. Forget its third argument and the tenant stays on the pooled connection for the next request to inherit.

UQL keeps the rule on the entity and enforces it either way: by the ORM on every engine, or natively by the database on Postgres and CockroachDB.

First step is per app, second per table/entity, and last per request.

Declare what a request knows. The keys are typed, so a misspelled one does not compile:

declare module 'uql-orm' {
interface UqlContext {
tenantId?: number;
userId?: string;
role?: string;
}
}

.tenant() makes a column the row’s tenant, read off the context:

import { Entity, f, Field } from 'uql-orm';
@Entity()
export class Invoice {
@Field(f.number().autoIncrement()) id?: number;
@Field(f.number().tenant()) tenantId?: number;
@Field(f.number().nullable()) total?: number | null;
}

Set the context in one middleware that every request passes through, from the verified session:

import express from 'express';
import { withContext } from 'uql-orm';
const app = express();
app.use(async (req, res, next) => {
const session = await getSession(req.headers.cookie);
if (!session) {
res.sendStatus(401);
return;
}
withContext({ tenantId: session.tenantId, userId: session.userId }, () =>
next(),
);
});

Other frameworks, tRPC and uql’s own HTTP API are on the Multi-tenancy page.

That is the whole setup. A handler now names no tenant, and cannot forget one. Under a session whose tenant is 42:

You write
await pool.findMany(Invoice, { $where: { total: { $gt: 100 } } });
PostgreSQL
SELECT "id", "tenantId", "total" FROM "Invoice" WHERE "total" > $1 AND "tenantId" = $2
-- $2 = 42

An insert that leaves tenantId out gets 42 too.

Real apps need more than “same tenant”. A user edits only their own invoices and never voids one; an auditor reads everything and writes nothing. In UQL that is data on the entity, a $where reading the context:

import { $context, Entity, f, Field } from 'uql-orm';
@Entity({
policies: [
{ allow: { ownerId: $context.userId, $new: { status: { $ne: 'void' } } } },
{ allow: {}, ops: ['read'], when: { role: 'auditor' } },
],
})
export class Invoice {
@Field(f.number().autoIncrement()) id?: number;
@Field(f.number().tenant()) tenantId?: number;
@Field(f.number().nullable()) total?: number | null;
@Field(f.string().nullable()) ownerId?: string | null;
@Field(f.string().default('draft')) status?: string;
}

A user can neither read someone else’s invoice nor hand their own to someone else. That is because ownerId sits under neither $old (the row as found) nor $new (the row as written), so it holds both. status sits under $new alone: no insert or update can leave an invoice void, though a void one can still be read. Keys are checked against the entity and the context, so ownerID or $context.userID does not compile.

A must pairing both rows is a transition. Here a bill’s status only moves forward:

import { Entity, f, Field } from 'uql-orm';
@Entity({
policies: [
{
must: {
$or: [
{
$old: { status: 'draft' },
$new: { status: { $in: ['draft', 'sent'] } },
},
{
$old: { status: 'sent' },
$new: { status: { $in: ['sent', 'paid'] } },
},
{ $old: { status: 'paid' }, $new: { status: 'paid' } },
],
},
ops: ['update'],
},
],
})
export class Bill {
@Field(f.number().autoIncrement()) id?: number;
@Field(f.string()) status?: string;
}

It holds every update and soft delete, on every engine. In native mode the database holds it with a trigger.

When a read comes back empty and you want to know why, ask:

import { explainPolicies, withContext } from 'uql-orm';
console.log(
withContext({ tenantId: 42, userId: 'u1' }, () =>
explainPolicies(Invoice, 'update'),
),
);
// $old: { $and: [{ tenantId: 42 }, { ownerId: 'u1' }] }
// $new: { $and: [{ tenantId: 42 }, { ownerId: 'u1' }, { status: { $ne: 'void' } }] }
Someone tries to What happens
Read another tenant’s rows with tenantId: 999 It is ANDed with the session’s tenant, and matches no row
Run a background job with no context It reads no row, where a missing WHERE would have read every one
Write tenantId: 7, or status: 'void' UqlSecurityError, a 403 over the HTTP API
Upsert another tenant’s row by its key It fails on the key, and nothing is overwritten
Reach the rows through a relation Populates, relation filters, counts and aggregates are held too, MongoDB included
Turn the policy off for one call No option does, and the HTTP API reads only query keys, so a client cannot send one

The first two, as Postgres receives them:

You write
await pool.findMany(Invoice, { $where: { tenantId: 999 } });
PostgreSQL
SELECT ... FROM "Invoice" WHERE "tenantId" = $1 AND "tenantId" = $2
-- $1 = 999, $2 = 42: no rows
-- the same read with no context
SELECT ... FROM "Invoice" WHERE 1 = 0

Those reads, and the writes in the table, ran on PGlite under uql-orm 0.116.3 before this was published.

Trusted cross-tenant work gets a pool of its own, and request code importing it stands out in review:

import { PgQuerierPool } from 'uql-orm/postgres';
export const trustedPool = new PgQuerierPool(
{ connectionString: process.env.ADMIN_DATABASE_URL },
{ policies: false },
);

By default UQL enforces policies itself, which is why they work on MySQL, SQLite, SQL Server and MongoDB alike. What it cannot hold is SQL it never sees: a psql session, a raw query, another service on the same database.

On Postgres, PGlite, Neon, Bun SQL and CockroachDB, one option hands the job to the database:

import { PgQuerierPool } from 'uql-orm/postgres';
export const pool = new PgQuerierPool(
{ connectionString: process.env.APP_DATABASE_URL },
{ policies: 'native' },
);

Migrations now install the same declarations as row-level security. This is what UQL generates for the Invoice above:

PostgreSQL
ALTER TABLE "Invoice" ENABLE ROW LEVEL SECURITY, FORCE ROW LEVEL SECURITY;
CREATE POLICY "_uql_Invoice__tenant_ac357d" ON "Invoice" AS RESTRICTIVE FOR ALL TO PUBLIC
USING ("tenantId" = (SELECT nullif(current_setting('uql.tenantId', true), '')::BIGINT))
WITH CHECK ("tenantId" = (SELECT nullif(current_setting('uql.tenantId', true), '')::BIGINT));
CREATE POLICY "_uql_Invoice__allow_1_da3b0b" ON "Invoice" AS PERMISSIVE FOR ALL TO PUBLIC
USING ("ownerId" = (SELECT nullif(current_setting('uql.userId', true), '')::TEXT))
WITH CHECK ("ownerId" = (SELECT nullif(current_setting('uql.userId', true), '')::TEXT) AND "status" <> 'void');
CREATE POLICY "_uql_Invoice__allow_2_read_97b390" ON "Invoice" AS PERMISSIVE FOR SELECT TO PUBLIC
USING ((SELECT nullif(current_setting('uql.role', true), '')::text) IN ('auditor'));

What each piece is for:

  • RESTRICTIVE on the tenant policy: no other policy can widen it.
  • FORCE: the table’s owner is held too.
  • nullif(..., ''): an unset key becomes NULL, which matches nothing.
  • (SELECT ...): Postgres reads the setting once per statement, not once per row.
  • set_config(..., true), which UQL runs in each transaction: the context stays local to it, so it is safe behind PgBouncer or Supavisor in transaction mode.
  • The hash ending each name: an unchanged policy is left alone, an edited one replaced, and drift reports one changed by hand.

Add role: $context.role and each transaction also runs as the database role the session names, which on Supabase means the same anon and authenticated roles your other clients already use.

  • ORM mode holds only what goes through UQL. For raw SQL and other services, use native mode.
  • Native mode is Postgres and CockroachDB. CockroachDB takes no relation in a native policy. A superuser or a role with BYPASSRLS is refused, since Postgres would skip every policy for it.
  • The context follows await but not callbacks: an emitter or a timer runs outside the request that registered it, and captureContext() carries it across.

The Multi-tenancy guide has every option, Supabase covers native policies beside the RLS you already have, and the comparison sets this beside other ORMs.



UQL is a JSON-native TypeScript ORM for Node.js and Bun. Supports PostgreSQL, PGlite, MySQL, MariaDB, MSSQL, SQLite, CockroachDB, Turso, Neon, Cloudflare D1 and MongoDB. Queries are plain JSON, typed to the leaf.