d1-drizzle-schema

어떤 스킬인가요?

Generate Drizzle ORM schemas for Cloudflare D1 databases with correct D1-specific patterns. Produces schema files, migration commands, type exports, and DATABASE_SCHEMA.md documentation. Handles D1 quirks: foreign keys always enforced, no native BOOLEAN/DATETIME types, 100 bound parameter limit, JSON stored as TEXT. Use when creating a new database, adding tables, or scaffolding a D1 data layer.

설치 방법

프로젝트 폴더에서 아래 명령어를 실행하고, 설치할 에이전트를 선택하세요.

설치 명령을 직접 실행해야 적용됩니다. 지원 에이전트와 필요한 권한·라이선스는 제작자의 안내를 확인하세요.

스킬 원문 파일

import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  schema: './src/server/db/schema.ts',
  out: './drizzle',
  dialect: 'sqlite',
  driver: 'd1-http',
  dbCredentials: {
    accountId: process.env.CLOUDFLARE_ACCOUNT_ID!,
    databaseId: process.env.CLOUDFLARE_D1_DATABASE_ID!,
    token: process.env.CLOUDFLARE_API_TOKEN!,
  },
  verbose: true,
  strict: true,
})

/**
 * D1 Drizzle Schema Template
 *
 * Demonstrates all common D1 column patterns:
 * - UUID primary key, text with enums, boolean as integer,
 *   timestamp as integer, typed JSON, foreign keys, indexes
 */
import { sqliteTable, text, integer, real, index, uniqueIndex } from 'drizzle-orm/sqlite-core'
import { relations } from 'drizzle-orm'

// --- Users ---

export const users = sqliteTable('users', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  name: text('name').notNull(),
  email: text('email').notNull(),
  role: text('role', { enum: ['admin', 'editor', 'viewer'] }).notNull().default('viewer'),
  emailVerified: integer('email_verified', { mode: 'boolean' }).notNull().default(false),
  preferences: text('preferences', { mode: 'json' }).$type<Record<string, unknown>>(),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
}, (table) => ({
  emailIdx: uniqueIndex('users_email_idx').on(table.email),
}))

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

// --- Posts ---

export const posts = sqliteTable('posts', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  title: text('title').notNull(),
  content: text('content'),
  status: text('status', { enum: ['draft', 'published', 'archived'] }).notNull().default('draft'),
  authorId: text('author_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  metadata: text('metadata', { mode: 'json' }).$type<Record<string, unknown>>(),
  publishedAt: integer('published_at', { mode: 'timestamp' }),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
}, (table) => ({
  authorIdx: index('posts_author_idx').on(table.authorId),
  statusIdx: index('posts_status_idx').on(table.status),
}))

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

// --- Type Exports ---

export type User = typeof users.$inferSelect
export type NewUser = typeof users.$inferInsert
export type Post = typeof posts.$inferSelect
export type NewPost = typeof posts.$inferInsert

# Column Patterns

Complete reference for every Drizzle ORM column type used with Cloudflare D1. All patterns verified against real D1 projects.

## Imports

```typescript
import { sqliteTable, text, integer, real, blob, index, uniqueIndex } from 'drizzle-orm/sqlite-core'
import { relations, sql } from 'drizzle-orm'
```

## Primary Keys

### Text UUID (preferred)

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

Generates UUIDs at insert time. Works in Workers runtime (crypto.randomUUID is available).

### Integer Autoincrement

```typescript
id: integer('id').primaryKey({ autoIncrement: true }),
```

Use when you need sequential IDs or when the table is insert-heavy and UUID overhead matters.

## Text

### Plain text

```typescript
name: text('name').notNull(),
description: text('description'),  // nullable
```

### Text with enum

```typescript
role: text('role', { enum: ['admin', 'editor', 'viewer'] }).notNull().default('viewer'),
status: text('status', { enum: ['draft', 'published', 'archived'] }).notNull().default('draft'),
```

Stored as TEXT in D1. Drizzle validates at the TypeScript level — no database-level constraint.

## Boolean

D1 has no native BOOLEAN. Use INTEGER with `mode: 'boolean'`:

```typescript
emailVerified: integer('email_verified', { mode: 'boolean' }).notNull().default(false),
enabled: integer('enabled', { mode: 'boolean' }).notNull().default(true),
```

Stored as 0/1 in D1. Drizzle auto-converts to/from `boolean` in TypeScript.

## Timestamps

D1 has no native DATETIME. Use INTEGER with `mode: 'timestamp'`:

```typescript
// Stores as unix epoch seconds, returns as Date object
createdAt: integer('created_at', { mode: 'timestamp' })
  .notNull()
  .$defaultFn(() => new Date()),

updatedAt: integer('updated_at', { mode: 'timestamp' })
  .notNull()
  .$defaultFn(() => new Date()),
```

