d1-drizzle-schema

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

D1 Drizzle Schema

D1 Drizzle 模式

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.

为Cloudflare D1生成正确的Drizzle ORM模式。D1基于SQLite，但存在一些重要差异，如果使用标准SQLite模式会导致隐蔽的bug。本技能可生成符合D1约束条件的正确模式。

Critical D1 Differences

D1的关键差异

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)

特性	标准SQLite	D1
外键	默认关闭	始终开启（无法禁用）
布尔类型	无	无 — 使用 `integer({ mode: 'boolean' })`
日期时间类型	无	无 — 使用 `integer({ mode: 'timestamp' })`
最大绑定参数数量	~999	100（影响批量插入）
JSON支持	需要扩展	始终可用（支持json_extract、->、->>）
并发处理	多写入者	单线程（一次仅处理一个查询）

Workflow

工作流程

Step 1: Describe the Data Model

步骤1：描述数据模型

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

收集需求：需要哪些表、什么关系、哪些需要索引。如果已有现成的描述，可直接推导模式。

Step 2: Generate Drizzle Schema

步骤2：生成Drizzle模式

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 for the full type reference.

使用符合D1规范的列模式创建模式文件：

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),
}))

完整的类型参考请查看references/column-patterns.md。

Step 3: Add Relations

步骤3：添加关联关系

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),
}))

Drizzle关联关系是查询构建器的辅助工具（与外键约束分离）：

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

步骤4：导出类型

typescript

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

typescript

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

Step 5: Set Up Drizzle Config

步骤5：配置Drizzle

Copy assets/drizzle-config-template.ts to

drizzle.config.ts

and update the schema path.

将assets/drizzle-config-template.ts复制到

drizzle.config.ts

并更新模式路径。

Step 6: Add Migration Scripts

步骤6：添加迁移脚本

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.

添加到

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"
}

测试前务必在本地和远程环境都执行。

Step 7: Generate DATABASE_SCHEMA.md

步骤7：生成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限制绑定参数数量为100个。计算批量大小：

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

D1 运行时使用方法

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()

typescript

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

// 在Worker的fetch处理函数中：
const db = drizzle(env.DB, { schema })

// 查询模式
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
Column type patterns for Drizzle + D1	references/column-patterns.md

场景	阅读文件
D1与SQLite的差异、JSON查询、限制	references/d1-specifics.md
Drizzle + D1的列类型模式	references/column-patterns.md

Assets

资源文件

File	Purpose
assets/drizzle-config-template.ts	Starter drizzle.config.ts for D1
assets/schema-template.ts	Example schema with all common D1 patterns

文件	用途
assets/drizzle-config-template.ts	适用于D1的drizzle.config.ts模板
assets/schema-template.ts	包含所有常见D1模式的示例模式