The tenantDb helper for scoped queries

A missing where leaks another company’s invoice

A teammate’s pull request adds a route that opens a single invoice. The fetch reads like this:

const [invoice] = await db.select().from(invoices).where(eq(invoices.id, id));

It selects from invoices, filters on the id, takes the row. Nothing jumps out, so you approve it and it ships.

But invoices holds every invoice from every company that uses your product, and this query asks only for the row with this id. It never adds “and only if it belongs to the current user’s org.” So when a user pastes a URL with an id that isn’t theirs, or just increments their own, the database hands the row over and their dashboard renders another company’s invoice: amounts, customer name, line items.

This is the highest-severity bug class in a multi-tenant SaaS. One company seeing another’s data ends trials, breaks contracts, and triggers breach disclosures. It is dangerous for the same reason your review missed it: the defect is the absence of a line, and a missing filter looks the same as nothing missing at all.

You already have everything you need. The requireOrgUser() from last lesson returns a trusted { user, orgId, role }, so the orgId is right there. Every row of invoices carries an organizationId column, which makes it a tenant-owned table . The fix is one predicate:

Looks fine in review

src/app/invoices/[id]/page.tsx

const [invoice] = await db
  .select()
  .from(invoices)
  .where(eq(invoices.id, id));

Passes review every time. There is no wrong code, only a filter that isn’t here. To catch it, the reviewer would have to check for a missing org predicate on every read, on every PR, forever.

What it actually returns

src/app/invoices/[id]/page.tsx

const { orgId } = await requireOrgUser();
const [invoice] = await db
  .select()
  .from(invoices)
  .where(and(eq(invoices.organizationId, orgId), eq(invoices.id, id)));

Correct, and that’s the trap. Pin the org, then the id, and the query can only return this org’s invoice. But the correctness is optional: the next person to write a fetch-by-id has to remember the and(eq(organizationId, ...)), and the day they don’t, the code still compiles, passes review, and ships.

Manual scoping is not the mistake. It’s airtight, and it’s what a careful developer writes. The bug is that the correctness is optional, and a defense that depends on every developer remembering it on every query has already failed; you just don’t know which PR will reveal it.

By the end of this lesson, the unscoped read won’t compile.

Why a scoped client beats more review

How do you defend against a bug whose signature is a missing line?

The instinct is more review and discipline. It fails, because reviewers habituate. The missing filter gets caught on the first PR, when everyone is watching the new tenancy rule, and slips through on the fortieth, buried in a diff that also touches three components and a test. You cannot pay attention, forever, to reliably catch nothing being there.

The fix is structural: change the shape of the call so the scoped query is the only one the normal path can express, and writing an unscoped one means reaching for a visibly more dangerous tool.

Caution

A guard you have to remember to call is not a guard: it can’t save you from forgetting it. What you build here is different. The unscoped call shape stops being expressible at all, so forgetting it no longer typechecks.

You get there with two clients that look different at the import line:

• db is the raw Drizzle client, unscoped, able to touch any org’s rows. It exists for admin tools, migrations, and scripts that legitimately span organizations, and you spot it on sight because it’s imported from @/db.
• tenantDb(orgId) is a thin wrapper around db, and the only client a request handler reaches for. Every call it exposes is org-scoped by construction.

A tenant read through bare db isn’t caught by inspecting its where; it’s caught because the wrong client is imported. That’s a one-line signal, the same on every PR, the kind a linter flags and a reviewer’s eye snags on.

Watch video

CodeOpinion (9 min) on the shared-database tenancy model this lesson assumes. It covers the “transparently add a tenant filter to every query” idea that tenantDb makes structural, plus trade-offs we skip, such as noisy neighbors and database-per-tenant.

A preview of the two shapes, before you build the wrapper:

db.query.invoices.findMany({ where: eq(invoices.status, 'open') });          // unscoped
tenantDb(orgId).query.invoices.findMany({ where: eq(invoices.status, 'open') }); // scoped

Architectural Principle #5 says consume libraries directly: don’t wrap Drizzle in your own query layer, because thin wrappers rot, leak, and obscure the API the team needs to learn. So a wrapper around Drizzle should give you pause.

tenantDb is a deliberate, named exception, and naming it is what keeps it honest; unnamed exceptions are how a codebase quietly grows a dozen accidental abstractions. The boundary is precise: wrap the tenant read and write path, leave raw db as the escape hatch. Direct Drizzle stays correct for admin scripts and migrations; tenantDb is mandatory for any request-handled read or write on a tenant table.