### Manual unix timestamps (when you don't want Date objects)

```typescript
timestamp: integer('timestamp').notNull().$defaultFn(() => Math.floor(Date.now() / 1000)),
```

## Numbers

### Integer

```typescript
count: integer('count').notNull().default(0),
sortOrder: integer('sort_order'),
```

### Real (float/decimal)

```typescript
price: real('price').notNull(),
latitude: real('latitude'),
longitude: real('longitude'),
```

## JSON

Store as TEXT with `mode: 'json'`. Drizzle handles JSON.stringify/parse automatically.

### Typed JSON (recommended)

```typescript
preferences: text('preferences', { mode: 'json' })
  .$type<{ theme: string; notifications: boolean }>()
  .$defaultFn(() => ({ theme: 'default', notifications: true })),

metadata: text('metadata', { mode: 'json' })
  .$type<Record<string, unknown>>(),

changes: text('changes', { mode: 'json' })
  .$type<Record<string, { old: unknown; new: unknown }>>(),
```

### Untyped JSON (when schema varies)

```typescript
rawData: text('raw_data'),  // manual JSON.stringify/parse
```

Use `{ mode: 'json' }` unless you need to query JSON fields in raw SQL — in that case, use plain `text()` and handle serialisation yourself.

## Foreign Keys

Foreign keys are **always enforced in D1** (cannot disable with PRAGMA).

```typescript
// Inline reference
userId: text('user_id')
  .notNull()
  .references(() => users.id, { onDelete: 'cascade' }),

// With set null
categoryId: text('category_id')
  .references(() => categories.id, { onDelete: 'set null' }),

// Self-referencing
parentId: text('parent_id')
  .references((): AnySQLiteColumn => categories.id),
```

**Cascade options**: `cascade`, `set null`, `restrict`, `no action` (default).

**Migration ordering**: When creating tables with circular FKs, use `PRAGMA defer_foreign_keys = on` at the start of the migration.

## Indexes

Defined in the table function callback (second argument to `sqliteTable`):

```typescript
export const posts = sqliteTable('posts', {
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),
  title: text('title').notNull(),
  authorId: text('author_id').notNull().references(() => users.id),
  status: text('status', { enum: ['draft', 'published'] }).notNull(),
  publishedAt: integer('published_at', { mode: 'timestamp' }),
}, (table) => ({
  // Single column index
  authorIdx: index('posts_author_idx').on(table.authorId),

  // Unique index
  slugIdx: uniqueIndex('posts_slug_idx').on(table.slug),

  // Composite index
  statusDateIdx: index('posts_status_date_idx').on(table.status, table.publishedAt),
}))
```

**Naming convention**: `{table}_{column(s)}_{idx|uniq}`.

## Relations

Drizzle relations are query builder helpers — not database-level constraints. Define alongside FKs.

### One-to-many

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

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

### Many-to-many (via junction table)

```typescript
export const postTags = sqliteTable('post_tags', {
  postId: text('post_id').notNull().references(() => posts.id, { onDelete: 'cascade' }),
  tagId: text('tag_id').notNull().references(() => tags.id, { onDelete: 'cascade' }),
}, (table) => ({
  pk: uniqueIndex('post_tags_pk').on(table.postId, table.tagId),
}))
```

## Type Exports

Always export inferred types for every table:

```typescript
export type User = typeof users.$inferSelect
export type NewUser = typeof users.$inferInsert
export type Post = typeof posts.$inferSelect
export type NewPost = typeof posts.$inferInsert
```

# D1 Specifics

Reference for Cloudflare D1 behaviour that differs from standard SQLite. Load this when troubleshooting D1 issues or when you need to write raw SQL against D1.

## D1 vs Standard SQLite

| Feature | Standard SQLite | D1 |
|---------|-----------------|-----|
| Foreign keys default | OFF | **ON (always enforced)** |
| `PRAGMA foreign_keys` | Can toggle freely | **Blocked** — always on |
| `PRAGMA defer_foreign_keys` | Available | Available (for migration ordering) |
| Other PRAGMAs | Full access | Restricted (table_list, table_info, table_xinfo only) |
| Bound parameters per query | ~999 | **100** |
| Max database size | Filesystem | 10 GB (paid) / 500 MB (free) |
| Max columns per table | Unlimited | **100** |
| Max string/BLOB size | Unlimited | **2 MB** |
| Max SQL statement length | Unlimited | **100 KB** |
| Max queries per Worker invocation | N/A | 1000 (paid) / 50 (free) |
| Max concurrent D1 connections | N/A | **6 per Worker** |
| Max query duration | N/A | **30 seconds** |
| Concurrency model | Multi-writer | **Single-threaded** (Durable Object) |
| BigInt support | Yes | **No** (JS 52-bit limit) |
| Virtual tables (FTS5) | Yes | Yes, but **blocks `wrangler d1 export`** |

