## Install

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

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

```bash
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

```typescript
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

```typescript
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

```typescript
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:

```typescript
// 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:

```bash
bunx drizzle-kit generate
bunx drizzle-kit migrate
```

***

## Usage

```typescript
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

| Option | Type | Description |
| --- | --- | --- |
| `db` | Drizzle database instance | Your Drizzle `db` object with `select`, `insert`, `delete` |
| `tables.policies` | Drizzle table | Policy table reference |
| `tables.roles` | Drizzle table | Role table reference |
| `tables.assignments` | Drizzle table | Assignment table reference |
| `tables.attrs` | Drizzle table | Subject attributes table reference |
| `ops.eq` | `(col, val) -> condition` | Drizzle `eq` operator |
| `ops.and` | `(...conditions) -> condition` | Drizzle `and` operator |
| `json` | `'native' \| 'string'` | JSON encoding. `'native'` (default) writes objects to `jsonb`/`json`; `'string'` JSON-stringifies for SQLite/text columns. Optional. |
| `onPolicyError` | `(err, ctx) -> void` | Called when a stored row fails JSON parse or shape validation; the row is dropped. Optional. |

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.stringify`s 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:

```typescript
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](/duck-iam/integrations/adapters/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`).

```typescript
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.