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

SCHEMA-PATTERNS.md

View on GitHub
# Schema Patterns

Reference for designing tables in `src/server/db/schema/`.

## Directory layout

`drizzle.config.ts` points `schema` at a directory. Each table gets its own file. `index.ts` re-exports everything.

```
src/server/db/
├── schema/
│   ├── index.ts        # re-exports all tables and *Relations
│   ├── users.ts
│   ├── posts.ts
│   └── comments.ts
├── client.ts           # createDb factory + DB type
└── migrations/         # generated by drizzle-kit, applied by wrangler
```

`schema/index.ts`:

```ts
export * from "./users";
export * from "./posts";
export * from "./comments";
```

Both the table (`users`) and the relations object (`usersRelations`) must flow through this barrel. Drizzle's relational query API resolves `db.query.users.findMany({ with: { posts: true } })` by walking the `schema` object passed to `drizzle(d1, { schema })`. Anything missing from `index.ts` is invisible to it. The failure mode is silent: a query compiles, runs, and returns rows without the `with` data.

## Naming

| What | Convention (new tables) |
|------|------------|
| Table name (SQL) | snake_case, plural: `users`, `blog_posts` |
| Column name (SQL) | snake_case: `created_at`, `author_id` |
| TS table export | camelCase plural matching SQL: `users`, `blogPosts` |
| TS column key | camelCase: `createdAt`, `authorId` |
| Relations export | `<table>Relations`: `usersRelations` |
| Foreign key column | `<referenced_table_singular>_id`: `author_id` referencing `users.id` |

The SQL/TS name pair is set explicitly in every column to keep migrations stable across refactors:

```ts
authorId: integer("author_id").notNull(),
```

**Legacy exception — do not "fix" in place.** Several older tables use camelCase SQL column names (`tasks.projectId`, `projects.createdAt`) or snake_case TS keys (`ideas.team_id`, `recordings.created_by`). When editing those tables, match the file's existing style. Renaming columns requires a real migration and app-wide query updates — never drive-by.

## Primary keys

### Auto-increment integer (default)

```ts
id: integer("id").primaryKey({ autoIncrement: true }),
```

Use this unless you have a specific reason. Compact, fast, indexed for free.

### UUID

```ts
import { sql } from "drizzle-orm";

id: text("id")
  .primaryKey()
  .$defaultFn(() => crypto.randomUUID()),
```

Use when IDs are exposed publicly, generated client-side, or need to be unguessable. Workers have `crypto.randomUUID()` natively.

## Timestamps

D1 has no native date type. This codebase has **two established conventions** — pick by domain, never convert one to the other without an explicit migration plan.

### A. Epoch milliseconds as `integer` (product tables)

Used by: `tasks`, `projects`, `teams`, `ideas`, `recordings`, `artifacts`, `comments`, `ai_usage`, calendar tables, etc.

```ts
createdAt: integer("created_at")
  .notNull()
  .$defaultFn(() => Date.now()),
updatedAt: integer("updated_at")
  .notNull()
  .$defaultFn(() => Date.now()),
```

TS type is `number` (ms since epoch). Call sites compare with `Date.now()` and pass numbers — not `Date` objects. Prefer this for **new product tables**.

Do **not** add `{ mode: "timestamp" }` to existing ms columns — that mode expects unix **seconds** and returns `Date`, which would break every caller.

### B. ISO-8601 strings as `text` (auth / sessions / vaults)

Used by: `users`, `sessions`, `apps`, `notifications`, `vaults`, OTP/TOTP, invites, user-events, etc.

```ts
const nowIso = () => new Date().toISOString();

createdAt: text("created_at").notNull().$defaultFn(nowIso),
updatedAt: text("updated_at").notNull().$defaultFn(nowIso),
```

TS type is `string`. Call sites use `.toISOString()` for writes and string compare for expiry. Prefer this only when extending auth/session-adjacent tables that already use it.

### Soft deletes

Match the table's style: `archived_at: integer()` on recordings, `archivedAt: text(...)` on projects, etc.

## Indexes and uniqueness

```ts
import { sqliteTable, text, integer, index, uniqueIndex } from "drizzle-orm/sqlite-core";

export const posts = sqliteTable(
  "posts",
  {
    id: integer("id").primaryKey({ autoIncrement: true }),
    slug: text("slug").notNull(),
    authorId: integer("author_id").notNull(),
  },
  (t) => ({
    slugIdx: uniqueIndex("posts_slug_idx").on(t.slug),
    authorIdx: index("posts_author_idx").on(t.authorId),
  }),
);
```

Always name indexes (`posts_slug_idx`). D1 surfaces named indexes in error messages and `EXPLAIN`.

`text("...").unique()` is a column-level shortcut equivalent to a unique index, fine for single-column uniqueness.

## Enums

SQLite has no `ENUM` type. Use `text` with a TS enum and a check constraint:

```ts
import { sql } from "drizzle-orm";

export const PostStatus = ["draft", "published", "archived"] as const;
export type PostStatus = (typeof PostStatus)[number];

status: text("status", { enum: PostStatus })
  .notNull()
  .default("draft"),
```

The `enum` option narrows the TS type. For runtime enforcement add a check constraint in the table extras callback.

## Foreign keys and cascades

```ts
authorId: integer("author_id")
  .notNull()
  .references(() => users.id, { onDelete: "cascade" }),
```

Always declare `onDelete` on **new** FKs. The default is `"no action"` which throws `FOREIGN KEY constraint failed` instead of doing what most apps want. Pick `"cascade"`, `"set null"`, or `"restrict"` deliberately.

D1 enforces foreign keys by default. No PRAGMA needed.

**Do not bulk-add `onDelete` to existing FKs** without checking delete paths — flipping a parent FK to `cascade` can wipe child rows that the app currently blocks on delete.

## JSON columns

Prefer plain `text()` and document the shape in a comment. Call sites own `JSON.parse` / `JSON.stringify`. Do not switch existing columns to `{ mode: "json" }` without auditing every reader/writer (double-encode / double-parse bugs).

```ts
// JSON: Array<{ text: string; owner?: string }>
action_items: text(),
```

For brand-new columns where every access goes through Drizzle (no raw SQL `json_extract`), `{ mode: "json" }` + `.$type<T>()` is fine.

## Relations

The `relations()` helper exists only for the relational query API. It does not affect the SQL schema.

`schema/users.ts`:

```ts
export const users = sqliteTable("users", { /* ... */ });

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

`schema/posts.ts`:

```ts
export const posts = sqliteTable("posts", {
  authorId: integer("author_id").notNull().references(() => users.id),
  /* ... */
});

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

For self-referencing or multi-edge relations, name them explicitly:

```ts
export const usersRelations = relations(users, ({ one, many }) => ({
  authoredPosts: many(posts, { relationName: "author" }),
  reviewedPosts: many(posts, { relationName: "reviewer" }),
}));

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

Re-export `usersRelations` and `postsRelations` from `schema/index.ts`. If the relation is missing from the barrel, `db.query.users.findMany({ with: { posts: true } })` returns rows without the `posts` field and no error.

## Type inference

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

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/defaulted columns marked optional. Prefer these to ad hoc interfaces, they stay in sync with the schema automatically.
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