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 |
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.
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 |