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 |
QUERIES.md
View on GitHub# Queries
Patterns for the query API. All examples take `db: DB` (the type exported from `src/server/db/client.ts`), never a raw `D1Database`. That's how schema wiring stays in one place and how relational queries keep working.
```ts
import type { DB } from "./server/db/client";
```
## Result methods: get vs all vs run
| Method | Returns | When |
|--------|---------|------|
| `.get()` | First row or `undefined` | You expect at most one row (lookup by PK or unique column) |
| `.all()` | Array of rows (possibly empty) | List queries |
| `.run()` | Metadata only (no rows) | `insert`, `update`, `delete` when you don't need the affected rows |
| `.returning()` + `.get()`/`.all()` | The mutated rows | `insert`/`update`/`delete` when you need the result back |
```ts
const user = await db.select().from(users).where(eq(users.id, 1)).get();
const list = await db.select().from(users).all();
await db.update(users).set({ name: "X" }).where(eq(users.id, 1)).run();
const created = await db.insert(users).values({ email, name }).returning().get();
```
## CRUD
```ts
import { eq, and, or, gt, lt, like, desc, asc } from "drizzle-orm";
import { users, posts } from "./server/db/schema";
// Read
async function findByEmail(db: DB, email: string) {
return db.select().from(users).where(eq(users.email, email)).get();
}
async function listRecentPosts(db: DB, limit = 20) {
return db
.select()
.from(posts)
.orderBy(desc(posts.createdAt))
.limit(limit)
.all();
}
// Create
async function createUser(db: DB, input: { email: string; name: string }) {
return db.insert(users).values(input).returning().get();
}
// Update
async function renameUser(db: DB, id: number, name: string) {
return db
.update(users)
.set({ name })
.where(eq(users.id, id))
.returning()
.get();
}
// Delete
async function deleteUser(db: DB, id: number) {
await db.delete(users).where(eq(users.id, id)).run();
}
```
Operators are imported from `drizzle-orm`: `eq`, `ne`, `gt`, `gte`, `lt`, `lte`, `and`, `or`, `not`, `like`, `inArray`, `notInArray`, `isNull`, `isNotNull`, `between`, `exists`.
## Joins (manual)
```ts
const rows = await db
.select({
post: posts,
author: users,
})
.from(posts)
.innerJoin(users, eq(posts.authorId, users.id))
.where(eq(users.id, 1))
.all();
```
The select shape determines the row shape. `rows[0].post.title`, `rows[0].author.name`. Use `leftJoin` for optional sides.
For `select()` without a shape, both tables flatten into one object keyed by table name. Always pass an explicit shape, it's clearer and faster to read.
## Relational query API
`db.query.<table>.findMany / findFirst` is the higher-level API. It works because `client.ts` passes `{ schema }` to `drizzle(...)`, and because `schema/index.ts` re-exports the `*Relations` objects.
```ts
const userWithPosts = await db.query.users.findFirst({
where: (u, { eq }) => eq(u.id, 1),
with: {
posts: {
orderBy: (p, { desc }) => desc(p.createdAt),
limit: 5,
},
},
});
```
```ts
const recent = await db.query.posts.findMany({
with: { author: true },
orderBy: (p, { desc }) => desc(p.createdAt),
limit: 20,
});
// recent[0].author.name
```
When `with` returns nothing or `db.query.X` is undefined, the relation isn't reaching drizzle. Check `schema/index.ts` re-exports the relevant `*Relations`.
The relational API issues multiple queries under the hood. For latency-sensitive paths, prefer manual joins.
## Transactions: db.batch
D1 does not support SQL `BEGIN`/`COMMIT`. `db.transaction()` throws. Use `db.batch([...])` for atomic multi-statement work.
```ts
const [created, _, _post] = await db.batch([
db.insert(users).values({ email, name }).returning(),
db.update(stats).set({ users: sql`${stats.users} + 1` }),
db.insert(posts).values({ title, content, authorId: sql`last_insert_rowid()` }),
]);
```
Each element is a query builder, not an awaited promise. The whole batch succeeds or fails atomically. Results come back as a tuple matching input order.
`db.batch` accepts at least one statement. For dynamic length:
```ts
const stmts = ids.map((id) =>
db.update(users).set({ active: false }).where(eq(users.id, id)),
);
await db.batch(stmts as [typeof stmts[0], ...typeof stmts]);
```
The cast is a type workaround. Drizzle's `batch` requires a non-empty tuple at the type level.
## Multi-row inserts (100-param limit)
D1 allows at most **100 bound parameters** per statement. A multi-row insert of N columns can take at most `floor(100 / N)` rows. Use `insertInChunks` from `src/server/lib/d1-insert.ts`:
```ts
import { insertInChunks } from "../lib/d1-insert";
// 3 bound params per row → max 33 rows per statement
await insertInChunks(rows, 3, (chunk) =>
db.insert(recordingAttendees).values(chunk).onConflictDoNothing(),
);
```
Symptoms of hitting the limit: cryptic insert failures or partial data. Prefer this over a single `.values(bigArray)` whenever the row count is unbounded (imports, attendee rosters, bulk grants).
## Conditional filters
Build optional filters as a conditions array, then combine with `and(...)`. `and()` of an empty array is `undefined`, and `.where(undefined)` is a no-op — no special-casing needed:
```ts
import { and, eq, like, type SQL } from "drizzle-orm";
async function searchUsers(db: DB, f: { name?: string; email?: string; active?: boolean }) {
const conditions: SQL[] = [];
if (f.name) conditions.push(like(users.name, `%${f.name}%`));
if (f.email) conditions.push(eq(users.email, f.email));
if (f.active !== undefined) conditions.push(eq(users.active, f.active));
return db.select().from(users).where(and(...conditions)).all();
}
```
For reusable query-composition helpers (pagination, shared filters), `.$dynamic()` lifts the one-call-per-method restriction:
```ts
function withPagination<T extends SQLiteSelect>(qb: T, page: number, size = 20) {
return qb.limit(size).offset(page * size);
}
const q = db.select().from(users).$dynamic();
const rows = await withPagination(q, 2).all();
```
## Debugging queries
```ts
// Log every query + params
const db = drizzle(d1, { schema, logger: true });
// Inspect SQL without executing
const q = db.select().from(users).where(eq(users.id, 1));
console.log(q.toSQL()); // { sql: "select ... where id = ?", params: [1] }
```
## Prepared statements
Re-use a query plan when running the same shape repeatedly:
```ts
import { sql } from "drizzle-orm";
const userByEmail = db
.select()
.from(users)
.where(eq(users.email, sql.placeholder("email")))
.prepare();
const a = await userByEmail.get({ email: "a@x.com" });
const b = await userByEmail.get({ email: "b@x.com" });
```
Prepared statements pay off in hot loops. For one-off queries the overhead of `.prepare()` outweighs the saving.
## Type inference
Drizzle types flow through queries automatically. You rarely need to spell them out, but:
```ts
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>;
async function upsert(db: DB, input: NewUser): Promise<User> {
return db.insert(users).values(input).returning().get();
}
```
If you hit `Type instantiation is excessively deep and possibly infinite` (usually on a join with several relations), annotate the function's return type with `InferSelectModel` instead of letting TS infer. The error is a TS depth limit, not a Drizzle bug.
## SQL escape hatch
When the builder doesn't have what you need:
```ts
import { sql } from "drizzle-orm";
await db
.update(posts)
.set({ viewCount: sql`${posts.viewCount} + 1` })
.where(eq(posts.id, id))
.run();
const stats = await db
.select({
day: sql<string>`date(${posts.createdAt}, 'unixepoch')`,
count: sql<number>`count(*)`,
})
.from(posts)
.groupBy(sql`date(${posts.createdAt}, 'unixepoch')`)
.all();
```
Annotate the result type (`sql<string>`) so TS knows what comes back. Parameterise with `${...}` to avoid SQL injection, never string-concatenate.
## Querying JSON columns in SQL
D1 always has the SQLite JSON functions available (no extension loading). Useful when a JSON `text` column needs filtering server-side instead of parsing every row in the Worker:
| Function | Returns | Example |
|----------|---------|---------|
| `json_extract(col, '$.path')` | SQL type matching the JSON value | `json_extract(data, '$.name')` → `Alice` |
| `col ->> '$.path'` | Always TEXT | `data ->> '$.score'` → `"42"` |
| `col -> '$.path'` | JSON representation | `data -> '$.score'` → `42` |
| `json_each(col)` | One row per array element | Expand a JSON array into rows |
```ts
const auEvents = await db
.select()
.from(events)
.where(sql`json_valid(${events.metadata}) AND ${events.metadata} ->> '$.country' = 'AU'`)
.all();
```
`json_extract()` throws `malformed JSON` (error 9015) if the column holds non-JSON text — guard with `json_valid()` whenever the column isn't guaranteed clean.
If a JSON field is filtered often, promote it to an indexable generated column instead of extracting per query:
```sql
ALTER TABLE sensor_data ADD COLUMN location TEXT
AS (json_extract(raw_data, '$.location')) STORED;
CREATE INDEX sensor_data_location_idx ON sensor_data(location);
```
(Hand-edit case for a generated migration — Drizzle Kit doesn't model generated columns from JSON.)
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 |