# Drizzle

Source: https://permdock.com/docs/adapters/drizzle

permdock/drizzle compiles portable conditions to Drizzle where clauses with toWhere, generates pgPolicy entries for RLS through drizzle-orm/supabase helpers, and reuses drizzle-zod schemas for generated definitions.

`permdock/drizzle` takes the portable condition returned by `permdock.where(permission)` and turns it into a Drizzle SQL expression bound to a table. The same conditions feed `permdock rls generate --target drizzle`, which emits `pgPolicy(...)` entries next to the table definition so drizzle-kit owns the migration.

## Purpose [#purpose]

A condition such as `where: { authorId: principal.id }` is evaluated in memory by `can`, serialised into snapshots for the client, and must also filter a list query in the database, or the UI and the API disagree about which rows exist. CASL proved that one condition AST can drive an in-memory interpreter and a `where` builder, and that priority must be respected when flattening allows and denies ([landscape](/docs/research/landscape)). `permdock/drizzle` is the Drizzle interpreter for PermDock's [conditions](/docs/concepts/conditions), and Drizzle is the primary RLS compile target because `pgPolicy` and `drizzle-orm/supabase` already model policies as code ([Postgres RLS](/docs/standards/postgres-rls)).

## API [#api]

```ts
import { toWhere } from "permdock/drizzle";
import { posts } from "./schema";

const rows = await db
  .select()
  .from(posts)
  .where(toWhere(permdock.where(permissions.post.read), posts));

// combine with app filters: toWhere returns a Drizzle SQL expression
await db
  .select()
  .from(posts)
  .where(
    and(
      toWhere(permdock.where(permissions.post.read), posts),
      eq(posts.published, true),
    ),
  );

// column mapping when schema field names differ from Drizzle column names
toWhere(condition, posts, { columns: { authorId: posts.author_id } });

// graph grants: related nodes compile to a subquery over the relation tables
toWhere(permdock.where(permissions.doc.read), docs, {
  relations: { closure: "permdock.permdock_closure" },
});

// one row: not found, or found with the decision
const check = await checkRow(
  db,
  posts,
  permdock.where(permissions.post.update),
  eq(posts.id, id),
);

// run a transaction as the subject, so RLS policies see it
await withSubject(db, permdock, (tx) => tx.select().from(posts), {
  dialect: "guc",
});
```

