Database Setup

This architecture supports multiple database providers through a unified repository pattern. You can use SQL Server, MySQL, MariaDB, or MongoDB.

Configuration

Database Provider Selection

Configure your database provider in appsettings.json:

{
  "Configurations": {
    "UseDatabase": "sqlserver"
  },
  "ConnectionStrings": {
    "MongoConnection": "mongodb+srv://user:password@cluster.mongodb.net/DatabaseName",
    "SqlConnection": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=YourDatabase;Integrated Security=True;"
  }
}

Available options:

"sqlserver" - Microsoft SQL Server
"mysql" - MySQL
"mariadb" - MariaDB
"mongodb" - MongoDB

Reference: Template-API/appsettings.json:9-15

SQL Server Setup

Configure Connection String

Update the SQL connection string in appsettings.json:

{
  "Configurations": {
    "UseDatabase": "sqlserver"
  },
  "ConnectionStrings": {
    "SqlConnection": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=HybridDDDArchitecture;Integrated Security=True;"
  }
}

For Azure SQL:

"SqlConnection": "Server=tcp:yourserver.database.windows.net,1433;Database=YourDB;User ID=admin;Password=your_password;Encrypt=True;"

For Remote SQL Server:

"SqlConnection": "Server=192.168.1.100;Database=YourDB;User ID=sa;Password=your_password;"

Create DbContext

Your DbContext should inherit from DbContext and define entity sets:

using Domain.Entities;
using Microsoft.EntityFrameworkCore;

namespace Infrastructure.Repositories.Sql
{
    internal class StoreDbContext : DbContext
    {
        public DbSet<YourEntity> YourEntities { get; set; }

        public StoreDbContext(DbContextOptions<StoreDbContext> options) : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Configure table names and relationships
            modelBuilder.Entity<YourEntity>().ToTable("YourEntity");
            
            // Configure primary key
            modelBuilder.Entity<YourEntity>()
                .HasKey(e => e.Id);
            
            // Configure properties
            modelBuilder.Entity<YourEntity>()
                .Property(e => e.PropertyOne)
                .IsRequired()
                .HasMaxLength(200);
        }
    }
}

Reference: Infrastructure/Repositories/Sql/StoreDbContext.cs:10-31

Install EF Core Tools

Install Entity Framework Core tools globally:

dotnet tool install --global dotnet-ef

Or update existing installation:

dotnet tool update --global dotnet-ef

Create Initial Migration

Navigate to the Infrastructure project and create your first migration:

cd Infrastructure
dotnet ef migrations add InitialCreate --startup-project ../Template-API

This creates a Migrations folder with migration files:

<timestamp>_InitialCreate.cs - Migration operations
<timestamp>_InitialCreate.Designer.cs - Migration metadata
StoreDbContextModelSnapshot.cs - Current model state

Apply Migrations

Update the database with the migration:

dotnet ef database update --startup-project ../Template-API

Or apply programmatically in Program.cs:

public static void Main(string[] args)
{
    var host = CreateHostBuilder(args).Build();

    // Apply migrations on startup
    using (var scope = host.Services.CreateScope())
    {
        var dbContext = scope.ServiceProvider.GetRequiredService<StoreDbContext>();
        dbContext.Database.Migrate();
    }

    host.Run();
}

Add New Migrations (After Changes)

When you modify entities or add new ones:

# Add migration
dotnet ef migrations add AddNewProperty --startup-project ../Template-API

# Review the generated migration in Migrations folder

# Apply migration
dotnet ef database update --startup-project ../Template-API

Remove last migration (if not applied):

dotnet ef migrations remove --startup-project ../Template-API

MongoDB Setup

Configure MongoDB Connection

Update the MongoDB connection string in appsettings.json:

{
  "Configurations": {
    "UseDatabase": "mongodb"
  },
  "ConnectionStrings": {
    "MongoConnection": "mongodb+srv://username:password@cluster.mongodb.net/DatabaseName?retryWrites=true&w=majority"
  }
}

For local MongoDB:

"MongoConnection": "mongodb://localhost:27017/YourDatabase"

Reference: Template-API/appsettings.json:13

Create Entity Map

MongoDB requires explicit collection mapping:

using Domain.Entities;
using MongoDB.Bson.Serialization;

namespace Infrastructure.Repositories.Mongo.Maps
{
    internal class YourEntityMap
    {
        public static void Configure()
        {
            BsonClassMap.RegisterClassMap<YourEntity>(map =>
            {
                map.AutoMap();
                map.MapIdProperty(x => x.Id);
                map.MapProperty(x => x.PropertyOne).SetIsRequired(true);
                map.MapProperty(x => x.PropertyTwo);
                map.SetIgnoreExtraElements(true);
            });
        }
    }
}

// In Infrastructure/Registrations/InfrastructureRegistration.cs
public static IServiceCollection AddInfrastructureServices(this IServiceCollection services, IConfiguration configuration)
{
    var useDatabase = configuration["Configurations:UseDatabase"];

    if (useDatabase == "mongodb")
    {
        // Register MongoDB mappings
        YourEntityMap.Configure();
        
        // Register MongoDB context
        services.AddSingleton<IMongoClient>(sp =>
        {
            var connectionString = configuration.GetConnectionString("MongoConnection");
            return new MongoClient(connectionString);
        });
    }

    return services;
}

Repository Implementation

SQL Repository

using Application.Repositories;
using Core.Infraestructure.Repositories.EntityFrameworkCore;
using Domain.Entities;

