Version: 2.0

Database Migrations

CloudflareD1.NET.Migrations provides a robust database migration system for managing schema changes over time. It includes a powerful fluent API and CLI tool for creating and managing migrations.

Overview

Migrations allow you to:

Version control your database schema
Track changes over time with migration history
Deploy safely with automatic rollback support
Collaborate with your team using consistent schema definitions

Installation

Install the migrations package:

dotnet add package CloudflareD1.NET.Migrations

Install the CLI tool globally:

dotnet tool install -g dotnet-d1

Quick Start

1. Create Your First Migration

dotnet d1 migrations add CreateUsersTable

This creates a new migration file with a timestamp:

using CloudflareD1.NET.Migrations;

public class CreateUsersTable_20241027120000 : Migration
{
    public override string Id => "20241027120000";
    public override string Name => "CreateUsersTable";

    public override void Up(MigrationBuilder builder)
    {
        builder.CreateTable("users", t =>
        {
            t.Integer("id").PrimaryKey().AutoIncrement();
            t.Text("name").NotNull();
            t.Text("email").NotNull().Unique();
            t.Integer("age");
            t.Text("created_at").Default("CURRENT_TIMESTAMP");
        });

        builder.CreateIndex("idx_users_email", "users", new[] { "email" }, unique: true);
    }

    public override void Down(MigrationBuilder builder)
    {
        builder.DropIndex("idx_users_email");
        builder.DropTable("users");
    }
}

2. Apply Migrations

dotnet d1 database update

3. List Applied Migrations

dotnet d1 migrations list

4. Rollback Migrations

# Rollback the last migration
dotnet d1 database rollback

# Rollback to a specific migration
dotnet d1 database rollback --to 20241027120000

Migration Builder API

The MigrationBuilder provides a fluent API for defining schema changes:

Creating Tables

public override void Up(MigrationBuilder builder)
{
    builder.CreateTable("posts", t =>
    {
        // Primary key with auto-increment
        t.Integer("id").PrimaryKey().AutoIncrement();
        
        // Required text fields
        t.Text("title").NotNull();
        t.Text("content").NotNull();
        
        // Optional field
        t.Text("excerpt");
        
        // Field with default value
        t.Text("status").Default("draft");
        
        // Numeric fields
        t.Integer("view_count").Default(0);
        t.Real("rating");
        
        // Blob field
        t.Blob("thumbnail");
        
        // Foreign key
        t.Integer("author_id").NotNull();
        t.ForeignKey("author_id", "users", "id", onDelete: "CASCADE");
        
        // Timestamps
        t.Text("created_at").Default("CURRENT_TIMESTAMP");
        t.Text("updated_at");
    });
}

Column Types

// Integer (whole numbers)
t.Integer("count").NotNull();

// Text (strings)
t.Text("name").NotNull();

// Real (floating point)
t.Real("price").NotNull();

// Blob (binary data)
t.Blob("data");

Column Modifiers

// Primary key
t.Integer("id").PrimaryKey();

// Auto-increment (only for INTEGER PRIMARY KEY)
t.Integer("id").PrimaryKey().AutoIncrement();

// Not null
t.Text("email").NotNull();

// Unique
t.Text("username").Unique();

// Default value
t.Text("status").Default("active");
t.Integer("count").Default(0);

Table Constraints

builder.CreateTable("order_items", t =>
{
    t.Integer("order_id").NotNull();
    t.Integer("product_id").NotNull();
    t.Integer("quantity").NotNull();
    
    // Composite primary key
    t.PrimaryKey("order_id", "product_id");
    
    // Foreign keys
    t.ForeignKey("order_id", "orders", "id", onDelete: "CASCADE");
    t.ForeignKey("product_id", "products", "id");
    
    // Unique constraint
    t.Unique("order_id", "product_id");
    
    // Check constraint
    t.Check("quantity > 0");
});

Altering Tables

// Add a column
builder.AddColumn("users", "phone", "TEXT", nullable: true);

// Add a column with constraints
builder.AddColumn("users", "status", "TEXT", nullable: false, defaultValue: "'active'");

// Drop a column
builder.DropColumn("users", "phone");

// Rename a column
builder.RenameColumn("users", "fullname", "full_name");

