Tenant

better-drizzle/tenant scopes every model that has a tenant column. A client from db.$tenant({ organizationId }) only reads and writes rows of that tenant. The filter covers the root query and also relation loads, relation filters, _count, relation sorts, writes, upserts, and the lookups behind connect, disconnect, and set. Without a tenant, scoped models throw instead of running unfiltered.

Install

npm install better-drizzle

pnpm add better-drizzle

yarn add better-drizzle

bun add better-drizzle

Setup

import { better } from 'better-drizzle';
import { tenant } from 'better-drizzle/tenant';

const client = better(db, {
	plugins: [
		tenant({
			columns: ['organizationId', 'workspaceId'],
			global: ['organizations', 'plans'],
		}),
	],
});

Each model is scoped by the configured columns it has. With the setup above, projects (with organizationId) is scoped by organization, tasks (with both columns) by organization and workspace, and plans is never filtered. A model with none of the columns must be listed in global, set to false in models, or be a junction; otherwise better() throws PLUGIN_REQUIRED_COLUMN_MISSING.

Column names are the property keys of your Drizzle tables, not the database column names.

Options

Scoping a client

$tenant() takes an object keyed by tenant column and returns a new client. The original client stays unscoped. Values are typed from the insert type of each column.

const scoped = client.$tenant({ organizationId: session.organizationId });

const projects = await scoped.projects.findMany({
	include: { tasks: true },
	where: { archived: false },
});

A context can be partial. Each model needs a value for every column it uses, so an organization-only client reads projects but throws TENANT_REQUIRED on tasks. Narrow it with the missing columns:

const org = client.$tenant({ organizationId: 'org_1' });
const workspace = org.$tenant({ workspaceId: 7 });

await org.projects.findMany(); // organization filter
await workspace.tasks.findMany(); // organization and workspace filters

Narrowing only adds columns. Setting a column that is already set throws TENANT_ALREADY_SCOPED, even with the same value. $tenant() throws INVALID_ARGS for an empty object, a key outside columns, or a null/undefined value.

client.$tenant.values returns the active values ({} on an unscoped client). The scope survives $withContext(), extends(), transactions, nested savepoints, and the implicit transactions of relation writes.

app.use(async (c, next) => {
	c.set('db', client.$tenant({ organizationId: c.get('session').orgId }));
	await next();
});

$tenant() clones the client like $withContext(), so one call per request costs the same.

Bypassing the tenant

$withoutTenant({ reason }) returns a client without the tenant scope, for jobs, migrations, and admin tools. reason is required and must be a non-empty string. Every operation on that client passes meta.tenantBypass = { reason } to hooks, so bypasses can be logged:

const admin = client.$withoutTenant({ reason: 'nightly-billing' });
await admin.projects.count(); // every tenant

const audited = better(db, {
	hooks: {
		beforeQuery({ meta, table }) {
			if (meta?.tenantBypass) console.warn('tenant bypass', table, meta.tenantBypass);
		},
	},
	plugins: [tenant({ columns: 'organizationId' })],
});

$withoutPlugins() on a scoped delegate drops every plugin, the tenant scope included. Its operations pass meta.tenantBypass = { reason: '$withoutPlugins' }. On an unscoped client nothing is bypassed and no meta is added.

The recorded bypass reason cannot be replaced by per-call meta or a later $withContext() call. Calling $tenant() on a bypassed client clears the marker when it restores the tenant scope.

What is filtered

On a scoped client, the core adds the tenant predicate to every statement that references a scoped table.

const scoped = client.$tenant({ organizationId });

const rows = await scoped.projects.findMany({
	where: { name: { contains: 'api' } },
});

A related row of another tenant is treated as missing. If a task of organization a points at a project of organization b, include: { project: true } returns project: null, and where: { project: { is: null } } matches the task.

Writes

Creates stamp the tenant columns, so they are optional in the input types of create, createMany, upsert, and upsertMany, and in the $zod and ata create schemas.

const project = await scoped.projects.create({ data: { name: 'API' } });
// project.organizationId === organizationId

Tenant columns cannot move a row to another tenant. Use $withoutTenant() for that.

Upserts

An upsert can hit a unique key that belongs to another tenant. The plugin never updates that row; how the call ends depends on the dialect.

On PostgreSQL and SQLite, an upsert whose where does not identify a key also runs as a read followed by a write, and a key of another tenant ends in the database's unique violation.

Relation writes

connect, disconnect, and set resolve their selectors inside the tenant. A selector that points at another tenant's row throws Relation selector did not match a record. and nothing is written. set on a .through() relation replaces only links to the tenant's targets; links to rows of other tenants stay. A junction table that has tenant columns gets them stamped on new links.

When a foreign key includes a tenant column, connect accepts the context value and throws TENANT_MISMATCH for another one.

Junction tables

A table without tenant columns that is only reached through .through() relations between scoped models inherits the scope of both ends. It needs no tenant column and no global entry. Direct access to such a junction (scoped.projectMembers.findMany()) still throws TENANT_REQUIRED, because the junction alone cannot be filtered. Use $withoutTenant() to read it directly, or give it tenant columns.

Nullable models

