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:
WHERE "tenantId" = $1You 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.
The usual fixes
Section titled “The usual fixes”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.
Three steps, once
Section titled “Three steps, once”First step is per app, second per table/entity, and last per request.
1. Declare the context
Section titled “1. Declare the context”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; }}2. Mark the tenant column
Section titled “2. Mark the tenant column”.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;}3. Set the context once per request
Section titled “3. Set the context once per request”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(), );});import { UqlModule } from 'uql-orm/nestjs';
type AuthedRequest = { user: { id: string; tenantId: number } };
UqlModule.forRoot<AuthedRequest>({ pool, contextOf: (req) => ({ tenantId: req.user.tenantId, userId: req.user.id }),});import { Hono } from 'hono';import { withContext } from 'uql-orm';
const app = new Hono();
app.use('*', async (c, next) => { const session = await getSession(c.req.header('cookie')); if (!session) return c.text('Unauthorized', 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:
await pool.findMany(Invoice, { $where: { total: { $gt: 100 } } });SELECT "id", "tenantId", "total" FROM "Invoice" WHERE "total" > $1 AND "tenantId" = $2-- $2 = 42An insert that leaves tenantId out gets 42 too.
A tenant is only the first rule
Section titled “A tenant is only the first rule”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' } }] }Trying to get around it
Section titled “Trying to get around it”| Someone tries to | What happens |
|---|---|
Read another tenant’s rows with tenant |
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 tenant, or status: 'void' |
Uql, 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:
await pool.findMany(Invoice, { $where: { tenantId: 999 } });SELECT ... FROM "Invoice" WHERE "tenantId" = $1 AND "tenantId" = $2-- $1 = 999, $2 = 42: no rows
-- the same read with no contextSELECT ... FROM "Invoice" WHERE 1 = 0Those 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 },);Native row-level security
Section titled “Native row-level security”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:
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:
RESTRICTIVEon the tenant policy: no other policy can widen it.FORCE: the table’s owner is held too.nullif(..., ''): an unset key becomesNULL, 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.
Limits
Section titled “Limits”- 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
BYPASSRLSis refused, since Postgres would skip every policy for it. - The context follows
awaitbut not callbacks: an emitter or a timer runs outside the request that registered it, andcaptureContext()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.