ADR 010: Drizzle ORM for D1 Database Access

Context

All Cloudflare D1 database access in the platform currently uses raw SQL via env.DB.prepare(). While functional, this approach has significant drawbacks:

• No type safety: Query results are untyped (unknown) unless manually cast, making it easy to introduce runtime errors when column names change or new columns are added.
• No shared schema: Each Worker duplicates CREATE TABLE statements and query logic. Schema changes require manually updating every file that references the affected table.
• Manual migrations: Database migrations are raw .sql files applied via wrangler d1 execute. There is no schema diffing, no migration generation, and no way to verify that code matches the current schema.
• Fragile query building: Dynamic queries (e.g., optional filters in getDeploymentHistory) resort to string concatenation, which is error-prone and hard to review.

The platform needs a lightweight ORM that works natively with Cloudflare D1, provides TypeScript type inference from schema definitions, and supports the D1 batch transaction API for atomicity guarantees.

Decision

Adopt Drizzle ORM (drizzle-orm@^0.45.1) with drizzle-kit (drizzle-kit@^0.31.9) for all D1 database access. Create a shared @a2r/db-schema monorepo package that contains all Drizzle schema definitions, and consume it from every Worker that accesses D1.

Schema Definition

All tables are defined in the shared @a2r/db-schema package using Drizzle's sqliteTable helper. This single source of truth replaces all raw CREATE TABLE SQL scattered across documentation and migration files.

// packages/db-schema/src/schema.ts

import { sqliteTable, text, integer, index, uniqueIndex } from "drizzle-orm/sqlite-core";
import { sql } from "drizzle-orm";

// ── version_configs ──────────────────────────────────────────────
export const versionConfigs = sqliteTable(
  "version_configs",
  {
    id: integer("id").primaryKey({ autoIncrement: true }),
    environment: text("environment").notNull(), // 'dev' | 'staging' | 'production'
    mfeName: text("mfe_name").notNull(),
    version: text("version").notNull(),
    entryUrl: text("entry_url").notNull(),
    integrityHash: text("integrity_hash"),
    isActive: integer("is_active", { mode: "boolean" }).default(false),
    activatedAt: text("activated_at"),
    activatedBy: text("activated_by"),
    createdAt: text("created_at").default(sql`(CURRENT_TIMESTAMP)`),
    createdBy: text("created_by").notNull(),
  },
  (table) => [
    uniqueIndex("uq_version_configs_env_mfe_version").on(
      table.environment,
      table.mfeName,
      table.version
    ),
    index("idx_version_configs_active")
      .on(table.environment, table.mfeName, table.isActive),
    index("idx_version_configs_lookup")
      .on(table.environment, table.mfeName, table.version),
    index("idx_version_configs_history")
      .on(table.environment, table.mfeName, table.createdAt),
  ]
);

// ── deployment_events ────────────────────────────────────────────
export const deploymentEvents = sqliteTable(
  "deployment_events",
  {
    id: integer("id").primaryKey({ autoIncrement: true }),
    environment: text("environment").notNull(),
    mfeName: text("mfe_name").notNull(),
    version: text("version").notNull(),
    eventType: text("event_type").notNull(), // 'registered' | 'activated' | 'deactivated' | 'rollback'
    metadata: text("metadata"),              // JSON blob
    createdAt: text("created_at").default(sql`(CURRENT_TIMESTAMP)`),
    createdBy: text("created_by").notNull(),
  },
  (table) => [
    index("idx_deployment_events_lookup")
      .on(table.environment, table.mfeName, table.createdAt),
    index("idx_deployment_events_type")
      .on(table.eventType, table.createdAt),
  ]
);

// ── version_changes (03-cloudflare-infrastructure audit trail) ───
export const versionChanges = sqliteTable(
  "version_changes",
  {
    id: integer("id").primaryKey({ autoIncrement: true }),
    mfeName: text("mfe_name").notNull(),
    version: text("version").notNull(),
    previousVersion: text("previous_version"),
    manifestUrl: text("manifest_url").notNull(),
    environment: text("environment").notNull(),
    changedBy: text("changed_by").notNull(),
    reason: text("reason").notNull().default("Manual update"),
    timestamp: text("timestamp").notNull().default(sql`(datetime('now'))`),
  },
  (table) => [
    uniqueIndex("uq_version_changes_mfe_ver_env_ts").on(
      table.mfeName,
      table.version,
      table.environment,
      table.timestamp
    ),
    index("idx_version_changes_mfe_env")
      .on(table.mfeName, table.environment, table.timestamp),
    index("idx_version_changes_env_timestamp")
      .on(table.environment, table.timestamp),
    index("idx_version_changes_changed_by")
      .on(table.changedBy, table.timestamp),
  ]
);

Query Patterns — Before and After

Duplicate check (raw SQL):

const existing = await env.DB.prepare(
  'SELECT id FROM version_configs WHERE environment = ? AND mfe_name = ? AND version = ?'
)
  .bind(body.environment, body.mfeName, body.version)
  .first();

Duplicate check (Drizzle):

import { eq, and } from "drizzle-orm";
import { versionConfigs } from "@a2r/db-schema";