Note

This is one of two matched carve-outs from “consume libraries directly.” This one wraps the data layer so tenant scope can’t be skipped. The other, authedAction, wraps the Server Action boundary so the auth and validation seams can’t be skipped; it arrives in a later chapter on roles and permissions.

The helper: wrap the query API, inject the org filter

We build this in two passes: first make it correct at runtime so the org filter is injected, then layer types on top so skipping the scope fails to compile.

Start at the call site you’re aiming for:

const db = tenantDb(orgId);
const rows = await db.query.invoices.findMany({
  where: eq(invoices.status, 'open'),
});

Notice what’s not there: no organizationId anywhere. You ask for open invoices; the org scope is the wrapper’s job. The predicate you used to remember is now structural and impossible to leave out, because you’re no longer the one writing it.

The wrapper wraps the Drizzle relational query API you already know, the db.query.<table>.findMany({ where, with }) surface, for one table to start:

import 'server-only';
import { and, eq } from 'drizzle-orm';
import type { SQL } from 'drizzle-orm';
import { db } from '@/db';
import { invoices } from '@/db/schema';

export const tenantDb = (orgId: string) => ({
  query: {
    invoices: {
      findMany: (config?: { where?: SQL }) =>
        db.query.invoices.findMany({
          ...config,
          where: and(eq(invoices.organizationId, orgId), config?.where),
        }),
    },
  },
});

The factory signature. tenantDb takes an orgId and returns a plain object exposing only the slice of Drizzle the app uses, with the org scope baked in. Closing over orgId is the whole trick: every method the object exposes already knows the org.

import 'server-only';
import { and, eq } from 'drizzle-orm';
import type { SQL } from 'drizzle-orm';
import { db } from '@/db';
import { invoices } from '@/db/schema';

export const tenantDb = (orgId: string) => ({
  query: {
    invoices: {
      findMany: (config?: { where?: SQL }) =>
        db.query.invoices.findMany({
          ...config,
          where: and(eq(invoices.organizationId, orgId), config?.where),
        }),
    },
  },
});

The query.invoices surface mirrors the real client’s shape, so the call site reads almost identically to raw Drizzle. Only the source of the org filter changes.

import 'server-only';
import { and, eq } from 'drizzle-orm';
import type { SQL } from 'drizzle-orm';
import { db } from '@/db';
import { invoices } from '@/db/schema';

export const tenantDb = (orgId: string) => ({
  query: {
    invoices: {
      findMany: (config?: { where?: SQL }) =>
        db.query.invoices.findMany({
          ...config,
          where: and(eq(invoices.organizationId, orgId), config?.where),
        }),
    },
  },
});

The heart of the helper. Spread the caller’s config through unchanged, then override where with and(eq(invoices.organizationId, orgId), config?.where). The org predicate always comes first; the caller’s where rides as the second argument.

import 'server-only';
import { and, eq } from 'drizzle-orm';
import type { SQL } from 'drizzle-orm';
import { db } from '@/db';
import { invoices } from '@/db/schema';

export const tenantDb = (orgId: string) => ({
  query: {
    invoices: {
      findMany: (config?: { where?: SQL }) =>
        db.query.invoices.findMany({
          ...config,
          where: and(eq(invoices.organizationId, orgId), config?.where),
        }),
    },
  },
});

The undefined case. With no caller where, config?.where is undefined, and Drizzle’s and(predicate, undefined) drops it, leaving the org predicate alone. So tenantDb(orgId).query.invoices.findMany() returns this org’s invoices, never everyone’s. We still write the and explicitly so the intent is on the page.

← Previous 1 / 1 Next →

Three conventions are worth a word. import 'server-only' marks the file server-only because it imports the database client, so a stray import from a Client Component becomes a build error instead of a leaked connection string. The factory is a const arrow exported by name, the house default. In the real codebase it also carries an explicit return type, because here the type is what enforces the scope.

Now the part that proves the design out: it holds even against someone trying to slip past it. Suppose a developer writes a where designed to escape the scope:

tenantDb(orgId).query.invoices.findMany({
  where: or(eq(invoices.organizationId, otherOrgId), eq(invoices.status, 'open')),
});

They’ve asked for another org’s rows or any open row. It doesn’t leak, because the wrapper pins their entire or(...) under the org and:

