Add PostgreSQL support with unified migration-based initialization

[nanotaboada]

Problem

Currently, the project uses SQLite as the database, 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: #130 (Implement Flyway for database migrations)

Proposed Solution

Add PostgreSQL support alongside SQLite, leveraging the Flyway migration infrastructure introduced in #130. 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: Flyway migrations (from Implement Flyway for database migrations #130) 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:
      - SPRING_PROFILES_ACTIVE=production
      - SPRING_DATASOURCE_URL=jdbc:postgresql://postgres:5432/playersdb
      - SPRING_DATASOURCE_USERNAME=postgres
      - SPRING_DATASOURCE_PASSWORD=${POSTGRES_PASSWORD}
    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:

# PostgreSQL Configuration
POSTGRES_PASSWORD=your_secure_password_here

Phase 2: Dependencies

Add to pom.xml:

<!-- PostgreSQL JDBC Driver -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

Note: Flyway dependencies are added as part of #130.

Phase 3: Application Configuration

Update application.properties (local/SQLite — no changes needed if #130 already configures Flyway).

Create application-production.properties:

spring.datasource.url=${SPRING_DATASOURCE_URL}
spring.datasource.username=${SPRING_DATASOURCE_USERNAME}
spring.datasource.password=${SPRING_DATASOURCE_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

Phase 4: Verify Migration Compatibility

Confirm that the Flyway migrations introduced in #130 use SQL compatible with both SQLite and PostgreSQL (standard DDL, no provider-specific syntax). Address any dialect differences if found.

Phase 5: Documentation Updates

Update README.md:

• Add "Database Options" section explaining SQLite vs PostgreSQL use cases
• Document environment variables (POSTGRES_PASSWORD, SPRING_PROFILES_ACTIVE)
• Add Docker quick start: docker compose up
• Confirm local development: ./mvnw spring-boot:run (SQLite via Flyway from Implement Flyway for database migrations #130)

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

• postgresql JDBC driver added to pom.xml
• application-production.properties configures PostgreSQL datasource
• SPRING_PROFILES_ACTIVE=production activates PostgreSQL in Docker
• Flyway migrations from Implement Flyway for database migrations #130 apply cleanly to PostgreSQL

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

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

[coderabbitai]

🔗 Related PRs

#235 - feat: migrate database from H2 to SQLite [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!