const existing = await db
  .select({ id: versionConfigs.id })
  .from(versionConfigs)
  .where(
    and(
      eq(versionConfigs.environment, body.environment),
      eq(versionConfigs.mfeName, body.mfeName),
      eq(versionConfigs.version, body.version)
    )
  )
  .get();

Insert (raw SQL):

await env.DB.prepare(
  `INSERT INTO version_configs (environment, mfe_name, version, entry_url, integrity_hash, created_by)
   VALUES (?, ?, ?, ?, ?, ?)`
)
  .bind(body.environment, body.mfeName, body.version, body.entryUrl, body.integrityHash ?? null, body.createdBy)
  .run();

Insert (Drizzle):

const result = await db.insert(versionConfigs).values({
  environment: body.environment,
  mfeName: body.mfeName,
  version: body.version,
  entryUrl: body.entryUrl,
  integrityHash: body.integrityHash ?? null,
  createdBy: body.createdBy,
});

D1 batch transaction (raw SQL):

const stmtDeactivate = env.DB.prepare(
  `UPDATE version_configs SET is_active = false WHERE environment = ? AND mfe_name = ? AND is_active = true`
).bind(body.environment, body.mfeName);

const stmtActivate = env.DB.prepare(
  `UPDATE version_configs SET is_active = true, activated_at = datetime('now'), activated_by = ? WHERE id = ?`
).bind(body.activatedBy, targetVersion.id);

await env.DB.batch([stmtDeactivate, stmtActivate, stmtEvent]);

D1 batch transaction (Drizzle):

import { eq, and } from "drizzle-orm";
import { versionConfigs, deploymentEvents } from "@a2r/db-schema";

await db.batch([
  db.update(versionConfigs)
    .set({ isActive: false })
    .where(
      and(
        eq(versionConfigs.environment, body.environment),
        eq(versionConfigs.mfeName, body.mfeName),
        eq(versionConfigs.isActive, true)
      )
    ),
  db.update(versionConfigs)
    .set({
      isActive: true,
      activatedAt: sql`datetime('now')`,
      activatedBy: body.activatedBy,
    })
    .where(eq(versionConfigs.id, targetVersion.id)),
  db.insert(deploymentEvents).values({
    environment: body.environment,
    mfeName: body.mfeName,
    version: body.version,
    eventType: eventType,
    metadata: JSON.stringify({ previousVersion: targetVersion.version }),
    createdBy: body.activatedBy,
  }),
]);

drizzle-kit Configuration

// drizzle.config.ts (at monorepo root or in each Worker)
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./node_modules/@a2r/db-schema/src/schema.ts",
  out: "./migrations",
  dialect: "sqlite",
  driver: "d1-http",
});

Migration Workflow

D1 Batch Transactions

Drizzle preserves D1's batch() API for atomic multi-statement operations. The db.batch([...]) method maps directly to D1's env.DB.batch(), executing all statements in a single transaction. If any statement fails, the entire batch is rolled back.

Consequences

Positive

• Type-safe queries: Column names, types, and relationships are inferred from the schema. TypeScript catches mismatches at compile time.
• Single source of truth: The @a2r/db-schema package defines all tables once. Workers import schema objects instead of duplicating SQL strings.
• Automated migrations: drizzle-kit generate diffs the TypeScript schema against the last migration and generates incremental SQL files. No more hand-writing migration scripts.
• Lightweight runtime: Drizzle has no heavy runtime dependencies. The drizzle-orm/d1 driver wraps the native D1 binding with zero overhead beyond query building.
• D1-native: Drizzle supports D1's batch transaction API (db.batch()), D1 Time Travel, and the D1 HTTP API for remote development.
• Drizzle Studio: Provides a visual database browser for local development, removing the need for manual wrangler d1 execute inspection commands.

Negative

• New dependency: Every Worker that accesses D1 now depends on drizzle-orm and @a2r/db-schema. This increases the dependency surface and bundle size slightly.
• Codegen step: Schema changes require running drizzle-kit generate before committing. CI enforces this with drizzle-kit generate --check, but it adds a step to the development workflow.
• Learning curve: Developers familiar with raw SQL need to learn Drizzle's query builder API. However, Drizzle's API closely mirrors SQL syntax, minimizing the learning gap.

Alternatives Considered

Raw SQL (current approach)

The current approach of using env.DB.prepare() with raw SQL strings. Zero dependencies and full control, but no type safety, no shared schema, manual migrations, and fragile dynamic query construction.

Kysely

A type-safe SQL query builder for TypeScript. Excellent type inference, but no native Cloudflare D1 adapter. Would require a custom driver wrapper and does not provide schema definition or migration tooling comparable to drizzle-kit.

Prisma

Feature-rich ORM with schema-first design and powerful migration system. However, Prisma has no native D1 support (requires the Prisma Accelerate proxy or experimental D1 adapter), includes a heavy Rust-based query engine that adds significant bundle size, and its runtime model is not optimized for the Cloudflare Workers execution environment (V8 isolates with strict CPU time limits).

Related Documentation

• Architecture Overview — Technology stack and system context
• Cloudflare Infrastructure — D1 schema, Version Config Service, common query patterns
• Local Development — drizzle-kit commands for local database management
• Repository Strategy — @a2r/db-schema shared package in the monorepo
• Version Management — Version registration, activation, and promotion queries
• CI/CD & Deployment — Migration pipeline and schema verification