Add multi-database provider configuration

## Parent Epic Part of #58 - Migrate to Entity Framework Core ## Description Implement configuration system to support multiple database providers (SQLite, PostgreSQL, SQL Server, MariaDB) with runtime selection. ## Configuration Schema ### appsettings.json ```json { "Database": { "Provider": "SQLite", "ConnectionString": "Data Source=/data/castr.db" } } ``` ### Environment Variables ```bash # Override via environment (Docker-friendly) Database__Provider=PostgreSQL Database__ConnectionString=Host=db;Database=castr;Username=castr;Password=secret ``` ### Provider Options | Provider | Value | Connection String Example | |----------|-------|---------------------------| | SQLite | `SQLite` | `Data Source=/data/castr.db` | | PostgreSQL | `PostgreSQL` | `Host=localhost;Database=castr;Username=user;Password=pass` | | SQL Server | `SqlServer` | `Server=localhost;Database=castr;User Id=sa;Password=pass;TrustServerCertificate=true` | | MariaDB/MySQL | `MariaDB` | `Server=localhost;Database=castr;User=user;Password=pass` | ## Configuration Model ```csharp // Models/DatabaseConfig.cs public class DatabaseConfig { public const string SectionName = "Database"; public DatabaseProvider Provider { get; set; } = DatabaseProvider.SQLite; public string ConnectionString { get; set; } = "Data Source=/data/castr.db"; } public enum DatabaseProvider { SQLite, PostgreSQL, SqlServer, MariaDB } ``` ## DbContext Factory ```csharp // Data/DatabaseServiceExtensions.cs public static class DatabaseServiceExtensions { public static IServiceCollection AddCastrDatabase( this IServiceCollection services, IConfiguration configuration) { var dbConfig = configuration .GetSection(DatabaseConfig.SectionName) .Get<DatabaseConfig>() ?? new DatabaseConfig(); services.AddDbContext<CastrDbContext>(options => { switch (dbConfig.Provider) { case DatabaseProvider.SQLite: options.UseSqlite(dbConfig.ConnectionString); break; case DatabaseProvider.PostgreSQL: options.UseNpgsql(dbConfig.ConnectionString); break; case DatabaseProvider.SqlServer: options.UseSqlServer(dbConfig.ConnectionString); break; case DatabaseProvider.MariaDB: var serverVersion = ServerVersion.AutoDetect(dbConfig.ConnectionString); options.UseMySql(dbConfig.ConnectionString, serverVersion); break; default: throw new ArgumentException($"Unsupported database provider: {dbConfig.Provider}"); } // Enable sensitive data logging in development #if DEBUG options.EnableSensitiveDataLogging(); options.EnableDetailedErrors(); #endif }); return services; } } ``` ## Program.cs Integration ```csharp // Program.cs builder.Services.AddCastrDatabase(builder.Configuration); ``` ## Docker Compose Examples ### SQLite (default) ```yaml services: castr: environment: - Database__Provider=SQLite - Database__ConnectionString=Data Source=/data/castr.db volumes: - castr-data:/data ``` ### PostgreSQL ```yaml services: castr: environment: - Database__Provider=PostgreSQL - Database__ConnectionString=Host=db;Database=castr;Username=castr;Password=secret depends_on: - db db: image: postgres:16-alpine environment: - POSTGRES_DB=castr - POSTGRES_USER=castr - POSTGRES_PASSWORD=secret volumes: - postgres-data:/var/lib/postgresql/data ``` ## Tasks - [ ] Create DatabaseConfig model and enum - [ ] Implement AddCastrDatabase extension method - [ ] Add provider-specific configuration options - [ ] Update appsettings.json with new schema - [ ] Update docker-compose.yml with examples - [ ] Update CONFIGURATION.md documentation - [ ] Add connection string validation - [ ] Handle provider-specific quirks (e.g., MySQL case sensitivity) ## Acceptance Criteria - [ ] Can switch providers via configuration - [ ] Works with environment variable overrides - [ ] Clear error messages for invalid configuration - [ ] Documentation updated with all providers - [ ] Docker examples for each provider

joezombie commented

2026-01-18 22:30:16 +00:00

(Migrated from github.com)

Parent Epic

Part of #58 - Migrate to Entity Framework Core

Description

Implement configuration system to support multiple database providers (SQLite, PostgreSQL, SQL Server, MariaDB) with runtime selection.

