samuelpatro/.claude / skills/drizzle-orm-d1
drizzle-orm-d1
VerifiedType-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.
| File | Kind | Size |
|---|---|---|
| ERRORS.md | markdown | 10.1 KB |
| MIGRATIONS.md | markdown | 6.4 KB |
| QUERIES.md | markdown | 9.3 KB |
| SCHEMA-PATTERNS.md | markdown | 7.9 KB |
| SKILL.md | skill md | 12.2 KB |
MIGRATIONS.md
View on GitHub# Migrations
Generation and local application always run on your machine. Remote application and deploy go through the package.json scripts, which bake in the production env selection — never call `wrangler d1 migrations apply --remote` or `wrangler deploy` raw. The top-level `wrangler.jsonc` config is **dev-only**; production lives under `env.production` (real APP_URL, prod D1/R2, `send_email`), selected via `--env production` (migrations) and `CLOUDFLARE_ENV=production` (build + deploy).
## Wrangler binding
`wrangler.jsonc` connects your Worker to D1 and tells wrangler where to find migrations. The `migrations_dir` value must match the `out` value in `drizzle.config.ts`.
```jsonc
{
"name": "my-worker",
"main": "src/index.ts",
"compatibility_date": "2025-01-01",
"d1_databases": [
{
"binding": "DB",
"database_name": "my-database",
"database_id": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
"migrations_dir": "./src/server/db/migrations"
}
]
}
```
The `binding` value (`DB`) is exactly what your code reads as `env.DB`. Mismatch is the source of `env.DB is undefined`.
## Daily loop
```
edit schema → generate → apply local → query → commit
```
```bash
bun run db:generate
bun run db:migrate:local
```
Remote comes later, deliberately, via `bun run db:migrate:remote` — and always **before** `bun run deploy`.
## bun scripts
The matrix `package.json` scripts (the binding name `DB` doubles as the database identifier):
```json
{
"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 the load-bearing part: without it, `--remote` targets the top-level (dev) config's D1, not production. Always use the script, never raw wrangler.
## Read every generated migration before applying
Non-negotiable when the change rebuilds a table (anything SQLite can't express as `ALTER`: dropping a NOT NULL, changing a type or an FK). drizzle-kit wraps rebuilds in `PRAGMA foreign_keys=OFF`, which is a **no-op inside D1's migration transaction** — the `DROP TABLE` either fails the migration at COMMIT (restrict/no-action children) or cascades into child tables and silently deletes their rows. See [ERRORS.md](ERRORS.md) #14 for the park-and-restore rewrite, and always verify against a *populated* local DB — rebuilds pass on an empty one and fail in production.
Useful checks:
```bash
bunx drizzle-kit check # detects colliding/raced migrations (multiple branches generating)
bunx drizzle-kit pull # introspects an existing DB into a Drizzle schema (adopting a legacy DB)
```
**drizzle-kit 1.0 (beta) note**: it generates nested migration folders (`migrations/<name>/migration.sql`), which wrangler silently ignores — `migrations apply` reports "No migrations found". Stay on 0.x until wrangler supports it, or flatten the files after generate. ([drizzle-orm#5266](https://github.com/drizzle-team/drizzle-orm/issues/5266))
## Renames
Drizzle Kit cannot tell rename from drop+add. When you rename a column or table, generation prompts:
```
Is column `username` in `users` table created or renamed from another column?
❯ ~ name › username (rename)
+ username (create column)
```
Pick `rename` to preserve data. Picking `create column` will issue a `DROP COLUMN` + `ADD COLUMN` and lose every row's value. The prompt is the only signal, read it.
Tables work the same way:
```
Is `accounts` table created or renamed from another table?
❯ ~ users › accounts (rename)
+ accounts (create table)
```
## When to hand-edit a generated SQL file
Almost never. The generated file in `src/server/db/migrations/NNNN_*.sql` is committed and replayed in CI, so it must be deterministic and idempotent for a fresh DB. Hand-edit only when:
- You need a data migration alongside a structural change (add a column, then `UPDATE users SET ...`). Add the `UPDATE` to the generated file.
- A statement is destructive (`DROP COLUMN`) and you want to stage it (rename to `_deprecated_*` first, drop in a later migration).
- The migration *creates* tables with circular foreign keys — prepend `PRAGMA defer_foreign_keys = on;` so ordering doesn't matter. This works ONLY for creation ordering: it does **not** rescue a table rebuild that drops a referenced table (the deferred-violation counter never clears — see [ERRORS.md](ERRORS.md) #14 for the park-and-restore pattern that does work).
If you edit the file, never re-run `drizzle-kit generate` over the same migration. Generate the next one on top of your edited state.
## Remote apply & deploy
1. `bun run db:migrate:remote`
2. `bun run deploy`
In that order. Migrate before deploy. A Worker that boots against a schema it doesn't expect crashes harder than one that waits an extra minute.
Two invariants are non-negotiable: the same migration files are applied in both environments, and the application order matches commit order. Wrangler enforces both via the `d1_migrations` table it maintains in your D1.
Tests are a third safety net: the route-test suite builds an in-memory SQLite **from the real migration files** (`src/test/db.ts`, once per worker), so schema drift between migrations and schema TS fails the test run fast — route tests exercise real Drizzle queries, not mocks.
## Common workflow failures
| Symptom | Cause | Fix |
|---------|-------|-----|
| `wrangler d1 migrations list` shows no migrations | `migrations_dir` in `wrangler.jsonc` doesn't match `out` in `drizzle.config.ts` | Align them, both pointing at `./src/server/db/migrations` |
| Migration "applied remotely" but production still broken | Raw `wrangler d1 migrations apply DB --remote` without `--env production` migrated the dev-config D1, not production | Always `bun run db:migrate:remote`; check `wrangler d1 migrations list DB --remote --env production` to see production's actual state |
| `drizzle-kit generate` produces empty migration | No detectable diff. Either schema didn't change, or you edited a generated SQL file and re-ran generate. | Write changes in TS, regenerate. Don't edit prior migrations |
| `no such table: d1_migrations` | First-time setup; wrangler creates this on first apply | Run `wrangler d1 migrations apply ... --local` once |
Full error catalog is in [ERRORS.md](ERRORS.md).
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| 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: 198k | Updated | Claude CodeCodex | Verified | Install |
| 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: 194k | Updated | Claude CodeCodex | Verified | Install |
| ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report. | Stars: 194k | Updated | Claude CodeCodex | Verified | Install |
| 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: 194k | Updated | Claude CodeCodex | Verified | Install |