Add PostgreSQL support with unified migration-based initialization

[nanotaboada]

Problem

Currently, the project uses SQLite as the database via aiosqlite, which is excellent for local development simplicity (single file, no setup, zero external dependencies). However, SQLite has limitations for production-like scenarios:

• ❌ Limited concurrency (write locks entire database)
• ❌ No true client-server architecture
• ❌ Missing advanced features (stored procedures, replication, etc.)
• ❌ Not representative of real-world deployment environments

The project needs a production-ready database solution with a unified initialization strategy that works identically for both databases.

Depends on: #2 (Implement Alembic for Database Migrations)

Proposed Solution

Add PostgreSQL support alongside SQLite, leveraging the Alembic migration infrastructure introduced in #2. Both databases will use the same versioned migrations on startup. This provides:

1. Dual database support: PostgreSQL for Docker/production, SQLite for local dev and testing
2. Single source of truth: Alembic migrations (from Implement Alembic for database migrations #2) define schema and seed data for both
3. Auto-initialization: Both databases auto-create and seed on first run
4. Unified workflow: Same behavior for development and production
5. Production patterns: Demonstrates real-world database configuration
6. Educational value: Shows proper database initialization in both environments

Suggested Approach

Phase 1: Docker Infrastructure Setup

Configure Docker Compose to orchestrate PostgreSQL alongside the application service.

Update compose.yaml:

services:
  web:
    build: .
    ports:
      - "9000:9000"
    environment:
      - DB_PROVIDER=postgresql
      - DATABASE_URL=postgresql+asyncpg://postgres:${POSTGRES_PASSWORD}@postgres:5432/playersdb
    depends_on:
      postgres:
        condition: service_healthy

  postgres:
    image: postgres:17-bookworm
    container_name: playersdb-postgres
    environment:
      - POSTGRES_DB=playersdb
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD:-postgres}
    ports:
      - "5432:5432"
    volumes:
      - postgres-data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  postgres-data:
    driver: local

Create .env.example:

# Database Configuration
DB_PROVIDER=sqlite
POSTGRES_PASSWORD=your_secure_password_here

Phase 2: Dependencies

Add to pyproject.toml:

dependencies = [
  "asyncpg",
  # existing deps...
]

Note: Alembic is added as part of #2.

Phase 3: Database Provider Abstraction

Update database configuration (e.g. databases/database.py):

import os
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker

provider = os.getenv("DB_PROVIDER", "sqlite")

if provider == "postgresql":
    DATABASE_URL = os.getenv(
        "DATABASE_URL",
        "postgresql+asyncpg://postgres:postgres@localhost:5432/playersdb"
    )
else:
    DATABASE_URL = "sqlite+aiosqlite:///players-sqlite3.db"

engine = create_async_engine(DATABASE_URL, echo=os.getenv("ENV") == "development")
AsyncSessionLocal = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

Ensure that the Alembic env.py introduced in #2 also reads DATABASE_URL from the environment so it targets the correct database.

Phase 4: Verify Migration Compatibility

Confirm that the Alembic migrations introduced in #2 use SQL compatible with both SQLite and PostgreSQL. Address any dialect differences if found (e.g. AUTOINCREMENT vs SERIAL, type mappings).

Phase 5: Documentation Updates

Update README.md:

• Add "Database Options" section explaining SQLite vs PostgreSQL use cases
• Document environment variables (POSTGRES_PASSWORD, DB_PROVIDER, DATABASE_URL)
• Add Docker quick start: docker compose up
• Confirm local development: uv run fastapi dev (SQLite via Alembic from Implement Alembic for database migrations #2)

Acceptance Criteria

Docker Compose

• postgres service defined with health check
• PostgreSQL data persists via volume (postgres-data)
• .env.example committed with POSTGRES_PASSWORD placeholder
• .env is git-ignored
• Web service depends on PostgreSQL health check

Application Code

• asyncpg added to pyproject.toml
• Database URL driven by DB_PROVIDER / DATABASE_URL environment variables
• SQLite used by default (local dev and testing)
• PostgreSQL used when DB_PROVIDER=postgresql
• Alembic migrations from Implement Alembic for database migrations #2 apply cleanly to PostgreSQL
• Alembic env.py reads DATABASE_URL from the environment

Testing

• Application starts successfully with SQLite (local)
• Application starts successfully with PostgreSQL in Docker
• All CRUD operations work with both providers
• Database persists after container restart (PostgreSQL)
• Existing tests pass with SQLite configuration
• Docker Compose health checks pass

Documentation

• README.md updated with dual-database approach
• Environment variables documented
• .env.example explains required variables

References

• asyncpg
• SQLAlchemy Async
• PostgreSQL Docker Official Images
• Docker Compose Health Checks
• Depends on: Implement Alembic for database migrations #2 (Implement Alembic for Database Migrations)

[coderabbitai]

🔗 Related PRs

#361 - feat(container): add Docker Compose support with persistent SQLite volume [merged]

---

📝 Issue Planner

_{Check the box below or use the @coderabbitai plan command to generate an implementation plan and prompts that you can use with your favorite coding assistant.}

• Create Plan

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!