D1 Migration

What Is D1 Migration?

D1 Migration is a guided workflow designed to streamline database schema migrations for Cloudflare D1, leveraging Drizzle ORM as the migration generator. The workflow focuses on generating migration scripts, inspecting for potentially dangerous SQL, and safely applying updates to both local and remote D1 databases.

It also provides guidance for resolving stuck or partially-applied migrations, making it an essential skill for developers managing schema evolution in Cloudflare's serverless SQLite environment.

Why Use D1 Migration?

Cloudflare D1 is a fast, serverless database solution, but its operational model introduces unique constraints around schema management and migrations. Drizzle ORM provides a code-first approach for defining and evolving database schemas, but its automated migration scripts can sometimes generate SQL that is incompatible with D1 or that risks data loss through destructive operations.

D1 Migration addresses these issues by:

• Providing a repeatable, cautious workflow for generating and applying migrations.
• Emphasizing manual inspection of generated SQL to catch and fix dangerous patterns before execution.
• Offering clear strategies for handling migration failures, stuck migrations, and partial application scenarios common in distributed and edge environments. By adopting the D1 Migration workflow, development teams can reduce downtime, prevent accidental data loss, and maintain healthy schema evolution practices on Cloudflare D1.

How to Get Started

The D1 Migration workflow is designed to fit naturally into existing TypeScript/JavaScript projects using Drizzle ORM. Below are the core steps to initiate and manage migrations:

1. Generate a New Migration After updating your

Drizzle schema definitions:

pnpm db:generate

This command will output a new SQL migration file in your configured migrations directory (commonly drizzle/).

2. Inspect the Generated SQL

Open the generated .sql file and review its contents thoroughly. Drizzle sometimes emits SQL that may be destructive or incompatible with Cloudflare D1, especially for certain schema changes. Example of a Risky Migration:

CREATE TABLE `users_new` (...); INSERT INTO `users_new` SELECT id, name, new_column FROM `users`; DROP TABLE `users`; ALTER TABLE `users_new` RENAME TO `users`;

If new_column does not exist on the original table, this migration will fail or lose data.

3. Edit for Safety

For simple additions such as new columns, it is often safer to manually adjust the migration:

ALTER TABLE `users` ADD COLUMN `new_column` TEXT DEFAULT 'default_value';

Replace risky blocks with safer SQL as appropriate.

4. Apply the Migration Apply the migration to your local

D1 database:

wrangler d1 migrations apply <DB_NAME> --local

For remote databases (production/staging):

wrangler d1 migrations apply <DB_NAME>

Monitor for errors during this process.

5. Troubleshoot and Fix Issues

If a migration fails or gets stuck, follow the strategies outlined in the "Best Practices" section below.

Key Features

The D1 Migration workflow offers several essential capabilities:

• Drizzle Integration: Seamlessly generate migration scripts from your Drizzle ORM schema definitions.
• SQL Inspection: Strong emphasis on manual review of generated SQL to catch destructive or unsupported changes before execution.
• Local and Remote Application: Apply migrations to both local and remote D1 instances with clear command-line steps.
• Error Recovery: Guidance for fixing stuck migrations, handling partially-applied migrations, and rolling forward after resolving issues.
• Customization: Flexibility to manually edit migration scripts to suit D1’s operational constraints.

Best Practices

Adopting the following best practices will help ensure smooth, safe migrations with Cloudflare D1:

Always Inspect Generated SQL

Never blindly apply auto-generated migration scripts.

Destructive patterns (such as table recreation during a column default change) can result in data loss or failed migrations.

Recognize Table Recreation

Patterns If you see migration scripts that create a new table, copy data, and drop the old table, review the changes carefully. D1 may not handle these operations reliably, especially if the column structure has changed. Example Fix: If the migration is only adding a column, prefer:

ALTER TABLE `my_table` ADD COLUMN `added_column` TEXT DEFAULT 'value';

Test Migrations Locally

First Apply all migrations to a local D1 instance before deploying to remote environments.

This helps catch compatibility issues early.

Handle Partial Failures

If a migration fails midway (e.g., due to network issues or SQL errors), D1 may have applied only part of the script. Manually verify the schema and data integrity before retrying or fixing the migration file.

Clean Up Stuck Migrations

If a migration gets stuck (shows as pending or errored), use the following strategies:

• Reset the migration state: If safe, manually update the migrations table or use D1’s migration management commands to reset.
• Edit and reapply: Fix the migration file and reapply, ensuring idempotency where possible.

Important Notes - **Destructive

Migrations:** Drizzle may generate destructiveSQL for operations like renaming columns, dropping tables, or changing types. Always review and, if necessary, refactor these migrations to avoid accidental data loss.

• D1 Limitations: Cloudflare D1 does not support every SQLite feature. Be cautious with advanced SQL (e.g., certain triggers, foreign key constraints, or complex ALTER statements) and consult the Cloudflare D1 documentation for current limitations.

• Migration Ordering: Ensure migrations are applied in the correct order, especially when collaborating in teams. Out-of-order migrations can lead to schema drift and unexpected errors.

• Backups: Before applying migrations to production, always back up your data. While D1 is serverless, data recovery options are limited compared to traditional databases.

Conclusion

The D1 Migration workflow, when combined with careful SQL inspection and robust error handling, provides a safe and reliable path for evolving your Cloudflare D1 database schema. By following these best practices and notes, you can confidently manage migrations and minimize downtime or data loss in your serverless applications.