How to Use Drizzle ORM with SQLite for Edge Database Queries: A Complete Cloudflare D1 Guide

Use Drizzle ORM's d1-http driver inside a Cloudflare Worker to execute type-safe SQLite queries at the edge by binding the D1 database through @opennextjs/cloudflare and defining schemas with drizzle-orm/sqlite-core.

The fullstack-next-cloudflare repository demonstrates production-ready patterns for running Drizzle ORM with SQLite on Cloudflare's edge network. By leveraging Cloudflare D1 and the d1-http driver, you can perform low-latency database operations directly from Worker environments while maintaining full TypeScript type safety and Zod validation.

Architecture Overview

The implementation follows a layered architecture that separates schema definitions, database configuration, and business logic. At the foundation, drizzle.config.ts configures the production D1 connection using driver: "d1-http", while drizzle.local.config.ts enables local SQLite file development. The database client layer in src/db/index.ts creates a drizzle instance bound to the Worker's environment bindings. Business logic resides in server actions under src/modules/todos/actions/, which import table schemas and execute queries using the edge client. Every query enforces multi-tenant safety through requireAuth() from src/modules/auth/utils/auth-utils.ts, ensuring data isolation by appending where(eq(..., user.id)) clauses.

Configuring Drizzle for Cloudflare D1

You must configure Drizzle ORM to use the D1 HTTP driver for production and a local SQLite file for development. This dual-configuration approach ensures type safety across environments.

In drizzle.config.ts, specify the SQLite dialect and D1 HTTP driver for edge deployment:

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

export default defineConfig({
  dialect: "sqlite",
  driver: "d1-http",
  schema: "./src/db/schema.ts",
  out: "./migrations",
  // Cloudflare credentials provided via environment
});

For local development, drizzle.local.config.ts targets a local SQLite database file, allowing you to test queries without deploying to Cloudflare:

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

export default defineConfig({
  dialect: "sqlite",
  schema: "./src/db/schema.ts",
  out: "./migrations",
  dbCredentials: {
    url: "./local.db",
  },
});

Defining SQLite Table Schemas

Define your SQLite tables using sqliteTable from drizzle-orm/sqlite-core and generate Zod schemas for runtime validation. In src/modules/todos/schemas/todo.schema.ts, the todos table includes foreign key constraints, default values, and enum handling:

// src/modules/todos/schemas/todo.schema.ts
import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";
import { createInsertSchema, createSelectSchema } from "drizzle-zod";
import { z } from "zod";

export const todos = sqliteTable("todos", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  description: text("description"),
  categoryId: integer("category_id").references(() => categories.id),
  userId: text("user_id")
    .notNull()
    .references(() => user.id, { onDelete: "cascade" }),
  status: text("status")
    .$type<TodoStatusType>()
    .notNull()
    .default(TodoStatus.PENDING),
  priority: text("priority")
    .$type<TodoPriorityType>()
    .notNull()
    .default(TodoPriority.MEDIUM),
  createdAt: text("created_at").notNull().default("CURRENT_TIMESTAMP"),
  updatedAt: text("updated_at").notNull().default("CURRENT_TIMESTAMP"),
});

export const insertTodoSchema = createInsertSchema(todos);
export const selectTodoSchema = createSelectSchema(todos);
export type Todo = z.infer<typeof selectTodoSchema>;

This schema definition provides type-safe table objects that Drizzle uses to generate SQL queries while drizzle-zod ensures runtime data validation.

Initializing the Edge Database Client

Create the database client inside a server action or API route using @opennextjs/cloudflare to access the Worker's environment bindings. The getDb() function in src/db/index.ts initializes the Drizzle client with your schema:

// src/db/index.ts
import { getCloudflareContext } from "@opennextjs/cloudflare";
import { drizzle } from "drizzle-orm/d1";
import * as schema from "./schema";

export async function getDb() {
  const { env } = await getCloudflareContext();
  // `next_cf_app` is the D1 binding name defined in wrangler.jsonc
  return drizzle(env.next_cf_app, { schema });
}

The drizzle() function receives the D1 database binding (env.next_cf_app) and your schema definition, returning a type-safe client capable of executing SQL queries directly against the D1 SQLite instance at the edge.

Executing Type-Safe CRUD Operations

With the client initialized, perform database operations using Drizzle's fluent API. All queries execute inside the Cloudflare Worker, providing low-latency access to SQLite data from the edge network.

Selecting Data with Joins

Fetch related data using joins and ordering. In src/modules/todos/actions/get-todos.action.ts, the query joins the categories table and filters by the authenticated user:

// src/modules/todos/actions/get-todos.action.ts
import { and, eq } from "drizzle-orm";
import { categories, getDb } from "@/db";
import { requireAuth } from "@/modules/auth/utils/auth-utils";
import { type Todo, todos } from "@/modules/todos/schemas/todo.schema";

export default async function getAllTodos(): Promise<Todo[]> {
  const user = await requireAuth();
  const db = await getDb();

  const rows = await db
    .select({
      id: todos.id,
      title: todos.title,
      description: todos.description,
      status: todos.status,
      priority: todos.priority,
      categoryName: categories.name,
      createdAt: todos.createdAt,
    })
    .from(todos)
    .leftJoin(
      categories,
      and(eq(todos.categoryId, categories.id), eq(categories.userId, user.id)),
    )
    .where(eq(todos.userId, user.id))
    .orderBy(todos.createdAt);

  return rows;
}