namespace Infrastructure.Repositories.Sql
{
    internal class YourEntityRepository : EntityFrameworkRepository<YourEntity, string>, IYourEntityRepository
    {
        public YourEntityRepository(StoreDbContext context) : base(context)
        {
        }

        // Add custom query methods here
        public async Task<YourEntity> FindByPropertyAsync(string property)
        {
            return await Context.Set<YourEntity>()
                .FirstOrDefaultAsync(x => x.PropertyOne == property);
        }
    }
}

MongoDB Repository

using Application.Repositories;
using Core.Infraestructure.Repositories.MongoDb;
using Domain.Entities;
using MongoDB.Driver;

namespace Infrastructure.Repositories.Mongo
{
    internal class YourEntityRepository : MongoDbRepository<YourEntity>, IYourEntityRepository
    {
        public YourEntityRepository(IMongoClient client) : base(client, "DatabaseName", "YourEntityCollection")
        {
        }

        // Add custom query methods here
        public async Task<YourEntity> FindByPropertyAsync(string property)
        {
            var filter = Builders<YourEntity>.Filter.Eq(x => x.PropertyOne, property);
            return await Collection.Find(filter).FirstOrDefaultAsync();
        }
    }
}

Common Migration Commands

Create Migration

# From Infrastructure directory
dotnet ef migrations add MigrationName --startup-project ../Template-API

Naming conventions:

InitialCreate - First migration
AddPropertyToEntity - Adding properties
CreateTableName - New tables
RemoveColumnName - Removing columns

Apply Migrations

# Apply all pending migrations
dotnet ef database update --startup-project ../Template-API

# Apply specific migration
dotnet ef database update MigrationName --startup-project ../Template-API

# Rollback to specific migration
dotnet ef database update PreviousMigrationName --startup-project ../Template-API

# Rollback all migrations
dotnet ef database update 0 --startup-project ../Template-API

View Migrations

# List all migrations
dotnet ef migrations list --startup-project ../Template-API

# Generate SQL script
dotnet ef migrations script --startup-project ../Template-API

# Generate SQL for specific migration
dotnet ef migrations script PreviousMigration TargetMigration --startup-project ../Template-API

Remove Migration

# Remove last migration (must not be applied)
dotnet ef migrations remove --startup-project ../Template-API

# Force remove (use with caution)
dotnet ef migrations remove --force --startup-project ../Template-API

Database Initialization

Seed Data

Create a seeding service for initial data:

public class DatabaseSeeder
{
    private readonly StoreDbContext _context;

    public DatabaseSeeder(StoreDbContext context)
    {
        _context = context;
    }

    public async Task SeedAsync()
    {
        // Ensure database is created
        await _context.Database.EnsureCreatedAsync();

        // Check if data already exists
        if (await _context.YourEntities.AnyAsync())
            return;

        // Seed initial data
        var entities = new[]
        {
            new YourEntity("Initial 1", 1),
            new YourEntity("Initial 2", 2)
        };

        await _context.YourEntities.AddRangeAsync(entities);
        await _context.SaveChangesAsync();
    }
}

Call Seeder in Program.cs

public static void Main(string[] args)
{
    var host = CreateHostBuilder(args).Build();

    using (var scope = host.Services.CreateScope())
    {
        var context = scope.ServiceProvider.GetRequiredService<StoreDbContext>();
        var seeder = new DatabaseSeeder(context);
        seeder.SeedAsync().Wait();
    }

    host.Run();
}

Troubleshooting

Migration Build Failed

Error: Build failed before running migrationsSolution:

Ensure your project compiles: dotnet build
Check that startup project is specified
Verify all NuGet packages are restored

dotnet restore
dotnet build
dotnet ef migrations add MigrationName --startup-project ../Template-API

Connection String Issues

Error: Cannot connect to databaseSolution:

Verify connection string format
Check database server is running
Test connection with SQL Management Studio or similar tool
Ensure firewall allows connection
For LocalDB, verify it’s installed: sqllocaldb info

Entity Not Included in Model

Error: Entity type not included in the modelSolution:

Add DbSet to DbContext:

public DbSet<YourEntity> YourEntities { get; set; }

Configure in OnModelCreating:

modelBuilder.Entity<YourEntity>().ToTable("YourEntity");

Create new migration

Next Steps

Creating Entities

Learn how to create domain entities

Event Handling

Implement domain and integration events

Get Started

Architecture

Core Modules

Guides

Database Setup

Database Setup

Configuration

Database Provider Selection

SQL Server Setup

MongoDB Setup

Repository Implementation

SQL Repository

MongoDB Repository

Common Migration Commands

Database Initialization

Seed Data

Call Seeder in Program.cs

Troubleshooting

Next Steps

Creating Entities

Event Handling

Build docs developers (and LLMs) love

Get Started

Architecture

Core Modules

Guides

​Database Setup

​Configuration

​Database Provider Selection

​SQL Server Setup

​MongoDB Setup

​Repository Implementation

​SQL Repository

​MongoDB Repository

​Common Migration Commands

​Database Initialization

​Seed Data

​Call Seeder in Program.cs

​Troubleshooting

​Next Steps

Creating Entities

Event Handling

Build docs developers (and LLMs) love

Database Setup

Configuration

Database Provider Selection

SQL Server Setup

MongoDB Setup

Repository Implementation

SQL Repository

MongoDB Repository

Common Migration Commands

Database Initialization

Seed Data

Call Seeder in Program.cs

Troubleshooting

Next Steps