drizzle-scoped-db

One forgotten WHERE clause and a query returns rows it should never see. drizzle-scoped-db guards against that.

It wraps a Drizzle ORM handle in a typed, scoped one (orgDb, tenantDb, workspaceDb). The guardrail fits any predicate a query must never forget: tenant, org, user, region, soft-delete. Scope predicates are injected into your queries automatically, and in strict mode a scoped query that forgets its predicate throws at the call site, before it reaches the database.

// Throws MissingScopedWhereError instead of returning every workspace's projects
await workspaceDb.select().from(projects);

// Allowed: scoped predicate is present (and re-injected as defense in depth)
await workspaceDb.select().from(projects).where(eq(projects.workspaceId, workspaceId));

TL;DR

• 🛡️ Strict by default. A missing where or scope predicate throws instead of leaking rows.
• 🤖 Catches the mistakes humans, codegen, and AI agents make. The forgotten scope filter surfaces in review and at runtime, not in an incident.
• 🧩 Dialect-generic. Built on Drizzle core types (Postgres, SQLite, MySQL, SingleStore), no DB lock-in. Layers with RLS rather than replacing it.

Where this fits

drizzle-scoped-db is an application-layer guardrail in the query builder. It isn't database-enforced isolation, and it's built to sit alongside RLS, not compete with it.

	Enforcement layer	Isolation model	DB lock-in	Catches app-code mistakes
drizzle-scoped-db	App (query builder)	Shared tables + injected predicate	None (dialect-generic)	✅ typed + loud failures
Drizzle native RLS	Database	Shared tables + row policies	Postgres-only	❌ enforced below the app
drizzle-multitenant	App (middleware)	Schema-per-tenant	Postgres-only	n/a (different model)
pgvpd	Proxy / wire	RLS via protocol proxy	Postgres-only	❌
Nile	DB vendor	Virtual tenant DBs	Nile-specific	❌

RLS gives you a boundary the application can't bypass, but it lives in the database: per-row policy evaluation, connection-pooling friction, silent failures that are hard to debug, and Postgres only. PlanetScale covers the tradeoffs in detail. drizzle-scoped-db keeps the scope boundary in application code instead, where it's visible in TypeScript, type-checked, and loud: a forgotten predicate throws instead of returning the wrong rows. If you want a database-level backstop too, layer RLS underneath. See How this relates to RLS.

Why use it

• Pass typed scoped DB handles instead of the raw DB.
• Declare scoping rules once per table.
• Strict mode by default: missing where or missing scope predicate throws.
• Inject scope predicates into supported selects, joins, mutations, and relational root queries.
• Validate scoped inserts before they reach the database.
• Catch missing predicates in human-written, generated, or agent-authored code.

Use cases

drizzle-scoped-db is a guardrail for any predicate a query must never forget. The scope is whatever you express as a Drizzle where:

• Tenant or org isolation. Keep tenant_id = currentTenant on every query so one customer never sees another's rows.
• Per-user data. Force user_id = currentUser on private rows.
• Region or data residency. Keep region = 'eu' on every query.
• Soft deletes. Always exclude deleted rows with isNull(table.deletedAt) via defineScopedTable.
• Visibility. A read handle that injects published = true, so public endpoints never surface drafts.
• Row-level ACLs. A composite predicate such as owner_id = me OR shared_with @> me.

These share the same boundaries: the guardrail covers tables with rules, on the wrapped handle, in application code, not as a database-enforced boundary. See Security model.

How this relates to RLS

RLS is enforced by the database. drizzle-scoped-db is enforced by the application path: scoped code receives a scoped Drizzle handle instead of the raw DB. It focuses on typed query builders, explicit scoped capabilities, and loud failures when predicates are missing.

const workspaceDb = createScopedDb(db, {
  scopeName: "workspace",
  scopeValue: workspaceId,
  rules,
});

const project = await workspaceDb
  .select({
    id: projects.id,
    name: projects.name,
  })
  .from(projects)
  .where(and(eq(projects.id, projectId), eq(projects.workspaceId, workspaceId)));

// Also injected automatically: eq(projects.workspaceId, workspaceId)

Conceptually, strict mode makes scoped reads look like this:

WHERE projects.id = projectId
  AND projects.workspace_id = workspaceId -- caller wrote this; strict mode checks it
  AND projects.workspace_id = workspaceId -- wrapper injects this again

