Add MySQL Support as Alternative Database Backend

Description

Currently, the project is tightly coupled to PostgreSQL as its database backend. While PostgreSQL is excellent, many projects and organizations use MySQL/MariaDB. Since the project already uses SQLAlchemy as an ORM, we can leverage its database-agnostic nature to support multiple database backends, giving users more flexibility in choosing their preferred database system.

Problem Statement

• Project is hardcoded to use PostgreSQL exclusively
• Limited flexibility for users who prefer or require MySQL/MariaDB
• Database-specific configurations and dependencies are not abstracted
• Potential barriers for adoption in MySQL-centric environments

Proposed Solution

Implement database backend flexibility by:

1. Abstracting database-specific configurations
2. Adding MySQL/MariaDB driver support alongside PostgreSQL
3. Making database selection configurable via environment variables
4. Ensuring all database operations work seamlessly with both backends

Implementation Details

The implementation should include:

1. Update Dependencies

• Add MySQL driver options to pyproject.toml:
  • pymysql (pure Python driver)
  • mysqlclient (C-based driver, better performance)
  • aiomysql (async support)
• Make PostgreSQL drivers optional dependencies
• Create dependency groups for each database backend

[tool.poetry.dependencies]
# Core dependencies
sqlalchemy = "^2.0"

[tool.poetry.group.postgres.dependencies]
psycopg2-binary = "^2.9"
asyncpg = "^0.29"

[tool.poetry.group.mysql.dependencies]
pymysql = "^1.1"
aiomysql = "^0.2"
cryptography = "^41.0"  # Required by pymysql

2. Update Environment Configuration

• Add DATABASE_TYPE environment variable to env.template
• Update src/config/settings.py to handle multiple database types
• Implement database URL builder based on selected type

# Example environment variables
DATABASE_TYPE=postgres  # or mysql
DATABASE_USER=your_user
DATABASE_PASS=your_password
DATABASE_PORT=5432  # or 3306 for MySQL
DATABASE_NAME=your_database
DATABASE_ENDPOINT=localhost

3. Abstract Database Configuration

• Create database factory/builder pattern in configuration
• Handle dialect-specific connection parameters
• Support both sync and async drivers for each backend
• Implement proper connection string formatting per database type

4. Update Alembic Migrations

• Ensure migrations are database-agnostic
• Test migrations on both PostgreSQL and MySQL
• Handle dialect-specific data types appropriately
• Document any database-specific migration considerations

5. Update Docker Configuration

• Add MySQL service option to docker-compose.yml
• Create separate compose profiles for PostgreSQL and MySQL
• Update Dockerfile to support both database drivers
• Provide configuration examples for both databases

# Example docker-compose structure
services:
  postgres:
    profiles: ["postgres"]
    # PostgreSQL configuration
  
  mysql:
    profiles: ["mysql"]
    # MySQL configuration

6. Update Tests

• Ensure test suite works with both database backends
• Add database type detection in test fixtures
• Consider parametrized tests for database-specific behaviors
• Update Testcontainers implementation (from #X) to support MySQL

7. Documentation Updates

• Update README.md with MySQL setup instructions
• Document how to switch between databases
• Add troubleshooting section for each database type
• Provide performance and feature comparison guide
• Update architecture documentation

Database-Specific Considerations

Type Mappings

• Ensure SQLAlchemy column types work correctly on both databases
• Handle UUID differences (native in PostgreSQL, CHAR(36) in MySQL)
• Address JSON field support (JSONB in PostgreSQL, JSON in MySQL 5.7+)
• Handle boolean types (native in PostgreSQL, TINYINT in MySQL)

Migration Compatibility

• Use sa.Text() instead of sa.String() for long text fields
• Avoid PostgreSQL-specific features (ARRAY, JSONB operators) in migrations
• Test all existing migrations against MySQL
• Document any limitations or differences

Connection Pool Settings

• Optimize pool settings per database type
• Handle connection timeout differences
• Configure appropriate dialect-specific parameters

Benefits

• Increased flexibility and broader adoption potential
• Users can choose their preferred database system
• Leverages SQLAlchemy's database-agnostic capabilities
• Easier migration between database systems
• Better alignment with diverse infrastructure requirements
• Maintains the project's boilerplate philosophy of flexibility

Acceptance Criteria

• MySQL driver dependencies added to pyproject.toml with optional groups
• DATABASE_TYPE environment variable implemented and documented
• Database connection logic refactored to support both PostgreSQL and MySQL
• All existing migrations work on both database backends
• Docker Compose configurations available for both databases
• Test suite passes with both PostgreSQL and MySQL
• README.md updated with setup instructions for both databases
• No breaking changes to existing PostgreSQL configurations
• Code remains maintainable without excessive conditionals
• Performance characteristics documented for both backends

Out of Scope

• SQLite support (can be addressed in a separate issue if needed)
• Other database systems (Oracle, SQL Server, etc.)
• Database-specific optimization features
• Migration between PostgreSQL and MySQL for existing data

Technical Considerations

Potential Challenges

1. UUID Handling: PostgreSQL has native UUID type, MySQL uses CHAR(36)
2. JSON Operations: Different query syntaxes for JSON fields
3. Array Types: PostgreSQL supports arrays natively, MySQL doesn't
4. Transaction Isolation: Default behaviors differ between databases
5. Full-Text Search: Implementation differs significantly

Recommended Approach

• Start with a feature branch to test compatibility
• Use SQLAlchemy's dialect-specific inspection where needed
• Implement database-agnostic patterns in new code
• Add integration tests for both backends
• Consider using Alembic's render_as_batch for SQLite compatibility (future-proofing)

References

• [SQLAlchemy Dialects Documentation](https://docs.sqlalchemy.org/en/20/dialects/)
• [MySQL Connector/Python Documentation](https://dev.mysql.com/doc/connector-python/en/)
• [Alembic with Multiple Databases](https://alembic.sqlalchemy.org/en/latest/cookbook.html)
• [FastAPI with MySQL Example](https://fastapi.tiangolo.com/tutorial/sql-databases/)
• [SQLAlchemy MySQL Dialect](https://docs.sqlalchemy.org/en/20/dialects/mysql.html)

Additional Context

This enhancement maintains the project's philosophy as a flexible, production-ready boilerplate. By supporting multiple database backends, we enable teams to use this boilerplate regardless of their existing database infrastructure or preferences.

The implementation should prioritize maintainability and avoid creating excessive database-specific code branches. The goal is to provide flexibility while keeping the codebase clean and easy to understand.

Related Issues: #81 (Testcontainers implementation - should support both PostgreSQL and MySQL)