Database Setup

The previous sections covered how Spring talks to databases and how to generate JPA entities. Now it's time to wire up a real database. We're replacing the embedded H2 with PostgreSQL 17 and using Flyway to manage schema migrations.

The Plan

Here's what we're setting up:

• PostgreSQL as the production database
• Flyway to run versioned SQL migrations on startup
• pgAdmin as a web UI for database inspection
• A restricted application user (sakila_app) that only has DML privileges, not DDL

The two users are the key security decision here. The admin user (sakila) owns the schema and runs migrations. The application connects as sakila_app, which can only SELECT, INSERT, UPDATE, and DELETE. If your app connection is compromised, an attacker can't DROP tables or ALTER schema. Small thing, big difference in a breach.

Files Overview

Files to Create/Modify

Java

File Tree

.
├── ...
├── database/
│   ├── flyway/
│   │   ├── Dockerfile
│   │   └── migrations/
│   │       ├── V1__create_sakila_schema.sql
│   │       ├── V2__insert_sample_data.sql
│   │       └── V3__grant_app_user_privileges.sql
│   └── postgres/
│       ├── Dockerfile
│       └── init-users.sh
├── docker-compose.yml
└── spring-java/
    ├── ...
    ├── build.gradle
    └── src
        └── main
            └── resources
                ├── ...
                └── application.yaml

Kotlin

File Tree

.
├── ...
├── database/
│   ├── flyway/
│   │   ├── Dockerfile
│   │   └── migrations/
│   │       ├── V1__create_sakila_schema.sql
│   │       ├── V2__insert_sample_data.sql
│   │       └── V3__grant_app_user_privileges.sql
│   └── postgres/
│       ├── Dockerfile
│       └── init-users.sh
├── docker-compose.yml
└── spring-kotlin/
    ├── ...
    ├── build.gradle.kts
    └── src
        └── main
            └── resources
                ├── ...
                └── application.yaml

Groovy

File Tree

.
├── ...
├── database/
│   ├── flyway/
│   │   ├── Dockerfile
│   │   └── migrations/
│   │       ├── V1__create_sakila_schema.sql
│   │       ├── V2__insert_sample_data.sql
│   │       └── V3__grant_app_user_privileges.sql
│   └── postgres/
│       ├── Dockerfile
│       └── init-users.sh
├── docker-compose.yml
└── spring-groovy/
    ├── ...
    ├── build.gradle
    └── src
        └── main
            └── resources
                ├── ...
                └── application.yaml

Flyway Migrations

Flyway runs versioned SQL files in order. Each file runs exactly once, and Flyway tracks what's already been applied in a flyway_schema_history table.

We have three migrations:

• V1: Create the Sakila Schema. This creates all the tables, primary keys, foreign keys, and indices for the Sakila sample database.

• V2: Insert Sample Data. V2 inserts 47,000+ lines of sample data. It's a large file converted from H2 format to PostgreSQL-compatible SQL.

• V3: Grant App User Privileges. This runs after V1 and V2, so all tables exist by the time it grants privileges.

  database/flyway/migrations/V3__grant_app_user_privileges.sql

  -- ============================================================================
  -- Flyway Migration V3: Grant DML Privileges to Application User
  -- ============================================================================
  --
  -- This migration enforces the principle of least privilege by granting
  -- the sakila_app user (created by postgres/init-users.sh) with DML-only
  -- privileges (SELECT, INSERT, UPDATE, DELETE) on all tables and sequences.
  --
  -- The sakila (admin) user retains full DDL privileges for future migrations.
  --
  
  -- Grant schema usage
  GRANT USAGE ON SCHEMA public TO sakila_app;
  
  -- Grant DML privileges on all existing tables
  GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO sakila_app;
  
  -- Grant sequence privileges for auto-increment IDs (SERIAL columns)
  GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO sakila_app;
  
  -- Set default privileges for future tables created by sakila (admin) user
  ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO sakila_app;
  
  -- Set default privileges for future sequences
  ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO sakila_app;

The separation matters. V3 runs as the admin user but only grants DML rights to sakila_app. The ALTER DEFAULT PRIVILEGES lines make sure any tables created by future migrations are also accessible to the app user automatically.

Docker Setup

Real-World Setups Look Different

It's uncommon to find the database in the same Docker Compose file as the application services. Databases are usually managed separately, whether because of technical debt making them hard to containerize, or separation of concerns (imagine accidentally wiping out the volume where your production data lives). Here, everything lives together for simplicity's sake.

PostgreSQL Image