// Rename a table
builder.RenameTable("user", "users");

Managing Indexes

// Create a simple index
builder.CreateIndex("idx_users_email", "users", new[] { "email" });

// Create a unique index
builder.CreateIndex("idx_users_username", "users", new[] { "username" }, unique: true);

// Create a composite index
builder.CreateIndex("idx_users_name", "users", new[] { "first_name", "last_name" });

// Drop an index
builder.DropIndex("idx_users_email");

Dropping Tables

// Drop a table (with IF EXISTS by default)
builder.DropTable("old_table");

// Drop a table without IF EXISTS
builder.DropTable("old_table", ifExists: false);

Raw SQL

// Execute custom SQL
builder.Sql("INSERT INTO users (name, email) VALUES ('Admin', 'admin@example.com')");
builder.Sql("CREATE TRIGGER update_timestamp AFTER UPDATE ON posts BEGIN ... END");

CLI Tool

Installation

dotnet tool install -g dotnet-d1

Commands

Create a New Migration

dotnet d1 migrations add <MigrationName>

Example:

dotnet d1 migrations add CreateUsersTable
dotnet d1 migrations add AddEmailIndexToUsers
dotnet d1 migrations add AddPostsTable

Scaffold from Database

Generate migrations automatically by comparing your database schema:

# Scaffold migration from database changes
dotnet d1 migrations scaffold <MigrationName> --connection <database-path>

Example workflow:

# 1. Make changes to your SQLite database
sqlite3 local.db "CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT)"

# 2. Scaffold a migration from those changes
dotnet d1 migrations scaffold AddProductsTable --connection local.db

# 3. Review the generated migration file
# The tool will create Migrations/20241027120000_AddProductsTable.cs

# 4. Make more changes
sqlite3 local.db "ALTER TABLE products ADD COLUMN price REAL"

# 5. Scaffold again - only detects NEW changes
dotnet d1 migrations scaffold AddProductPrice --connection local.db

How it works:

First scaffold creates a snapshot of your database schema
Subsequent scaffolds compare current schema to the snapshot
Only generates migrations for the differences
Automatically creates .migrations-snapshot.json to track state

What it detects:

✅ New tables
✅ New columns (ALTER TABLE ADD COLUMN)
✅ Dropped tables
✅ New/dropped indexes
✅ Column types, constraints (PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT)

Code-First Model Diff

Generate migrations from your Code-First model classes:

# Generate migration from your DbContext model
dotnet d1 migrations diff <MigrationName> --context <FullyQualifiedContextType> --assembly <path-to-dll>

Options:

--context (required): Fully qualified type name of your D1Context subclass (e.g., MyApp.Data.AppDbContext)
--assembly (required): Path to the compiled assembly containing your context (e.g., bin/Debug/net8.0/MyApp.dll)
--connection (optional): Path to local database for comparison (defaults to local-model.db)

Example workflow:

Define your Code-First model:

using CloudflareD1.NET.CodeFirst;

namespace MyApp.Models
{
    [Table("users")]
    public class User
    {
        [Key]
        [Column("id")]
        public int Id { get; set; }

        [Column("name")]
        [Required]
        public string Name { get; set; } = string.Empty;

        [Column("email")]
        public string? Email { get; set; }

        [Column("created_at")]
        public DateTime CreatedAt { get; set; }
    }

    public class AppDbContext : D1Context
    {
        public D1Set<User> Users { get; set; } = null!;
    }
}

Build your application:

dotnet build

Generate migration from your model:

dotnet d1 migrations diff InitialCreate --context MyApp.Models.AppDbContext --assembly bin/Debug/net8.0/MyApp.dll

Review the generated migration:

// Migrations/20241027140000_InitialCreate.cs
public class InitialCreate : Migration
{
    public override void Up(MigrationBuilder builder)
    {
        builder.CreateTable("users", table =>
        {
            table.Integer("id").PrimaryKey();
            table.Text("name").NotNull();
            table.Text("email");
            table.Text("created_at").NotNull();
        });
    }

    public override void Down(MigrationBuilder builder)
    {
        builder.DropTable("users");
    }
}

Make changes to your model and generate another migration:

// Add a new property
[Column("is_active")]
public bool IsActive { get; set; } = true;

