Drizzle adapter — @ascendance-hub/sapphire-drizzle

The Drizzle adapter converts a Sapphire IR (SapphireSchemaNode) into a Drizzle table definition — pgTable, mysqlTable, or sqliteTable, depending on the chosen dialect — ready to pass into drizzle(connection, { schema: { ... } }). Three dialects are first-class; everything else (D1, Neon HTTP, Bun’s bun:sqlite) layers on top because Drizzle’s own driver split is orthogonal to the column-builder shape.

Unofficial. A community adapter — not affiliated with, sponsored, or endorsed by the Drizzle Team.

Install

npm install @ascendance-hub/sapphire-core @ascendance-hub/sapphire-drizzle drizzle-orm

Both @ascendance-hub/sapphire-core and drizzle-orm are peer dependencies.

Supported drizzle-orm range

^0.44 || ^0.45

Pinned conservatively because Drizzle ships frequent column-builder breaking changes. The adapter is verified against 0.44.x and 0.45.x; later majors are not guaranteed until we rev the peer range.

Register the adapter

import { Sapphire, registerAdapter } from '@ascendance-hub/sapphire-core'
import { toDrizzleSchema } from '@ascendance-hub/sapphire-drizzle'

registerAdapter('drizzle', toDrizzleSchema)

export const a = new Sapphire({ defaultAdapter: 'drizzle' })

The adapter is not auto-registered — call this once in your entry point.

Quickstart (Postgres)

import { drizzle } from 'drizzle-orm/node-postgres'
import { toDrizzleSchema } from '@ascendance-hub/sapphire-drizzle'
import { a } from './sapphire'

const User = a
  .object({
    name: a.string().min(1),
    email: a.string().email().unique(),
    age: a.number().int().min(0).optional(),
  })
  .name('User')

const users = toDrizzleSchema(User.toSchema(), { dialect: 'pg' })

const db = drizzle(connectionString, { schema: { users } })
// `db.select().from(users)` is fully typed by Drizzle.

For multi-table emission with cross-table refs, share a single DrizzleTableRegistry instance across calls (see Refs below).

Adapter signature

toDrizzleSchema(node, { dialect: 'pg',     ... }) → PgTableWithColumns<...>      (declared as any)
toDrizzleSchema(node, { dialect: 'mysql',  ... }) → MySqlTableWithColumns<...>   (declared as any)
toDrizzleSchema(node, { dialect: 'sqlite', ... }) → SQLiteTableWithColumns<...>  (declared as any)

Three overloads, each returning any. The actual runtime table is genuine — only the declared TS type is widened. The reason: Drizzle’s column-builder generics aren’t reliably exported across the supported peer range, and pinning a single signature would silently break for users on the other minor. Pass the returned table into drizzle(conn, { schema: { users } }) and use the connection-level types for queries.

IR → Drizzle mapping (per dialect)

Universal modifiers (every column)

applyCommon walks each node and chains modifiers onto the built column in order:

1. default(v) → col.default(v).
2. required && !nullable → col.notNull().
3. unique → col.unique().
4. Then escape-hatch meta.drizzle entries (see below).

All other Sapphire modifiers (min/max/regex/format/startsWith/endsWith/multipleOf/finite/safe/coerce/transforms) stay in safeParse — they are not enforced at the DB level. The single exception is MySQL varchar, which uses maxLength to size the column (default 255).

Implicit primary key

Every emitted table auto-prepends an id column unless you opt out:

MySQL uses int — not serial, which is bigint unsigned. int keeps the implicit PK type-compatible with the int columns emitted for ref foreign keys; a serial PK would reject those FKs (MySQL errno 150).

Controlled by options.primaryKey:

toDrizzleSchema(node, { dialect: 'pg' }) // → id (default)
toDrizzleSchema(node, { dialect: 'pg', primaryKey: 'pk' }) // → pk
toDrizzleSchema(node, { dialect: 'pg', primaryKey: false }) // → no implicit PK; you declare your own via meta
toDrizzleSchema(node, { dialect: 'pg', primaryKey: ['orgId', 'slug'] }) // → composite PK

Composite primary key

Pass an array of field names as primaryKey to emit a table-level composite primary key — PRIMARY KEY(col_a, col_b) — with no implicit id:

const ArticleRevision = a.object({
  articleId: a.ref('Article'),
  version: a.number().int(),
  body: a.string(),
})

const revisions = toDrizzleSchema(ArticleRevision.toSchema(), {
  dialect: 'pg',
  tableName: 'article_revisions',
  primaryKey: ['articleId', 'version'],
})

Every name must be a declared field, and at least two are required — use the plain string form for a single-column PK. The composite PK is emitted through the same third-argument callback of pgTable / mysqlTable / sqliteTable that carries composite indexes.

A table with a composite PK cannot be the target of a ref: a single-column foreign key has nothing to point at. The references(...) callback throws a clear error if a ref targets such a table.

[!NOTE] Extended (identifying) primary key. To make a child table’s primary key be a foreign key into its parent — a 1:1 identifying relationship — declare the ref field and name it as the PK: primaryKey: 'parentId' with a parentId: a.ref('Parent') field emits integer('parentId').references(...).primaryKey().

Refs + DrizzleTableRegistry

Drizzle requires references(() => targetTable.column) to be lazy, because the target table may not exist when the column is declared (forward refs, cycles). Sapphire’s ref('User') is wired into a registry that the lazy callback queries at query time:

import { toDrizzleSchema, DrizzleTableRegistry } from '@ascendance-hub/sapphire-drizzle'
import { a } from './sapphire'

const tables = new DrizzleTableRegistry()