Configuration Schema

appsettings.json

{
  "Database": {
    "Provider": "SQLite",
    "ConnectionString": "Data Source=/data/castr.db"
  }
}

Environment Variables

# Override via environment (Docker-friendly)
Database__Provider=PostgreSQL
Database__ConnectionString=Host=db;Database=castr;Username=castr;Password=secret

Provider Options

Provider	Value	Connection String Example
SQLite	`SQLite`	`Data Source=/data/castr.db`
PostgreSQL	`PostgreSQL`	`Host=localhost;Database=castr;Username=user;Password=pass`
SQL Server	`SqlServer`	`Server=localhost;Database=castr;User Id=sa;Password=pass;TrustServerCertificate=true`
MariaDB/MySQL	`MariaDB`	`Server=localhost;Database=castr;User=user;Password=pass`

Configuration Model

// Models/DatabaseConfig.cs
public class DatabaseConfig
{
    public const string SectionName = "Database";
    
    public DatabaseProvider Provider { get; set; } = DatabaseProvider.SQLite;
    public string ConnectionString { get; set; } = "Data Source=/data/castr.db";
}

public enum DatabaseProvider
{
    SQLite,
    PostgreSQL,
    SqlServer,
    MariaDB
}

DbContext Factory

// Data/DatabaseServiceExtensions.cs
public static class DatabaseServiceExtensions
{
    public static IServiceCollection AddCastrDatabase(
        this IServiceCollection services,
        IConfiguration configuration)
    {
        var dbConfig = configuration
            .GetSection(DatabaseConfig.SectionName)
            .Get<DatabaseConfig>() ?? new DatabaseConfig();
        
        services.AddDbContext<CastrDbContext>(options =>
        {
            switch (dbConfig.Provider)
            {
                case DatabaseProvider.SQLite:
                    options.UseSqlite(dbConfig.ConnectionString);
                    break;
                    
                case DatabaseProvider.PostgreSQL:
                    options.UseNpgsql(dbConfig.ConnectionString);
                    break;
                    
                case DatabaseProvider.SqlServer:
                    options.UseSqlServer(dbConfig.ConnectionString);
                    break;
                    
                case DatabaseProvider.MariaDB:
                    var serverVersion = ServerVersion.AutoDetect(dbConfig.ConnectionString);
                    options.UseMySql(dbConfig.ConnectionString, serverVersion);
                    break;
                    
                default:
                    throw new ArgumentException($"Unsupported database provider: {dbConfig.Provider}");
            }
            
            // Enable sensitive data logging in development
            #if DEBUG
            options.EnableSensitiveDataLogging();
            options.EnableDetailedErrors();
            #endif
        });
        
        return services;
    }
}

Program.cs Integration

// Program.cs
builder.Services.AddCastrDatabase(builder.Configuration);

Docker Compose Examples

SQLite (default)

services:
  castr:
    environment:
      - Database__Provider=SQLite
      - Database__ConnectionString=Data Source=/data/castr.db
    volumes:
      - castr-data:/data

PostgreSQL

services:
  castr:
    environment:
      - Database__Provider=PostgreSQL
      - Database__ConnectionString=Host=db;Database=castr;Username=castr;Password=secret
    depends_on:
      - db
  db:
    image: postgres:16-alpine
    environment:
      - POSTGRES_DB=castr
      - POSTGRES_USER=castr
      - POSTGRES_PASSWORD=secret
    volumes:
      - postgres-data:/var/lib/postgresql/data

Tasks

Create DatabaseConfig model and enum
Implement AddCastrDatabase extension method
Add provider-specific configuration options
Update appsettings.json with new schema
Update docker-compose.yml with examples
Update CONFIGURATION.md documentation
Add connection string validation
Handle provider-specific quirks (e.g., MySQL case sensitivity)

Acceptance Criteria

Can switch providers via configuration
Works with environment variable overrides
Clear error messages for invalid configuration
Documentation updated with all providers
Docker examples for each provider

2026-01-26 22:25:40 +00:00

Completed: Added multi-database provider support for SQLite, PostgreSQL, SQL Server, and MySQL/MariaDB via DatabaseConfiguration. Includes proper error handling for invalid provider names and MySQL auto-detection failures.

Rows
Columns

Add multi-database provider configuration #62