Troubleshooting Guide

When things go wrong with migrations or D1, this guide will help you diagnose and fix the issue. Each section includes the exact error messages you might see, making this guide searchable when you encounter a problem.

Common Migration Errors

”Table already exists” / SQLITE_ERROR

Error message:

SQLITE_ERROR: table `users` already exists

or

Error: D1_EXEC_ERROR: SQLITE_ERROR: table users already exists

Cause: The migration tried to create a table that already exists in the database. This typically happens when:

• A migration was partially applied before failing
• You manually created the table outside of migrations
• The migration was applied but not recorded in the migrations table

Solution:

flowchart TD
  A["Table already exists error"] --> B{Is this local dev?}
  B -->|Yes| C["Reset local database"]
  B -->|No| D{Was migration partially applied?}
  D -->|Yes| E["Use D1 Time Travel to restore"]
  D -->|No| F["Manually record migration as applied"]

  C --> G["pnpm reset:all"]
  E --> H["Restore to pre-migration state"]
  F --> I["Insert into __drizzle_migrations"]

Expand

For local development:

Terminal

# Reset everything and start fresh
pnpm reset:all

For remote environments (staging/production):

Terminal

# Check what's actually in the database
pnpm wrangler d1 execute DB --remote --command "SELECT name FROM sqlite_master WHERE type='table'"

# Check which migrations have been applied
pnpm wrangler d1 execute DB --remote --command "SELECT * FROM __drizzle_migrations"

If the table exists but the migration wasn’t recorded, you can manually mark it as applied:

Terminal

# Get current timestamp
date +%s

# Insert migration record (use actual timestamp and hash)
pnpm wrangler d1 execute DB --remote --command \
  "INSERT INTO __drizzle_migrations (hash, created_at) VALUES ('migration_file_hash', 1234567890)"

---

“No such column” / SQLITE_ERROR

Error message:

SQLITE_ERROR: no such column: bio

or

Error: D1_ERROR: no such column: users.bio

Cause: Your application code references a column that doesn’t exist in the database. This happens when:

• You updated the schema but haven’t run the migration
• The migration failed silently
• You’re querying the wrong environment’s database

Solution:

Terminal

# Check pending migrations
pnpm wrangler d1 migrations list DB --local

# Check actual table structure
pnpm wrangler d1 execute DB --local --command "PRAGMA table_info(users)"

# Apply pending migrations
pnpm wrangler d1 migrations apply DB --local

Expected output after PRAGMA:

┌─────┬────────────┬─────────┬─────────┬──────────────┬──────┐
│ cid │ name       │ type    │ notnull │ dflt_value   │ pk   │
├─────┼────────────┼─────────┼─────────┼──────────────┼──────┤
│ 0   │ id         │ INTEGER │ 1       │              │ 1    │
│ 1   │ name       │ TEXT    │ 1       │              │ 0    │
│ 2   │ email      │ TEXT    │ 1       │              │ 0    │
└─────┴────────────┴─────────┴─────────┴──────────────┴──────┘

If bio isn’t in the list, generate and apply the migration:

Terminal

pnpm drizzle-kit generate
pnpm wrangler d1 migrations apply DB --local

---

“UNIQUE constraint failed”

Error message:

SQLITE_CONSTRAINT: UNIQUE constraint failed: users.email

or

Error: D1_EXEC_ERROR: SQLITE_CONSTRAINT: UNIQUE constraint failed: users.email

Cause: You tried to insert a row with a duplicate value in a column that has a UNIQUE constraint.

Solution:

Check for existing data before inserting:

src/routes/users.ts

// Option 1: Check if exists first
const existing = await db.select().from(users).where(eq(users.email, email));
if (existing.length > 0) {
  throw new Error('Email already registered');
}

// Option 2: Use INSERT OR REPLACE (upsert)
await db.insert(users)
  .values({ email, name })
  .onConflictDoUpdate({
    target: users.email,
    set: { name }
  });

