Understand PostgreSQL, JPA and Flyway

PostgreSQL runs as a separate database server. JPA maps Java objects to relational data. Flyway manages schema evolution. These are different responsibilities. PDF Batch Studio Server uses JPA for job data and Flyway for its tables. We first inspect a small SQL schema before reading the real adapter and entity.

Example files

01Start a separate PostgreSQL learning database

Use Docker Compose with the sample file. The database binds only to 127.0.0.1:15432. Credentials are explicitly local example values. Run docker compose up -d, then execute SQL with docker compose exec -T db psql -U learner -d learning < V1__jobs.sql.

compose.yaml · yamlOpen file

services:
  db:
    image: postgres:17
    environment:
      POSTGRES_USER: learner
      POSTGRES_PASSWORD: local-tutorial-only
      POSTGRES_DB: learning
    ports:
      - "127.0.0.1:15432:5432"
    volumes:
      - learning-data:/var/lib/postgresql/data
volumes:
  learning-data:

02Read schema and transaction

UUID identifies the job. CHECK limits permitted learning states. BEGIN and COMMIT connect the job with its event-log row. This relationship forms the basis of a later outbox.

V1__jobs.sql · sqlOpen file

CREATE TABLE jobs (
  id UUID PRIMARY KEY,
  status TEXT NOT NULL CHECK (status IN ('DRAFT', 'READY', 'DONE'))
);
CREATE TABLE job_events (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  job_id UUID NOT NULL REFERENCES jobs(id),
  event_type TEXT NOT NULL
);
BEGIN;
INSERT INTO jobs VALUES ('00000000-0000-0000-0000-000000000001', 'DRAFT');
INSERT INTO job_events(job_id, event_type) VALUES ('00000000-0000-0000-0000-000000000001', 'JobCreated');
COMMIT;
SELECT j.status, e.event_type FROM jobs j JOIN job_events e ON e.job_id=j.id;

03Understand JPA as mapping

Read BatchJobEntity and JpaJobRepositoryAdapter at the pinned version. The entity describes the persisted shape. The adapter translates between it and BatchJob in the core. A domain class therefore does not need JPA annotations.

04Keep migrations immutable

A published V1 file is not rewritten retrospectively. New changes get V2 and later versions. Flyway checks applied migrations. Testcontainers verifies the same rules using PostgreSQL rather than only a simplified substitute database.

Expected result

The query shows a DRAFT job and a JobCreated entry. Both were written in one transaction.

Your exercise

Write a V2 migration adding a created_at column. What must happen for existing rows?

Show the solution and explanation

Define an appropriate default or explicit backfill before making the column NOT NULL. Test a database containing an existing job. A successful empty installation does not prove an upgrade migration works.

Project context and sources

The example is simplified for learning. The linked source version shows its use in the project and the checks applied there.