2. Database Setup

In this step we connect to Cloudflare D1 (a serverless SQLite database) using Drizzle ORM, and use @cfast/env to validate our Worker bindings at startup.

By the end of this step, the home page will load posts from the database instead of showing static text.

New dependencies

Package	Why
@cfast/env	Type-safe, runtime-validated Worker bindings
@cfast/db	Permission-aware Drizzle wrapper (we use raw Drizzle for now, permissions come in Step 4)
drizzle-orm	SQL query builder and ORM for D1
drizzle-kit	Migration generator (dev dependency)

New and changed files

• Directorystep-02-database/

  • Directoryapp/

    • schema.ts — Drizzle table definitions (new)
    • env.server.ts — binding validation with defineEnv (new)
    • db.server.ts — database client factory (new)
    • root.tsx
    • routes.ts
    • Directoryroutes/

      • home.tsx — updated with a loader
  • package.json — updated
  • react-router.config.ts
  • tsconfig.json
  • wrangler.jsonc

Walkthrough

1. Define the schema

   Drizzle schemas are TypeScript-first. Each table is a plain function call that produces a typed table reference. We start with a single posts table.

   app/schema.ts

   import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";
   
   export const posts = sqliteTable("posts", {
     id: integer("id").primaryKey({ autoIncrement: true }),
     title: text("title").notNull(),
     content: text("content").notNull(),
     authorId: text("author_id").notNull(),
     createdAt: integer("created_at", { mode: "timestamp" })
       .notNull()
       .$defaultFn(() => new Date()),
   });

   Design notes:

   • integer("created_at", { mode: "timestamp" }) stores dates as Unix timestamps in SQLite but surfaces them as Date objects in TypeScript.
   • $defaultFn(() => new Date()) provides a default at the application level, not in SQL. This runs in the Worker runtime.
   • authorId is a plain text for now. In Step 3 we add a users table and a foreign key.

2. Validate bindings with defineEnv()

   Cloudflare Workers receive bindings (D1 databases, KV namespaces, secrets) as an untyped object. @cfast/env validates them once at startup and gives you a fully typed result.

   app/env.server.ts

   import { defineEnv } from "@cfast/env";
   
   export const env = defineEnv({
     DB: { type: "d1" },
   });
   
   export type Env = ReturnType<typeof env.get>;

   The .server.ts suffix tells the React Router bundler to keep this file out of client bundles. Bindings are server-only — they do not exist in the browser.

   Caution

   If you forget to call env.init() before env.get(), you get a clear error instead of a mysterious undefined. That is the point of @cfast/env — fail fast, fail clearly.

3. Create the database client

   A thin wrapper that initializes Drizzle with our schema. In Step 4 this will become a permission-aware createDb() call.

   app/db.server.ts

   import { drizzle } from "drizzle-orm/d1";
   import * as schema from "./schema";
   import { env } from "./env.server";
   
   export function getDb() {
     const { DB } = env.get();
     return drizzle(DB, { schema });
   }

   Passing schema to drizzle() enables the relational query API, where you can use db.query.posts.findMany() in addition to the SQL-like db.select().from(posts).

4. Add a loader to the home page

   React Router loaders run on the server before the component renders. Here we query all posts, ordered by creation date.

   app/routes/home.tsx

   import type { LoaderFunctionArgs } from "react-router";
   import { useLoaderData } from "react-router";
   import { desc } from "drizzle-orm";
   import { env } from "../env.server";
   import { getDb } from "../db.server";
   import { posts } from "../schema";
   
   export async function loader({ context }: LoaderFunctionArgs) {
     env.init(context.cloudflare);
     const db = getDb();
   
     const allPosts = await db
       .select()
       .from(posts)
       .orderBy(desc(posts.createdAt));
   
     return { posts: allPosts };
   }
   
   export default function Home() {
     const { posts: postList } = useLoaderData<typeof loader>();
   
     return (
       <main style={{ padding: "2rem", fontFamily: "system-ui, sans-serif", maxWidth: "48rem", margin: "0 auto" }}>
         <h1>Team Blog</h1>
         {postList.length === 0 ? (
           <p style={{ color: "#666" }}>No posts yet. Check back later!</p>
         ) : (
           <ul style={{ listStyle: "none", padding: 0 }}>
             {postList.map((post) => (
               <li key={post.id} style={{ marginBottom: "1.5rem" }}>
                 <h2>{post.title}</h2>
                 <p>{post.content}</p>
               </li>
             ))}
           </ul>
         )}
       </main>
     );
   }

   Key pattern: env.init(context.cloudflare) is called in the loader, not in a global scope. Worker bindings are per-request via the context parameter. The init() call validates them once and caches the result; subsequent calls in the same request are no-ops.

Running migrations

Drizzle Kit generates SQL migration files from your schema.

# Generate a migration from the schema
npx drizzle-kit generate

# Apply it to local D1
npx wrangler d1 migrations apply DB --local

The first command creates a SQL file in a drizzle/ directory. The second runs it against your local D1 instance. In production, you would run the migration as part of your deploy pipeline.

Tip

To seed some test data, use wrangler d1 execute:

npx wrangler d1 execute DB --local \
  --command "INSERT INTO posts (title, content, author_id) VALUES ('Hello World', 'Our first post.', 'user-1')"

How data flows

Browser → Worker → React Router loader → Drizzle → D1 (SQLite)
                                            ↓
                                     typed query result
                                            ↓
                              useLoaderData() in component

The loader’s return value is serialized to JSON and sent to the browser during SSR. On client-side navigations, React Router fetches the loader data via an API call instead of a full page reload.

What @cfast/env gives you

Without @cfast/env, your loader would look like:

const db = drizzle(context.cloudflare.env.DB); // 'DB' is untyped

If someone renames the binding in wrangler.jsonc but forgets the code, you get a runtime crash on the first database query. With defineEnv(), the mismatch is caught immediately at startup with a message like:

@cfast/env: 1 binding error(s):
  - DB: Missing required D1 binding 'DB'. Check your wrangler.toml.

Next step

In Step 3: Authentication, we add Better Auth for passwordless login with magic email links.