Add PostgreSQL support as configurable database provider

[nanotaboada]

Problem

The project currently uses in-memory storage with no persistence layer. Issue #23 tracks the introduction of SQLite as the first database. Rather than then adding a fixed SQLite-local / PostgreSQL-production split (which would create a mixed-environment setup with subtle behavioral differences that are hard to catch locally), a better approach is to make the database engine fully configurable from the start.

Depends on: #64 (Replace rusqlite with Diesel ORM)

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. Update Cargo.toml

[dependencies]
diesel = { version = "2", features = ["sqlite", "postgres", "r2d2"] }
diesel_migrations = "2"
dotenvy = "0.15"

2. Update database initialization

Read DATABASE_PROVIDER at startup and build the appropriate connection pool:

pub enum DbPool {
    Sqlite(r2d2::Pool<ConnectionManager<SqliteConnection>>),
    Postgres(r2d2::Pool<ConnectionManager<PgConnection>>),
}

pub fn connect() -> DbPool {
    let provider = env::var("DATABASE_PROVIDER").unwrap_or_else(|_| "sqlite".to_string());
    let url = env::var("DATABASE_URL")
        .unwrap_or_else(|_| "sqlite:players-sqlite3.db".to_string());

    match provider.as_str() {
        "postgres" => {
            let manager = ConnectionManager::<PgConnection>::new(url);
            DbPool::Postgres(r2d2::Pool::builder().build(manager).expect("Failed to connect to PostgreSQL"))
        }
        _ => {
            let manager = ConnectionManager::<SqliteConnection>::new(url);
            DbPool::Sqlite(r2d2::Pool::builder().build(manager).expect("Failed to connect to SQLite"))
        }
    }
}

Apply embedded migrations on startup via embed_migrations!().

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:-sqlite:players-sqlite3.db}

  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
DATABASE_URL=sqlite:players-sqlite3.db

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

5. Verify migration compatibility

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

6. Update README

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

Acceptance Criteria

• diesel with sqlite, postgres, and r2d2 features in Cargo.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
• cargo run continues to work with SQLite unchanged
• Diesel migrations applied automatically on startup via embed_migrations!()
• Migrations compatible with both SQLite and PostgreSQL
• All CRUD operations work identically with both providers
• .env.example documents DATABASE_PROVIDER, DATABASE_URL, and POSTGRES_PASSWORD
• .env is git-ignored
• README.md updated
• CHANGELOG.md updated
• All existing tests pass

References

• Diesel
• Diesel Migrations
• Docker Compose Profiles
• PostgreSQL Docker Official Images
• Depends on: Replace rusqlite with Diesel ORM #64 (Replace rusqlite with Diesel ORM)