d1-drizzle-schema

$2a

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

When

Read

D1 vs SQLite, JSON queries, limits

references/d1-specifics.md

Column type patterns for 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