{"id":"d1-drizzle-schema","name":"d1-drizzle-schema","summary":"Cloudflare D1データベース向けに、正しいD1特有パターンを持つDrizzle ORMスキーマを生成してください。","body":"# D1 Drizzle Schema\n\nGenerate 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.\n\n## Critical D1 Differences\n\n| Feature | Standard SQLite | D1 |\n|---------|-----------------|-----|\n| Foreign keys | OFF by default | **Always ON** (cannot disable) |\n| Boolean type | No | No — use `integer({ mode: 'boolean' })` |\n| Datetime type | No | No — use `integer({ mode: 'timestamp' })` |\n| Max bound params | ~999 | **100** (affects bulk inserts) |\n| JSON support | Extension | **Always available** (json_extract, ->, ->>) |\n| Concurrency | Multi-writer | **Single-threaded** (one query at a time) |\n\n## Workflow\n\n### Step 1: Describe the Data Model\n\nGather requirements: what tables, what relationships, what needs indexing. If working from an existing description, infer the schema directly.\n\n### Step 2: Generate Drizzle Schema\n\nCreate schema files using D1-correct column patterns:\n\n```typescript\nimport { sqliteTable, text, integer, real, index, uniqueIndex } from 'drizzle-orm/sqlite-core'\n\nexport const users = sqliteTable('users', {\n  // UUID primary key (preferred for D1)\n  id: text('id').primaryKey().$defaultFn(() => crypto.randomUUID()),\n\n  // Text fields\n  name: text('name').notNull(),\n  email: text('email').notNull(),\n\n  // Enum (stored as TEXT, validated at schema level)\n  role: text('role', { enum: ['admin', 'editor', 'viewer'] }).notNull().default('viewer'),\n\n  // Boolean (D1 has no BOOL — stored as INTEGER 0/1)\n  emailVerified: integer('email_verified', { mode: 'boolean' }).notNull().default(false),\n\n  // Timestamp (D1 has no DATETIME — stored as unix seconds)\n  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),\n  updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),\n\n  // Typed JSON (stored as TEXT, Drizzle auto-serialises)\n  preferences: text('preferences', { mode: 'json' }).$type<UserPreferences>(),\n\n  // Foreign key (always enforced in D1)\n  organisationId: text('organisation_id').references(() => organisations.id, { onDelete: 'cascade' }),\n}, (table) => ({\n  emailIdx: uniqueIndex('users_email_idx').on(table.email),\n  orgIdx: index('users_org_idx').on(table.organisationId),\n}))\n```\n\nSee [references/column-patterns.md](references/column-patterns.md) for the full type reference.\n\n### Step 3: Add Relations\n\nDrizzle relations are query builder helpers (separate from FK constraints):\n\n```typescript\nimport { relations } from 'drizzle-orm'\n\nexport const usersRelations = relations(users, ({ one, many }) => ({\n  organisation: one(organisations, {\n    fields: [users.organisationId],\n    references: [organisations.id],\n  }),\n  posts: many(posts),\n}))\n```\n\n### Step 4: Export Types\n\n```typescript\nexport type User = typeof users.$inferSelect\nexport type NewUser = typeof users.$inferInsert\n```\n\n### Step 5: Set Up Drizzle Config\n\nCopy [assets/drizzle-config-template.ts](assets/drizzle-config-template.ts) to `drizzle.config.ts` and update the schema path.\n\n### Step 6: Add Migration Scripts\n\nAdd to `package.json`:\n```json\n{\n  \"db:generate\": \"drizzle-kit generate\",\n  \"db:migrate:local\": \"wrangler d1 migrations apply DB --local\",\n  \"db:migrate:remote\": \"wrangler d1 migrations apply DB --remote\"\n}\n```\n\n**Always run on BOTH local AND remote before testing.**\n\n### Step 7: Generate DATABASE_SCHEMA.md\n\nDocument the schema for future sessions:\n- Tables with columns, types, and constraints\n- Relationships and foreign keys\n- Indexes and their purpose\n- Migration workflow\n\n## Bulk Insert Pattern\n\nD1 limits bound parameters to 100. Calculate batch size:\n\n```typescript\nconst BATCH_SIZE = Math.floor(100 / COLUMNS_PER_ROW)\nfor (let i = 0; i < rows.length; i += BATCH_SIZE) {\n  await db.insert(table).values(rows.slice(i, i + BATCH_SIZE))\n}\n```\n\n## D1 Runtime Usage\n\n```typescript\nimport { drizzle } from 'drizzle-orm/d1'\nimport * as schema from './schema'\n\n// In Worker fetch handler:\nconst db = drizzle(env.DB, { schema })\n\n// Query patterns\nconst all = await db.select().from(schema.users).all()           // Array<User>\nconst one = await db.select().from(schema.users).where(eq(schema.users.id, id)).get()  // User | undefined\nconst count = await db.select({ count: sql`count(*)` }).from(schema.users).get()\n```\n\n## Reference Files\n\n| When | Read |\n|------|------|\n| D1 vs SQLite, JSON queries, limits | [references/d1-specifics.md](references/d1-specifics.md) |\n| Column type patterns for Drizzle + D1 | [references/column-patterns.md](references/column-patterns.md) |\n\n## Assets\n\n| File | Purpose |\n|------|---------|\n| [assets/drizzle-config-template.ts](assets/drizzle-config-template.ts) | Starter drizzle.config.ts for D1 |\n| [assets/schema-template.ts](assets/schema-template.ts) | Example schema with all common D1 patterns |","author":"@jezweb","ownerProfile":null,"authorContacts":null,"sourceUrl":"https://github.com/jezweb/claude-skills/tree/main/plugins/cloudflare/skills/d1-drizzle-schema","license":"MIT","category":"coding","lang":"en","tokens":1240,"stars":0,"calls30d":0,"claimed":false,"visibility":"public","origin":"crawler","version":"0.1.0","createdAt":"2026-08-22","updatedAt":"2026-08-22","files":[{"path":"assets/drizzle-config-template.ts","size":388,"sha256":"bb4731cad7932bb968c6de8dbc434e9d591cf52bc52cf84f3a0b24464e601406"},{"path":"assets/schema-template.ts","size":2310,"sha256":"c03cb2cca2ff4e232c973c34d553931c1aa12e2b83e0f6b52ef0d85498301012"},{"path":"references/column-patterns.md","size":5564,"sha256":"2d89847ba3c4dd0fcc1846a869c11b766e2a0d5e8e0553b9f26673183b7e037e"},{"path":"references/d1-specifics.md","size":4605,"sha256":"07abb6058ba3ff65820a2ac01051ae90cae0088b717368df663652765ff549da"}],"requires":{"mcp":[],"tools":[]},"safety":{"flags":[],"scannedAt":"2026-08-22","hasScripts":true,"networkEndpoints":[]}}