The predicate appears twice on purpose. You write it so the boundary is visible in code review and type-checked by TypeScript. Strict mode verifies you didn't forget it, then the wrapper injects its own copy as a backstop. The duplicate is redundant in the SQL and costs nothing; what it buys is a thrown error instead of a silent cross-scope read when someone forgets the predicate.

Application code that should be scoped should receive the scoped DB handle, not the raw Drizzle instance.

The two approaches are not mutually exclusive, and neither is strictly "above" the other:

• App-layer scoping (this package) keeps isolation where your code lives: typed, reviewable, dialect-generic, and loud on mistakes. It can't constrain code that deliberately bypasses the scoped handle (see Security model).
• Database RLS is a boundary the app can't bypass, but it's Postgres-only and carries the operational costs PlanetScale documents in RLS sounds great until it isn't: per-row policy evaluation, pooling friction, and silent failures.

Use app-layer scoping as your primary, visible guardrail; add RLS underneath when you also want a database-level boundary that holds even if app code goes around the wrapper. On MySQL, SingleStore, or other engines without RLS, app-layer scoping is the practical path.

Install

npm install @modemdev/drizzle-scoped-db drizzle-orm

pnpm add @modemdev/drizzle-scoped-db drizzle-orm

Drizzle is a peer dependency.

Quick start

import { createScopedDb, scopeByColumn } from "@modemdev/drizzle-scoped-db";
import { and, eq } from "drizzle-orm";
import { projects, tasks } from "./schema";

const workspaceDb = createScopedDb(db, {
  scopeName: "workspace",
  scopeValue: workspaceId,
  rules: [
    scopeByColumn(projects, projects.workspaceId, { insertKey: "workspaceId" }),
    scopeByColumn(tasks, tasks.workspaceId, { insertKey: "workspaceId" }),
  ],
});

const project = await workspaceDb
  .select()
  .from(projects)
  .where(and(eq(projects.id, projectId), eq(projects.workspaceId, workspaceId)));

The wrapper still injects the workspace predicate again as defense in depth.

Joined tables with declared rules receive their own scope predicates too. For joins, the joined table predicate is added to the join condition so leftJoin keeps its outer-join behavior:

const rows = await workspaceDb
  .select()
  .from(projects)
  .leftJoin(tasks, eq(tasks.projectId, projects.id))
  .where(and(eq(projects.id, projectId), eq(projects.workspaceId, workspaceId)));

// Also injected automatically:
// - eq(projects.workspaceId, workspaceId) in the WHERE clause
// - eq(tasks.workspaceId, workspaceId) in the JOIN condition

Insert validation

When insertKey is provided, inserted rows must match the current scope value.

await workspaceDb.insert(projects).values({
  id: projectId,
  workspaceId,
  name: "Roadmap",
});

// Throws InvalidScopedInsertError
await workspaceDb.insert(projects).values({
  id: projectId,
  workspaceId: "another-workspace",
  name: "Wrong workspace",
});

Batch inserts are validated row by row.

Update and delete

Scoped predicates are injected into mutations too.

await workspaceDb
  .update(tasks)
  .set({ status: "done" })
  .where(and(eq(tasks.id, taskId), eq(tasks.workspaceId, workspaceId)));

await workspaceDb
  .delete(tasks)
  .where(and(eq(tasks.id, taskId), eq(tasks.workspaceId, workspaceId)));

Relational query API

Declare queryName to scope db.query.<name>.findFirst and findMany.

const workspaceDb = createScopedDb(db, {
  scopeName: "workspace",
  scopeValue: workspaceId,
  rules: [
    scopeByColumn(projects, projects.workspaceId, {
      queryName: "projects",
      insertKey: "workspaceId",
    }),
  ],
});

const project = await workspaceDb.query.projects.findFirst({
  where: (project, { and, eq }) =>
    and(eq(project.id, projectId), eq(project.workspaceId, workspaceId)),
  with: {
    tasks: true,
  },
});

Tables without a matching rule pass through unchanged.

Relational with entries are root-only today: findFirst / findMany are scoped, but nested relation rows rely on scope-safe relationships, explicit relation filters, or database constraints.

Data model shape

This package works best when scope ownership is represented in your schema:

• scope columns on scoped tables
• scoped rules for protected tables
• indexes for scoped access paths, e.g. (scope_id, id) and (scope_id, foreign_id)
• globally unique IDs or constraints that reject invalid cross-scope references

Write rules explicitly for small schemas, or generate them once from schema metadata in an app-specific facade.

