DEV Community

Daniel Jonathan
Daniel Jonathan

Posted on

Getting Started with Database Migrations using FluentMigrator

#fluentmigrator #azure #sql #sqlserver

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
Enter fullscreen mode Exit fullscreen mode

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 )
Enter fullscreen mode Exit fullscreen mode

You write this:

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

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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=***"); }
Enter fullscreen mode Exit fullscreen mode

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;"
Enter fullscreen mode Exit fullscreen mode

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!
Enter fullscreen mode Exit fullscreen mode

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"); } }
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

<ItemGroup> <EmbeddedResource Include="Scripts\**\*.sql" /> </ItemGroup>
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Running and Validating

Start the entire stack:

docker compose up -d
Enter fullscreen mode Exit fullscreen mode

Validate migrations ran successfully:


# Check migration logs docker logs db-migrate
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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!

Top comments (0)