# Rebuild and generate incremental migration
dotnet build
dotnet d1 migrations diff AddIsActiveColumn --context MyApp.Models.AppDbContext --assembly bin/Debug/net8.0/MyApp.dll

How it works:

Analyzes your D1Context subclass and discovers D1Set<T> properties
Uses attributes and fluent configuration to build schema:
- Attributes: [Table], [Column], [Key], [Required], [NotMapped], [ForeignKey], [Index]
- Fluent API via OnModelCreating(ModelBuilder modelBuilder) for relationships and indexes
The CLI tries to construct your context using a D1Client and reads the Model property. When successful, your OnModelCreating runs and all fluent configuration is honored. If construction fails, it falls back to attribute-based model discovery.
Compares the model schema to the snapshot (.migrations-snapshot.json)
Generates migrations only for the differences
Updates the snapshot to reflect your current model

Conventions:

Properties without [Column] use the property name as column name (converted to snake_case)
Tables without [Table] use the class name pluralized and converted to snake_case
String properties map to TEXT, int to INTEGER, DateTime to TEXT, bool to INTEGER (0/1)
All properties are nullable unless marked with [Required]
Relationships default to {PrincipalName}Id as the foreign key if not specified
Delete behavior defaults to NO ACTION unless configured via .OnDelete(...)

Relationships (Foreign Keys):

You can configure relationships via attributes or fluent API:

Attribute-based:

public class Post
{
        [Key]
        [Column("id")] public int Id { get; set; }

        [Column("user_id")] public int UserId { get; set; }

        [ForeignKey(nameof(User))]
        public int UserIdFk { get; set; } // optional alternative when pointing to navigation

        public User User { get; set; } = null!;
}

Fluent API (recommended):

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
        modelBuilder.Entity<Post>()
                .HasOne(p => p.User)
                .WithMany(u => u.Posts)
                .HasForeignKey(p => p.UserId)
                .OnDelete(DeleteBehavior.Cascade); // NO ACTION | CASCADE | SET NULL | RESTRICT
}

Migrations generated from the model will include t.ForeignKey(...) table constraints with the configured ON DELETE behavior.

Indexes:

You can define indexes via attributes or fluent API:

Attribute-based:

[Index(nameof(Email), IsUnique = true, Name = "idx_unique_email")]
[Index(nameof(FirstName), nameof(LastName))]
public class Customer
{
        [Key] public int Id { get; set; }
        public string FirstName { get; set; } = string.Empty;
        public string LastName { get; set; } = string.Empty;
        public string Email { get; set; } = string.Empty;
}

Fluent API:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
        modelBuilder.Entity<Product>()
                .HasIndex(p => p.Sku)
                .IsUnique()
                .HasName("idx_unique_sku");

        modelBuilder.Entity<Product>()
                .HasIndex(p => p.Name);
}

Indexes appear in generated migrations via builder.CreateIndex(...) and builder.CreateUniqueIndex(...). Composite index names default to ix_{table}_{col1}_{col2} unless you specify a custom name.

Notes:

The CLI attempts to run OnModelCreating. If your context requires additional services for construction, prefer a constructor that accepts a D1Client so the CLI can instantiate it.
When OnModelCreating can't be invoked, attribute configuration is still honored.

See the ModelDiffSample for a complete working example.

List Migrations

# List all migrations with their status
dotnet d1 migrations list

Output:

Migrations:
✓ 20241027120000_CreateUsersTable (applied)
✓ 20241027130000_AddPostsTable (applied)
  20241027140000_AddCommentsTable (pending)

Apply Migrations

# Apply all pending migrations
dotnet d1 database update

# Apply migrations up to a specific migration
dotnet d1 database update --to 20241027130000

Rollback Migrations

# Rollback the last migration
dotnet d1 database rollback

# Rollback to a specific migration
dotnet d1 database rollback --to 20241027120000

Programmatic Usage

You can also use migrations programmatically in your application:

Setting Up the Migration Runner

using CloudflareD1.NET;
using CloudflareD1.NET.Migrations;

// Create D1 client
var client = new D1Client(options, logger);

