Skip to content

samuelpatro/.claude / skills/drizzle-orm-d1

drizzle-orm-d1

Verified

Type-safe ORM for Cloudflare D1 using Drizzle. Use when building D1 schemas, writing type-safe queries, generating migrations with Drizzle Kit, defining relations, using db.batch, or hitting D1_ERROR, BEGIN TRANSACTION failures, foreign key constraints, env.DB undefined, or schema inference issues.

Drizzle ORM for Cloudflare D1

Quick Start (10 Minutes)

1. Install Drizzle

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

2. Configure Drizzle Kit

drizzle.config.ts at the project root:

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/server/db/schema",
  out: "./src/server/db/migrations",
  dialect: "sqlite",
});

No driver, no dbCredentials. Drizzle Kit only generates SQL files. Wrangler applies them.

3. Define Schema

src/server/db/schema/users.ts:

import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";
import { relations } from "drizzle-orm";
import { posts } from "./posts";

export const users = sqliteTable("users", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  email: text("email").notNull().unique(),
  name: text("name").notNull(),
  createdAt: integer("created_at", { mode: "timestamp" })
    .$defaultFn(() => new Date())
    .notNull(),
});

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

src/server/db/schema/posts.ts:

import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";
import { relations } from "drizzle-orm";
import { users } from "./users";

export const posts = sqliteTable("posts", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  content: text("content").notNull(),
  authorId: integer("author_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
});

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
}));

src/server/db/schema/index.ts re-exports everything (tables AND *Relations):

export * from "./users";
export * from "./posts";

src/server/db/client.ts:

import { drizzle } from "drizzle-orm/d1";
import * as schema from "./schema";

export function createDb(d1: D1Database) {
  return drizzle(d1, { schema });
}

export type DB = ReturnType<typeof createDb>;

The barrel re-export is mandatory: client.ts does import * as schema from "./schema" and passes it to drizzle, and the relational query API can only see relations exported there.

4. Generate & Apply Migrations (local)

bun run db:generate        # drizzle-kit generate → SQL in ./src/server/db/migrations
bun run db:migrate:local   # apply to local D1

Remote is a deliberate, separate step: bun run db:migrate:remote, then bun run deploy — the scripts bake in the production env selection (see MIGRATIONS.md); never run raw wrangler --remote/deploy.

5. Query in Worker

import { createDb } from "./server/db/client";
import { users } from "./server/db/schema";
import { eq } from "drizzle-orm";

export default {
  async fetch(req: Request, env: { DB: D1Database }) {
    const db = createDb(env.DB);
    const list = await db.select().from(users).all();
    return Response.json(list);
  },
};

Helpers downstream take db: DB, never a raw D1Database.


Critical Rules

Always Do

RuleWhy
Use dialect: "sqlite" only in drizzle.config.tsDrizzle Kit's job is generating SQL; wrangler owns the database
Point schema at a directoryMulti-file schema with index.ts barrel is the project convention
Re-export every *Relations from schema/index.tsOtherwise the relational query API silently can't see them
Go through createDb(env.DB)Schema wiring stays in one place; the DB type flows to callers
Use bunx drizzle-kit generateNever write SQL by hand
Match the surrounding table's timestamp style (see SCHEMA-PATTERNS)Auth tables use ISO text; product tables use epoch-ms integers — do not mix or migrate
Use .$defaultFn(() => ...) for dynamic defaults.default() is for literal SQL defaults
Use db.batch([...]) for transactionsD1 does not support SQL BEGIN/COMMIT
Build .where() from condition operators (eq, ne, gt/gte/lt/lte, and, or, not, like, inArray, isNull, isNotNull, between, exists — all from drizzle-orm), not raw sql`...` templatesComposable, typo-safe, and typed against the column; reserve sql`...` for what operators can't express (aggregates, CASE WHEN, COLLATE, datetime math)
Chunk multi-row inserts via insertInChunks (src/server/lib/d1-insert.ts)D1 caps bound params at 100 per statement
Test migrations with --local before commitCatches schema drift before CI does
Read every generated migration before applyingTable rebuilds break on FK'd tables: D1 no-ops drizzle-kit's PRAGMA foreign_keys=OFF, so the drop either rolls back at COMMIT or cascades child rows away. See ERRORS.md #14
Verify rebuild migrations on a populated local DBAn empty DB passes any rebuild; production won't
Declare onDelete on every new FKD1 always enforces FKs; default no action fails deletes

Never Do

RuleWhy
Add driver or dbCredentials to drizzle.config.tsWe do not use the HTTP driver; wrangler is the only applier
Run raw wrangler d1 migrations apply DB --remote or raw wrangler deployWithout the production env selection they target the top-level (dev) config. Use bun run db:migrate:remote (bakes in --env production) and bun run deploy (bakes in CLOUDFLARE_ENV=production), migrate before deploy
Use drizzle-kit push against any D1Bypasses migrations, no audit trail, no rollback
Use SQL BEGIN TRANSACTION or db.transaction()Not supported on D1; use db.batch
Pass a raw D1Database to query helpersLoses schema wiring; relational queries break
Commit credentials in any config fileUse Cloudflare secrets and env vars

D1 Limits & Quirks

LimitValue
Bound parameters per statement100 (drives insert chunking)
Columns per table100
String/BLOB value size2 MB
SQL statement length100 KB
Queries per Worker invocation1000 (paid) / 50 (free)
Concurrent D1 connections per Worker6
Max query duration30 s
Database size10 GB (paid) / 500 MB (free)