We are extending the postgres:17-alpine image to run an init script that creates the sakila_app user during first startup.

database/postgres/Dockerfile

FROM postgres:17-alpine
COPY init-users.sh /docker-entrypoint-initdb.d/01-init-users.sh
RUN chmod +x /docker-entrypoint-initdb.d/01-init-users.sh

database/postgres/init-users.sh

#!/bin/bash
set -e

echo "Creating sakila_app user with DML-only privileges..."

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    -- Create restricted application user
    CREATE USER sakila_app WITH PASSWORD '$SAKILA_APP_PASSWORD';
    
    -- Grant connection to database
    GRANT CONNECT ON DATABASE $POSTGRES_DB TO sakila_app;
EOSQL

echo "sakila_app user created successfully"
echo "Privileges will be granted by Flyway migration V3 after tables are created"

The init script runs via Docker's /docker-entrypoint-initdb.d/ mechanism, which executes any scripts in that directory when the database is first initialized. It creates sakila_app with a password from an environment variable and grants connection rights. Actual table-level privileges come later via Flyway migration V3.

Flyway Image

Flyway runs as a one-shot container: it connects, applies pending migrations, then exits.

database/flyway/Dockerfile

FROM flyway/flyway:12-alpine
COPY database/flyway/migrations/ /flyway/sql/

We bake the SQL files into the image at build time. No bind mounts, no volume gymnastics. The context: . in docker-compose.yml is needed because the Dockerfile copies from database/flyway/migrations/ relative to the repo root.

Why a Standalone Flyway Container?

A standalone container makes the migration a prerequisite step that completes before the app starts, using the admin credentials. The app never needs to know the admin password. (You could run Flyway from the Spring Boot app itself using the spring-flyway autoconfiguration, but separating concerns keeps things cleaner.)

docker-compose.yml

docker-compose.yml

services:
  postgres:
    build:
      context: database/postgres
      dockerfile: Dockerfile
    container_name: postgres
    environment:
      - POSTGRES_DB=${POSTGRES_DB}
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - SAKILA_APP_PASSWORD=${SAKILA_APP_PASSWORD}
    ports:
      - "5432:5432"
    volumes:
      - postgres-data:/var/lib/postgresql/data
    restart: unless-stopped
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 20s
    networks:
      - monitoring

  flyway:
    build:
      context: .
      dockerfile: database/flyway/Dockerfile
    container_name: flyway
    command: migrate
    restart: "no"
    environment:
      - FLYWAY_URL=jdbc:postgresql://postgres:5432/${POSTGRES_DB}
      - FLYWAY_USER=${POSTGRES_USER}
      - FLYWAY_PASSWORD=${POSTGRES_PASSWORD}
      - FLYWAY_LOCATIONS=filesystem:/flyway/sql
    depends_on:
      postgres:
        condition: service_healthy
    networks:
      - monitoring

  pgadmin:
    image: dpage/pgadmin4:9
    container_name: pgadmin
    environment:
      - PGADMIN_DEFAULT_EMAIL=${PGADMIN_DEFAULT_EMAIL}
      - PGADMIN_DEFAULT_PASSWORD=${PGADMIN_DEFAULT_PASSWORD}
    ports:
      - "5050:80"
    volumes:
      - pgadmin-data:/var/lib/pgadmin
    restart: unless-stopped
    depends_on:
      postgres:
        condition: service_healthy
    networks:
      - monitoring

  spring-java:
    # ...
    environment:
      - SPRING_DATASOURCE_URL=jdbc:postgresql://postgres:5432/${POSTGRES_DB}
      - SPRING_DATASOURCE_USERNAME=sakila_app
      - SPRING_DATASOURCE_PASSWORD=${SAKILA_APP_PASSWORD}
    # ...
    depends_on:
      flyway:
        condition: service_completed_successfully
      tempo:
        condition: service_started
    # ...

  spring-kotlin:
    # ...
    environment:
      - SPRING_DATASOURCE_URL=jdbc:postgresql://postgres:5432/${POSTGRES_DB}
      - SPRING_DATASOURCE_USERNAME=sakila_app
      - SPRING_DATASOURCE_PASSWORD=${SAKILA_APP_PASSWORD}
    # ...
      flyway:
        condition: service_completed_successfully
      tempo:
        condition: service_started
    # ...

  spring-groovy:
    # ...
    environment:
      - SPRING_DATASOURCE_URL=jdbc:postgresql://postgres:5432/${POSTGRES_DB}
      - SPRING_DATASOURCE_USERNAME=sakila_app
      - SPRING_DATASOURCE_PASSWORD=${SAKILA_APP_PASSWORD}
    # ...
    depends_on:
      flyway:
        condition: service_completed_successfully
      tempo:
        condition: service_started
    # ...
  # ...