and(eq(invoices.organizationId, orgId), or(eq(invoices.organizationId, otherOrgId), ...))

The outer and requires organizationId to equal this org on every returned row, so the otherOrgId branch can only match a row belonging to both orgs at once, a contradiction. The org and is on the outside, and nothing the caller writes inside can climb out.

One more thing about the call site: the type of what you get back.

const scoped = tenantDb(orgId);
const rows = await scoped.query.invoices.findMany({ where: eq(invoices.status, 'open') });

That’s the runtime behavior done: the filter is injected, the unscoped result is unreachable through this client, and the call site barely changed. What it can’t yet do is turn a nonsensical call, scoping a table that has no org, into a compile error. That comes after writes.

Writes: inject on insert, predicate on update and delete

A wrapper that scopes reads but leaves writes raw still has a hole: a missing org filter on an update is as bad as on a select, and worse, because it mutates. So tenantDb wraps the three write shapes too.

insert, update, and delete are more methods on the same factory, each closing over orgId like the query surface above. Here is what each injects.

insert owns the organizationId column, stamping organizationId: orgId on every row:

Raw db — caller remembers

await db.insert(invoices).values({
  customerId,
  organizationId: orgId,
  amountCents,
  status: 'open',
});

The caller has to remember the column. Forget the highlighted line and the insert either fails on a not-null constraint or, if the column is nullable, writes an orphan row. The org on the row is the caller’s job, on every insert.

tenantDb — helper owns it

await tenantDb(orgId).insert(invoices).values({
  customerId,
  amountCents,
  status: 'open',
});

The helper owns the column. The wrapper stamps it on, so the caller can’t forget it and the insert can’t land in the wrong org: the only org it can carry is the one the wrapper closed over. If the caller passes an organizationId that disagrees with orgId, the helper throws, since that mismatch is a programmer bug, not a user error.

That throw follows the course’s error rule: return expected failures as a Result, throw on impossible ones. A mismatched org on a scoped insert is impossible by construction, so it throws loudly in development where the bug belongs.

update and delete target existing rows, so they can’t inject a column; instead they and-in the org predicate like reads. A scoped update runs where(and(eq(organizationId, orgId), callerWhere)), so an update aimed at another org’s row matches zero rows. Compare the two deletes. The raw one:

await db.delete(invoices).where(eq(invoices.id, id));

removes whatever invoice has that id, whoever owns it. The scoped one:

await tenantDb(orgId).delete(invoices).where(eq(invoices.id, id));

removes it only if it belongs to this org. The difference between “deleted the wrong company’s invoice” and “deleted nothing” is one and, and the wrapper makes it not yours to forget.

Sooner or later someone will want a .raw escape to run “just this one tricky query” against the unscoped client. Don’t add it: the moment the wrapper hands back the raw client, you’ve reintroduced the call shape you built it to remove. Keep the separately imported db as the only way to reach unscoped Drizzle, because that import is the visible, reviewable signal; a bypass hidden inside tenantDb is not.

Note

The wrapper enforces zero cross-org writes at the application layer. The database has its own backstops: foreign-key constraints keeping an invoice’s customerId pointed at a real customer, and, for the one table that earns the cost, Postgres Row-Level Security as a last line that holds even if the app layer is bypassed. That database-side defense is the next lesson.

Some tables have no org: the table registry

Now the second pass: turn the runtime backstop into a compile-time guarantee. This is the densest idea in the lesson, so we build it twice, a simple version first and the real one on top.

Not every table is tenant-owned. Better Auth’s user and verification tables are global: an account isn’t owned by an organization, and one account can belong to several, so they have no organizationId column. This call is meaningless:

tenantDb(orgId).query.user.findMany({ where: ... });

There is nothing to scope by. We don’t want it to silently return every user, or to fail only at runtime. We want a type error, caught before it compiles.

Pass one, the runtime backstop. Keep a set of the tenant-owned table names, and have the helper refuse anything else:

const TENANT_TABLES = ['invoices', 'customers', 'member'] as const;

Reach a table that isn’t in TENANT_TABLES and the helper throws. That’s the floor: loud and immediate, but still a runtime failure, firing in a test if you’re lucky and in production if you’re not.

Pass two, the type-level guarantee. Feed that same set to the type system, so only the tenant tables are even reachable on the wrapper’s query surface. The tool is a mapped type: take the union of tenant-table names and map the query type over only those keys.

const TENANT_TABLES = ['invoices', 'customers', 'member'] as const;

