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

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

Component	Technology	Purpose
Database	SQL Server 2022	Data storage
Migrations	FluentMigrator	Schema versioning
Data Access	Dapper	High-performance SQL mapping
API	ASP.NET Core 8	HTTP endpoints
Orchestration	Docker Compose	Container management

---

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?

Benefit	Description
One command setup	docker compose up - done
Consistent environments	Same on your machine, teammate's machine, CI/CD
No local installs	No SQL Server, no SDK required
Isolated	Won't conflict with other projects
Production-like	Test exactly what you'll deploy

---

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

Before we containerize, here's how Dapper fits into the architecture:

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

Dapper is the micro-ORM that maps SQL queries to C# objects. Here's a quick example from the ProductRepository:

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<Product?> 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<Product, Category, Product>(
            sql,
            (product, category) => { product.Category = category; return product; },
            new { Id = id },
            splitOn: "Id"
        );

        return result.FirstOrDefault();
    }

    public async Task<int> 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<int>(sql, product);
    }
}

Key Dapper patterns:

• QueryAsync<T> - Returns collection
• QueryFirstOrDefaultAsync<T> - Returns single or null
• ExecuteScalarAsync<T> - Returns single value (like inserted ID)
• ExecuteAsync - Returns rows affected

Why Dapper?

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

Now let's containerize this stack!

---

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/sdk:8.0 AS build
WORKDIR /src

# Copy project files for restore
COPY CommonComps/CommonComps.csproj CommonComps/
COPY ProductWebAPI/ProductWebAPI.csproj ProductWebAPI/

# Restore dependencies
WORKDIR /src/ProductWebAPI
RUN dotnet restore

# Copy source code
WORKDIR /src
COPY CommonComps/ CommonComps/
COPY ProductWebAPI/ ProductWebAPI/

# Build and publish
WORKDIR /src/ProductWebAPI
RUN dotnet publish -c Release -o /app/publish

# Runtime image
FROM mcr.microsoft.com/dotnet/aspnet:8.0
WORKDIR /app
COPY --from=build /app/publish .

# Expose the port
EXPOSE 8080

# Environment variables
ENV ASPNETCORE_URLS=http://+:8080
ENV ASPNETCORE_ENVIRONMENT=Production

# Run the application
ENTRYPOINT ["dotnet", "ProductWebAPI.dll"]

Key Points:

• Multi-stage build - SDK for building, runtime for running (smaller image)
• Layer caching - Copy .csproj files first, then dotnet restore (dependencies cached)
• Port 8080 - ASP.NET Core 8+ default
• Production mode - Optimized for container environments

---

Step 2: Dockerfile for the Migrator

File: src/ProductWebAPI.Database/Dockerfile

# Use the official .NET 8 SDK image for building
FROM mcr.microsoft.com/dotnet/sdk:8.0 AS build
WORKDIR /src

# Copy project file
COPY ProductWebAPI.Database/ProductWebAPI.Database.csproj ProductWebAPI.Database/

# Restore dependencies
WORKDIR /src/ProductWebAPI.Database
RUN dotnet restore

# Copy source code
WORKDIR /src
COPY ProductWebAPI.Database/ ProductWebAPI.Database/

# Build and publish
WORKDIR /src/ProductWebAPI.Database
RUN dotnet publish -c Release -o /app/publish

# Runtime image
FROM mcr.microsoft.com/dotnet/runtime:8.0
WORKDIR /app
COPY --from=build /app/publish .

# Run the migration tool
ENTRYPOINT ["dotnet", "ProductWebAPI.Database.dll"]

Key Points:

• Uses runtime image (not aspnet) - no web server needed
• ENTRYPOINT runs migrations and exits
• Exit code 0 = success, non-zero = failure

---

Step 3: Docker Compose - The Orchestrator

File: docker-compose-productapi.yml

services:
  # SQL Server Database
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2022-latest
    container_name: sqlserver-dev
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=${SA_PASSWORD:-YourStrongPassword123!}
      - MSSQL_PID=Developer
    ports:
      - "1433:1433"
    volumes:
      - sqlserver-data:/var/opt/mssql
    networks:
      - productapi-network
    healthcheck:
      test: /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "${SA_PASSWORD:-YourStrongPassword123!}" -Q "SELECT 1" -C || exit 1
      interval: 10s
      timeout: 3s
      retries: 10
      start_period: 30s

  # Database Migrations Runner (FluentMigrator)
  db-migrate:
    build:
      context: ./src
      dockerfile: ProductWebAPI.Database/Dockerfile
    container_name: db-migrate
    environment:
      - ConnectionStrings__DefaultConnection=Server=sqlserver;Database=ProductDB;User Id=sa;Password=${SA_PASSWORD:-YourStrongPassword123!};TrustServerCertificate=True;
    depends_on:
      sqlserver:
        condition: service_healthy
    networks:
      - productapi-network
    restart: "no"

  # Product Web API (Dapper)
  product-api:
    build:
      context: ./src
      dockerfile: ProductWebAPI/Dockerfile
    container_name: product-api
    environment:
      - ASPNETCORE_ENVIRONMENT=Development
      - ASPNETCORE_URLS=http://+:8080
      - ConnectionStrings__DefaultConnection=Server=sqlserver;Database=ProductDB;User Id=sa;Password=${SA_PASSWORD:-YourStrongPassword123!};TrustServerCertificate=True;
    ports:
      - "5050:8080"
    depends_on:
      db-migrate:
        condition: service_completed_successfully
    networks:
      - productapi-network
    restart: unless-stopped

volumes:
  sqlserver-data:
    driver: local

networks:
  productapi-network:
    driver: bridge

---

Step 4: Testing the API

Docker Desktop View

API Testing

Endpoints available:

• GET /api/products - Get all products
• GET /api/products/{id} - Get product by ID
• POST /api/products - Create a product
• PUT /api/products/{id} - Update a product
• DELETE /api/products/{id} - Delete a product

Swagger UI: http://localhost:5050/swagger

---

The result:

# From zero to running API:
git clone <repo>
docker compose up -d

# That's it! 🎉

• ✅ SQL Server running in container
• ✅ Migrations applied automatically
• ✅ Dapper-powered API ready at http://localhost:5050
• ✅ Swagger UI at http://localhost:5050/swagger

Happy containerizing!