// Option 3: Use INSERT OR IGNORE
await db.insert(users)
  .values({ email, name })
  .onConflictDoNothing();

---

“Foreign key constraint failed”

Error message:

SQLITE_CONSTRAINT: FOREIGN KEY constraint failed

Cause: You tried to:

• Insert a row referencing a non-existent parent row
• Delete a parent row that still has child references
• Update a foreign key to a non-existent value

Solution:

Terminal

# Check if foreign keys are enabled
pnpm wrangler d1 execute DB --local --command "PRAGMA foreign_keys"

For insert errors, ensure the parent record exists:

Example: Creating a post

// ❌ This fails if author_id doesn't exist
await db.insert(posts).values({
  title: 'My Post',
  authorId: 999, // Non-existent user!
});

// ✅ Verify the author exists first
const author = await db.select().from(users).where(eq(users.id, authorId));
if (!author.length) {
  throw new Error('Author not found');
}
await db.insert(posts).values({ title: 'My Post', authorId });

For delete errors, either delete children first or use CASCADE:

src/db/schema.ts

// Add ON DELETE CASCADE to automatically delete children
export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  authorId: integer('author_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  // ...
});

---

“NOT NULL constraint failed”

Error message:

SQLITE_CONSTRAINT: NOT NULL constraint failed: users.name

Cause: You tried to insert or update a row without providing a required (NOT NULL) field.

Solution:

Ensure all required fields are provided:

Example fix

// ❌ Missing required field
await db.insert(users).values({
  email: 'test@example.com',
  // name is missing!
});

// ✅ Include all required fields
await db.insert(users).values({
  email: 'test@example.com',
  name: 'Test User',
  createdAt: Date.now(),
});

If adding a NOT NULL column to an existing table, provide a default:

src/db/schema.ts

// For new columns on tables with existing data
bio: text('bio').notNull().default(''),

---

“Migration checksum mismatch”

Error message:

Error: Migration checksum mismatch for 0001_create_users.sql
Expected: abc123...
Got: def456...

Cause: A migration file was modified after it was applied to the database. D1 tracks migration hashes to detect tampering.

Solution:

flowchart TD
  A["Checksum mismatch"] --> B{Which environment?}
  B -->|Local only| C["Reset local database"]
  B -->|Staging| D["Restore from Time Travel"]
  B -->|Production| E["Do NOT modify - fix forward"]

  C --> F["pnpm reset:all"]
  D --> G["Restore to known good state"]
  E --> H["Create corrective migration"]

Expand

Never modify applied migrations. If you need to change something:

1. For local only: Reset the database (pnpm reset:all)
2. For shared environments: Create a new migration that makes the necessary correction

Terminal

# Create a corrective migration
pnpm drizzle-kit generate
# This will create a new migration to fix the issue

---

D1-Specific Quirks and Workarounds

Local vs Remote Behavior Differences

D1’s local emulation (via Miniflare) differs slightly from the remote D1 service.

Known Differences:

Behavior	Local D1	Remote D1
Performance	Uses file-based SQLite	Distributed edge database
Transactions	Full SQLite transactions	Batch operations recommended
Row limits	No practical limit	100KB per row max
Query timeout	None	30 second limit

Best Practice: Always test in staging before production:

Terminal

# Apply to staging first
pnpm wrangler d1 migrations apply DB --env staging

# Test your application
curl https://your-app-staging.workers.dev/api/users

# Then apply to production
pnpm wrangler d1 migrations apply DB --env production

---

“D1_ERROR: too many SQL variables”

Error message:

Error: D1_ERROR: too many SQL variables

Cause: SQLite has a limit on the number of bound parameters (SQLITE_MAX_VARIABLE_NUMBER). The default is 999, but D1 may have different limits.

Solution:

Batch your operations:

src/utils/batch-insert.ts

// ❌ Too many parameters at once
await db.insert(users).values(thousandsOfUsers);

