setup-d1-drizzle-in-tanstack-start

name: setup-d1-drizzle-in-tanstack-start description: This guide shows how to set up Cloudflare D1 with Drizzle ORM for database migrations in a TanStack Start project.

Prerequisites

• Wrangler CLI installed (wrangler package in devDependencies)
• Drizzle ORM and Drizzle Kit installed
• A Cloudflare account (for remote D1 database)

Step 1: Create a D1 Database

Create a new D1 database using Wrangler. Replace my-photo-gallery with your database name:

pnpm wrangler d1 create my-photo-gallery

Wrangler will:

• Create the database in your Cloudflare account
• Display the database ID and binding name
• Offer to automatically add the binding to wrangler.jsonc

Accept the prompts to let Wrangler configure the binding automatically.

Step 2: Configure Wrangler Migrations Directory

In wrangler.jsonc, add the migrations_dir property to your D1 database binding:

{
  "d1_databases": [
    {
      "binding": "DB",
      "database_name": "my-photo-gallery",
      "database_id": "YOUR_DATABASE_ID",
      "migrations_dir": "migrations"
    }
  ]
}

This tells Wrangler where to find your SQL migration files.

Step 3: Generate binding types

Run the following command to generate TypeScript types for your D1 database binding:

pnpm wrangler types

This creates a worker-configuration.d.ts file with types for your D1 database.

Step 4: Configure Drizzle

Create or update drizzle.config.ts:

import { config } from 'dotenv'
import { defineConfig } from 'drizzle-kit'

config({ path: ['.env.local', '.env'] })

export default defineConfig({
  out: './migrations',
  schema: './src/db/schema.ts',
  dialect: 'sqlite',
  strict: true,
  verbose: true,
})

Key settings:

• out: Output directory for generated migrations (matches Wrangler's migrations_dir)
• schema: Path to your Drizzle schema file
• dialect: Use sqlite for D1
• strict: Enables strict mode for safer migrations
• verbose: Shows detailed migration generation output

Step 5: Add npm Scripts

Add these scripts to package.json:

{
  "scripts": {
    "cf-typegen": "wrangler types",
    "db:generate": "drizzle-kit generate",
    "db:migrate": "pnpm run db:migrate:local",    
    "db:migrate:local": "wrangler d1 migrations apply my-photo-gallery --local",
    "db:migrate:remote": "wrangler d1 migrations apply my-photo-gallery --remote",
    "db:push": "drizzle-kit push",
    "db:pull": "drizzle-kit pull",
    "db:studio": "drizzle-kit studio"
  }
}

Replace my-photo-gallery with your database name.

Step 6: Define Your Schema

Create your database schema in src/db/schema.ts:

import { sqliteTable, integer, text } from 'drizzle-orm/sqlite-core'
import { sql } from 'drizzle-orm'

export const todos = sqliteTable('todos', {
  id: integer({ mode: 'number' }).primaryKey({
    autoIncrement: true,
  }),
  title: text().notNull(),
  createdAt: integer('created_at', { mode: 'timestamp' }).default(
    sql`(unixepoch())`,
  ),
})

Step 7: Generate and Apply Migrations

Generate Migration from Schema

pnpm db:generate

This creates SQL migration files in the migrations/ directory based on your schema.

Apply Migration Locally

pnpm db:migrate:local

This applies migrations to your local D1 development database (stored in .wrangler/state/).

Apply Migration to Remote

When ready to deploy to Cloudflare:

pnpm db:migrate:remote

This applies migrations to your production D1 database on Cloudflare.

Day-to-Day Workflow

For future schema changes:

1. Edit schema: Modify src/db/schema.ts
2. Generate migration: Run pnpm db:generate
3. Test locally: Run pnpm db:migrate:local
4. Deploy to production: Run pnpm db:migrate:remote

Additional Commands

• pnpm db:studio - Open Drizzle Studio to browse your database
• pnpm db:push - Push schema changes directly without generating migrations (dev only)
• pnpm db:pull - Pull/introspect existing database schema

Troubleshooting

Migration folder mismatch

Ensure drizzle.config.ts out directory matches wrangler.jsonc migrations_dir.

Local database not found

Run migrations with --local flag at least once to initialize the local database.

Remote deployment fails

Ensure you're authenticated with Wrangler: pnpm wrangler login