* `toWhere(condition, table, options?)` returns a Drizzle `SQL` and maps each condition field to a column of `table` by name (or via `options.columns`), and each operator (`eq`, `ne`, `in`, `notIn`, `gt`, `gte`, `lt`, `lte`, `isNull`, `contains`, `and`, `or`, `not`) to the Drizzle operator of the same meaning. `table` is any Drizzle table object; no cast is needed. `options.columns` accepts only columns of that table or `SQL`, so a column of another table is a type error.
* `options.relations` (`{ tables?, closure? }`) lets a `related` node compile: one subquery over the edge tables, the closure table when named, otherwise a recursive walk of the resource's own table bounded by the grant's depth ([relationships](/docs/concepts/relationships#snapshots-clients-and-where)). Without it a `related` node throws `non-portable-condition`.
* `checkRow(db, table, condition, key, options?)` selects the rows matching `key` with the filter as a boolean column and returns `{ found: false }` or `{ found: true, granted }`, so a handler can answer `404` and `403` apart with one query. More than one row matching `key` throws.
* `withSubject(db, permdock, fn, options?)` opens a transaction, sets the local role and the claim settings for the frozen subject in one `select set_config(…)` statement, then calls `fn(tx)`. `options` are `dialect` (`supabase`, `guc`, `neon`), `role` (default `authenticated` with a principal, `anon` without, `false` to leave the role), `gucPrefix`, `tenantClaim` and extra `claims`. They must match what `permdock rls generate` was configured with; `withSubject` does not read the config. `drizzle-orm` is an optional peer; tests can inject `options.operators`. The call stays two-step (`permdock.where` then `toWhere`) so the adapter never needs the instance.
* `contains` on an array column (`dataType: 'array'`) is `value = any(column)`; on a string it is `like` with `%`, `_` and `\` escaped; any other value is `@>`.
* NULL follows the in-memory evaluator: a comparison with a NULL row value or an unresolved reference never matches, and `not` keeps NULL rows (`not (x = 1)` compiles to `x <> 1 or x is null`).
* `memberOf` compiles from the frozen subject when `options.memberships` is omitted: tenant scope with an active `principal.tenant` is equality on that id, otherwise `inArray` over held tenants; team scope is `inArray` of team ids (dropping memberships whose tenant is not the active one); resource scope is `inArray` on the row field plus `or` over `parents`, a keyed parent (`{ field, resource }`) using only memberships on that resource. With `options.memberships`, it emits `exists (select 1 from <table> m ...)`: one bound parameter per role (the role predicate is skipped when the list is empty), `expires_at > now` with `now` bound in whole Unix seconds (rounded up, so a membership expires up to a second early, never late), the active tenant on tenant and team scopes, and `m.<resource> = '<name>'` when the table has a `resource` column. A keyed parent hop uses `memberships.resource[<parent>]` and fails closed when that resource has no table.
* `principal.<field>` and `context.<key>` references are resolved to bound parameters from the request-scoped `PermDock` before compilation; the result contains no user-controlled SQL.
* When nothing is granted, `toWhere` returns a constant `false` SQL expression so the query yields no rows (fail closed).
* Closure grants cannot compile; `permdock.where` reports them as `{ portable: false }` and `toWhere` throws `PermDockValidationError` with the offending grant.

RLS generation reuses the same compiler:

```ts
// emitted by: permdock rls generate --target drizzle --dialect supabase
import { sql } from "drizzle-orm";
import { pgPolicy } from "drizzle-orm/pg-core";
import { authenticatedRole, authUid } from "drizzle-orm/supabase";
import * as schema from "./schema";

export const postReadMember = pgPolicy("post_read_member", {
  as: "permissive",
  for: "select",
  to: authenticatedRole,
  using: sql`("author_id" = ${authUid})`,
}).link(schema.posts);
```

Each policy is linked to its table with `.link`, so the table definition stays untouched and drizzle-kit picks the policies up from the schema. The `guc` and `neon` dialects declare `pgRole('authenticated').existing()` in the file instead of importing from `drizzle-orm/supabase`. Grants, column privileges, `enable row level security` and the helper functions have no Drizzle form, so they go to `<out>.migration.sql` next to the policies file; apply it with your migrations. `rls.drizzle.schema` sets the import path and `rls.drizzle.exports` maps a table to its export name when it is not the table name in camelCase ([RLS](/docs/adapters/rls)).

## Request lifecycle [#request-lifecycle]

1. The request-scoped `PermDock` is created from the policy and subject.
2. `permdock.where(permission)` flattens the grants for that permission: each allow condition is ANDed with the negation of all higher-priority denies, and the results are ORed. Unconditional allow yields `true`; no grant yields `false`.
3. `toWhere` walks the resulting tree and produces a Drizzle SQL expression bound to the table's columns, with subject values as parameters.
4. The app composes the expression with its own filters and runs the query.
5. `on('decision')` fires once with `outcome` derived from whether anything was granted, plus `permdock.filter`-style metadata for observability.

For RLS, the same tree is compiled by the CLI at build time instead of per request; `principal.id` becomes `(select auth.uid())` under the `supabase` dialect, a GUC read under `guc`, and `auth.user_id()` under `neon` (see [RLS](/docs/adapters/rls)).

## What it validates [#what-it-validates]

* Every field in the condition exists on the table (or in `options.columns`); a missing column throws at compile time in tests and at first use in production with the field name.
* Operator and column type compatibility is left to Drizzle's own typing; `toWhere` is typed so `condition` must come from the resource whose schema the table was declared for when `drizzle-zod` schemas are used in the definition.
* Subject values are always parameters, never interpolated.
* Nothing about the rows themselves: `where` runs before rows exist, and rows coming back from the database are trusted (`validate: 'boundary'` does not run on them).

## How denials surface [#how-denials-surface]

* No grant: a constant `false` expression and an empty result; the caller sees zero rows, not an error. Combine with `assert(permissions.post.list)` beforehand to distinguish "forbidden" from "not found" ([CASL issue #794](https://github.com/stalniy/casl/issues/794) describes the ambiguity).
* Non-portable grant: `PermDockValidationError` with `reason: 'non-portable-condition'` and the grant's role, so it is caught in tests rather than at runtime.
* In RLS mode: a `USING` policy filters silently; a `WITH CHECK` policy raises `42501`; `permdock rls verify` classifies both.

## Example app [#example-app]

`apps/examples/drizzle`: a Hono API over an in-process PGlite database. `GET /posts` selects through the list filter, behind the Hono `permdock()` middleware. `PATCH /posts/:id` runs `protect` with a loader, then updates through the update filter; a denial is a `403` and a missing row a `404`, both Problem Details. `tests/integration/src/orm-parity.test.ts` compares `filter` in memory with `toWhere` results on node-postgres and PGlite for every operator, NULL and negation case, reference, `memberOf` scope, parent hop and expired membership (`ormParity` in `permdock/testing`), and `checkRow` with `can()` for each row. `orm-graph.test.ts` does the same for graph grants, `with-subject.test.ts` runs `withSubject` against generated RLS as a non-owner role, and `rls-orm-targets.test.ts` applies the generated `pgPolicy` file to Postgres.

## Why [#why]

* **A hand-written Standard Schema wins over a `drizzle-zod` schema.** `rls import --from drizzle` writes a separate `// @generated` module whose resources reference the tables' `drizzle-zod` schemas. A resource the application defines by hand keeps its own schema, and its entry in the generated module is left out of the merge: the hand-written schema is the boundary contract (invariant 9) and usually narrower than the table, for example without server-set columns. One resource is never defined with two schemas. Both are Standard Schemas, so `toWhere` and validation treat them the same, and `permdock usage` reports a condition on a field neither declares ([usage](/docs/cli/usage)).

## Related standards [#related-standards]

* [Postgres RLS](/docs/standards/postgres-rls): policy semantics that the generated `pgPolicy` entries follow, Drizzle `pgPolicy`, `drizzle-orm/supabase` helpers and `crudPolicy` for Neon.
* [Conditions](/docs/concepts/conditions): the portable operator set.
* [RLS adapter](/docs/adapters/rls): `generate`, `import`, `verify`.