Drizzle adapter

IamDrizzleAdapter maps the adapter interface onto four SQL tables through a Drizzle db handle. It is the only one of the six that implements all six optional methods. The package ships a ready schema for each of the three dialects Drizzle supports, so the usual setup is: re-export one schema file, run drizzle-kit generate, hand the adapter your db and the eq/and operators.

Install

npm i @gentleduck/iam drizzle-orm

drizzle-kit is a dev dependency (bun add -D drizzle-kit). drizzle-orm is an optional peer dependency of @gentleduck/iam; nothing else is required.

The adapter lives at @gentleduck/iam/adapters/drizzle, and each dialect's schema at its own subpath:

import { IamDrizzleAdapter, createIamDrizzleAdapter, iamDrizzleAdapter } from '@gentleduck/iam/adapters/drizzle'
import type { IamDrizzle } from '@gentleduck/iam/adapters/drizzle'

import { iamPolicies, iamRoles, iamAssignments, iamSubjectAttrs, combineAlgorithm } from '@gentleduck/iam/adapters/drizzle/pg'
import type { Pg } from '@gentleduck/iam/adapters/drizzle/pg'

Setup

createIamDrizzleAdapter<TConfig>(config) is the typed factory: it infers TAction, TResource, TRole, and TScope from your IamConfig.IAccessConfig instead of making you restate them. iamDrizzleAdapter(config) is a plain factory around new with no inference.

Options

IamDrizzle.IConfig<TDb, TType> is the single constructor argument.

Row-shape types are exported too: IamDrizzle.PolicyRow, RoleRow, AssignmentRow, AttrRow are the cross-dialect unions, and Pg, Mysql, Sqlite (from the dialect subpaths) are the concrete $inferSelect shapes if you are pinned to one database.

The two optional operators are gates, not decorations

Both omissions are correct but degraded, and silently so, which is why the adapter warns once per process at construction:

[@gentleduck/iam:drizzle] running on a degraded path; ops is missing
`isNull` - updateAssignmentScope() cannot match an unscoped (NULL) assignment in place and falls back to revoke + assign;
`or` - revokeRoleMany() issues one DELETE per row instead of one statement.
Pass them through from drizzle-orm: `ops: { eq, and, isNull, or }`.

The warning set is module-level rather than per-instance, because withClient re-runs the constructor for every transaction and a per-instance flag would turn one misconfiguration into one warning per transaction — which is how a startup diagnostic becomes log noise operators filter out.

ops takes drizzle's own operator types. That has been true only since ops.eq stopped being declared (col: unknown, val: unknown) => unknown: under strictFunctionTypes an unknown parameter is the narrowest thing a caller can satisfy, not the widest, so drizzle's real eq was not assignable to it and every caller wrote a re-widening wrapper with two casts in it. A tsc-checked type test pins the signatures now.

Storage layout

Four tables. iam_assignments.role_id is the only foreign key, and it cascades. Nothing else references anything.

Column types in the diagram are the Postgres ones; the dialect differences table lists what MySQL and SQLite use instead. Every delete against these tables is a hard delete — deletePolicy and deleteRole so a name can be reused, revokeRole because a revoked grant has no reason to be retained. There is no deleted_at column on any table; one was added in 5.5.0 and removed again in 5.5.1 as unreachable.

created_by and updated_by are written from the actor a caller supplies, and stay absent otherwise. They are two disjoint records: created_by answers "who first put this here" and is spread into the insert half of an upsert only; updated_by answers "who touched it last" and is spread into the update half. Getting that backwards silently rewrites who authored a policy every time somebody edits it. Both are spread rather than written as null, so a table predating the columns is untouched unless the caller actually names an actor.

Constraints and indexes

Named throughout with the prefixes pk_, fk_, uq_, idx_, ch_.

Postgres adds GIN indexes on rules and permissions for containment search (rules @> '[{"actions":["read"]}]'), a partial index on iam_roles.scope, and partial indexes on (subject_id, scope) and expires_at restricted to non-NULL rows. Those are for your own queries — the adapter never uses them directly, because it filters scope and the temporal window in JavaScript after selecting a subject's rows.

The three dialects

All three define the same four tables with the same names and the same column names. What differs is types, index expressions, and how much of a CHECK constraint the database actually enforces.

const adapter = new IamDrizzleAdapter({
  dialect: 'sqlite',
  db,
  tables: { policies: iamPolicies, roles: iamRoles, assignments: iamAssignments, attrs: iamSubjectAttrs },
  ops: { eq, and, isNull, or },
  json: 'string',
})

dialect: 'sqlite' and dialect: 'pg' take identical code paths today — every runtime branch in the adapter is a === 'mysql' test. Pass 'sqlite' anyway; it is what makes the config type match the tables, and it is the field a future divergence would branch on.

infinity is the divergence that matters

Postgres spells "never expires" as expires_at = 'infinity', and node-postgres parses that to a JS number rather than a Date. Drizzle's own timestamp() maps every driver value through new Date(...), and new Date('infinity') is an Invalid Date — indistinguishable from a genuinely corrupt column, which the adapter reads as "bound unreadable, row inactive". A grant written to mean always would read back as expired: a wrong DENY on the exact value an operator writes to prevent one.

