Skip to main content

SQL bridge + Drizzle adapters

WIP

A bring-your-own-ORM contract. Bundled Drizzle reference implementations for Postgres, MySQL, and SQLite.

The SQL bridge

duck-auth does not ship a Drizzle or Prisma adapter as a hard dependency. Instead, it exposes a thin contract - SqlBridge.IBridge - that any ORM can implement. The bridge is then wrapped into the four stores (identities / credentials / sessions / orgs) by authCreateSqlStores.

import { authCreateSqlStores } from '@gentleduck/auth/adapters/sql'

const stores = authCreateSqlStores({ bridge })

export const auth = new AuthEngine({
  stores: {
    identities: stores.identities,
    credentials: stores.credentials,
    sessions: stores.sessions,
    orgs: stores.orgs,
  },
})

Bundled Drizzle examples

For the common case, duck-auth ships reference Drizzle implementations for Postgres, MySQL, and SQLite. They live under adapters/drizzle/examples/{pg,mysql,sqlite} and are stable enough to use directly:

import { createDrizzlePgBridge } from '@gentleduck/auth/adapters/drizzle/pg'
import { drizzle } from 'drizzle-orm/node-postgres'
import { Pool } from 'pg'

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

const bridge = createDrizzlePgBridge(db, schema)   // schema = your Drizzle tables
const stores = authCreateSqlStores({ bridge })

Equivalent imports for MySQL and SQLite:

import { createDrizzleMysqlBridge } from '@gentleduck/auth/adapters/drizzle/mysql'
import { createDrizzleSqliteBridge } from '@gentleduck/auth/adapters/drizzle/sqlite'

Emit the schema

The CLI emits ready-to-run DDL that matches the bundled bridges:

bunx @gentleduck/auth migrate pg      # -> CREATE TABLE identities, credentials, sessions, ...
bunx @gentleduck/auth migrate mysql
bunx @gentleduck/auth migrate sqlite

The output is a single SQL file; pipe it to psql, mysql, or sqlite3 to install:

bunx @gentleduck/auth migrate pg > migrations/0001_auth.sql
psql $DATABASE_URL < migrations/0001_auth.sql

Or check it into your Drizzle migration folder and let drizzle-kit manage further migrations on top.

The bridge interface

For an ORM that isn't Drizzle, implement SqlBridge.IBridge directly:

interface IBridge {
  identities: IIdentity
  credentials: ICredential
  sessions: ISession
  orgs?: IOrg          // optional
}

interface IIdentity {
  findById(id, opts?: { tenantId? }): Promise<Identity | null>
  findByEmail(email, opts?: { tenantId? }): Promise<Identity | null>
  findByProviderSub(providerId, sub, opts?: { tenantId? }): Promise<Identity | null>
  insert(row: IdentityInsert): Promise<Identity>
  updateConditional(id, expectedVersion, patch): Promise<Identity>  // OCC
  softDelete(id, gracePeriodMs): Promise<void>
  restore(id): Promise<void>
  erase(id): Promise<void>
  insertProviderLink({ identityId, providerId, sub, email? }): Promise<void>
  deleteProviderLink({ identityId, providerId }): Promise<void>
  merge({ primaryId, absorbId, conflictPolicy }): Promise<void>
}

interface ICredential {
  findById(id): Promise<Credential | null>
  listByIdentity(identityId, kind?): Promise<Credential[]>
  findByProviderSub(providerId, sub): Promise<Credential | null>
  findByHashedSecret(hash): Promise<Credential | null>
  insert(row): Promise<Credential>
  updateConditional(id, expectedVersion, patch): Promise<Credential>
  revoke(id): Promise<void>
  delete(id): Promise<void>
  deleteByKind(identityId, kind): Promise<void>
}

interface ISession {
  insert(row): Promise<Session>
  findByHash(hash): Promise<Session | null>
  update(id, patch): Promise<Session>
  delete(id): Promise<void>
  listByIdentity(identityId): Promise<Session[]>
  deleteAllForIdentity(identityId, except?): Promise<void>
  deleteExpired(): Promise<number>
}

Run the compliance suite against your implementation to catch the dozen subtle bugs that bite every adapter ever shipped.

Schema highlights

The bundled schemas use these idioms; if you write a custom bridge, mirror them:

  • Identities: id (text PK), email (citext or lower(email) unique partial index), tenantId, profile (jsonb), version (integer, optimistic concurrency), deletedAt (nullable for soft delete grace).
  • Credentials: id (text PK), identityId (FK), kind (text: password / passkey / apikey / magic-link / recovery / provider-link / totp / backup-code), hashedSecret (text, indexed for fast lookup), metadata (jsonb), expiresAt, revokedAt.
  • Sessions: id (text PK), idHash (text unique - the stored hash of the wire token), identityId (FK), expiresAt, aal (aal1 / aal2), amr (text[]).
  • Orgs: id (text PK), name, tenantId, meta (jsonb). Memberships table: orgId, identityId, role.

Multi-tenant

Every query takes tenantId. The bundled schemas include tenantId columns on identities and orgs and filter every read by it. If you omit tenantId in calls, duck-auth uses null as the implicit single-tenant value - fine for single-tenant apps.

Pre-hashed password import

For migrating from another auth system, the bridge's credentials.insert accepts a pre-hashed secret directly. Combined with passwords.autoRehash, this lets you import bcrypt or PBKDF2 hashes verbatim and silently upgrade them as users sign in:

await stores.credentials.insert({
  identityId,
  kind: 'password',
  hashedSecret: '$2b$12$....',   // pre-hashed bcrypt
  metadata: { algorithm: 'bcrypt' },
})

// Custom hasher detects the bcrypt prefix and verifies natively;
// needsRehash() returns true; autoRehash replaces it with scrypt on
// next successful sign-in. No batch job needed.