// ✅ Batch into smaller chunks
const BATCH_SIZE = 100;
for (let i = 0; i < users.length; i += BATCH_SIZE) {
  const batch = users.slice(i, i + BATCH_SIZE);
  await db.insert(usersTable).values(batch);
}

---

“Request body too large”

Error message:

Error: Request body too large

or

Error: D1_ERROR: payload too large

Cause: D1 has limits on request/response sizes. For SQL execution, the query plus bound parameters can’t exceed the limit.

Solution:

• Break large operations into smaller batches
• For large data imports, use multiple smaller INSERT statements
• Consider streaming for very large datasets

Terminal

# Instead of one large import
pnpm wrangler d1 execute DB --remote --file large-import.sql

# Split into smaller files
pnpm wrangler d1 execute DB --remote --file import-part-1.sql
pnpm wrangler d1 execute DB --remote --file import-part-2.sql

---

Migrations Not Showing as Applied

Problem: You ran wrangler d1 migrations apply but the migration doesn’t show as applied.

Diagnosis:

Terminal

# List migrations status
pnpm wrangler d1 migrations list DB --local

# Check the migrations table directly
pnpm wrangler d1 execute DB --local --command "SELECT * FROM __drizzle_migrations"

Common Causes:

1. Wrong environment: You applied to --local but checking --remote
2. Wrong database: Multiple D1 databases configured
3. Migration failed silently: Check for errors in the output

Solution:

Terminal

# Verify which database you're using
cat wrangler.toml | grep -A5 "d1_databases"

# Apply with explicit environment
pnpm wrangler d1 migrations apply DB --local
# or
pnpm wrangler d1 migrations apply DB --env staging

---

“Cannot add a NOT NULL column with default value NULL”

Error message:

SQLITE_ERROR: Cannot add a NOT NULL column with default value NULL to table "users"

Cause: SQLite doesn’t allow adding a NOT NULL column without a default to a table that already has rows.

Solution:

Add a default value or make the column nullable:

src/db/schema.ts

// Option 1: Provide a default
bio: text('bio').notNull().default(''),

// Option 2: Allow NULL initially, then migrate data
bio: text('bio'), // nullable first

For Option 2, follow up with a data migration:

migrations/0003_populate_bio_defaults.sql

UPDATE users SET bio = '' WHERE bio IS NULL;

Then add the NOT NULL constraint in a subsequent migration (note: SQLite requires table recreation for this).

---

Reset Procedures

Reset Local Database Completely

When local development gets into a bad state:

Terminal

# Full reset - removes database and re-applies all migrations
pnpm reset:all

What this does:

1. Deletes the local D1 database file
2. Re-applies all migrations from the beginning
3. Gives you a clean slate

---

Reset to Specific Chapter (Tutorial Checkpoints)

For tutorial users who want to start from a specific point:

Terminal

# Chapter 1: Empty database (just users table structure)
pnpm reset:ch1

# Chapter 2: Users table with sample data
pnpm reset:ch2

# Chapter 3: Users + Posts tables
pnpm reset:ch3

# Chapter 4: Full schema with comments
pnpm reset:ch4

---

Reset Migrations State (Keep Data)

If you need to reset migration tracking without losing data (use with caution):

Terminal

# Check current state
pnpm wrangler d1 execute DB --local --command "SELECT * FROM __drizzle_migrations"

# Clear migration history (DANGEROUS - only for local dev)
pnpm wrangler d1 execute DB --local --command "DELETE FROM __drizzle_migrations"

# Re-record all migrations as applied (without running them)
# You'll need to insert entries for each migration file

Warning: This is an advanced operation. Only use this when you know the database schema matches what the migrations would produce.

---

Fix “Stuck” Migration State

When a migration partially applied and left things in a bad state:

flowchart TD
  A["Migration stuck"] --> B["Check what actually exists"]
  B --> C["Compare with migration SQL"]
  C --> D{Schema matches intent?}
  D -->|Yes| E["Manually record as applied"]
  D -->|No| F["Rollback and retry"]

  E --> G["INSERT INTO __drizzle_migrations"]
  F --> H["DROP partial changes + reset"]