type TenantTable = (typeof TENANT_TABLES)[number];

type TenantQuery = {
  [K in TenantTable]: {
    findMany: (config?: { where?: SQL }) => Promise<unknown[]>;
  };
};

export const tenantDb = (orgId: string): { query: TenantQuery } => {
  // ...injects and(eq(table.organizationId, orgId), config?.where) per table
};

TENANT_TABLES is the single source of truth, frozen with as const so its values stay literal. TenantTable lifts them into a union: 'invoices' | 'customers' | 'member'. One list, used twice: as the runtime backstop and as the type-level guarantee.

const TENANT_TABLES = ['invoices', 'customers', 'member'] as const;

type TenantTable = (typeof TENANT_TABLES)[number];

type TenantQuery = {
  [K in TenantTable]: {
    findMany: (config?: { where?: SQL }) => Promise<unknown[]>;
  };
};

export const tenantDb = (orgId: string): { query: TenantQuery } => {
  // ...injects and(eq(table.organizationId, orgId), config?.where) per table
};

TenantQuery is a mapped type. [K in TenantTable] gives the query object one key per name in the union, and only those, each with a scoped findMany. There is no user or verification key, because those names aren’t in the union.

const TENANT_TABLES = ['invoices', 'customers', 'member'] as const;

type TenantTable = (typeof TENANT_TABLES)[number];

type TenantQuery = {
  [K in TenantTable]: {
    findMany: (config?: { where?: SQL }) => Promise<unknown[]>;
  };
};

export const tenantDb = (orgId: string): { query: TenantQuery } => {
  // ...injects and(eq(table.organizationId, orgId), config?.where) per table
};

The return type pins the surface to exactly TenantQuery, and that one annotation does the enforcing: tenantDb(orgId).query.user now accesses a key the type says doesn’t exist, so TypeScript rejects it before the code runs. The pass-one backstop still sits underneath, but for any registered table the type system catches the mistake first, as a red squiggle in the editor.

← Previous 1 / 1 Next →

The registry is a list of names; the type system reads it; the query surface gets exactly those keys and no others. Reach for a non-tenant table and you’re reaching for a property TypeScript never knew about, underlined in red as you type it rather than caught by a reviewer or a test.

Watch video

New to the [K in TenantTable] syntax? Typed Rocks builds a mapped type from scratch in 7 minutes, the exact mechanism behind the scoped surface above.

The flow of that guarantee, in one picture:

TENANT_TABLES the registry — one source of truth

invoices customers member

tenantDb(orgId) typed factory

Maps the registry into the query surface — one key per registered table, and no others.

scoped query surface what compiles

query.invoices

query.customers

query.member

query.user not in registry → type error

The tenant-table list feeds the type system, which decides what the scoped client lets you reach.

The word is overloaded, so be precise: a registry here is one authoritative list the types derive from. Add a tenant-owned table to the schema, add its name to TENANT_TABLES, and the surface grows to include it. Forget, and the wrapper won’t let you query it through there, a far better failure than scoping it wrong.

documents is a tenant-owned table, but it's missing from the registry — so tenantDb(orgId).query.documents doesn't exist as a type, and the @ts-expect-error on the user line is reported as 'unused' because the registry isn't wired up yet. Add 'documents' to TENANT_TABLES so the scoped surface includes it. The user call below must stay a type error — user is global, not tenant-owned, so it must remain unreachable.

• Fix all errors

Booting type-checker… Reset

The canonical action: requireOrgUser, then tenantDb

You have a scoped client. Now fix the shape of a tenant-scoped Server Action so the unscoped version looks wrong on sight. This opener heads every action that touches tenant data:

const { user, orgId } = await requireOrgUser();
const db = tenantDb(orgId);
const rows = await db.query.invoices.findMany({ where: eq(invoices.status, 'open') });

Three lines: validate the session for a trusted orgId, narrow the client to that org, then read with no manual filter to forget. The scope is fixed on line two, so no later line can drop it.

orgId comes from requireOrgUser() and only from there: not a route param, form field, or header, nothing the client can set. That is the security invariant the pattern rests on:

Danger

tenantDb trusts its caller: it scopes to whatever orgId you hand it. An orgId from the URL gives a perfectly scoped query against an org the user may have no right to read. The helper guarantees scoping to some org; only requireOrgUser() guarantees it’s the user’s. The orgId must come from the validated session, every time.

