Schema Design for D1: Managing SQL Migrations with Wrangler

Previously in this course, we covered the Introduction to D1 SQL Database, where we set up our database instance. Now that we have a database ready, we need to define its structure.

In this lesson, we will move from a blank database to a structured schema. You'll learn how to write a SQL migration file, apply it to your D1 instance using the Wrangler CLI, and verify that your tables are ready for your application data.

Understanding SQL Migrations

A migration is a version-controlled file containing the SQL commands required to change your database structure. Rather than running manual CREATE TABLE commands in a console, migrations allow you to track, share, and automate your database evolution.

Think of a migration file as a "step" in your project's history. When you need to add a feature, you create a new migration, apply it to your development environment, and eventually push it to production. For a deeper dive into the theory behind this, you might appreciate the context provided in Implementing Schema Migrations: A Guide for Scalable Databases.

Designing Your First Schema

For our project, we need a way to store metadata for the files we are uploading to R2. We will create a file_metadata table.

Create a directory named migrations in the root of your project. Inside it, create a file named 0001_create_file_metadata.sql.

SQL
-- Migration 0001: Create file_metadata table
DROP TABLE IF EXISTS file_metadata;

CREATE TABLE file_metadata (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    filename TEXT NOT NULL,
    size_bytes INTEGER NOT NULL,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

This SQL is standard SQLite, which D1 uses under the hood. We use AUTOINCREMENT for the primary key to ensure every entry has a unique, sequential ID.

Executing Migrations via Wrangler

Now that the file exists, we need to tell Wrangler to apply this migration to our D1 database. Wrangler manages the state of your migrations, ensuring each file is only run once against your specific database instance.

Run the following command in your terminal:

Bash
npx wrangler d1 migrations apply <DATABASE_NAME> --local

Replace <DATABASE_NAME> with the name you defined in your wrangler.toml. The --local flag applies this to your local development database. If you want to apply this to your production Cloudflare database, simply omit the --local flag.

Wrangler will look into your migrations folder, identify that 0001 has not been applied yet, and execute the SQL inside.

Verifying Table Creation

Once the command finishes, you can verify that the table exists by opening your local D1 database. You can do this directly through Wrangler:

Bash
npx wrangler d1 execute <DATABASE_NAME> --local --command "SELECT name FROM sqlite_master WHERE type='table';"

If everything worked, you should see file_metadata returned in the output. You can also run a DESCRIBE equivalent or simply try to select from the empty table to confirm it's ready for data.

Common Pitfalls

• Forgetting the migrations directory: Wrangler expects a specific directory structure. Ensure your files are named with a numeric prefix (e.g., 0001_, 0002_) so Wrangler can apply them in the correct chronological order.
• Editing existing migrations: Never modify a migration file that has already been pushed to production. If you need to change a column, create a new migration file that uses ALTER TABLE to perform the update.
• Syntax errors: D1 uses SQLite syntax. If you are coming from PostgreSQL or MySQL, keep in mind that certain features (like specific data types or advanced constraint syntaxes) might differ slightly.

FAQ

Q: Do I need to manually keep track of which migrations have run? A: No. Wrangler automatically creates a d1_migrations table inside your database to track which files have been executed, preventing accidental re-runs.

Q: Can I use a GUI to view my D1 data? A: Yes. You can use any SQLite-compatible database browser (like DB Browser for SQLite) to open the local .sqlite file generated by Wrangler during development.

Q: How do I handle rollbacks? A: D1 doesn't support automatic "down" migrations. If you make a mistake, you must write a new "up" migration that reverses the changes (e.g., DROP COLUMN).

Recap

In this lesson, we moved from an empty database to a structured schema. We learned that migrations are the safest way to manage database evolution, used the Wrangler CLI to apply our schema, and verified our changes with a direct SQL query. This setup provides the foundation for the CRUD operations we will implement next.

Up next: Basic CRUD: Create and Read — we will write our first Worker functions to insert data into this table and query it back.