Cloudflare D1 + Drizzle ORM: SQLite at the Edge Without the Pain

npm create cloudflare@latest my-d1-app -- --type hello-world
cd my-d1-app
npm install drizzle-orm
npm install -D drizzle-kit wrangler

# wrangler.toml
name = "my-d1-app"
main = "src/index.ts"
compatibility_date = "2025-04-01"

[[d1_databases]]
binding = "DB"
database_name = "my-database"
database_id = "your-database-id-here"

# Create the database
npx wrangler d1 create my-database
# Copy the database_id from output into wrangler.toml

// src/db/schema.ts
import { sqliteTable, text, integer, index } from 'drizzle-orm/sqlite-core';
import { sql } from 'drizzle-orm';

export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  plan: text('plan', { enum: ['free', 'pro', 'enterprise'] }).notNull().default('free'),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
}, (table) => ({
  emailIdx: index('users_email_idx').on(table.email),
  planIdx: index('users_plan_idx').on(table.plan),
}));

export const posts = sqliteTable('posts', {
  id: text('id').primaryKey(),
  userId: text('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  title: text('title').notNull(),
  slug: text('slug').notNull().unique(),
  content: text('content').notNull(),
  publishedAt: integer('published_at', { mode: 'timestamp' }),
  createdAt: integer('created_at', { mode: 'timestamp' }).notNull().default(sql`(unixepoch())`),
}, (table) => ({
  userIdIdx: index('posts_user_id_idx').on(table.userId),
  slugIdx: index('posts_slug_idx').on(table.slug),
}));

export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Post = typeof posts.$inferSelect;

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './migrations',
  dialect: 'sqlite',
  driver: 'd1-http',
  dbCredentials: {
    accountId: process.env.CLOUDFLARE_ACCOUNT_ID!,
    databaseId: process.env.CLOUDFLARE_DATABASE_ID!,
    token: process.env.CLOUDFLARE_D1_TOKEN!,
  },
});

# Generate migration SQL from schema
npx drizzle-kit generate

# Apply to local D1 (for development)
npx wrangler d1 migrations apply my-database --local

# Apply to production D1
npx wrangler d1 migrations apply my-database --remote

// src/index.ts
import { Hono } from 'hono';
import { drizzle } from 'drizzle-orm/d1';
import { eq, desc, and, isNotNull } from 'drizzle-orm';
import * as schema from './db/schema';

type Env = {
  DB: D1Database;
};

const app = new Hono<{ Bindings: Env }>();

// Initialize Drizzle — one line, no connection pool needed
const getDb = (env: Env) => drizzle(env.DB, { schema });

// Get user by email
app.get('/users/:email', async (c) => {
  const db = getDb(c.env);
  const email = c.req.param('email');

  const user = await db
    .select()
    .from(schema.users)
    .where(eq(schema.users.email, email))
    .get();

  if (!user) return c.json({ error: 'User not found' }, 404);
  return c.json(user);
});

// Get published posts with author
app.get('/posts', async (c) => {
  const db = getDb(c.env);

  const posts = await db
    .select({
      id: schema.posts.id,
      title: schema.posts.title,
      slug: schema.posts.slug,
      publishedAt: schema.posts.publishedAt,
      author: {
        id: schema.users.id,
        name: schema.users.name,
      }
    })
    .from(schema.posts)
    .innerJoin(schema.users, eq(schema.posts.userId, schema.users.id))
    .where(isNotNull(schema.posts.publishedAt))
    .orderBy(desc(schema.posts.publishedAt))
    .limit(20)
    .all();

  return c.json(posts);
});

// Create user
app.post('/users', async (c) => {
  const db = getDb(c.env);
  const body = await c.req.json<{ email: string; name: string }>();

  const newUser: schema.NewUser = {
    id: crypto.randomUUID(),
    email: body.email,
    name: body.name,
    plan: 'free',
  };

  const user = await db.insert(schema.users).values(newUser).returning().get();
  return c.json(user, 201);
});

// Batch operations (D1 native)
app.post('/batch', async (c) => {
  const db = getDb(c.env);

  // D1 batch: multiple statements in one round trip
  const userId = crypto.randomUUID();
  const postId = crypto.randomUUID();

  await db.batch([
    db.insert(schema.users).values({
      id: userId,
      email: '[email protected]',
      name: 'Batch User',
      plan: 'pro',
    }),
    db.insert(schema.posts).values({
      id: postId,
      userId,
      title: 'First Post',
      slug: 'first-post',
      content: 'Hello from the edge!',
    })
  ]);

  return c.json({ userId, postId });
});

export default app;

# Start local dev server with local D1
npx wrangler dev --local

# The local D1 persists between restarts
# Inspect it directly:
npx wrangler d1 execute my-database --local --command "SELECT * FROM users"

// Force read from primary (not replica)
// D1 automatically routes writes to primary and reads to nearest replica
// For consistency after write, use the Durable Objects pattern or add a short cache TTL

// Pattern: write → return the written data from the insert
const newPost = await db
  .insert(schema.posts)
  .values({ ...postData })
  .returning()  // Returns the inserted row from primary
  .get();

// Safe to use newPost immediately — came from the same write