Expand

Step 1: Inspect current state

Terminal

# See all tables
pnpm wrangler d1 execute DB --local --command "SELECT name FROM sqlite_master WHERE type='table'"

# See table structure
pnpm wrangler d1 execute DB --local --command "PRAGMA table_info(users)"

# See indexes
pnpm wrangler d1 execute DB --local --command "PRAGMA index_list(users)"

Step 2: Compare with migration

Terminal

cat migrations/0005_problematic_migration.sql

Step 3: Fix based on findings

Terminal

# If schema is correct, just record the migration
pnpm wrangler d1 execute DB --local --command \
  "INSERT INTO __drizzle_migrations (hash, created_at) VALUES ('0005_problematic_migration', strftime('%s','now'))"

# If schema is wrong, drop and retry (LOCAL ONLY)
pnpm reset:all

---

Drizzle-Specific Issues

”Cannot find module ‘drizzle-kit’”

Error message:

Cannot find module 'drizzle-kit'

Solution:

Terminal

pnpm install drizzle-kit --save-dev

---

“No schema changes detected”

Message:

No schema changes detected

Cause: Your schema.ts matches what’s already been generated in migrations.

This is usually fine. It means your schema is up to date.

If you expected changes:

Terminal

# Check the current schema file
cat src/db/schema.ts

# Compare with last migration
ls -la migrations/
cat migrations/$(ls -t migrations/ | head -1)

---

Type Errors After Schema Changes

Error message:

Property 'bio' does not exist on type 'InferSelectModel<typeof users>'

Cause: TypeScript types are out of sync with your schema changes.

Solution:

1. Ensure you’ve updated schema.ts
2. Regenerate types (if using separate type generation)
3. Restart your TypeScript language server

Terminal

# Restart TS server in VS Code: Cmd+Shift+P -> "TypeScript: Restart TS Server"

# Or just rebuild
pnpm typecheck

---

Quick Diagnostic Commands

Use these commands to quickly understand your database state:

Diagnostic Commands

# List all tables
pnpm wrangler d1 execute DB --local --command "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"

# List all migrations applied
pnpm wrangler d1 execute DB --local --command "SELECT * FROM __drizzle_migrations ORDER BY created_at"

# Check pending migrations
pnpm wrangler d1 migrations list DB --local

# Get table schema
pnpm wrangler d1 execute DB --local --command "PRAGMA table_info(users)"

# Get foreign keys
pnpm wrangler d1 execute DB --local --command "PRAGMA foreign_key_list(posts)"

# Get indexes
pnpm wrangler d1 execute DB --local --command "PRAGMA index_list(users)"

# Count rows in a table
pnpm wrangler d1 execute DB --local --command "SELECT COUNT(*) FROM users"

# Check database file size (local only)
ls -lh .wrangler/state/v3/d1/miniflare-D1DatabaseObject/*.sqlite

---

Error Message Quick Reference

Error Snippet	Likely Cause	Quick Fix
table already exists	Migration re-run	Reset local or check migration state
no such column	Missing migration	Run pending migrations
UNIQUE constraint failed	Duplicate data	Check for existing record
FOREIGN KEY constraint failed	Missing parent	Create parent record first
NOT NULL constraint failed	Missing required field	Provide all required values
checksum mismatch	Modified migration file	Never edit applied migrations
too many SQL variables	Large batch insert	Chunk into smaller batches
payload too large	Request size limit	Split into multiple operations
no such table	Table not created	Apply migrations
database is locked	Concurrent access	Wait and retry

---

Getting More Help

If you encounter an issue not covered here:

1. Check the Cloudflare D1 docs: developers.cloudflare.com/d1
2. Drizzle ORM docs: orm.drizzle.team
3. Search the error message: Most SQLite errors are well-documented
4. Cloudflare Discord: Active community support

When reporting issues, include:

• The exact error message
• The command you ran
• Your wrangler.toml D1 configuration (without secrets)
• Relevant migration files