Add PostgreSQL support as configurable database provider #286

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.

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.

Depends on: #130 (Implement Flyway for database migrations)

Suggested Approach

1. Add PostgreSQL dependency

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

2. Update application configuration

Read DATABASE_PROVIDER to select the datasource. When postgres, activate the PostgreSQL datasource via environment-driven properties:

application.properties (SQLite default — no changes if #130 already configures it)

application-postgres.properties:

spring.datasource.url=${DATABASE_URL}
spring.datasource.username=${DATABASE_USERNAME:postgres}
spring.datasource.password=${DATABASE_PASSWORD}
spring.datasource.driver-class-name=org.postgresql.Driver
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=validate
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration

A startup bean or environment post-processor reads DATABASE_PROVIDER and sets spring.profiles.active=postgres when appropriate.

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:-}
      - DATABASE_PASSWORD=${POSTGRES_PASSWORD:-}

  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=jdbc:postgresql://postgres:5432/players
POSTGRES_PASSWORD=your_secure_password_here

5. Verify migration compatibility

Confirm that the Flyway migrations from #130 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

• postgresql JDBC driver added to pom.xml
• DATABASE_PROVIDER env var controls provider selection (sqlite default, postgres opt-in)
• application-postgres.properties configures PostgreSQL datasource
• docker compose up works with SQLite, no PostgreSQL container started
• DATABASE_PROVIDER=postgres docker compose --profile postgres up works with PostgreSQL
• ./mvnw spring-boot:run continues to work with SQLite unchanged
• Flyway migrations from Implement Flyway for database migrations #130 apply cleanly to both providers on startup
• 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

• PostgreSQL JDBC Driver
• Spring Boot Profiles
• Docker Compose Profiles
• PostgreSQL Docker Official Images
• Depends on: Implement Flyway for database migrations #130 (Implement Flyway for database migrations)