Dockerizing Your Web API with SQL Server, Dapper, and FluentMigrator

Source: Dev.to

Introduction

In Parts 1 and 2 we covered FluentMigrator for schema management and CI/CD automation. In this part we’ll dockerize the entire stack—SQL Server, migrations, and the Dapper‑powered Web API—so you can spin up a complete development environment with a single command.

The Complete Stack

What We’re Building

┌─────────────────────────────────────────────────────────────┐
│                    docker compose up                        │
└─────────────────────────────────────────────────────────────┘
                              │
              ┌───────────────┼───────────────┐
              ▼               ▼               ▼
        ┌──────────┐   ┌──────────┐   ┌──────────┐
        │ sqlserver│   │db-migrate│   │product‑api│
        │  :1433   │   │ (runs    │   │  :5050   │
        │          │◄──│  once)   │──►│          │
        └──────────┘   └──────────┘   └──────────┘
              │               │               │
              └───────────────┴───────────────┘
                              │
                     productapi‑network

The flow

1. sqlserver starts and waits until healthy.
2. db‑migrate runs FluentMigrator migrations (once), then exits.
3. product‑api starts only after migrations succeed.

Why Dockerize?

Project Structure

ProductApi/
├── docker-compose.yml              # Orchestrates everything
├── src/
│   ├── ProductWebAPI/
│   │   ├── Dockerfile              # API container
│   │   ├── Controllers/
│   │   └── Services/
│   ├── ProductWebAPI.Database/
│   │   ├── Dockerfile              # Migration runner
│   │   └── Migrations/
│   └── CommonComps/
│       ├── Models/                 # Domain models
│       └── Repositories/          # Dapper implementations

Quick Recap: Dapper in This Stack

┌─────────────────────────────────────────────────────────────┐
│                    ProductsController                        │
│                    (HTTP endpoints)                          │
└─────────────────────────┬───────────────────────────────────┘
                          │
┌─────────────────────────▼───────────────────────────────────┐
│                    ProductService                            │
│                    (Business logic)                          │
└─────────────────────────┬───────────────────────────────────┘
                          │
┌─────────────────────────▼───────────────────────────────────┐
│                 ProductRepository (Dapper)                   │
│          Write SQL → Get C# objects back                     │
└─────────────────────────┬───────────────────────────────────┘
                          │
┌─────────────────────────▼───────────────────────────────────┐
│              SQL Server (FluentMigrator‑managed)             │
└─────────────────────────────────────────────────────────────┘

Example Repository

public class ProductRepository : IProductRepository
{
    private readonly string _connectionString;

    public ProductRepository(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection")
            ?? throw new ArgumentNullException("Connection string not found");
    }

    private IDbConnection CreateConnection() => new SqlConnection(_connectionString);

    public async Task GetByIdAsync(int id)
    {
        const string sql = @"
            SELECT p.*, c.*
            FROM Products p
            INNER JOIN Categories c ON p.CategoryId = c.Id
            WHERE p.Id = @Id";

        using var connection = CreateConnection();

        var result = await connection.QueryAsync(
            sql,
            (product, category) => { product.Category = category; return product; },
            new { Id = id },
            splitOn: "Id"
        );

        return result.FirstOrDefault();
    }

    public async Task CreateAsync(Product product)
    {
        const string sql = @"
            INSERT INTO Products (Name, SKU, Description, Price, CategoryId, IsActive, CreatedAt)
            OUTPUT INSERTED.Id
            VALUES (@Name, @SKU, @Description, @Price, @CategoryId, @IsActive, GETUTCDATE())";

        using var connection = CreateConnection();
        return await connection.ExecuteScalarAsync(sql, product);
    }
}

Key Dapper Patterns

• QueryAsync – Returns a collection.
• QueryFirstOrDefaultAsync – Returns a single item or null.
• ExecuteScalarAsync – Returns a single value (e.g., inserted ID).
• ExecuteAsync – Returns the number of rows affected.

Why Dapper?

• ⚡ Near raw ADO.NET performance.
• 💪 Full control over SQL.
• 📦 Lightweight (no heavy ORM overhead).

Step 1: Dockerfile for the Web API

File: src/ProductWebAPI/Dockerfile

# Use the official .NET 8 SDK image for building
FROM mcr.microsoft.com/dotnet