# ...
volumes:
  postgres-data:
    driver: local
  pgadmin-data:
    driver: local
  # ...

Here's what's happening in this compose file:

• postgres has a healthcheck. Flyway's depends_on waits for service_healthy, meaning it won't try to connect until PostgreSQL is actually ready to accept connections.
• flyway has restart: "no". It's a one-shot job. Once it finishes, it stays finished.
• Spring Boot services depend on flyway with condition: service_completed_successfully. They don't start until migrations are done.
• postgres-data and pgadmin-data volumes persist data across restarts.

Spring Boot Configuration

Add the PostgreSQL Driver

Add runtimeOnly 'org.postgresql:postgresql' to each module's build file. That's the JDBC driver Spring Boot needs to connect to PostgreSQL.

Java

build.gradle

// ...
dependencies {
  // ...
  runtimeOnly 'org.postgresql:postgresql'
}
// ...

Kotlin

build.gradle.kts

// ...
dependencies {
  // ...
  runtimeOnly("org.postgresql:postgresql")
}
// ...

Groovy

build.gradle

// ...
dependencies {
  // ...
  runtimeOnly 'org.postgresql:postgresql'
}
// ...

application.yaml

The datasource and JPA settings point to PostgreSQL. The credentials come from environment variables, which Docker Compose injects at runtime.

resources/application.yaml

# ...
  datasource:
    url: ${SPRING_DATASOURCE_URL}
    driver-class-name: org.postgresql.Driver
    username: ${SPRING_DATASOURCE_USERNAME}
    password: ${SPRING_DATASOURCE_PASSWORD}
  jpa:
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    hibernate:
      ddl-auto: none
    show-sql: false
  flyway:
    enabled: false
# ...

flyway.enabled: false tells Spring Boot not to run Flyway itself. Our standalone Flyway container handles that. Setting it to true here would mean the app tries to run migrations on every startup using sakila_app credentials, which don't have DDL privileges. It would fail.

ddl-auto: none means Hibernate doesn't touch the schema at all. Flyway owns that.

Deploying the Stack in Coolify

The docker-compose.yml is ready to deploy wherever Docker is running. We'll use the same Coolify instance set up in the deployment on a VPS guide.

Set Up Environment Variables

Before deploying, add the new variables to your Coolify application's environment configuration. These only need to be available at runtime. No other checks are needed.

Variable	Description
POSTGRES_DB	Database name (e.g., sakila)
POSTGRES_USER	Admin user (e.g., sakila)
POSTGRES_PASSWORD	Admin user password
SAKILA_APP_PASSWORD	Password for the restricted sakila_app user
PGADMIN_DEFAULT_EMAIL	pgAdmin login email
PGADMIN_DEFAULT_PASSWORD	pgAdmin login password

Deploy and Assign a Domain to pgAdmin

Trigger a deployment. Once it's running, go to the Configuration tab in Coolify and assign a domain to the pgadmin service: https://sakila-pgadmin.your-domain.whatever:80. Coolify's reverse proxy will route traffic to the pgAdmin container on port 80.

Connecting pgAdmin to PostgreSQL

Once pgAdmin is running, open it in your browser and log in with the PGADMIN_DEFAULT_EMAIL and PGADMIN_DEFAULT_PASSWORD you set. Then register the PostgreSQL server.

Right-click Servers → Register → Server... and fill in two tabs:

General tab:

Field	Value
Name	Sakila Database (or whatever you want)

Connection tab:

Field	Value
Host name/address	postgres
Port	5432
Maintenance database	sakila
Username	sakila
Password	your POSTGRES_PASSWORD value
Save password?	on

The hostname is postgres (the Docker service name) because pgAdmin and PostgreSQL share the same Docker network. Docker's internal DNS resolves the service name to the correct container, so no IP address is needed.

Once connected, you'll have full access to browse the schema, run queries, and inspect the Sakila data:

Terminal

curl -s https://sakila-java.pollito.tech/api/films/1 | jq
{
  "instance": "/api/films/1",
  "status": 200,
  "timestamp": "2026-03-13T16:19:38.475466237Z",
  "trace": "625422f841d818b44d771d428cf802a4",
  "data": {
    "description": "A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies",
    "id": 1,
    "language": "English",
    "length": 86,
    "rating": "PG",
    "releaseYear": 2006,
    "title": "ACADEMY DINOSAUR"
  }
}