Epic: Migrate to Entity Framework Core with multi-database support #58

New issue

Open

opened 2026-01-18 22:28:55 +00:00 by joezombie · 1 comment

joezombie commented

2026-01-18 22:28:55 +00:00

(Migrated from github.com)

Overview

Migrate from raw SQL with Microsoft.Data.Sqlite to Entity Framework Core, supporting multiple database providers:

SQLite (default, for simple deployments)
PostgreSQL (recommended for production)
Microsoft SQL Server
MariaDB/MySQL

Current State

2 separate database systems: Central database + per-feed databases
Raw SQL: All queries are handwritten parameterized SQL
SQLite only: No support for other database providers
Manual migrations: Schema created in code at startup
Duplicated logic: Similar code in PodcastDatabaseService and CentralDatabaseService

Target State

Single database: All data in one database (central)
EF Core: Type-safe queries with LINQ, automatic change tracking
Multi-provider: Support SQLite, PostgreSQL, SQL Server, MariaDB
Code-first migrations: EF Core migrations for schema management
Repository pattern: Clean separation between data access and business logic

Benefits

Type safety: Compile-time query validation
Maintainability: Less boilerplate, cleaner code
Scalability: PostgreSQL/SQL Server for larger deployments
Migrations: Proper schema versioning and updates
Testing: Easier to mock and test data access

Implementation Phases

Phase	Issue	Description
1	#59	Add EF Core packages and create DbContext
2	#60	Create entity models with relationships
3	#61	Implement repository pattern for data access
4	#62	Add multi-database provider configuration
5	#63	Create EF Core migrations
6	#64	Migrate services to use EF Core repositories
7	#65	Remove legacy per-feed database system
8	#66	Add integration tests for data access layer

Database Schema (Target)

┌─────────────┐       ┌─────────────────┐
│   Feeds     │───────│    Episodes     │
└─────────────┘       └─────────────────┘
      │                      │
      │               ┌──────┴──────┐
      │               │             │
┌─────┴─────┐   ┌─────┴─────┐  ┌────┴────┐
│ Activity  │   │ Downloads │  │  Queue  │
│   Logs    │   │  Videos   │  │  Items  │
└───────────┘   └───────────┘  └─────────┘

Configuration Example

{
  "Database": {
    "Provider": "PostgreSQL",
    "ConnectionString": "Host=localhost;Database=castr;Username=user;Password=pass"
  }
}

Or for SQLite (default):

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

Estimated Effort

Phase	Complexity	Dependencies
1 - DbContext	Low	None
2 - Entities	Medium	Phase 1
3 - Repositories	Medium	Phase 2
4 - Multi-provider	Medium	Phase 1
5 - Migrations	Low	Phases 1-2
6 - Service migration	High	Phases 1-5
7 - Legacy removal	Low	Phase 6
8 - Tests	Medium	Phases 1-6

## Overview Migrate from raw SQL with Microsoft.Data.Sqlite to Entity Framework Core, supporting multiple database providers: - SQLite (default, for simple deployments) - PostgreSQL (recommended for production) - Microsoft SQL Server - MariaDB/MySQL ## Current State - **2 separate database systems**: Central database + per-feed databases - **Raw SQL**: All queries are handwritten parameterized SQL - **SQLite only**: No support for other database providers - **Manual migrations**: Schema created in code at startup - **Duplicated logic**: Similar code in `PodcastDatabaseService` and `CentralDatabaseService` ## Target State - **Single database**: All data in one database (central) - **EF Core**: Type-safe queries with LINQ, automatic change tracking - **Multi-provider**: Support SQLite, PostgreSQL, SQL Server, MariaDB - **Code-first migrations**: EF Core migrations for schema management - **Repository pattern**: Clean separation between data access and business logic ## Benefits 1. **Type safety**: Compile-time query validation 2. **Maintainability**: Less boilerplate, cleaner code 3. **Scalability**: PostgreSQL/SQL Server for larger deployments 4. **Migrations**: Proper schema versioning and updates 5. **Testing**: Easier to mock and test data access ## Implementation Phases | Phase | Issue | Description | |-------|-------|-------------| | 1 | #59 | Add EF Core packages and create DbContext | | 2 | #60 | Create entity models with relationships | | 3 | #61 | Implement repository pattern for data access | | 4 | #62 | Add multi-database provider configuration | | 5 | #63 | Create EF Core migrations | | 6 | #64 | Migrate services to use EF Core repositories | | 7 | #65 | Remove legacy per-feed database system | | 8 | #66 | Add integration tests for data access layer | ## Database Schema (Target) ``` ┌─────────────┐ ┌─────────────────┐ │ Feeds │───────│ Episodes │ └─────────────┘ └─────────────────┘ │ │ │ ┌──────┴──────┐ │ │ │ ┌─────┴─────┐ ┌─────┴─────┐ ┌────┴────┐ │ Activity │ │ Downloads │ │ Queue │ │ Logs │ │ Videos │ │ Items │ └───────────┘ └───────────┘ └─────────┘ ``` ## Configuration Example ```json { "Database": { "Provider": "PostgreSQL", "ConnectionString": "Host=localhost;Database=castr;Username=user;Password=pass" } } ``` Or for SQLite (default): ```json { "Database": { "Provider": "SQLite", "ConnectionString": "Data Source=/data/castr.db" } } ``` ## Estimated Effort | Phase | Complexity | Dependencies | |-------|------------|--------------| | 1 - DbContext | Low | None | | 2 - Entities | Medium | Phase 1 | | 3 - Repositories | Medium | Phase 2 | | 4 - Multi-provider | Medium | Phase 1 | | 5 - Migrations | Low | Phases 1-2 | | 6 - Service migration | High | Phases 1-5 | | 7 - Legacy removal | Low | Phase 6 | | 8 - Tests | Medium | Phases 1-6 |

joezombie commented

2026-01-27 08:38:01 +00:00

(Migrated from github.com)

Progress Update

Most of the EF Core migration is complete:

Completed:

✅ #59 - Add EF Core packages and create DbContext
✅ #60 - Create EF Core entity models with relationships
✅ #61 - Implement repository pattern for data access
✅ #62 - Add multi-database provider configuration
✅ #64 - Migrate services to use EF Core repositories
✅ #65 - Remove legacy per-feed database system
✅ #66 - Add integration tests for EF Core data access layer

Remaining:

⏳ #63 - Create EF Core migrations for schema management

Current State:

All services now use EF Core through IPodcastDataService facade
Database initialization uses EnsureCreatedAsync() (works but blocks migrations)
Legacy database services have been removed (2500+ lines of raw SQL deleted)
Test coverage: 77.48% line, 66.41% branch, 84.22% method
152 tests pass

## Progress Update Most of the EF Core migration is complete: ### Completed: - ✅ #59 - Add EF Core packages and create DbContext - ✅ #60 - Create EF Core entity models with relationships - ✅ #61 - Implement repository pattern for data access - ✅ #62 - Add multi-database provider configuration - ✅ #64 - Migrate services to use EF Core repositories - ✅ #65 - Remove legacy per-feed database system - ✅ #66 - Add integration tests for EF Core data access layer ### Remaining: - ⏳ #63 - Create EF Core migrations for schema management ### Current State: - All services now use EF Core through IPodcastDataService facade - Database initialization uses EnsureCreatedAsync() (works but blocks migrations) - Legacy database services have been removed (2500+ lines of raw SQL deleted) - Test coverage: 77.48% line, 66.41% branch, 84.22% method - 152 tests pass