So the pg schema declares a custom column type that keeps ±Infinity as numbers. The SQL type is unchanged, so this is not a migration, and the comparisons then give all four spellings the right answer with no special case: now < -Infinity is false, so a -infinity start has always begun; now >= Infinity is false, so an infinity expiry never lapses. MySQL and SQLite have no infinity value — there, "never expires" is NULL, which the same code already handles.

dialect and json are independent knobs. dialect picks the write API (conflict handling); json picks the encoding. MySQL uses dialect: 'mysql' with json: 'native'; SQLite uses dialect: 'sqlite' with json: 'string'.

Transactions and consistency

The adapter opens no transactions of its own. Every method is one or two statements on the db handle you passed. To join a transaction you own, use withClient through the engine:

let pending
await db.transaction(async (tx) => {
  const perms = engine.withTransaction(tx)
  await perms.admin.assignRole(userId, 'admin', orgId)
  await tx.insert(members).values({ userId, orgId })
  pending = perms.pending
})
await pending.flush()   // invalidate and broadcast only after the commit

withClient re-makes the adapter against the opaque handle, keeping every other config field — json: 'string' in particular, or a rebound adapter would write a different encoding into the same columns mid-transaction. It returns a new adapter; the original keeps writing to its own client. Reads on the bound view go to the rebound client too, and cache invalidations buffer in pending rather than firing inside the transaction, so a rolled-back grant never evicts another node's cache for a write that did not happen.

Within a single statement, what is and is not atomic:

Upserts and the MySQL branch

MySQL has no ON CONFLICT clause. Its onDuplicateKeyUpdate() fires on any unique-index violation, not just the one you named, so a blanket onDuplicateKeyUpdate would let a row with a fresh id that collides on some other unique index silently overwrite that unrelated row — id included. Postgres and SQLite reject that, because their onConflictDoUpdate is scoped to the target column and throws when the conflict is elsewhere.

On the shipped schemas that collision cannot arise: iam_policies and iam_roles carry a primary key and no other unique index. What the branch protects today is a consumer table that declares a secondary unique index of its own, which config.tables allows.

So on MySQL the adapter checks for the row by id first and issues a plain UPDATE or INSERT. A genuine secondary-unique collision then surfaces as a thrown duplicate-entry error from the INSERT — matching the fail-closed behaviour of the other two dialects instead of corrupting a neighbouring row. The cost is the extra SELECT and the loss of atomicity noted in the table above: two concurrent saveRole calls for the same new id can both see nothing and both insert. The primary key rejects the loser, loudly, which is the right failure — but it is a failure a pg or sqlite deployment does not see.

assignRole takes the same shape at a smaller scale: insert().values().onConflictDoNothing() on pg and sqlite, insert().ignore().values() on MySQL, because MySQL's insert-ignore is a builder-order modifier rather than a trailing call. Either way a repeated (subject, role, scope) is a no-op, which is the idempotency the compliance suite requires.

Scope

scope is a nullable column on both iam_roles and iam_assignments. NULL means global; any non-NULL, non-blank string is a scoped grant.

Reads do not filter scope in SQL. getSubjectRoles and getSubjectScopedRoles both run the same query — SELECT * FROM iam_assignments WHERE subject_id = ? — and split the result in JavaScript:

// getSubjectRoles: unscoped only, deduplicated, active only
[...new Set(rows.filter((r) => r.scope == null && isActive(r, now)).map((r) => r.roleId))]

// getSubjectScopedRoles: scoped only, one entry per (role, scope)
rows.filter((r) => r.scope != null && isActive(r, now)).map((r) => ({ role: r.roleId, scope: r.scope, ...attributes }))

revokeRole is the mirror image and does filter in SQL: with a scope it deletes only (subject, role, scope); without one it deletes every row for (subject, role), scoped and unscoped alike.

Moving an assignment between scopes

updateAssignmentScope(subjectId, roleId, fromScope, toScope, actor?) (since 5.5.0) moves a grant in place, preserving the row's id and created_at instead of destroying and recreating it. It returns false — and the engine transparently falls back to revokeRole + assignRole — in two cases: ops.isNull was not configured, or nothing matched fromScope.

When the target scope already has its own row, the source row is deleted rather than updated, because an UPDATE would violate uq_iam_assignments_subject_role_scope. The method still returns true: the caller asked for "this subject holds this role at toScope", and afterwards they do. actor, when given, lands in updated_by.

Temporal grants and per-grant attributes

Since 5.6.0, assignRole takes an optional fourth argument, IamAdapter.IAssignOptions:

await adapter.assignRole('alice', 'editor', 'org-1', {
  startsAt: new Date('2026-01-01T00:00:00Z'),
  expiresAt: new Date('2026-04-01T00:00:00Z'),
  attributes: { department: 'sales' },
})

