# Create a D1 database
npx wrangler d1 create my-database
 
# Add to wrangler.toml

# wrangler.toml
[[d1_databases]]
binding = "DB"
database_name = "my-database"
database_id = "xxxx-xxxx-xxxx"

# Create migration
npx wrangler d1 migrations create my-database init

-- migrations/0001_init.sql
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TEXT DEFAULT (datetime('now'))
);
 
CREATE TABLE posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  author_id INTEGER NOT NULL REFERENCES users(id),
  published BOOLEAN DEFAULT FALSE,
  created_at TEXT DEFAULT (datetime('now'))
);
 
CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_published ON posts(published);

# Apply migrations
npx wrangler d1 migrations apply my-database

// src/index.ts — Cloudflare Worker with D1
export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const url = new URL(request.url);
    if (url.pathname === "/api/posts" && request.method === "GET") {
      const { results } = await env.DB.prepare(
        `SELECT posts.*, users.name as author_name
         FROM posts
         JOIN users ON posts.author_id = users.id
         WHERE posts.published = TRUE
         ORDER BY posts.created_at DESC
         LIMIT ?`
      ).bind(20).all();
      return Response.json({ posts: results });
    }
    if (url.pathname === "/api/posts" && request.method === "POST") {
      const body = await request.json();
      const result = await env.DB.prepare(
        "INSERT INTO posts (title, content, author_id) VALUES (?, ?, ?) RETURNING *"
      ).bind(body.title, body.content, body.authorId).first();
      return Response.json({ post: result }, { status: 201 });
    }
    return new Response("Not found", { status: 404 });
  },
};

// src/db/schema.ts — Drizzle schema for D1
import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";
export const users = sqliteTable("users", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  email: text("email").notNull().unique(),
  name: text("name").notNull(),
});
export const posts = sqliteTable("posts", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  content: text("content").notNull(),
  authorId: integer("author_id").notNull().references(() => users.id),
  published: integer("published", { mode: "boolean" }).default(false),
});

// src/index.ts — Using Drizzle with D1
import { drizzle } from "drizzle-orm/d1";
import { eq } from "drizzle-orm";
import * as schema from "./db/schema";
export default {
  async fetch(request: Request, env: Env) {
    const db = drizzle(env.DB, { schema });
    const publishedPosts = await db.query.posts.findMany({
      where: eq(schema.posts.published, true),
      with: { author: true },
      orderBy: (posts, { desc }) => [desc(posts.id)],
      limit: 20,
    });
    return Response.json({ posts: publishedPosts });
  },
};