Add PostgreSQL support as configurable database provider

[nanotaboada]

Problem

The project currently uses SQLite with no option to run a different database engine. The original plan was to add PostgreSQL for Docker/production deployments, but a fixed SQLite-local / PostgreSQL-production split introduces a mixed-environment setup: different engines in dev and prod, which creates subtle behavioral differences that are hard to catch locally.

A better approach is to make the database engine fully configurable, so developers choose one provider and use it consistently — local dev, Docker, and any deployment all run the same engine.

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

Proposed Solution

Introduce a DATABASE_PROVIDER environment variable that selects the database engine for the entire stack:

• DATABASE_PROVIDER=sqlite (default): SQLite everywhere. Zero infrastructure required. Works on any machine, including legacy hardware. No Docker needed.
• DATABASE_PROVIDER=postgres: PostgreSQL everywhere. Requires Docker. Opt-in for developers who want a server-based engine or full production parity.

The default is sqlite to keep the barrier to entry as low as possible — clone, run, done.

Suggested Approach

1. Add asyncpg dependency

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

2. Update database initialization

Read DATABASE_PROVIDER at startup and configure the appropriate async engine:

import os

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

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

engine = create_async_engine(DATABASE_URL, echo=os.getenv("ENV") == "development")

Ensure Alembic's env.py from #2 also reads DATABASE_URL from the environment so migrations target the correct database.

3. Update compose.yaml

Use Docker Compose profiles so the postgres service only starts when explicitly requested:

services:
  web:
    environment:
      - DATABASE_PROVIDER=${DATABASE_PROVIDER:-sqlite}
      - DATABASE_URL=${DATABASE_URL:-}

  postgres:
    image: postgres:17-alpine
    profiles: [postgres]
    environment:
      POSTGRES_DB: players
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-postgres}
    volumes:
      - postgres-data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  postgres-data:

Usage:

# SQLite (default, no Docker dependency)
docker compose up

# PostgreSQL (opt-in)
DATABASE_PROVIDER=postgres docker compose --profile postgres up

4. Update .env.example

# Database provider: sqlite (default) | postgres
DATABASE_PROVIDER=sqlite

# Required only when DATABASE_PROVIDER=postgres
DATABASE_URL=postgresql+asyncpg://postgres:your_secure_password_here@postgres:5432/players
POSTGRES_PASSWORD=your_secure_password_here

5. Verify migration compatibility

Confirm that the Alembic migrations from #2 use SQL compatible with both SQLite and PostgreSQL. Address any dialect differences if found.

6. Add ADR

Add docs/adr/0011-configurable-database-provider.md, superseding docs/adr/0001-sqlite-as-database-engine.md. Document the decision, the two options, and why sqlite is the default.

7. Update README

Add a "Database" section documenting the two modes and how to switch between them.

Acceptance Criteria

• asyncpg added to pyproject.toml
• DATABASE_PROVIDER env var controls provider selection (sqlite default, postgres opt-in)
• docker compose up works with SQLite, no PostgreSQL container started
• DATABASE_PROVIDER=postgres docker compose --profile postgres up works with PostgreSQL
• uv run fastapi dev continues to work with SQLite unchanged
• Alembic migrations from Implement Alembic for database migrations #2 apply cleanly to both providers on startup
• Alembic env.py reads DATABASE_URL from the environment
• All CRUD operations work identically with both providers
• .env.example documents DATABASE_PROVIDER, DATABASE_URL, and POSTGRES_PASSWORD
• .env is git-ignored
• docs/adr/0011-configurable-database-provider.md added, superseding docs/adr/0001-sqlite-as-database-engine.md
• README.md updated
• CHANGELOG.md updated
• All existing tests pass

References

• asyncpg
• SQLAlchemy Async
• Docker Compose Profiles
• PostgreSQL Docker Official Images
• Depends on: Implement Alembic for database migrations #2 (Implement Alembic for Database Migrations)
• Related: ADR docs/adr/0001-sqlite-as-database-engine.md

[coderabbitai]

🔗 Related PRs

#361 - feat(container): add Docker Compose support with persistent SQLite volume [merged]
#563 - feat(migrations): implement Alembic for database migrations (#2) [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!