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.

FileKindSize
ERRORS.mdmarkdown10.1 KB
MIGRATIONS.mdmarkdown6.4 KB
QUERIES.mdmarkdown9.3 KB
SCHEMA-PATTERNS.mdmarkdown7.9 KB
SKILL.mdskill md12.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.)
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: 198kUpdated 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