cloudflare-d1

Cloudflare D1 Skill

Load with: base.md + typescript.md

Cloudflare D1 is a serverless SQLite database designed for Cloudflare Workers with global distribution and zero cold starts.

Sources: D1 Docs | Drizzle + D1 | Wrangler CLI

---

Core Principle

SQLite at the edge, migrations in version control, Drizzle for type safety.

D1 brings SQLite's simplicity to serverless. Design for horizontal scale (multiple small databases) rather than vertical (one large database). Use Drizzle ORM for type-safe queries and migrations.

---

D1 Stack

Component	Purpose
D1	Serverless SQLite database
Workers	Edge runtime for your application
Wrangler	CLI for development and deployment
Drizzle ORM	Type-safe ORM with migrations
Drizzle Kit	Migration tooling
Hono	Lightweight web framework (optional)

---

Project Setup

Create Worker Project

# Create new project
npm create cloudflare@latest my-app -- --template "worker-typescript"
cd my-app

# Install dependencies
npm install drizzle-orm
npm install -D drizzle-kit

Create D1 Database

# Create database (creates both local and remote)
npx wrangler d1 create my-database

# Output:
# [[d1_databases]]
# binding = "DB"
# database_name = "my-database"
# database_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"

Configure wrangler.toml

name = "my-app"
main = "src/index.ts"
compatibility_date = "2024-01-01"

[[d1_databases]]
binding = "DB"
database_name = "my-database"
database_id = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
migrations_dir = "drizzle"
migrations_table = "drizzle_migrations"

Generate TypeScript Types

# Generate env types from wrangler.toml
npx wrangler types

# Creates worker-configuration.d.ts:
# interface Env {
#   DB: D1Database;
# }

---

Drizzle ORM Setup

Schema Definition

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

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  role: text('role', { enum: ['user', 'admin'] }).default('user'),
  createdAt: text('created_at').default(sql`CURRENT_TIMESTAMP`),
  updatedAt: text('updated_at').default(sql`CURRENT_TIMESTAMP`)
});

export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
  content: text('content'),
  authorId: integer('author_id').references(() => users.id),
  published: integer('published', { mode: 'boolean' }).default(false),
  viewCount: integer('view_count').default(0),
  createdAt: text('created_at').default(sql`CURRENT_TIMESTAMP`)
});

export const tags = sqliteTable('tags', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull().unique()
});

export const postTags = sqliteTable('post_tags', {
  postId: integer('post_id').references(() => posts.id),
  tagId: integer('tag_id').references(() => tags.id)
});

// Type exports
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Post = typeof posts.$inferSelect;
export type NewPost = typeof posts.$inferInsert;

Drizzle Config

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

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

Database Client

// src/db/index.ts
import { drizzle } from 'drizzle-orm/d1';
import * as schema from './schema';

export function createDb(d1: D1Database) {
  return drizzle(d1, { schema });
}

export type Database = ReturnType<typeof createDb>;
export * from './schema';

---

Migration Workflow

Generate Migration

# Generate migration from schema changes
npx drizzle-kit generate

# Output: drizzle/0000_initial.sql

Apply Migrations Locally

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

# Or via Drizzle
npx drizzle-kit migrate

Apply Migrations to Production

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

# Preview first (dry run)
npx wrangler d1 migrations apply my-database --remote --dry-run

Migration File Example

-- drizzle/0000_initial.sql
CREATE TABLE `users` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `email` text NOT NULL,
  `name` text NOT NULL,
  `role` text DEFAULT 'user',
  `created_at` text DEFAULT CURRENT_TIMESTAMP,
  `updated_at` text DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX `users_email_unique` ON `users` (`email`);

CREATE TABLE `posts` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `title` text NOT NULL,
  `content` text,
  `author_id` integer REFERENCES `users`(`id`),
  `published` integer DEFAULT false,
  `view_count` integer DEFAULT 0,
  `created_at` text DEFAULT CURRENT_TIMESTAMP
);

---

Worker Implementation

Basic Worker with Hono