const User = a.object({ name: a.string() }).name('User')
const Post = a
  .object({
    title: a.string(),
    author: a.ref('User'),
  })
  .name('Post')

const users = toDrizzleSchema(User.toSchema(), { dialect: 'pg', tables })
const posts = toDrizzleSchema(Post.toSchema(), { dialect: 'pg', tables })

// `posts.author.references(() => users.id)` resolves at query time.

Cycles (User ↔ Post)

Emit both with the same registry. The lazy callback resolves once the cycle is closed. If the callback fires before the target table is registered, the adapter throws:

drizzle adapter: ref target table "User" not registered.
Emit it before invoking queries that traverse this reference.

Order of toDrizzleSchema calls doesn’t matter — what matters is that all related tables are emitted before you run a query that traverses the ref.

Composite indexes

ObjectField.index(keys, opts?) accumulates into node.indexes. The adapter emits each via the third-arg callback of pgTable / mysqlTable / sqliteTable, in the array form (the object form is deprecated in drizzle-orm ≥ 0.44):

const Post = a
  .object({
    authorId: a.number().int(),
    createdAt: a.date(),
  })
  .name('Post')
  .index(['authorId', 'createdAt'])
  .index(['authorId'], { unique: true })

const posts = toDrizzleSchema(Post.toSchema(), { dialect: 'pg' })
// Emits two indexes named `Post_idx_0`, `Post_idx_1` (unique).

Names follow <tableName>_idx_<i>, stable and unique within the table.

Schema-level options

The second argument to toDrizzleSchema(node, options):

.adapter('drizzle', opts) escape hatch

Values from .adapter('drizzle', { ... }) are read from node.meta.drizzle and applied as method calls on the column builder. Two scopes are supported:

• Top-level keys — applied for every dialect.
• Dialect sub-keys (pg, mysql, sqlite) — applied only when options.dialect matches.

a.string().adapter('drizzle', {
  // Top-level: applied for any dialect that has the method.
  primaryKey: true,
  // Dialect-specific: only applied when emitting for pg.
  pg: { array: true },
  // Same key under a different dialect bucket — ignored when emitting for pg.
  mysql: { fixed: 12 },
})

Argument handling

Silent skip on missing methods

function callChain(col: any, method: string, args: unknown): any {
  if (typeof col[method] !== 'function') return col
  // ...
}

Methods missing on the column builder (e.g. .array() on a text column under SQLite) are silently skipped — meta is best-effort. The flip side: typos go undetected at runtime. Pair the escape hatch with tests that assert the runtime column properties (isUnique, notNull, etc.).

To swap the column constructor itself (e.g. force text instead of varchar in MySQL), declare the table by hand — the escape hatch only chains methods on an already-built column.

Limitations

[!WARNING] No runtime validation in Drizzle. Drizzle is a query builder, not a validator. Sapphire keeps validation in safeParse; the Drizzle adapter only shapes the table.

[!WARNING] Composite kinds collapse to JSON columns. array, tuple, union, record, and nested object all map to jsonb (pg) / json (mysql) / text({ mode: 'json' }) (sqlite). DB-level validation of the composite shape is lost — keep it in safeParse. Specific consequences:

• union — branches are opaque to the database; you cannot filter by which branch a row matched, nor enforce mutual exclusion at the DB level.
• record — keyField constraints (.uuid(), .regex(...)) run only in safeParse; the DB accepts any string key.
• tuple — fixed length is not DB-enforced; the column accepts any JSON array shape.
• In all cases: call safeParse before insert / update to keep DB rows consistent with the schema’s intent.

[!WARNING] enum is text/varchar by default. pgEnum / mysqlEnum require a separately-declared type with a unique name and are opt-in via .adapter('drizzle', { pg: { ... } }).

[!WARNING] String validators are runtime-only. minLength, regex, format (email/url/uuid), startsWith, endsWith are not enforced at the DB level. Exception: MySQL — node.maxLength sizes varchar(N).

[!WARNING] SQLite uuid is text. SQLite has no native UUID type. The adapter emits text(name) regardless of format; rely on safeParse for format checking.

[!WARNING] Boolean and date in SQLite are emulated. Stored as integer({ mode: 'boolean' }) and integer({ mode: 'timestamp' }) (unix seconds). Drizzle handles serialisation transparently — but date precision is second-level by default.

[!WARNING] Return types of toDrizzleSchema are any per overload. The runtime table is real; the TS type is widened because Drizzle’s column-builder generics aren’t reliably exported across the supported peer range.

[!WARNING] No relations() API. Sapphire refs become Drizzle references(...) only. The higher-level relations(users, ({ many }) => ({ ... })) API is deferred to V1_FUTURE.

[!WARNING] Migrations are out of scope. Pair the generated tables with drizzle-kit in your own pipeline.

[!WARNING] ObjectField.timestamps() is a no-op in this adapter. The Mongoose-flavoured concept doesn’t translate to Drizzle directly — declare createdAt/updatedAt explicitly with a.date().default(() => new Date()) if you want them.

[!WARNING] primaryKey: false disables refs into that table. The adapter resolves refs by reading the target’s actual PK from the DrizzleTableRegistry (column name registered at emit time). If a target was emitted with primaryKey: false, the registry holds pkName: null and the references(...) callback throws at first query traversal with a clear message. To use refs into a custom-PK table: emit the target with primaryKey: 'yourColumn' (named PK) so the registry records the right column. A per-ref column override (a.ref('User', { column: 'uuid' })) is on the V1_FUTURE list.

Related

• Fields and modifiers — what each IR kind models.
• Refs and relations — ref lifecycle across adapters.
• Escape hatch — universal .adapter(name, opts) contract.
• Recipes → One schema, many adapters.
• Recipes → Writing a custom adapter.