Getting Started with Database Migrations using FluentMigrator

Why Your Database Schema Deserves Version Control

Your code is tracked, reviewed, and tested.
Your database schema? Not always.

If you’ve ever found yourself unsure whether a schema change was applied consistently across environments, this guide will help.

---

The Problem

Your application code lives in Git, but your database schema lives... where exactly?

When you:

• Add a new column
• Create a new table
• Modify an index

How do you ensure every developer and every environment has the same schema?

The old way: Scattered SQL scripts, manual execution, fingers crossed.

The better way: Migrations as code—version-controlled, repeatable, and reviewable.

---

What Are Database Migrations?

Think of migrations as "Git commits for your database schema."

Each migration is a numbered file that describes a single change:

Migration 001: Create Users table
Migration 002: Create Products table
Migration 003: Add email column to Users
Migration 004: Create index on Products.SKU

Each migration can be:

• Committed to Git
• Code reviewed
• Applied in order
• Rolled back if needed

---

Enter FluentMigrator

FluentMigrator is a .NET library that lets you write database migrations in C# instead of SQL.

Instead of this:

CREATE TABLE Products (
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(200) NOT NULL,
    Price DECIMAL(18,2) NOT NULL
)

You write this:

Create.Table("Products")
    .WithColumn("Id").AsInt32().PrimaryKey().Identity()
    .WithColumn("Name").AsString(200).NotNullable()
    .WithColumn("Price").AsDecimal(18, 2).NotNullable();

Benefits:

• Type-safe (compile-time checks)
• Database-agnostic (works with SQL Server, PostgreSQL, MySQL, etc.)
• Easy to test
• Familiar C# syntax

---

Real-World Example: ProductWebAPI with FluentMigrator

Let me show you a real production example from the ProductWebAPI project.

Step 1: Create a Migration Project

dotnet new console -n ProductWebAPI.Database
cd ProductWebAPI.Database
dotnet add package FluentMigrator
dotnet add package FluentMigrator.Runner
dotnet add package FluentMigrator.Runner.SqlServer
dotnet add package Microsoft.Data.SqlClient

Project Structure:

ProductWebAPI.Database/
├── ProductWebAPI.Database.csproj
├── Program.cs                    # Migration runner
├── Dockerfile                    # Docker support
├── Migrations/
│   ├── M001_CreateInitialSchema.cs
│   ├── M002_SeedInitialData.cs
│   └── M003_CreateViews.cs
└── Scripts/
    └── Views/
        ├── vw_ProductSummary.sql
        └── vw_CategoryProductCount.sql

Step 2: Write Your First Migration

Here's the actual Migration 1 from ProductWebAPI - it creates a complete e-commerce schema:

File: Migrations/M001_CreateInitialSchema.cs

What this creates:

• 3 tables: Categories, Products, ProductMetadata
• 2 foreign keys: Products → Categories, ProductMetadata → Products (with cascade)
• 3 indexes: For query performance
• 1 unique constraint: Prevents duplicate metadata keys per product

Step 3: Create the Migration Runner

Here's the actual Program.cs from ProductWebAPI.Database:

// Helper: Ensure database exists
static void EnsureDatabaseExists(string connectionString)
{
    var builder = new Microsoft.Data.SqlClient.SqlConnectionStringBuilder(connectionString);
    var databaseName = builder.InitialCatalog;
    builder.InitialCatalog = "master";

    using var connection = new Microsoft.Data.SqlClient.SqlConnection(builder.ConnectionString);
    connection.Open();

    using var command = connection.CreateCommand();
    command.CommandText = $@"
        IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'{databaseName}')
        BEGIN
            CREATE DATABASE [{databaseName}]
        END";
    command.ExecuteNonQuery();

    Console.WriteLine($"Database '{databaseName}' is ready.");
}

// Helper: Mask password in connection string
static string MaskConnectionString(string connectionString)
{
    return System.Text.RegularExpressions.Regex.Replace(
        connectionString,
        @"Password=([^;]+)",
        "Password=***");
}

Features:

• ✅ Auto-creates database if missing
• ✅ Accepts connection string from args or environment
• ✅ Masks password in console output
• ✅ Proper error handling with exit codes
• ✅ Clear logging

Step 4: Run It!

# Run locally
dotnet run

# Or specify connection string
dotnet run "Server=localhost;Database=ProductDB;User Id=sa;Password=Pass123!;TrustServerCertificate=True;"

Output:

=== Database Migrator ===
Target Database: Server=localhost,1433;Database=ProductDB;User Id=sa;Password=***;TrustServerCertificate=True;
Creating database if it doesn't exist...
Database 'ProductDB' is ready.
Running migrations...
20251128001: CreateInitialSchema migrating
20251128001: CreateInitialSchema migrated
✅ Migrations completed successfully!

Your database now has a complete e-commerce schema!

---

More Real Examples from ProductWebAPI

Migration 2: Seeding Initial Data

Here's the actual Migration 2 that seeds the database with initial categories and products:

File: Migrations/M002_SeedInitialData.cs

Migration 3: Creating SQL Views with Embedded Scripts

Here's how to create SQL Server views using FluentMigrator with embedded SQL files:

File: Migrations/M003_CreateViews.cs

using FluentMigrator;

namespace ProductWebAPI.Database.Migrations;

[Migration(20251128003)]
public class CreateViews : Migration
{
    public override void Up()
    {
        // Execute embedded SQL scripts
        Execute.EmbeddedScript("ProductWebAPI.Database.Scripts.Views.vw_ProductSummary.sql");
        Execute.EmbeddedScript("ProductWebAPI.Database.Scripts.Views.vw_CategoryProductCount.sql");
    }