Quirks: FKs are always enforced — PRAGMA foreign_keys is blocked, only PRAGMA defer_foreign_keys works (see MIGRATIONS.md). Other PRAGMAs are restricted to table_list/table_info/table_xinfo. D1 is single-threaded (one query at a time, backed by a Durable Object). No BigInt — values beyond JS's 53-bit safe-integer range lose precision. FTS5 virtual tables work but block wrangler d1 export.


Top 5 Critical Errors

#ErrorSolution
1D1_ERROR: Cannot use BEGIN TRANSACTIONUse db.batch([...]) instead of db.transaction()
2FOREIGN KEY constraint failedDefine cascading: .references(() => users.id, { onDelete: "cascade" })
3env.DB is undefinedThe binding in wrangler.jsonc d1_databases must equal the property name (DB)
4db.query.X is undefined or relation missing in resultRe-export *Relations from schema/index.ts and pass { schema } to drizzle(...)
5Type instantiation is excessively deep and possibly infiniteAnnotate with InferSelectModel<typeof users> instead of letting TS infer

See: ERRORS.md for the full catalog with code samples.


Common Patterns Summary

PatternUse CaseReference
CRUD operationsBasic database operationsQUERIES.md (CRUD section)
Relations & joinsManual joins and relational query APIQUERIES.md (Joins, Relational query API)
Batch operationsAtomic multi-statement work (D1 batch API)QUERIES.md (Transactions: db.batch)
Schema designNaming, indexes, soft deletes, UUIDs, enumsSCHEMA-PATTERNS.md
Prepared statementsHot paths reusing the same query shapeQUERIES.md (Prepared statements)

Configuration Summary

FilePurposeReference
drizzle.config.tsDrizzle Kit configuration (dialect-only)This SKILL.md, Quick Start step 2
wrangler.jsoncD1 binding + migrations_dirMIGRATIONS.md (Wrangler binding)
src/server/db/client.tscreateDb factory + DB typeThis SKILL.md, Quick Start step 3
src/server/db/schema/index.tsBarrel re-export feeding the relational query APIThis SKILL.md, Quick Start step 3
package.jsonbun scripts for migrationsMIGRATIONS.md (bun scripts)

bun scripts:

{
  "db:generate": "drizzle-kit generate",
  "db:migrate:local": "wrangler d1 migrations apply DB --local",
  "db:migrate:remote": "wrangler d1 migrations apply DB --remote --env production",
  "db:seed": "wrangler d1 execute DB --local --file=./src/server/db/seed.sql"
}

--env production on the remote script is load-bearing — the top-level wrangler.jsonc config is dev-only; production bindings live under env.production.


Migration Workflow

StepCommandNotes
1. Edit schemaEdit files in src/server/db/schema/Add the table, re-export from index.ts
2. Generatebun run db:generateCreates SQL in src/server/db/migrations — read the generated file (rebuild? see ERRORS.md #14)
3. Apply locallybun run db:migrate:localVerify against local D1 — populated, not empty, if the migration rebuilds a table
4. Commit and open PRgit commit && git pushTests rebuild an in-memory DB from the real migration files, so drift fails fast
5. Apply remotebun run db:migrate:remoteTargets env.production via --env production
6. Deploybun run deployAlways after the remote migration, never before

See: MIGRATIONS.md for wrangler.jsonc setup, renames, hand-edit cases, and common workflow failures.


TypeScript Type Inference

import type { InferSelectModel, InferInsertModel } from "drizzle-orm";
import { users } from "./server/db/schema";

export type User = InferSelectModel<typeof users>;
export type NewUser = InferInsertModel<typeof users>;

InferSelectModel is the row shape returned by select. InferInsertModel is the shape accepted by insert, with autoIncrement and defaulted columns marked optional.


When to Load References

ReferenceLoad when...
ERRORS.mdDebugging D1 errors, transaction failures, binding issues, migration failures
SCHEMA-PATTERNS.mdDesigning schemas, naming, timestamps, UUIDs, soft deletes, indexes, relations
MIGRATIONS.mdSetting up wrangler.jsonc, configuring migrations_dir, troubleshooting renames
QUERIES.mdWriting non-trivial queries, joins, relational queries, db.batch, prepared statements

Bundled Resources

References: SCHEMA-PATTERNS.md, MIGRATIONS.md, QUERIES.md, ERRORS.md


Dependencies

{
  "dependencies": {
    "drizzle-orm": "^0.45.2"
  },
  "devDependencies": {
    "drizzle-kit": "^0.31.10"
  }
}

Official Documentation


Token Savings: progressive disclosure via four sibling references Error Prevention: top 5 inline, full catalog in ERRORS.md

slack-gif-creatoranthropics/skillsKnowledge and utilities for creating animated GIFs optimized for Slack. Provides constraints, validation tools, and animation concepts. Use when users request animated GIFs for Slack like "make me a GIF of X doing Y for Slack."Stars: 197kUpdated Claude CodeCodexVerifiedInstall
ponytailDietrichGebert/ponytailLazy senior dev mode for any coding task: the smallest change that fully solves it, and a reply a busy human understands in one read.Stars: 194kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report.Stars: 194kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailList every `shortcut:` comment (and older `ponytail:` ones) as a debt ledger. One-shot report, changes nothing. Use for "ponytail debt", "what did ponytail defer", "list the shortcuts", /ponytail-debt.Stars: 194kUpdated Claude CodeCodexVerifiedInstall