The three values go to starts_at, expires_at, and attributes. Both reads then filter on the half-open window [startsAt, expiresAt): the tests pin that a grant is active at the exact instant startsAt is reached, active one millisecond before expiresAt, and inactive at expiresAt itself. A row with both bounds NULL is always active, which is exactly how every assignment written before these columns existed behaves.

attributes surfaces on the scoped role as IamRequest.IScopedRole.attributes, readable from a policy condition as subject.scopedRoles[].attributes and distinct from the subject's own global attribute bag. The key is omitted entirely, rather than set to null, when the column is empty; an empty object is preserved as {} rather than treated as absent. A corrupt value — invalid JSON, a JSON null, a number, a boolean, an array — drops just that field, reports through onPolicyError, and leaves the assignment itself intact, because a bad per-grant blob should not remove a role. Drizzle is the only adapter that implements IAssignOptions; the other five throw and name the option rather than dropping it.

getSubjectGrantBoundary exists here for the same reason: it answers the earliest future starts_at or expires_at across a subject's grants so the engine can cap its cache entry there, instead of serving a grant that lapsed thirty seconds into a sixty-second TTL.

Row validation

Every read runs each row through parsePolicyRow / parseRoleRow from @gentleduck/iam/core/validate, and every failure is reported through onPolicyError with { adapter: 'drizzle', rowId } and the joined validator issues. What happens next is not symmetric.

A bad role row is dropped and the rest of the catalog is returned, because role permissions are allow-only: losing one can only cost a subject a grant. A bad policy row makes the read throw — policy "p7" cannot be read and will not be skipped - a dropped policy may be the one that denies — so one unreadable row fails every read of the policy table and the engine denies everything until it is repaired. That cost is the point: under policyCombine: 'and' even an allow-only policy votes deny when none of its rules match, so there is no subset of policies an adapter can safely drop without knowing a combine mode it cannot see. getPolicy on a corrupt row throws rather than returning null, because a corrupt row must not be able to impersonate a deleted one.

Both halves of the parse are covered: a JSON column that will not parse, and one that parses into the wrong shape. The second is the nastier — rules: '[]' stored as a raw string reads loosely like a policy with no rules, which under deny-overrides is a policy that denies nothing.

Subject attributes are the deliberate exception.

setSubjectAttributes is the one path that recovers instead: it catches a corrupt read, reports it, and merges onto {} so an operator can always overwrite a broken row rather than being locked out of fixing it.

Migrations

The tables are regular Drizzle tables, so drizzle-kit generate against your barrel produces the migration and drizzle-kit migrate applies it. Schema changes that have shipped, in case you are upgrading across them:

Because json: 'native' and json: 'string' are both accepted on read, flipping the mode does not need a data migration: old rows keep their encoding, new ones take the new one, and both parse.

Swapping in your own table definitions is supported — extra columns, a different table name, a different physical column name — as long as the keys you pass in tables.* expose the property names the adapter reads. Those property names, not the table names, are the real contract:

policies:    id name description version algorithm rules targets createdBy updatedBy createdAt updatedAt
roles:       id name description permissions inherits scope metadata createdBy updatedBy createdAt updatedAt
assignments: id subjectId roleId scope startsAt expiresAt attributes createdBy updatedBy createdAt updatedAt
attrs:       subjectId data createdBy updatedBy createdAt updatedAt

A consumer table that declares a secondary unique index of its own is the case the MySQL upsert branch protects; the shipped schemas have none.

Gotchas

• assignRole is idempotent, savePolicy is an upsert, revokeRole is a no-op on an unknown grant. All three tolerate repetition, which is what admin.import({ mode: 'merge' }) relies on.
• The engine, not the adapter, applies adapterTimeoutMs. IReadOptions.signal is accepted for interface compatibility and ignored — Drizzle's builders take no signal — so a slow query keeps running in the background after the engine has already timed out and failed closed. Keep these queries fast, and set statement timeouts at the database.
• listPolicies and listRoles select the whole table. The engine refuses result sets over maxPolicies / maxRoles (both default 10_000). Raise the caps rather than paginating inside the adapter.
• MySQL leaves AssignmentRow.id nullable (no adapter-side default in the inferred type), so an error report for a bad assignment row falls back to the subject_id as its rowId.
• A deleted role takes its assignments with it through the FK cascade. Deleting a policy affects nothing else.
• assignRoleMany and revokeRoleMany return null on MySQL, which has no RETURNING. That is not "nothing was written" — the grants land, and null says the driver cannot name which of them were new, so the engine leaves changed off the outcome rather than guessing.
• Batch guards run over the whole batch before the first write. One bad row refuses the batch entirely; a partially-applied revocation is the worse of the two outcomes.
• A MySQL identifier over 191 characters is a write error and fine on the other two. On MySQL below 8.0.16 every ch_* CHECK is parsed and ignored, so a blank subject_id — the one of the three with no adapter-side guard — is stored.

See also

• Adapters overview for how the four stores fit together
• Prisma adapter for the same tables behind Prisma Client
• Custom adapter for the full interface contract and the compliance suite
• Scoped roles for what a scope means at evaluation time
• Validation for the parsePolicyRow / parseRoleRow result shape