A model listed as { columns, nullable: true } matches the tenant value or NULL, so NULL rows are shared by every tenant:

tenant({
	columns: 'organizationId',
	models: { templates: { columns: ['organizationId'], nullable: true } },
});

Creates always stamp the context value. Shared rows are read-only for a scoped client: update, updateMany, updateEach, delete, deleteMany, the update branch of upsert and upsertMany, and relation writes that change the related row match the tenant value only, never NULL. Pointing your own row at a shared row (connect when your row holds the foreign key) still works. An update on a shared row returns null (count 0 for batch writes), and setting its tenant column to your own value does not claim it. An upsert that conflicts with a shared row fails like a conflict with another tenant's row. Create, change, or delete shared rows through $withoutTenant().

Global models and overrides

Models in global, or set to false in models, are never filtered nor stamped, even when they have a tenant column. models can also restrict a model to some of the columns:

tenant({
	columns: ['organizationId', 'workspaceId'],
	models: {
		auditLogs: false,
		boards: ['workspaceId'],
	},
});

A listed column that the model lacks throws PLUGIN_REQUIRED_COLUMN_MISSING; a listed column outside columns throws INVALID_ARGS.

Missing tenant

With missing: 'throw' (the default), an operation on a scoped model without a value for each of its columns throws TENANT_REQUIRED before any SQL runs. The error carries table, column, and details.columns. Global models keep working on the root client.

missing: 'allow' runs those operations unfiltered on the missing columns. It exists for gradual migrations; leave it on only until every code path calls $tenant().

Strict mode

strict: true removes scoped models from the root client type, as properties and as repository(name) names (table keys and database names), including its $withContext() and extends() clients and its transactions. Only $tenant() and $withoutTenant() clients expose them, so a forgotten $tenant() fails at compile time. Global models stay on the root client. At runtime, root calls still throw TENANT_REQUIRED. strict with missing: 'allow' is a type error and a bootstrap error.

Raw SQL

Raw SQL cannot be filtered, so raw decides what scoped clients may do with $raw, $executeRaw, and $rawUnsafe. Unscoped and $withoutTenant() clients are not affected.

await scoped.$raw(
	sql`select count(*) from projects where organization_id = ${orgId}`,
	{ tenant: 'trusted' },
);

The opt-in reaches raw hooks in rawOptions. Tagged-template calls (scoped.$raw`...`) cannot pass options, so under 'deny' they always throw on a scoped client; pass a sql object with options instead.

PostgreSQL row-level security

rls: true adds the database as a second line of defense. For each operation on a scoped client, the plugin runs select set_config(<setting>, <value>, true) for every active tenant column inside the operation's transaction:

alter table projects enable row level security;
alter table projects force row level security;
create policy tenant_isolation on projects
	using (organization_id = nullif(current_setting('app.organization_id', true), ''))
	with check (organization_id = nullif(current_setting('app.organization_id', true), ''));

tenant({ columns: 'organizationId', raw: 'set-local', rls: true });

• The setting defaults to app.<database column name> of the first model that has the column. Override it with rls: { settings: { organizationId: 'tenant.org' } }.
• Each configured tenant column needs a distinct, non-empty setting name; invalid or duplicate names fail at client initialization.
• Use nullif(current_setting(..., true), '') in each RLS policy. PostgreSQL resets a custom setting to an empty string when the tenant scope is removed inside a transaction; nullif keeps that state from matching rows with an empty tenant ID.
• With rls enabled, $tenant() rejects empty string values because the policy treats an empty setting as absent.
• Settings are transaction-local. Standalone operations, .explain(), prepared executions, and 'set-local' raw calls run in an implicit transaction. Inside transaction(), the settings run when the active tenant changes; concurrent calls from sibling tenant clients are serialized with their SQL so each uses its own setting.
• Prepared reads re-prepare on each execution under RLS, because the settings must run in the same transaction. Expect prepared reads to cost about as much as regular reads there.
• Policies apply only to roles that are not superusers and do not have BYPASSRLS. Connect as an application role.
• With raw: 'deny' or 'allow', a raw call does not install settings by itself. Outside a transaction, the policy sees no tenant setting and hides the rows. Inside an existing transaction, a raw call can use a setting installed by an earlier scoped operation; switching to $withoutTenant() clears that setting before the raw call.

rls on MySQL or SQLite throws PLUGIN_DIALECT_UNSUPPORTED; raw: 'set-local' without rls throws INVALID_ARGS.

With other plugins

• cache: scoped reads include the tenant values in their cache key, so tenants never share entries. Invalidation stays per model, across tenants.
• soft-delete: soft deletes and restore() keep the tenant scope.
• zod and ata: tenant columns are optional in create schemas, like other generated columns.
• uuid and timestamps: they fill their own columns; the tenant stamp runs on the same insert.
• rules: its tenant rules check hook payloads only. Use this plugin for isolation.

Errors

Limits

• Raw SQL is not rewritten. Only the raw policy and RLS cover it.
• The scope is a filter on better-drizzle queries. Drizzle queries on the underlying db bypass it; use RLS when other code shares the database.
• Cache invalidation is per model, not per tenant.