// Get all migrations from your assembly
var migrations = Assembly.GetExecutingAssembly()
    .GetTypes()
    .Where(t => t.IsSubclassOf(typeof(Migration)) && !t.IsAbstract)
    .Select(t => (Migration)Activator.CreateInstance(t)!)
    .ToList();

// Create migration runner
var runner = new MigrationRunner(client, migrations);

Applying Migrations

// Apply all pending migrations
var appliedMigrations = await runner.MigrateAsync();
Console.WriteLine($"Applied {appliedMigrations.Count} migrations");

// Apply migrations up to a specific migration
var appliedMigrations = await runner.MigrateToAsync("20241027130000");

// Get pending migrations
var pendingMigrations = await runner.GetPendingMigrationsAsync();
Console.WriteLine($"{pendingMigrations.Count} migrations pending");

// Get applied migrations
var appliedMigrations = await runner.GetAppliedMigrationsAsync();
foreach (var migrationId in appliedMigrations)
{
    Console.WriteLine($"✓ {migrationId}");
}

Rolling Back Migrations

// Rollback the last migration
var rolledBackId = await runner.RollbackAsync();
if (rolledBackId != null)
{
    Console.WriteLine($"Rolled back migration: {rolledBackId}");
}

// Rollback to a specific migration
var rolledBackIds = await runner.RollbackToAsync("20241027120000");
Console.WriteLine($"Rolled back {rolledBackIds.Count} migrations");

Migration History

Migrations are tracked in a special __migrations table:

Column	Type	Description
migration_id	TEXT	Unique timestamp-based migration ID
migration_name	TEXT	Human-readable migration name
applied_at	TEXT	Timestamp when migration was applied

You can query this table directly if needed:

SELECT * FROM __migrations ORDER BY applied_at DESC;

Best Practices

1. Never Modify Applied Migrations

Once a migration has been applied to production, never modify it. Instead, create a new migration to make changes:

❌ Don't do this:

// Modifying an already applied migration
public override void Up(MigrationBuilder builder)
{
    builder.CreateTable("users", t => {
        t.Integer("id").PrimaryKey();
        t.Text("name").NotNull();
        // Adding email column later - DON'T DO THIS!
        t.Text("email").NotNull();
    });
}

✅ Do this instead:

// Create a new migration
public override void Up(MigrationBuilder builder)
{
    builder.AddColumn("users", "email", "TEXT", nullable: false);
}

2. Always Implement Down()

Always implement the Down() method to allow rollbacks:

public override void Up(MigrationBuilder builder)
{
    builder.CreateTable("users", t => { /* ... */ });
}

public override void Down(MigrationBuilder builder)
{
    builder.DropTable("users");
}

3. Use Descriptive Names

Use clear, descriptive names for your migrations:

✅ Good:

CreateUsersTable
AddEmailIndexToUsers
AddPostStatusColumn

❌ Bad:

Migration1
UpdateDatabase
Fix

4. Keep Migrations Small

Break large schema changes into smaller, focused migrations:

✅ Good:

dotnet d1 migrations add CreateUsersTable
dotnet d1 migrations add CreatePostsTable
dotnet d1 migrations add AddUserPostsRelationship

❌ Bad:

dotnet d1 migrations add CreateAllTables

5. Test Rollbacks

Always test that your migrations can be rolled back:

# Apply migration
dotnet d1 database update

# Test rollback
dotnet d1 database rollback

# Reapply
dotnet d1 database update

6. Use Transactions Wisely

Each migration runs in its own context. For complex migrations, ensure operations are idempotent:

public override void Up(MigrationBuilder builder)
{
    // Use IF EXISTS for safety
    builder.DropTable("temp_table", ifExists: true);
    
    // Create table
    builder.CreateTable("new_table", t => { /* ... */ });
}

7. Handle Data Migrations Carefully

When renaming or restructuring columns, preserve data:

public override void Up(MigrationBuilder builder)
{
    // 1. Add new column
    builder.AddColumn("users", "full_name", "TEXT");
    
    // 2. Copy data
    builder.Sql("UPDATE users SET full_name = first_name || ' ' || last_name");
    
    // 3. Drop old columns
    builder.DropColumn("users", "first_name");
    builder.DropColumn("users", "last_name");
}

Common Patterns

Adding a Timestamp Column to Existing Table

