Create EF Core migrations for schema management

## Parent Epic Part of #58 - Migrate to Entity Framework Core ## Description Set up EF Core migrations for proper database schema versioning and management across all supported providers. ## Initial Migration Create the initial migration with the complete schema: ```bash # Create initial migration dotnet ef migrations add InitialCreate --output-dir Data/Migrations # Generate SQL scripts for each provider (for review) dotnet ef migrations script --output migrations-sqlite.sql -- --provider SQLite dotnet ef migrations script --output migrations-postgres.sql -- --provider PostgreSQL ``` ## Migration Strategy ### Automatic Migrations (Development/Simple Deployments) ```csharp // Program.cs - Apply pending migrations on startup using (var scope = app.Services.CreateScope()) { var db = scope.ServiceProvider.GetRequiredService<CastrDbContext>(); await db.Database.MigrateAsync(); } ``` ### Manual Migrations (Production) ```bash # Generate SQL script for deployment dotnet ef migrations script --idempotent --output deploy.sql # Or use bundle (recommended) dotnet ef migrations bundle --output efbundle ./efbundle --connection "your-connection-string" ``` ## Configuration for Migrations ```csharp // Data/DesignTimeDbContextFactory.cs public class DesignTimeDbContextFactory : IDesignTimeDbContextFactory<CastrDbContext> { public CastrDbContext CreateDbContext(string[] args) { var provider = Environment.GetEnvironmentVariable("EF_PROVIDER") ?? "SQLite"; var connectionString = Environment.GetEnvironmentVariable("EF_CONNECTION") ?? "Data Source=castr-design.db"; var optionsBuilder = new DbContextOptionsBuilder<CastrDbContext>(); switch (provider) { case "PostgreSQL": optionsBuilder.UseNpgsql(connectionString); break; case "SqlServer": optionsBuilder.UseSqlServer(connectionString); break; case "MariaDB": var serverVersion = ServerVersion.AutoDetect(connectionString); optionsBuilder.UseMySql(connectionString, serverVersion); break; default: optionsBuilder.UseSqlite(connectionString); break; } return new CastrDbContext(optionsBuilder.Options); } } ``` ## Data Migration from Legacy Databases Create a one-time data migration service: ```csharp // Services/LegacyDataMigrationService.cs public class LegacyDataMigrationService { public async Task MigrateFromLegacyDatabasesAsync( Dictionary<string, PodcastFeedConfig> legacyFeeds) { // 1. Check if migration already completed // 2. Read data from legacy per-feed databases // 3. Import to new EF Core database // 4. Mark migration as complete } } ``` ## File Structure ``` Castr/Data/ ├── Migrations/ │ ├── 20240118000000_InitialCreate.cs │ ├── 20240118000000_InitialCreate.Designer.cs │ └── CastrDbContextModelSnapshot.cs └── DesignTimeDbContextFactory.cs ``` ## Tasks - [ ] Create DesignTimeDbContextFactory for migrations - [ ] Generate initial migration - [ ] Test migration on all providers (SQLite, PostgreSQL, SQL Server, MariaDB) - [ ] Add automatic migration option for development - [ ] Create data migration service for legacy databases - [ ] Document migration procedures in README - [ ] Add migration bundle to CI/CD pipeline ## Acceptance Criteria - [ ] `dotnet ef migrations add` works correctly - [ ] Migrations apply cleanly on all providers - [ ] Legacy data can be migrated to new schema - [ ] Rollback migrations work correctly - [ ] Migration scripts are idempotent

joezombie commented

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

(Migrated from github.com)

Parent Epic

Part of #58 - Migrate to Entity Framework Core

Description

Set up EF Core migrations for proper database schema versioning and management across all supported providers.

Initial Migration

Create the initial migration with the complete schema:

# Create initial migration
dotnet ef migrations add InitialCreate --output-dir Data/Migrations

# Generate SQL scripts for each provider (for review)
dotnet ef migrations script --output migrations-sqlite.sql -- --provider SQLite
dotnet ef migrations script --output migrations-postgres.sql -- --provider PostgreSQL

Migration Strategy

Automatic Migrations (Development/Simple Deployments)

// Program.cs - Apply pending migrations on startup
using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<CastrDbContext>();
    await db.Database.MigrateAsync();
}

Manual Migrations (Production)

# Generate SQL script for deployment
dotnet ef migrations script --idempotent --output deploy.sql

# Or use bundle (recommended)
dotnet ef migrations bundle --output efbundle
./efbundle --connection "your-connection-string"

Configuration for Migrations

// Data/DesignTimeDbContextFactory.cs
public class DesignTimeDbContextFactory : IDesignTimeDbContextFactory<CastrDbContext>
{
    public CastrDbContext CreateDbContext(string[] args)
    {
        var provider = Environment.GetEnvironmentVariable("EF_PROVIDER") ?? "SQLite";
        var connectionString = Environment.GetEnvironmentVariable("EF_CONNECTION") 
            ?? "Data Source=castr-design.db";
        
        var optionsBuilder = new DbContextOptionsBuilder<CastrDbContext>();
        
        switch (provider)
        {
            case "PostgreSQL":
                optionsBuilder.UseNpgsql(connectionString);
                break;
            case "SqlServer":
                optionsBuilder.UseSqlServer(connectionString);
                break;
            case "MariaDB":
                var serverVersion = ServerVersion.AutoDetect(connectionString);
                optionsBuilder.UseMySql(connectionString, serverVersion);
                break;
            default:
                optionsBuilder.UseSqlite(connectionString);
                break;
        }
        
        return new CastrDbContext(optionsBuilder.Options);
    }
}

Data Migration from Legacy Databases

Create a one-time data migration service:

// Services/LegacyDataMigrationService.cs
public class LegacyDataMigrationService
{
    public async Task MigrateFromLegacyDatabasesAsync(
        Dictionary<string, PodcastFeedConfig> legacyFeeds)
    {
        // 1. Check if migration already completed
        // 2. Read data from legacy per-feed databases
        // 3. Import to new EF Core database
        // 4. Mark migration as complete
    }
}

File Structure

Castr/Data/
├── Migrations/
│   ├── 20240118000000_InitialCreate.cs
│   ├── 20240118000000_InitialCreate.Designer.cs
│   └── CastrDbContextModelSnapshot.cs
└── DesignTimeDbContextFactory.cs

Tasks

Create DesignTimeDbContextFactory for migrations
Generate initial migration
Test migration on all providers (SQLite, PostgreSQL, SQL Server, MariaDB)
Add automatic migration option for development
Create data migration service for legacy databases
Document migration procedures in README
Add migration bundle to CI/CD pipeline

Acceptance Criteria

dotnet ef migrations add works correctly
Migrations apply cleanly on all providers
Legacy data can be migrated to new schema
Rollback migrations work correctly
Migration scripts are idempotent

Rows
Columns

Create EF Core migrations for schema management #63