// src/index.ts
import { Hono } from 'hono';
import { createDb, users, posts } from './db';
import { eq, desc } from 'drizzle-orm';

type Bindings = {
  DB: D1Database;
};

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

// Middleware to inject db
app.use('*', async (c, next) => {
  c.set('db', createDb(c.env.DB));
  await next();
});

// List users
app.get('/users', async (c) => {
  const db = c.get('db');
  const allUsers = await db.select().from(users);
  return c.json(allUsers);
});

// Get user by ID
app.get('/users/:id', async (c) => {
  const db = c.get('db');
  const id = parseInt(c.req.param('id'));

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

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

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

  const result = await db.insert(users).values({
    email: body.email,
    name: body.name
  }).returning();

  return c.json(result[0], 201);
});

// Update user
app.put('/users/:id', async (c) => {
  const db = c.get('db');
  const id = parseInt(c.req.param('id'));
  const body = await c.req.json<Partial<{ email: string; name: string }>>();

  const result = await db.update(users)
    .set({ ...body, updatedAt: new Date().toISOString() })
    .where(eq(users.id, id))
    .returning();

  if (result.length === 0) {
    return c.json({ error: 'User not found' }, 404);
  }
  return c.json(result[0]);
});

// Delete user
app.delete('/users/:id', async (c) => {
  const db = c.get('db');
  const id = parseInt(c.req.param('id'));

  const result = await db.delete(users).where(eq(users.id, id)).returning();

  if (result.length === 0) {
    return c.json({ error: 'User not found' }, 404);
  }
  return c.json({ deleted: true });
});

export default app;

Raw D1 API (Without ORM)

// src/index.ts
export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const url = new URL(request.url);

    if (url.pathname === '/users' && request.method === 'GET') {
      const { results } = await env.DB.prepare(
        'SELECT * FROM users ORDER BY created_at DESC'
      ).all();
      return Response.json(results);
    }

    if (url.pathname === '/users' && request.method === 'POST') {
      const body = await request.json() as { email: string; name: string };

      const result = await env.DB.prepare(
        'INSERT INTO users (email, name) VALUES (?, ?) RETURNING *'
      ).bind(body.email, body.name).first();

      return Response.json(result, { status: 201 });
    }

    return new Response('Not Found', { status: 404 });
  }
};

---

Query Patterns

Select Queries

import { eq, and, or, like, gt, desc, asc, count, sql } from 'drizzle-orm';

// Basic select
const allPosts = await db.select().from(posts);

// Select specific columns
const titles = await db.select({ id: posts.id, title: posts.title }).from(posts);

// Where clause
const published = await db.select().from(posts).where(eq(posts.published, true));

// Multiple conditions
const recentPublished = await db.select().from(posts).where(
  and(
    eq(posts.published, true),
    gt(posts.createdAt, '2024-01-01')
  )
);

// OR conditions
const featured = await db.select().from(posts).where(
  or(
    eq(posts.viewCount, 1000),
    like(posts.title, '%featured%')
  )
);

// Order and limit
const topPosts = await db.select()
  .from(posts)
  .orderBy(desc(posts.viewCount))
  .limit(10);

// Pagination
const page2 = await db.select()
  .from(posts)
  .orderBy(desc(posts.createdAt))
  .limit(10)
  .offset(10);

// Count
const postCount = await db.select({ count: count() }).from(posts);

Joins

// Inner join
const postsWithAuthors = await db.select({
  post: posts,
  author: users
})
.from(posts)
.innerJoin(users, eq(posts.authorId, users.id));

// Left join
const allPostsWithAuthors = await db.select()
  .from(posts)
  .leftJoin(users, eq(posts.authorId, users.id));

// Many-to-many via junction table
const postsWithTags = await db.select({
  post: posts,
  tag: tags
})
.from(posts)
.leftJoin(postTags, eq(posts.id, postTags.postId))
.leftJoin(tags, eq(postTags.tagId, tags.id));

Insert, Update, Delete

// Insert single
const newUser = await db.insert(users).values({
  email: '[email protected]',
  name: 'John Doe'
}).returning();

// Insert mul

---

*Content truncated.*