public override void Up(MigrationBuilder builder)
{
    builder.AddColumn("users", "created_at", "TEXT", 
        nullable: false, 
        defaultValue: "CURRENT_TIMESTAMP");
}

public override void Down(MigrationBuilder builder)
{
    builder.DropColumn("users", "created_at");
}

Creating a Junction Table

public override void Up(MigrationBuilder builder)
{
    builder.CreateTable("user_roles", t =>
    {
        t.Integer("user_id").NotNull();
        t.Integer("role_id").NotNull();
        t.PrimaryKey("user_id", "role_id");
        t.ForeignKey("user_id", "users", "id", onDelete: "CASCADE");
        t.ForeignKey("role_id", "roles", "id", onDelete: "CASCADE");
    });
    
    builder.CreateIndex("idx_user_roles_user", "user_roles", new[] { "user_id" });
    builder.CreateIndex("idx_user_roles_role", "user_roles", new[] { "role_id" });
}

Renaming a Column with Data Preservation

public override void Up(MigrationBuilder builder)
{
    // SQLite requires a multi-step process
    builder.AddColumn("users", "email_address", "TEXT");
    builder.Sql("UPDATE users SET email_address = email");
    builder.DropColumn("users", "email");
    builder.RenameColumn("users", "email_address", "email");
}

Troubleshooting

Migration History Table

If you need to manually inspect or fix migration history:

-- View all applied migrations
SELECT * FROM __migrations ORDER BY applied_at;

-- Manually mark a migration as applied (use with caution!)
INSERT INTO __migrations (migration_id, migration_name, applied_at)
VALUES ('20241027120000', 'CreateUsersTable', datetime('now'));

-- Remove a migration from history (use with caution!)
DELETE FROM __migrations WHERE migration_id = '20241027120000';

Reset All Migrations

To completely reset your database (⚠️ this will delete all data):

-- Drop all tables
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS posts;
-- ... drop all your tables

-- Drop migration history
DROP TABLE IF EXISTS __migrations;

Then reapply all migrations:

dotnet d1 database update

Overview​

Installation​

Quick Start​

1. Create Your First Migration​

2. Apply Migrations​

3. List Applied Migrations​

4. Rollback Migrations​

Migration Builder API​

Creating Tables​

Column Types​

Column Modifiers​

Table Constraints​

Altering Tables​

Managing Indexes​

Dropping Tables​

Raw SQL​

CLI Tool​

Installation​

Commands​

Create a New Migration​

Scaffold from Database​

Code-First Model Diff​

List Migrations​

Apply Migrations​

Rollback Migrations​

Programmatic Usage​

Setting Up the Migration Runner​

Applying Migrations​

Rolling Back Migrations​

Migration History​

Best Practices​

1. Never Modify Applied Migrations​

2. Always Implement Down()​

3. Use Descriptive Names​

4. Keep Migrations Small​

5. Test Rollbacks​

6. Use Transactions Wisely​

7. Handle Data Migrations Carefully​

Common Patterns​

Adding a Timestamp Column to Existing Table​

Creating a Junction Table​

Renaming a Column with Data Preservation​

Troubleshooting​

Migration History Table​

Reset All Migrations​

See Also​

Overview

Installation

Quick Start

1. Create Your First Migration

2. Apply Migrations

3. List Applied Migrations

4. Rollback Migrations

Migration Builder API

Creating Tables

Column Types

Column Modifiers

Table Constraints

Altering Tables

Managing Indexes

Dropping Tables

Raw SQL

CLI Tool

Installation

Commands

Create a New Migration

Scaffold from Database

Code-First Model Diff

List Migrations

Apply Migrations

Rollback Migrations

Programmatic Usage

Setting Up the Migration Runner

Applying Migrations

Rolling Back Migrations

Migration History

Best Practices

1. Never Modify Applied Migrations

2. Always Implement Down()

3. Use Descriptive Names

4. Keep Migrations Small

5. Test Rollbacks

6. Use Transactions Wisely

7. Handle Data Migrations Carefully

Common Patterns

Adding a Timestamp Column to Existing Table

Creating a Junction Table

Renaming a Column with Data Preservation

Troubleshooting

Migration History Table

Reset All Migrations

See Also