## JSON in D1

JSON functions are always available (no extension loading needed).

### Storage

JSON is stored as `TEXT` columns. Drizzle handles serialisation with `{ mode: 'json' }`.

### Extraction Functions

| Function | Returns | Example |
|----------|---------|---------|
| `json_extract(col, '$.path')` | SQL type matching JSON type | `json_extract(data, '$.name')` → `"Alice"` |
| `col -> '$.path'` | JSON representation | `data -> '$.score'` → `42` (as JSON) |
| `col ->> '$.path'` | SQL TEXT | `data ->> '$.score'` → `"42"` (as TEXT) |
| `json_each(value)` | Rows (top-level array) | Expand array into rows |
| `json_tree(value)` | Rows (full nested) | Expand entire structure |

### Type Coercion

| JSON type | D1 type |
|-----------|---------|
| `null` | `NULL` |
| number (integer) | `INTEGER` |
| number (decimal) | `REAL` |
| boolean | `INTEGER` (1 = true, 0 = false) |
| string | `TEXT` |
| object/array | `TEXT` |

### Generated Columns from JSON

D1 supports generated columns — extract JSON fields as indexable columns:

```sql
CREATE TABLE sensor_data (
  raw_data TEXT,
  location AS (json_extract(raw_data, '$.location')) STORED
);
CREATE INDEX idx_location ON sensor_data(location);
```

### JSON Gotcha

`json_extract()` throws `malformed JSON` (error 9015) if the column contains non-JSON text. Guard with `json_valid()`:

```sql
SELECT * FROM events
WHERE json_valid(metadata) AND json_extract(metadata, '$.country') = 'AU'
```

## Query Result Formats

### `.all<T>()` — Array of row objects

```typescript
const { results, success, meta } = await env.DB
  .prepare("SELECT * FROM users WHERE role = ?")
  .bind("admin")
  .all<UserRow>()
// results: UserRow[]
// meta: { duration, rows_read, rows_written, last_row_id, changes, size_after }
```

### `.first()` — Single row or null

```typescript
const row = await env.DB.prepare("SELECT * FROM users WHERE id = ?").bind(id).first()
// row: Record<string, unknown> | null

// With column name — returns scalar:
const count = await env.DB.prepare("SELECT COUNT(*) as count FROM users").first('count')
// count: number | null
```

### `.run()` — Execute mutation (no rows returned)

```typescript
const result = await env.DB
  .prepare("INSERT INTO users (id, name) VALUES (?, ?)")
  .bind(id, name)
  .run()
// result: { success, meta: { changes, last_row_id, ... } }
```

### `.raw()` — Array of arrays (no column names)

```typescript
const rows = await env.DB.prepare("SELECT id, name FROM users").raw()
// rows: [["abc", "Alice"], ["def", "Bob"]]

// With column names:
const rows = await env.DB.prepare("SELECT id, name FROM users").raw({ columnNames: true })
// rows: [["id", "name"], ["abc", "Alice"], ["def", "Bob"]]
```

### `.batch()` — Multiple statements in one transaction

```typescript
const [r1, r2] = await env.DB.batch([
  env.DB.prepare("INSERT INTO users VALUES (?, ?)").bind(id1, name1),
  env.DB.prepare("INSERT INTO users VALUES (?, ?)").bind(id2, name2),
])
// Returns: D1Result[] — one per statement, all in single transaction
```

## Batch Insert Calculation

D1's 100 parameter limit means: `max_rows_per_insert = Math.floor(100 / columns_per_row)`

| Columns | Max rows per INSERT |
|---------|-------------------|
| 5 | 20 |
| 10 | 10 |
| 15 | 6 |
| 20 | 5 |

Symptoms of hitting the limit: silent failure, partial data, or cryptic "Failed to insert" error.

---
name: d1-drizzle-schema
description: "Generate Drizzle ORM schemas for Cloudflare D1 databases with correct D1-specific patterns. Produces schema files, migration commands, type exports, and DATABASE_SCHEMA.md documentation. Handles D1 quirks: foreign keys always enforced, no native BOOLEAN/DATETIME types, 100 bound parameter limit, JSON stored as TEXT. Use when creating a new database, adding tables, or scaffolding a D1 data layer."
compatibility: claude-code-only
---

# D1 Drizzle Schema

Generate correct Drizzle ORM schemas for Cloudflare D1. D1 is SQLite-based but has important differences that cause subtle bugs if you use standard SQLite patterns. This skill produces schemas that work correctly with D1's constraints.

