Drizzle ORM

Drizzle ORM conventions for all my TypeScript projects.

SKILL.md is the entrypoint. Read this first.

If the project is deployed on Cloudflare or uses D1, Hyperdrive, Durable Objects, wrangler, or cloudflare:workers, you MUST also read the companion doc ./cloudflare.md before writing code. That file contains the Cloudflare-only runtime, driver, and migration rules.

CRITICAL: Always use drizzle beta, NEVER v0

Always install drizzle-orm@beta and drizzle-kit@beta (currently 1.0.0-beta.x). NEVER use drizzle-orm@latest which resolves to v0.x — it lacks defineRelations, 2-param DrizzleSqliteDODatabase, and other v1 features used throughout this skill.

pnpm install drizzle-orm@beta
pnpm install drizzle-kit@beta --save-dev

Docs reference: https://orm.drizzle.team/llms.txt — full docs index for LLMs. Fetch this when you need to look up something not covered here.

CRITICAL: Duplicate drizzle-orm in pnpm monorepos

In pnpm monorepos, drizzle-orm can get installed as two separate copies when different packages in the workspace resolve it with different peer dependency sets (e.g. one with @cloudflare/workers-types, one without). TypeScript sees them as incompatible types because drizzle-orm uses private class fields internally.

Symptoms: Types have separate declarations of a private property 'cachedTables', db.query.tableName is possibly undefined, orm.eq() not assignable to parameter, where clauses rejected with type errors. These errors appear on db.insert(), db.update(), db.delete(), db.query, and orm.eq/and/or calls when the schema is imported from a different workspace package than the one calling drizzle.

Diagnosis: search for duplicates in the lockfile:

grep " drizzle-orm@" pnpm-lock.yaml

If you see multiple entries with different peer dep suffixes in parentheses, you have duplicates.

Fix: run pnpm dedupe drizzle-orm from the workspace root. This collapses the duplicate entries into one. If that doesn't work, load the pnpm skill for the full deduplication workflow.

Never work around this with type casts (as any, as unknown as T, !). The casts hide the real problem and break silently when drizzle internals change.

Project structure

In projects with multiple packages (monorepos), put all database code in a dedicated db package at the workspace root. Read the npm-package skill for how to set up the package with proper package.json, tsconfig.json, exports, and build.

my-project/
  db/                        # the db package
    src/
      schema.ts              # tables + relations
      index.ts               # exports drizzle client, schema, types
    drizzle/                  # generated migrations
    drizzle.config.ts
    package.json              # name: "db", exports: { ".": "./src/index.ts" }
  api/                        # worker / server package
    src/
      index.ts                # imports from "db"
    package.json              # dependencies: { "db": "workspace:^" }
  pnpm-workspace.yaml         # packages: [db, api, ...]

The db package owns:

• Schema (src/schema.ts) — tables, relations, types
• Migrations (drizzle/) — generated SQL files
• Drizzle client (src/index.ts) — exported db instance or factory function
• drizzle.config.ts — dialect, schema path, migrations output

Other packages import from db directly:

import { db, schema } from 'db'
// or for environments needing runtime bindings (Hyperdrive, DO):
import { createDb, schema } from 'db'

For single-package projects, put schema at src/schema.ts (not in a db/ subfolder).

What the db package exports

For environments where the connection is static (libSQL, direct Postgres):

// db/src/index.ts
import { drizzle } from 'drizzle-orm/libsql'
import * as schema from './schema.ts'

export { schema }
export type { relations } from './schema.ts'

export const db = drizzle({
  connection: {
    url: process.env.DATABASE_URL!,
    authToken: process.env.DATABASE_AUTH_TOKEN!,
  },
  schema,
  relations: schema.relations,
})

For runtime-bound environments like Cloudflare, read ./cloudflare.md. It covers D1, Durable Objects, Hyperdrive, export conditions, D1 HTTP access from Node, and Cloudflare-specific migration rules.

Namespace imports

Always use namespace imports to avoid polluting local scope with generic names like eq, and, gt, text, integer:

import * as orm from 'drizzle-orm'
import * as s from 'drizzle-orm/sqlite-core'
import * as p from 'drizzle-orm/pg-core'

Use short single-letter aliases for dialect modules in schema files. Schema files are dominated by column/table/index definitions; you write s.text, s.integer, s.sqliteTable, s.index dozens of times per file. The shorter prefix keeps the schema scannable and lets the actual column definitions stand out instead of the namespace:

export const user = s.sqliteTable('user', {
  id: s.text('id').primaryKey(),
  name: s.text('name').notNull(),
  role: s.text('role', { enum: ['admin', 'member'] }).notNull(),
  createdAt: s.integer('created_at', { mode: 'number' }).notNull(),
})

Same for Postgres: p.pgTable, p.text, p.pgEnum, p.index.

Never destructure — import { eq, and, text } from 'drizzle-orm' is banned.

Relations definition (v2)

Docs: https://orm.drizzle.team/docs/relations-v2 | Migration guide: https://orm.drizzle.team/docs/relations-v1-v2

Define relations in the same file as your schema using defineRelations. Pass the tables as an object:

// src/schema.ts
import { defineRelations } from 'drizzle-orm'

// ... table definitions ...

export const relations = defineRelations({ accounts, boards }, (r) => ({
  accounts: {
    boards: r.many.boards(),
  },
  boards: {
    account: r.one.accounts({
      from: r.boards.accountId,
      to: r.accounts.id,
    }),
  },
}))

Pass both schema and relations to drizzle():

Many-to-many — use a junction table with cascade deletes on both FKs, and through in relations:

// Tables
const users = s.sqliteTable('users', {
  id: s.text('id').primaryKey().$defaultFn(() => ulid()),
  name: s.text('name').notNull(),
})

const orgs = s.sqliteTable('orgs', {
  id: s.text('id').primaryKey().$defaultFn(() => ulid()),
  name: s.text('name').notNull(),
})

