[Epic] Add MySQL as Optional Database Backend

[mfittko]

Summary

Add MySQL as an optional drop-in replacement for PostgreSQL to provide more flexibility for users familiar with MySQL. This follows the same pattern as our PostgreSQL implementation, maintaining SQLite as the default while offering MySQL for production deployments.

---

Motivation

• Some teams and organizations standardize on MySQL/MariaDB instead of PostgreSQL
• Providing MySQL support reduces deployment friction for MySQL-native environments
• Maintains consistency with our existing multi-database approach (SQLite + PostgreSQL)
• Makes it easier to test and deploy in environments where MySQL is already available

---

Architecture Overview

flowchart TB
    subgraph Application["LLM Proxy Application"]
        CONFIG["Configuration Layer"]
        FACTORY["Database Factory"]
        PROXY["Proxy Core"]
    end
    
    subgraph DatabaseLayer["Database Layer"]
        SQLITE["SQLite\n(Default)"]
        PG["PostgreSQL\n(Existing)"]
        MYSQL["MySQL\n(NEW)"]
    end
    
    subgraph BuildSystem["Build System"]
        DEFAULT["Default Build\n(SQLite only)"]
        PG_BUILD["-tags postgres"]
        MYSQL_BUILD["-tags mysql"]
        FULL_BUILD["-tags postgres,mysql"]
    end
    
    CONFIG --> FACTORY
    FACTORY --> SQLITE
    FACTORY --> PG
    FACTORY --> MYSQL
    
    DEFAULT -.-> SQLITE
    PG_BUILD -.-> PG
    MYSQL_BUILD -.-> MYSQL
    FULL_BUILD -.-> PG
    FULL_BUILD -.-> MYSQL

Loading

---

Implementation Approach

Following the PostgreSQL implementation pattern from phase-5-postgres-support.md:

flowchart LR
    subgraph Phase1["Phase 1: Foundation"]
        DRIVER["Driver Integration\n#241"]
    end
    
    subgraph Phase2["Phase 2: Core"]
        MIGRATIONS["Schema Migrations\n#242"]
    end
    
    subgraph Phase3["Phase 3: DevOps"]
        DOCKER["Docker Compose\n#243"]
        HELM["Helm Charts\n#244"]
    end
    
    subgraph Phase4["Phase 4: Quality"]
        TESTS["Integration Tests\n#245"]
    end
    
    subgraph Phase5["Phase 5: Docs"]
        DOCS["Documentation\n#246"]
    end
    
    DRIVER --> MIGRATIONS
    MIGRATIONS --> DOCKER
    MIGRATIONS --> HELM
    DOCKER --> TESTS
    HELM --> TESTS
    TESTS --> DOCS

Loading

---

Goals

• Implement MySQL support with the same quality and feature parity as PostgreSQL
• Use build tags to keep binaries lean (similar to PostgreSQL implementation)
• Provide Docker Compose profile for easy local MySQL testing
• Update Helm charts to support MySQL deployment
• Maintain backward compatibility - no breaking changes to existing SQLite/PostgreSQL users
• Achieve 90%+ test coverage including integration tests

---

Non-Goals

• Database-specific performance optimizations (can be addressed separately)
• Migration tools between SQLite/PostgreSQL/MySQL (out of scope)
• Advanced MySQL features (clustering, replication) - focus on core functionality first

---

Sub-Issues

Issue	Title	Dependencies	Status
#241	MySQL Driver Integration and Connection Handling	None	✅ Done (PR #247)
#242	MySQL Schema Migrations	#241	✅ Done (PR #248)
#243	Docker Compose MySQL Profile	#241, #242	✅ Done (PR #253)
#244	Helm Chart MySQL Support	#241, #242, #243	✅ Ready to Merge (PR #255 - approved)
#245	MySQL Integration Tests and CI	#241, #242, #243	✅ Ready to Merge (PR #256 - approved)
#246	Documentation Updates	All above	🔲 Ready to Assign (all dependencies complete)

---

Technical Overview

Component Mapping

Component	PostgreSQL	MySQL (NEW)
Driver Package	github.com/jackc/pgx/v5	github.com/go-sql-driver/mysql
Build Tag	postgres	mysql
Factory File	factory_postgres.go	factory_mysql.go
Stub File	factory_postgres_stub.go	factory_mysql_stub.go
Migration Path	migrations/sql/postgres/	migrations/sql/mysql/
Integration Tests	postgres_integration_test.go	mysql_integration_test.go
Docker Profile	postgres	mysql
Default Port	5432	3306

Environment Variables

Variable	Values	Description
DB_DRIVER	sqlite, postgres, mysql	Database driver selection
DATABASE_URL	Connection string	Required for MySQL/PostgreSQL
DATABASE_PATH	File path	Required for SQLite

---

Deployment Options

flowchart TB
    subgraph Local["Local Development"]
        DC_SQLITE["SQLite\n(Default)"]
        DC_PG["docker compose --profile postgres"]
        DC_MYSQL["docker compose --profile mysql"]
    end
    
    subgraph Kubernetes["Kubernetes/Helm"]
        HELM_SQLITE["database.type=sqlite"]
        HELM_PG["database.type=postgres"]
        HELM_MYSQL["database.type=mysql"]
    end
    
    subgraph Production["Production Options"]
        MANAGED_PG["AWS RDS PostgreSQL\nCloud SQL PostgreSQL\nAzure PostgreSQL"]
        MANAGED_MYSQL["AWS RDS MySQL\nCloud SQL MySQL\nAzure MySQL"]
    end

Loading

---

Success Criteria

• Application starts and operates correctly with DB_DRIVER=mysql
• All existing functionality works with MySQL backend
• Docker Compose includes working MySQL service with health checks
• Helm charts deploy successfully with MySQL configuration
• Integration tests pass for MySQL with build tags
• Documentation covers MySQL setup, configuration, and migration
• CI pipeline validates MySQL support
• Test coverage remains ≥ 90%

---

Acceptance Criteria

• All sub-issues completed and tested
• MySQL option available in production builds
• Documentation updated and reviewed
• All tests passing (unit + integration)
• No regression in SQLite or PostgreSQL functionality
• Helm deployment tested with MySQL configuration

---

Related Work

• Phase 5: PostgreSQL Support - Reference implementation
• Database selection guide: docs/database/database-selection.md
• Current factory pattern: internal/database/factory.go

---

Note: This epic follows our established patterns from PostgreSQL implementation. Each sub-issue should be tackled independently with TDD approach, maintaining our 90%+ coverage requirement.