Both pieces slot into the five-seam Server Action shape: parse, authorize, mutate, revalidate, return. requireOrgUser() is the authorize seam; tenantDb(orgId) keeps mutate and every read scoped. The seams haven’t changed; tenancy made two of them concrete.

Try it. At review time you don’t audit every where, you check the client and where the orgId comes from. The PR below has three bugs this lesson exists to prevent — comment on every line you’d flag:

Review this Server Action the way you'd review a teammate's PR. Three things here are exactly the bugs the tenantDb pattern exists to stop — leave a comment on each line you'd flag. Click any line to leave a review comment, then press Submit review.

src/app/invoices/actions.ts

'use server';

export async function listOpenInvoices(searchParams: { orgId: string }) {
  const orgId = searchParams.orgId;
  const open = await db.query.invoices.findMany({
    where: eq(invoices.status, 'open'),
  });

  const customers = await tenantDb(orgId).query.customers.findMany();

  return { open, customers };
}

Submit review

The correct version derives orgId from the session and threads it through tenantDb for both reads:

'use server';

export async function listOpenInvoices() {
  const { orgId } = await requireOrgUser();
  const db = tenantDb(orgId);

  const open = await db.query.invoices.findMany({
    where: eq(invoices.status, 'open'),
  });
  const customers = await db.query.customers.findMany();

  return { open, customers };
}

Legitimate cross-org reads

The rule so far is absolute: tenant reads go through tenantDb, scoped, no exceptions. But some reads genuinely span every org, an admin dashboard that triages support tickets across all customers, a nightly revenue rollup, a BI export. Being unscoped is their whole point, so they can’t go through tenantDb.

Those reads use the unwrapped db, and they live in a dedicated admin/ or scripts/ directory, where the raw-db import is the loud signal you want. Different client, different file. A reviewer there knows they’re in cross-org territory; a raw-db import anywhere else is a red flag.

Caution

Do not add a tenantDb({ allOrgs: true }) mode. A bypass inside the helper turns “reach for a different, scary client” into “pass a flag,” and the guarantee erodes one innocent flag at a time. The missing escape valve is the feature: a separate client in a separate file keeps every cross-org read a deliberate decision.

The ecosystem reaches for heavier tools you can decline: ORM extensions and proxies that auto-inject the org filter by reflection, like Prisma’s client extensions and Drizzle proxy tricks. This stack does the opposite, an explicit wrapper of about forty lines where the type signature is the documentation, a misshapen call won’t compile, and the raw client stays one import away. You outgrow it only past a table count the wrapper can’t track by hand, which a young SaaS rarely hits. Explicit and boring beats clever and magic when a mistake means one company seeing another’s data.

One last thing: the name. This pattern travels under forTenant, dbFor, orgDb, tenantScoped, and a dozen more. Pick one and grep for it; a second name is the day two people build the same thing twice. This course uses tenantDb.

Check your understanding

Sort each scenario into the client it should use:

Sort each task into the client it should use — the scoped `tenantDb(orgId)` for request-handled tenant data, or the raw `db` (in a separate admin/script file) for legitimate cross-org work. Drag each item into the bucket it belongs to, then press Check.

tenantDb(orgId) Request-handled, one org's data

raw db, separate file Cross-org admin / scripts / migrations

Render an invoice for the signed-in user’s current org

List this org’s customers for a dropdown

Archive an invoice from a Server Action

The nightly all-orgs revenue rollup

A migration backfilling a new column on every row

The internal support console reading any customer’s data

Check Reset

The fix for the worst multi-tenant bug, a tenant read that forgot its org filter, isn’t to remember harder but to make forgetting fail to compile: two clients, one scoped by construction and one raw and visible.

The next lesson goes one layer down, to Postgres Row-Level Security, a second wall in the database for the one table high-stakes enough to deserve it even after tenantDb guards the app layer.

External resources

Drizzle — Relational queries

orm.drizzle.team

The query.<table>.findMany API the helper wraps, including the where and with options.

Drizzle — Filter operators

orm.drizzle.team

and / eq / or and the rest of the predicate builders the helper composes.

Enforcing a tenantId where clause (Drizzle discussion)

github.com

The community wrestling with the exact problem this lesson solves — wrappers, proxies, lint rules, and RLS compared.

TypeScript Handbook — Mapped Types

typescriptlang.org

The official reference for the [K in TenantTable] syntax that derives the scoped query surface, including key remapping.