drizzle adapter

Install

import { IamDrizzleAdapter } from '@gentleduck/iam/adapters/drizzle'

Works with any Drizzle ORM driver. Pre-built schema modules are shipped for all three SQL dialects.

bun add drizzle-orm
bun add -D drizzle-kit

Pre-built schemas

Pick the entry that matches your database. All three define the same four tables (access_policies, access_roles, access_assignments, access_subject_attrs) with dialect-appropriate column types.

PostgreSQL

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

• jsonb for rules, permissions, targets, metadata, attributes, and inherits
• GIN indexes on rules and permissions for containment search (@>)
• Partial indexes for scoped roles and assignments (WHERE scope IS NOT NULL)
• Unique constraints use NULLS NOT DISTINCT so global rows (NULL scope) stay unique
• timestamptz with defaultNow() and auto-updated updated_at

MySQL

import {
  iamPolicies,
  iamRoles,
  iamAssignments,
  iamSubjectAttrs,
} from '@gentleduck/iam/adapters/drizzle/schema/mysql'

• json columns (MySQL native)
• varchar(191) for IDs (utf8mb4 + index-friendly)
• datetime(3) with per-row CURRENT_TIMESTAMP(3) precision
• COALESCE(scope, '') unique indexes so global rows stay unique (MySQL has no NULLS NOT DISTINCT)

SQLite

import {
  iamPolicies,
  iamRoles,
  iamAssignments,
  iamSubjectAttrs,
} from '@gentleduck/iam/adapters/drizzle/schema/sqlite'

• text for JSON columns - requires the adapter in json: 'string' mode
• integer ms epoch for timestamps
• COALESCE(scope, '') unique indexes + partial indexes for scoped rows
• Default [] for inherits text column

What each schema includes

• Named constraints throughout: pk_ primary key, fk_ foreign key, uq_ unique, idx_ index, ch_ check
• FK cascade on roleId -> iamRoles.id
• Unique on (subjectId, roleId, scope) for idempotent assignments, with NULL scopes collapsed (NULLS NOT DISTINCT on PG, COALESCE on MySQL/SQLite)
• Lookup index on subjectId plus a roleId index for FK deletes and reverse lookups
• CHECK constraints: non-blank name/subject, version >= 1, valid algorithm
• created_by / updated_by audit columns (set via triggers or admin writes; the adapter leaves them NULL)
• Auto-managed created_at / updated_at

Generating migrations

Re-export the schema from your own schema barrel so drizzle-kit picks it up:

// db/schema.ts
export * from '@gentleduck/iam/adapters/drizzle/schema/pg'

// ...your own tables alongside
import { pgTable, text } from 'drizzle-orm/pg-core'
export const users = pgTable('users', {
  id: text('id').primaryKey(),
  email: text('email').notNull().unique(),
})

Configure drizzle.config.ts to point at this barrel, then:

bunx drizzle-kit generate
bunx drizzle-kit migrate

Usage

import { drizzle } from 'drizzle-orm/node-postgres'
import { eq, and } from 'drizzle-orm'
import { Pool } from 'pg'
import { IamDrizzleAdapter } from '@gentleduck/iam/adapters/drizzle'
import { IamEngine } from '@gentleduck/iam'
import {
  iamPolicies,
  iamRoles,
  iamAssignments,
  iamSubjectAttrs,
} from '@gentleduck/iam/adapters/drizzle/schema/pg'

const pool = new Pool({ connectionString: process.env.DATABASE_URL })
const db = drizzle(pool)

const adapter = new IamDrizzleAdapter({
  db,
  tables: {
    policies: iamPolicies,
    roles: iamRoles,
    assignments: iamAssignments,
    attrs: iamSubjectAttrs,
  },
  ops: { eq, and },
})

const engine = new IamEngine({ adapter, cacheTTL: 60 })

Constructor config

You can swap in your own table definitions if you need extra columns or a different naming scheme - only the column names listed in the schema files are required.

JSON handling

The json config option controls how payload columns (rules, targets, permissions, inherits, metadata, data) are written:

• json: 'native' (default) - writes plain objects/arrays. Use with PostgreSQL jsonb and MySQL json so the data stays queryable (GIN indexes, jsonb_typeof, no double-encoding).
• json: 'string' - JSON.stringifys every payload. Required for SQLite (TEXT columns) or any text-backed column.

The read path accepts both shapes, so switching modes is migration-safe. For SQLite:

const adapter = new IamDrizzleAdapter({
  db,
  tables: { policies: iamPolicies, roles: iamRoles, assignments: iamAssignments, attrs: iamSubjectAttrs },
  ops: { eq, and },
  json: 'string', // SQLite stores JSON as TEXT
})

On PostgreSQL inherits is now jsonb (an array of parent role IDs), aligning with the shared adapter's serialization across all three dialects.

Notes & caveats

• assignRole uses onConflictDoNothing - already idempotent, calling twice is safe.
• setSubjectAttributes is read-merge-write - same race risk as Prisma. Wrap in a transaction for high-contention writes.
• Custom column names - if you rename columns in the table definition, the adapter still works as long as the keys passed in tables.* match. Drizzle's table inference handles the rest.

When to use

• Production apps already on Drizzle
• SQL-first teams that want zero runtime overhead
• Edge runtimes (Drizzle works on Cloudflare D1, Neon serverless, etc.)

For ORM-style queries with relation modeling, see Prisma.

Types

All types live under the Drizzle namespace at @gentleduck/iam/adapters/drizzle. Type-only - zero bundle cost.

• Drizzle.IConfig - constructor config for IamDrizzleAdapter (db, tables, ops).

import type { Drizzle } from '@gentleduck/iam/adapters/drizzle'

const config: Drizzle.IConfig = {
  db,
  tables: { policies, roles, assignments, attrs },
  ops: { eq, and },
}

The deprecated bare alias IDrizzleConfig remains for back-compat and will be removed in 3.0.