D1 Drizzle Schema

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

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:

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.

Step 3: Add Relations

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

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

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

Step 5: Set Up Drizzle Config

Copy assets/drizzle-config-template.ts to drizzle.config.ts and update the schema path.

Step 6: Add Migration Scripts

Add to package.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:

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

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

Assets

Column Patterns

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

Imports

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

Primary Keys

Text UUID (preferred)

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

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

Integer Autoincrement

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

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

Text with enum

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':

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':

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

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

Numbers

Integer

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

Real (float/decimal)

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)

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)

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

// 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):

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

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)

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:

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

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

Type Coercion

Generated Columns from JSON

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

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

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

Query Result Formats

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

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

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)

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)

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

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)

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