// Junction table — cascade both sides so deleting a user or org cleans up memberships
const orgUsers = s.sqliteTable('org_users', {
  id: s.text('id').primaryKey().$defaultFn(() => ulid()),
  userId: s.text('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  orgId: s.text('org_id').notNull().references(() => orgs.id, { onDelete: 'cascade' }),
  role: s.text('role', { enum: ['owner', 'admin', 'member'] }).notNull().default('member'),
  createdAt: s.integer('created_at', { mode: 'number' }).notNull(),
}, (table) => [
  s.index('org_users_user_id_idx').on(table.userId),
  s.index('org_users_org_id_idx').on(table.orgId),
])

// Relations — both sides get many-to-many via through
export const relations = defineRelations({ users, orgs, orgUsers }, (r) => ({
  users: {
    orgs: r.many.orgs({
      from: r.users.id.through(r.orgUsers.userId),
      to: r.orgs.id.through(r.orgUsers.orgId),
    }),
  },
  orgs: {
    users: r.many.users({
      from: r.orgs.id.through(r.orgUsers.orgId),
      to: r.users.id.through(r.orgUsers.userId),
    }),
  },
}))

Query usage:

// Get user with all their orgs
const user = await db.query.users.findFirst({
  where: { id: userId },
  with: { orgs: true },
})

// Get org with all members
const org = await db.query.orgs.findFirst({
  where: { id: orgId },
  with: { users: true },
})

Query API (Prisma-like)

Docs: https://orm.drizzle.team/docs/rqb-v2 | Filters: https://orm.drizzle.team/docs/operators

Reads: always use db.query — the relational query API with object-style where. Never use db.select().from() for reads.

Latency rule: prefer db.query because it emits exactly one SQL statement, even when using with and relation filters. This is especially important on high-latency databases like D1 and serverless Postgres, where extra round-trips dominate response time. If you can express the read with relations, db.query is usually the best choice.

// Simple equality — just pass the value
const user = await db.query.accounts.findFirst({
  where: { refreshToken: someToken },
})

// Multiple conditions (implicit AND)
const accounts = await db.query.accounts.findMany({
  where: { status: 'active', workspaceId: 'ws_123' },
})

// Complex filters with operators
const posts = await db.query.posts.findMany({
  where: {
    AND: [
      { authorId: userId },
      { createdAt: { gt: cutoff } },
    ],
  },
  with: {
    comments: true,
    author: true,
  },
  orderBy: { createdAt: 'desc' },
  limit: 20,
})

// OR conditions
const results = await db.query.accounts.findMany({
  where: {
    OR: [
      { status: 'active' },
      { name: { like: 'John%' } },
    ],
  },
})

// Filter by relations (v2 only!)
const usersWithPosts = await db.query.users.findMany({
  where: {
    id: { gt: 10 },
    posts: { content: { like: 'M%' } },
  },
})

Key rules:

• Use object-style where — no operator imports needed. Pass values directly for equality, use { gt: }, { like: }, { in: } etc. for operators
• Always inline where objects directly in db.query.* calls. Do not extract them into reusable constants. Inline objects give better property autocomplete and clearer TypeScript errors at the call site.
• Use AND, OR, NOT for logical combinations
• Use with to include relations (like Prisma's include)
• db.query with with still runs as one SQL query, not N queries. Prefer it for latency-sensitive reads.
• Use orderBy as object: { createdAt: 'desc' }
• Use findFirst (adds LIMIT 1) or findMany
• NEVER use orm.inArray(), orm.eq(), or other operator functions inside db.query where — the query API only accepts object-style filters. orm.inArray(schema.users.id, ids) will fail with a type error. Instead, use { id: { in: ids } } or loop with findFirst per ID.
• Do not use columns to select specific fields. Listing every column you want adds noise, rarely helps performance on small rows, and makes the returned object not conform to drizzle Zod schemas (createSelectSchema). The only valid use is omitting a large field like a binary blob or long text body, and in that case use the exclusion form: columns: { blobField: false }. This keeps the query clean and returns everything except the excluded field.

Derive, don't re-query

When one result set can answer two questions, don't issue two statements.

Authorization from the list you're fetching anyway. A route that needs "is the caller a member" + "list all members" is one query, not a findFirst followed by a findMany: fetch the list and find() the caller's own row. Return null when absent so a non-member never sees the list.

const members = await db.query.orgMember.findMany({
  where: { orgId },
  with: { user: true, org: true },
})
const me = members.find((row) => row.userId === userId)
if (!me?.org) return null // not a member — leak nothing
return { role: me.role, org: me.org, members }

Fallbacks from the batch you already ran. Design batched reads so they answer both "is the requested resource valid" and "where to fall back". E.g. fetching all memberships to validate one org also yields the personal-org fallback for free — never issue a second query (or bounce through another route) to re-derive what the batch already returned.

Writes: use db.insert, db.update, db.delete — no query API for writes.

// For write .where() clauses, use orm.eq since there is no object-style where for writes
await db.update(schema.accounts)
  .set({ accessToken: newToken, updatedAt: Date.now() })
  .where(orm.eq(schema.accounts.id, accountId))
  .limit(1)

CRITICAL: Safe updates and deletes

Every db.update() and db.delete() MUST have a .where() clause. Never call .update().set(...) or .delete() without .where(). A missing where silently affects every row in the table. There is no drizzle config to enforce this at runtime; it is a discipline rule.

Every single-row update/delete MUST have .limit(1). This caps the SQL statement at the database level so even if the where clause is wrong (e.g. a field resolved to undefined and matched unexpectedly), at most 1 row is affected. Only skip .limit(1) when you are intentionally updating or deleting multiple rows (bulk status change, batch cleanup, etc.).

// Single-row update — always .where() + .limit(1)
await db.update(schema.users)
  .set({ name: 'New Name' })
  .where(orm.eq(schema.users.id, userId))
  .limit(1)

// Single-row delete — always .where() + .limit(1)
await db.delete(schema.sessions)
  .where(orm.eq(schema.sessions.id, sessionId))
  .limit(1)

// Bulk update — .where() required, .limit(1) intentionally omitted
await db.update(schema.notifications)
  .set({ read: true })
  .where(orm.eq(schema.notifications.userId, userId))

CRUD examples

Docs: Insert https://orm.drizzle.team/docs/insert | Update https://orm.drizzle.team/docs/update | Delete https://orm.drizzle.team/docs/delete | Upsert https://orm.drizzle.team/docs/guides/upsert

All examples below show both SQLite and Postgres when the syntax differs.

Insert

// Single insert with returning (same for SQLite and Postgres)
const [newAccount] = await db.insert(schema.accounts)
  .values({
    name: 'John',
    email: 'john@example.com',
    status: 'active',
    createdAt: Date.now(),     // SQLite: epoch ms
    // createdAt: new Date(),  // Postgres: Date object (or use .defaultNow())
  })
  .returning()

// Bulk insert — pass an array
await db.insert(schema.accounts)
  .values([
    { name: 'Alice', email: 'alice@example.com' },
    { name: 'Bob', email: 'bob@example.com' },
  ])
  .returning()

Read with relations

// Find one account with all its boards
const account = await db.query.accounts.findFirst({
  where: { id: accountId },
  with: {
    boards: true,
  },
})

// Find many with nested relations, filtering, ordering
const accounts = await db.query.accounts.findMany({
  where: {
    status: 'active',
    createdAt: { gt: cutoffDate },
  },
  with: {
    boards: {
      where: { status: 'active' },
      orderBy: { createdAt: 'desc' },
      limit: 10,
    },
  },
  orderBy: { name: 'asc' },
  limit: 50,
})

Update

// Update by condition — same for SQLite and Postgres
await db.update(schema.accounts)
  .set({ name: 'New Name', updatedAt: Date.now() })
  .where(orm.eq(schema.accounts.id, accountId))
  .limit(1)

// Update with returning (get back the updated row)
const [updated] = await db.update(schema.accounts)
  .set({ status: 'archived' })
  .where(orm.eq(schema.accounts.id, accountId))
  .limit(1)
  .returning()

IMPORTANT: Never include primary keys in UPDATE SET clauses on SQLite/D1. When SQLite sees UPDATE user SET id = ?, name = ? WHERE id = ?, it checks all foreign key constraints referencing that id, even if the value isn't changing. If the user has 1000 sessions, that's 1000+ extra row reads billed by D1. Always use explicit field lists in .set({}) and never pass the full object. This applies to any ORM layer on SQLite, not just Drizzle.

// BAD — passes id in SET, triggers FK constraint checks on every referencing row
await db.update(schema.users).set(userParam).where(orm.eq(schema.users.id, userParam.id))

// GOOD — explicit fields, no id in SET
await db.update(schema.users)
  .set({ name: userParam.name, updatedAt: Date.now() })
  .where(orm.eq(schema.users.id, userParam.id))
  .limit(1)

Delete

// Delete by condition
await db.delete(schema.boards)
  .where(orm.eq(schema.boards.id, boardId))
  .limit(1)

// Delete with returning (get back the deleted row)
const [deleted] = await db.delete(schema.boards)
  .where(orm.eq(schema.boards.id, boardId))
  .limit(1)
  .returning()

With onDelete: 'cascade' on foreign keys, deleting a parent automatically deletes all children:

// Deleting an account cascades to all its boards
await db.delete(schema.accounts)
  .where(orm.eq(schema.accounts.id, accountId))
  .limit(1)

Upsert (insert or update on conflict)

Syntax is the same for SQLite and Postgres — both use ON CONFLICT DO UPDATE:

// Upsert by primary key
await db.insert(schema.accounts)
  .values({
    id: accountId,
    name: 'John',
    email: 'john@example.com',
    createdAt: Date.now(),
    updatedAt: Date.now(),
  })
  .onConflictDoUpdate({
    target: schema.accounts.id,
    set: {
      name: 'John',
      email: 'john@example.com',
      updatedAt: Date.now(),
    },
  })

Upsert by unique column:

await db.insert(schema.accounts)
  .values({
    notionUserId: 'notion_123',
    name: 'John',
    accessToken: newToken,
    refreshToken: newRefreshToken,
    createdAt: Date.now(),
    updatedAt: Date.now(),
  })
  .onConflictDoUpdate({
    target: schema.accounts.notionUserId,
    set: {
      name: 'John',
      accessToken: newToken,
      refreshToken: newRefreshToken,
      updatedAt: Date.now(),
    },
  })
  .returning()

Upsert with excluded — use the proposed values dynamically:

import { sql } from 'drizzle-orm'

// When upserting multiple rows, use `excluded` to reference the proposed value
await db.insert(schema.accounts)
  .values(accountsToUpsert)
  .onConflictDoUpdate({
    target: schema.accounts.notionUserId,
    set: {
      name: sql`excluded.name`,
      accessToken: sql`excluded.access_token`,
      updatedAt: sql`excluded.updated_at`,
    },
  })

Upsert with composite unique key:

await db.insert(schema.usersToGroups)
  .values({ userId: 1, groupId: 5 })
  .onConflictDoUpdate({
    target: [schema.usersToGroups.userId, schema.usersToGroups.groupId],
    set: { assignedAt: Date.now() },
  })

Upsert with conditional update (Postgres & SQLite):

// Only update if existing row is older
await db.insert(schema.accounts)
  .values(newAccount)
  .onConflictDoUpdate({
    target: schema.accounts.id,
    set: { name: sql`excluded.name`, updatedAt: sql`excluded.updated_at` },
    setWhere: sql`${schema.accounts.updatedAt} < excluded.updated_at`,
  })

Insert or ignore (do nothing on conflict):

await db.insert(schema.accounts)
  .values({ id: accountId, name: 'John' })
  .onConflictDoNothing({ target: schema.accounts.id })

Use onConflictDoNothing() in delete-then-create patterns (e.g. "delete all children, recreate from scratch") where concurrent requests can race. Without it, two overlapping requests both delete and then both try to insert, causing unique constraint violations and deadlocks. This is the lightweight alternative to wrapping everything in a serializable transaction, which causes contention under load.

Type inference

Docs: https://orm.drizzle.team/docs/goodies

Derive types directly from the schema — never define separate interfaces:

// Select type (what you get back from queries)
type Account = typeof schema.accounts.$inferSelect

// Insert type (what you pass to db.insert)
type NewAccount = typeof schema.accounts.$inferInsert

// Use in function signatures
function processAccount(account: typeof schema.accounts.$inferSelect) { ... }

Enum union types

For SQLite text enums, define the allowed values in the column config and derive the union type from $inferSelect or $inferInsert. Do not duplicate a separate TypeScript union next to the schema.

export const botTokens = s.sqliteTable('bot_tokens', {
  botMode: s
    .text('bot_mode', { enum: ['self_hosted', 'gateway'] })
    .notNull()
    .default('self_hosted'),
})

export type BotMode = typeof botTokens.$inferSelect.botMode
// "self_hosted" | "gateway"

Use the same pattern for status and preference columns:

export type VerbosityLevel = typeof channelVerbosity.$inferSelect.verbosity
export type WorktreeStatus = typeof threadWorktrees.$inferSelect.status
export type ThreadSessionSource = typeof threadSessions.$inferSelect.source

SQLite does not enforce these enum values at runtime. text({ enum: [...] }) only affects TypeScript insert/select inference. Add a CHECK constraint manually only when database-level enforcement is actually needed.

Zod schema generation

Docs: https://orm.drizzle.team/docs/zod

Always prefer generating Zod schemas from your Drizzle tables instead of duplicating the same fields by hand in API code. This keeps validation, OpenAPI output, and DB schema in sync.

If the repo uses Drizzle v1 beta (drizzle-orm@1.0.0-beta.x), import from drizzle-orm/zod directly. Only use drizzle-zod on older Drizzle versions.

import { createInsertSchema, createSelectSchema, createUpdateSchema } from 'drizzle-orm/zod'

const insertAccountSchema = createInsertSchema(schema.accounts)
const selectAccountSchema = createSelectSchema(schema.accounts)

// Override or refine fields
const createBoardInput = createInsertSchema(schema.boards, {
  trackedRepos: z.array(z.string()),  // override text → proper array
})

// Use in spiceflow route
app.route({
  method: 'POST',
  path: '/api/boards',
  request: createBoardInput.omit({ id: true, createdAt: true }),
  response: selectBoardSchema.pick({ id: true }),
  async handler({ request }) { ... },
})

Prefer composition over duplication:

const projectSummarySchema = createSelectSchema(schema.project).pick({
  id: true,
  orgId: true,
  name: true,
  createdAt: true,
  updatedAt: true,
})

const projectCreateSchema = createInsertSchema(schema.project).pick({
  name: true,
  orgId: true,
})

const projectListResponseSchema = z.object({
  projects: z.array(projectSummarySchema),
})

Rules:

• Prefer createSelectSchema(table).pick(...) for response items derived from a table
• Prefer createInsertSchema(table).pick(...) / createUpdateSchema(table).pick(...) for request bodies
• Only hand-write Zod objects for envelopes, computed fields, or shapes that do not map 1:1 to a table row
• If an API shape mostly mirrors a table, derive it from the table first and then .extend() with the extra fields

Schema best practices

Docs: https://orm.drizzle.team/docs/sql-schema-declaration | Indexes: https://orm.drizzle.team/docs/indexes-constraints

File location

Put schema at src/schema.ts (not in a db/ subfolder). In monorepos, this lives inside the db package. For large projects split by domain: src/schema-users.ts, src/schema-posts.ts, then re-export from src/schema.ts.

ULID IDs

Use ULID for primary keys — sortable, unique, human-readable, no collisions:

import { ulid } from 'ulid'

const projects = s.sqliteTable('projects', {
  projectId: s.text('project_id').primaryKey().notNull().$defaultFn(() => ulid()),
  // ...
})

For Postgres:

const projects = p.pgTable('projects', {
  projectId: p.text('project_id').primaryKey().notNull().$defaultFn(() => ulid()),
  // ...
})

Table-specific ID columns

Always name primary key columns after the table, not just id. For example, the projects table uses projectId, the accounts table uses accountId, the environments table uses environmentId.

This makes joins, filters, and relations self-documenting. When you see projectId in a child table, you immediately know which table it references. The FK column name matches the referenced PK column name, so you never have to mentally map between different names.

const projects = s.sqliteTable('projects', {
  projectId: s.text('project_id').primaryKey().$defaultFn(() => ulid()),
  name: s.text('name').notNull(),
})