    public override void Down()
    {
        Execute.Sql("DROP VIEW IF EXISTS vw_ProductSummary");
        Execute.Sql("DROP VIEW IF EXISTS vw_CategoryProductCount");
    }
}

File: Scripts/Views/vw_ProductSummary.sql

CREATE VIEW vw_ProductSummary AS
SELECT
    p.Id,
    p.Name AS ProductName,
    p.SKU,
    p.Description,
    p.Price,
    c.Id AS CategoryId,
    c.Name AS CategoryName,
    CASE
        WHEN p.Price > 500 THEN 'Premium'
        WHEN p.Price > 100 THEN 'Standard'
        ELSE 'Budget'
    END AS PriceTier,
    p.IsActive,
    p.CreatedAt,
    p.UpdatedAt
FROM Products p
INNER JOIN Categories c ON p.CategoryId = c.Id
WHERE p.IsActive = 1

File: Scripts/Views/vw_CategoryProductCount.sql

CREATE VIEW vw_CategoryProductCount AS
SELECT
    c.Id AS CategoryId,
    c.Name AS CategoryName,
    c.Description,
    COUNT(p.Id) AS TotalProducts,
    COUNT(CASE WHEN p.IsActive = 1 THEN 1 END) AS ActiveProducts,
    COUNT(CASE WHEN p.IsActive = 0 THEN 1 END) AS InactiveProducts,
    ISNULL(SUM(p.Price), 0) AS TotalInventoryValue,
    ISNULL(AVG(p.Price), 0) AS AveragePrice,
    c.CreatedAt AS CategoryCreatedAt
FROM Categories c
LEFT JOIN Products p ON c.Id = p.CategoryId
GROUP BY c.Id, c.Name, c.Description, c.CreatedAt

Don't forget to mark SQL files as embedded resources in your .csproj:

<ItemGroup>
  <EmbeddedResource Include="Scripts\**\*.sql" />
</ItemGroup>

---

Best Practices

✅ Do

1. One change per migration - Keep it focused
2. Always write Down() - Enable rollbacks
3. Use descriptive names - M003_AddEmailToUsers not M003_Update
4. Use version numbers - YYYYMMDD + sequence (e.g., 20251128001)
5. Test before committing - Run locally first

❌ Don't

1. Modify existing migrations - Create new ones instead
2. Delete migrations - They're your history
3. Skip Down() methods - You'll need rollbacks eventually
4. Use hard-coded data - Use configuration or seed migrations

---

Running in Docker with Validation

Here's the actual Docker setup from ProductWebAPI that runs migrations automatically.

Dockerfile for the Migrator

File: ProductWebAPI.Database/Dockerfile

Complete Docker Compose Setup

File: docker-compose.yml

Key features:

• ✅ Health checks - SQL Server must be ready before migrations run
• ✅ Ordered startup - sqlserver → db-migrate → product-api
• ✅ Run-once migrations - restart: "no" prevents re-running

Startup Sequence

1. sqlserver        → Starts, healthcheck validates SQL is ready
2. db-migrate       → Runs all migrations, exits with code 0

Running and Validating

Start the entire stack:

docker compose up -d

Validate migrations ran successfully:

# Check migration logs
docker logs db-migrate

---

Understanding the VersionInfo Table

This is critical: FluentMigrator automatically creates a VersionInfo table to track which migrations have been executed.

How It Works

When you run migrations, FluentMigrator:

1. Creates a VersionInfo table (if it doesn't exist)
2. Records each migration's version number after successful execution
3. Skips migrations that already exist in VersionInfo on subsequent runs

Query the version table:

bash
# Connect to SQL Server
docker exec -it sqlserver-dev /opt/mssql-tools18/bin/sqlcmd \
  -S localhost -U sa -P "YourStrongPassword123!" -C

# Check migration history
SELECT Version, AppliedOn, Description
FROM VersionInfo
ORDER BY AppliedOn;
GO

Example output:

Version        AppliedOn                  Description
-------------- -------------------------- ----------------------------------
20251128001    2025-11-28 10:15:23.123    CreateInitialSchema
20251128002    2025-11-28 10:15:24.456    SeedInitialData
20251128003    2025-11-28 10:15:25.789    CreateViews

Why This Matters

Prevents duplicate execution:

• Run dotnet run multiple times → Only new migrations execute
• Safe to run in CI/CD repeatedly → No data duplication
• Rolling deployments work correctly → Each instance checks VersionInfo

Manual override (use with caution):

If you need to re-run a migration (development only):

sql
-- Remove a migration from history (THIS WILL RE-RUN IT)
DELETE FROM VersionInfo WHERE Version = 20251128002;

Production rule: Never delete from VersionInfo in production. Always create a new migration to fix issues.

---

What's Next?

You now have:

• ✅ Version-controlled database schema
• ✅ Repeatable deployments
• ✅ Rollback capability
• ✅ Team collaboration without conflicts

Next steps:

1. Add FluentMigrator to your existing project
2. Create migrations for your current schema
3. Set up automated migrations in CI/CD (see Part 2)

---

Key Takeaways

• Database migrations are version control for your schema
• FluentMigrator makes migrations type-safe and database-agnostic
• Each migration has Up() and Down() methods
• Version numbers determine execution order
• Migrations work great with Docker and CI/CD

Stop worrying about "Did I update the database?" and start treating your schema as code.

Happy migrating!