Explicit rules:

const rules = [
  scopeByColumn(projects, projects.workspaceId, { insertKey: "workspaceId" }),
  scopeByColumn(tasks, tasks.workspaceId, { insertKey: "workspaceId" }),
  scopeByColumn(comments, comments.workspaceId, { insertKey: "workspaceId" }),
];

Generated rules:

const tenantScopedRules = Object.values(schema)
  .filter((table) => isDrizzleTable(table) && "tenantId" in table)
  .map((table) =>
    scopeByColumn(table, table.tenantId, {
      insertKey: "tenantId",
      columnName: "tenant_id",
    }),
  );

With either shape, the wrapper can scope root tables and joined tables with rules. Your schema still owns data consistency, such as preventing a task in one scope from referencing another scope's project.

Strict mode

Strict mode is enabled by default and intended for most app code. Scoped selects, updates, deletes, and relational queries must include a where clause with the declared scope predicate.

Callers write the scope predicate, the wrapper verifies it, then injects it again. If generated code, agent-authored code, or a rushed refactor forgets the predicate, the query throws.

const workspaceDb = createScopedDb(db, {
  scopeName: "workspace",
  scopeValue: workspaceId,
  rules: [scopeByColumn(projects, projects.workspaceId)],
});

// Throws MissingScopedWhereError
await workspaceDb.select().from(projects);

// Throws MissingScopedPredicateError
await workspaceDb.select().from(projects).where(eq(projects.id, projectId));

// Allowed; the wrapper still injects its own scope predicate as defense in depth.
await workspaceDb
  .select()
  .from(projects)
  .where(and(eq(projects.id, projectId), eq(projects.workspaceId, workspaceId)));

The predicate must sit on the scoped table itself: filtering a joined table's same-named column (e.g. eq(tasks.workspaceId, workspaceId) while selecting projects) does not satisfy the check, though aliased self-joins still do.

Custom defineScopedTable rules need hasScopeInWhere for strict validation. Opt out with strict: false if you want pure predicate injection:

const workspaceDb = createScopedDb(db, {
  scopeName: "workspace",
  scopeValue: workspaceId,
  strict: false,
  rules: [scopeByColumn(projects, projects.workspaceId)],
});

await workspaceDb.select().from(projects).where(eq(projects.id, projectId));
// Executes with: and(eq(projects.id, projectId), eq(projects.workspaceId, workspaceId))

Custom scope rules

Use defineScopedTable for composite scopes or predicates that are not a single equality column.

import { createScopedDb, defineScopedTable } from "@modemdev/drizzle-scoped-db";
import { and, eq } from "drizzle-orm";

const scopedDb = createScopedDb(db, {
  scopeName: "workspace-region",
  scopeValue: { workspaceId, regionId },
  rules: [
    defineScopedTable(records, {
      where: (scope) =>
        and(eq(records.workspaceId, scope.workspaceId), eq(records.regionId, scope.regionId)),
      validateInsert: (row, scope) =>
        row.workspaceId === scope.workspaceId && row.regionId === scope.regionId,
    }),
  ],
});

Escape hatches

ScopedDb intentionally does not mirror the full Drizzle API. It covers the common guarded path — scoped selects, joins, CRUD mutations, relational reads, and transactions — without pretending every advanced Drizzle shape is scope-safe.

When you need to step outside that surface, use an explicit escape hatch so you (and your agent) can see the audit boundary.

Local escape: .$unsafeUnscoped()

Use after scoped insert validation, usually for upserts/conflict handlers:

workspaceDb
  .insert(records)
  .values({ workspaceId, regionId, key, value }) // scope-validated here
  .$unsafeUnscoped()
  .onConflictDoUpdate({ target: [records.workspaceId, records.key], set: { value } });

The inserted values were checked, but the conflict target, set, and follow-up where clauses are yours to keep scope-safe. Prefer unique keys that include the scope columns, and never let set move a row across scopes.

Root escape: _unsafeUnscopedDb

Use when there is no scoped chain to start from:

workspaceDb._unsafeUnscopedDb;

Common cases: migrations, admin jobs, test setup, cross-scope maintenance, raw SQL, CTEs/subqueries, $dynamic, or query shapes the scoped facade does not model.

Security model

drizzle-scoped-db protects supported Drizzle query-builder calls that go through the scoped wrapper. It is not a complete database isolation system and cannot protect code that bypasses the scoped capability.