## Critical D1 Differences

| Feature | Standard SQLite | D1 |
|---------|-----------------|-----|
| Foreign keys | OFF by default | **Always ON** (cannot disable) |
| Boolean type | No | No — use `integer({ mode: 'boolean' })` |
| Datetime type | No | No — use `integer({ mode: 'timestamp' })` |
| Max bound params | ~999 | **100** (affects bulk inserts) |
| JSON support | Extension | **Always available** (json_extract, ->, ->>) |
| Concurrency | Multi-writer | **Single-threaded** (one query at a time) |

## Workflow

### Step 1: Describe the Data Model

Gather requirements: what tables, what relationships, what needs indexing. If working from an existing description, infer the schema directly.

### Step 2: Generate Drizzle Schema

Create schema files using D1-correct column patterns:

```typescript
import { sqliteTable, text, integer, real, index, uniqueIndex } from 'drizzle-orm/sqlite-core'

export const users = sqliteTable('users', {
  // UUID primary key (preferred for D1)
  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),

  // Text fields
  name: text('name').notNull(),
  email: text('email').notNull(),

  // Enum (stored as TEXT, validated at schema level)
  role: text('role', { enum: ['admin', 'editor', 'viewer'] }).notNull().default('viewer'),

  // Boolean (D1 has no BOOL — stored as INTEGER 0/1)
  emailVerified: integer('email_verified', { mode: 'boolean' }).notNull().default(false),

  // Timestamp (D1 has no DATETIME — stored as unix seconds)
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),
  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),

  // Typed JSON (stored as TEXT, Drizzle auto-serialises)
  preferences: text('preferences', { mode: 'json' }).$type<UserPreferences>(),

  // Foreign key (always enforced in D1)
  organisationId: text('organisation_id').references(() => organisations.id, { onDelete: 'cascade' }),
}, (table) => ({
  emailIdx: uniqueIndex('users_email_idx').on(table.email),
  orgIdx: index('users_org_idx').on(table.organisationId),
}))
```

See [references/column-patterns.md](references/column-patterns.md) for the full type reference.

### Step 3: Add Relations

Drizzle relations are query builder helpers (separate from FK constraints):

```typescript
import { relations } from 'drizzle-orm'

export const usersRelations = relations(users, ({ one, many }) => ({
  organisation: one(organisations, {
    fields: [users.organisationId],
    references: [organisations.id],
  }),
  posts: many(posts),
}))
```

### Step 4: Export Types

```typescript
export type User = typeof users.$inferSelect
export type NewUser = typeof users.$inferInsert
```

### Step 5: Set Up Drizzle Config

Copy [assets/drizzle-config-template.ts](assets/drizzle-config-template.ts) to `drizzle.config.ts` and update the schema path.

### Step 6: Add Migration Scripts

Add to `package.json`:
```json
{
  "db:generate": "drizzle-kit generate",
  "db:migrate:local": "wrangler d1 migrations apply DB --local",
  "db:migrate:remote": "wrangler d1 migrations apply DB --remote"
}
```

**Always run on BOTH local AND remote before testing.**

### Step 7: Generate DATABASE_SCHEMA.md

Document the schema for future sessions:
- Tables with columns, types, and constraints
- Relationships and foreign keys
- Indexes and their purpose
- Migration workflow

## Bulk Insert Pattern

D1 limits bound parameters to 100. Calculate batch size:

```typescript
const BATCH_SIZE = Math.floor(100 / COLUMNS_PER_ROW)
for (let i = 0; i < rows.length; i += BATCH_SIZE) {
  await db.insert(table).values(rows.slice(i, i + BATCH_SIZE))
}
```

## D1 Runtime Usage

```typescript
import { drizzle } from 'drizzle-orm/d1'
import * as schema from './schema'

// In Worker fetch handler:
const db = drizzle(env.DB, { schema })

// Query patterns
const all = await db.select().from(schema.users).all()           // Array<User>
const one = await db.select().from(schema.users).where(eq(schema.users.id, id)).get()  // User | undefined
const count = await db.select({ count: sql`count(*)` }).from(schema.users).get()
```

## Reference Files

| When | Read |
|------|------|
| D1 vs SQLite, JSON queries, limits | [references/d1-specifics.md](references/d1-specifics.md) |
| Column type patterns for Drizzle + D1 | [references/column-patterns.md](references/column-patterns.md) |

## Assets

| File | Purpose |
|------|---------|
| [assets/drizzle-config-template.ts](assets/drizzle-config-template.ts) | Starter drizzle.config.ts for D1 |
| [assets/schema-template.ts](assets/schema-template.ts) | Example schema with all common D1 patterns |