The leftJoin combines tables while the and() operator ensures both the category relationship and user ownership match.

Inserting Records with Validation

Insert data using Zod-validated schemas to prevent malformed entries. The createInsertSchema validator ensures type safety before database insertion:

// src/modules/todos/actions/create-todo.action.ts
import { insertTodoSchema, todos } from "@/modules/todos/schemas/todo.schema";
import { getDb } from "@/db";
import { requireAuth } from "@/modules/auth/utils/auth-utils";

export async function createTodoAction(formData: FormData) {
  const user = await requireAuth();
  const raw = {
    title: formData.get("title") as string,
    description: formData.get("description") as string,
    categoryId: Number(formData.get("categoryId")),
    userId: user.id,
    status: "PENDING",
  };
  
  const validated = insertTodoSchema.parse(raw);

  const db = await getDb();
  const [todo] = await db
    .insert(todos)
    .values(validated)
    .returning();

  return todo;
}

The returning() method retrieves the inserted row, matching SQLite's INSERT ... RETURNING * syntax.

Updating with Conditional Fields

Handle partial updates and enum conversions safely. In src/modules/todos/actions/update-todo.action.ts, the code conditionally includes enum fields only when present:

// src/modules/todos/actions/update-todo.action.ts
const { status, priority, ...rest } = validatedData;

await db
  .update(todos)
  .set({
    ...rest,
    ...(status && { status: status as (typeof TodoStatus)[keyof typeof TodoStatus] }),
    ...(priority && { priority: priority as (typeof TodoPriority)[keyof typeof TodoPriority] }),
    updatedAt: new Date().toISOString(),
  })
  .where(and(eq(todos.id, todoId), eq(todos.userId, user.id)))
  .returning();

This pattern prevents overwriting existing enum values with null while ensuring only the todo owner can modify the record.

Deleting with Ownership Verification

Remove records only after verifying user ownership using compound eq() conditions:

// src/modules/todos/actions/delete-todo.action.ts
import { and, eq } from "drizzle-orm";

await db
  .delete(todos)
  .where(and(eq(todos.id, todoId), eq(todos.userId, user.id)));

The and(eq(...), eq(...)) guard guarantees row-level security, ensuring users cannot delete data belonging to other accounts.

Enforcing Multi-Tenant Security

Every database action must verify authentication and restrict queries to the current user. The requireAuth() utility in src/modules/auth/utils/auth-utils.ts resolves the authenticated session, and each query appends where(eq(todos.userId, user.id)) clauses. This architectural pattern ensures data isolation at the application level, preventing unauthorized access to SQLite rows even when queries execute from the edge network.

Summary

• Configure Drizzle with drizzle.config.ts for production D1 (driver: "d1-http") and drizzle.local.config.ts for local SQLite development.
• Define schemas using sqliteTable from drizzle-orm/sqlite-core and drizzle-zod to generate type-safe tables and validation schemas.
• Initialize the client via getDb() in src/db/index.ts using @opennextjs/cloudflare to access the env.next_cf_app D1 binding.
• Execute queries using Drizzle's fluent API (select, insert, update, delete) with eq(), and(), and leftJoin() operators for complex operations.
• Secure all operations by wrapping actions with requireAuth() and filtering every query by user.id to enforce row-level security in multi-tenant edge applications.

Frequently Asked Questions

How does the Drizzle client access the D1 database binding in a Cloudflare Worker?

The client uses @opennextjs/cloudflare to retrieve the Worker's environment. In src/db/index.ts, the getCloudflareContext() function provides access to env.next_cf_app, which is the D1 database binding defined in wrangler.jsonc. The drizzle(env.next_cf_app, { schema }) call creates a client instance bound to that specific D1 database, enabling edge execution.

What pattern ensures user data isolation when querying SQLite at the edge?

The repository implements row-level security through the requireAuth() utility from src/modules/auth/utils/auth-utils.ts. Every server action calls this function to obtain the current user, then appends where(eq(todos.userId, user.id)) (or and(eq(todos.id, id), eq(todos.userId, user.id))) to database queries. This guarantees that SQL operations only return or modify rows owned by the authenticated user.

Can I use the same Drizzle schema for local development and Cloudflare D1?

Yes. The schema definitions in files like src/modules/todos/schemas/todo.schema.ts use drizzle-orm/sqlite-core, which is compatible with both standard SQLite and Cloudflare D1. The configuration files handle environment differences: drizzle.local.config.ts connects to a local .db file, while drizzle.config.ts uses the d1-http driver. The schema itself remains environment-agnostic.

Why use the d1-http driver instead of the standard SQLite driver for edge queries?

The d1-http driver is specifically designed for Cloudflare Workers and D1 databases. Unlike standard SQLite drivers that require direct file system access (impossible in the Workers V8 isolate environment), the D1 HTTP driver communicates via HTTP requests to the D1 service. This allows Drizzle ORM to execute SQL queries at the edge while maintaining the same type-safe API as local SQLite development.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}