The wrapper scopes supported selects, joins, mutations, root relational queries, and validated inserts. The schema shape in Data model shape still matters: your data model needs ownership columns, indexes, and relationship invariants that match how your app scopes data.

Not protected:

• raw SQL, _unsafeUnscopedDb, or helpers that close over the raw DB
• query builder methods reached after .$unsafeUnscoped() or through _unsafeUnscopedDb
• tables or joined tables without rules
• nested relational with rows unless your relationships, filters, or constraints enforce scope safety
• invalid cross-scope rows that your database constraints allow
• deliberate bypasses of the scoped DB capability

RLS, database permissions, and other database-native controls can be layered with scoped handles when you need enforcement outside the typed application query-builder path.

Dialect support

The package uses Drizzle core Table, Column, and SQL types, so rules are not tied to pg-core.

Expected support:

• PostgreSQL
• SQLite
• MySQL
• SingleStore
• any Drizzle driver with the standard select, insert, update, delete, and optional query APIs

selectDistinctOn is exposed only when the wrapped Drizzle instance provides it, which is primarily a PostgreSQL feature.

Wrapped APIs

Currently wrapped:

• select().from(table).where(...), including .leftJoin(...) / .innerJoin(...) tables with rules
• selectDistinct().from(table).where(...), including .leftJoin(...) / .innerJoin(...) tables with rules
• selectDistinctOn(...).from(table).where(...) when supported by the driver, including .leftJoin(...) / .innerJoin(...) tables with rules
• insert(table).values(...), plus .returning(...), .$returningId() when supported, and .$unsafeUnscoped() for raw continuation
• update(table).set(...).where(...)
• delete(table).where(...)
• query.<queryName>.findFirst(...)
• query.<queryName>.findMany(...)
• transaction(...), with a scoped transaction DB passed to the callback

Tables without rules and unwrapped APIs pass through to the underlying Drizzle instance.

API

createScopedDb(db, options)

type CreateScopedDbOptions<TScope> = {
  scopeName: string;
  scopeValue: TScope;
  rules: ScopedTableRule<TScope>[];
  strict?: boolean; // defaults to true
  unscopedDbPropertyName?: string; // defaults to '_unsafeUnscopedDb'
  scopeValueProperty?: string;
  toJSON?: (scopeValue: TScope, scopeName: string) => unknown;
  extensions?: (scopeValue: TScope, scopeName: string) => Record<string, unknown>;
  errors?: ScopedDbErrors<TScope>;
};

scopeByColumn(table, column, options)

type ScopeByColumnOptions<TScope> = {
  queryName?: string;
  tableName?: string;
  insertKey?: string;
  columnName?: string;
  equals?: (rowValue: unknown, scopeValue: TScope) => boolean;
};

defineScopedTable(table, rule)

type ScopedTableRule<TScope, TInsert = Record<string, unknown>> = {
  table: Table;
  queryName?: string;
  tableName?: string;
  where: (scopeValue: TScope) => SQL | undefined;
  validateInsert?: (row: TInsert, scopeValue: TScope) => boolean;
  // Required when createScopedDb({ strict: true }) is enabled.
  hasScopeInWhere?: (condition: SQL | undefined) => boolean;
};

assertDrizzleCompatibility(condition, expectedColumnName, expectedTable?)

Optional startup assertion for projects that rely on strict mode or containsColumnFilter.

import { assertDrizzleCompatibility } from "@modemdev/drizzle-scoped-db";
import { eq } from "drizzle-orm";

// Name-only check (backward compatible)
assertDrizzleCompatibility(eq(projects.workspaceId, "compat-check"), "workspace_id");

// Table-aware check (recommended when using scopeByColumn's default detector)
assertDrizzleCompatibility(eq(projects.workspaceId, "compat-check"), "workspace_id", projects);

If a Drizzle upgrade changes the internal SQL chunk shape, this fails fast instead of letting strict validation silently return false. Pass the table to also verify that column chunks expose table identity for alias-safe disambiguation.

Errors

• MissingScopedWhereError
• MissingScopedPredicateError
• InvalidScopedInsertError

You can replace these with custom error factories in createScopedDb({ errors }).

Development

pnpm test
pnpm coverage

Release prep also records a committed performance and heap-growth snapshot:

pnpm bench:release
pnpm bench:release:compare

The package has 100% statement, branch, function, and line coverage.

Sponsor

Sponsored by Modem.

License

MIT.