The Project | About Us | Contribute | Donations | License

HOME

One loose end remains, carried over since 04-02. spring.jpa.hibernate.ddl-auto: update is still creating and modifying tables on its own every time the application starts. It has been convenient while we designed entities and relationships, but nobody knows exactly what SQL has been run against the database, there is no record of the changes, there is no way to reproduce the schema in another environment and there is no way to undo anything. That cannot reach production.

This lesson closes the module by replacing that automatism with versioned migrations: the schema stops being a side effect of the Java classes and becomes source code, written, reviewed and versioned in Git like anything else. We will write CicloUrbana's complete initial schema in PostgreSQL SQL, the migration that retires module 1's DemoStationLoader, and we will learn to evolve a schema in production without stopping the service.

Why ddl-auto will not do in production
The schema as versioned code
Flyway versus Liquibase
Integration with Spring Boot
The script naming convention
The flyway_schema_history table
V1: CicloUrbana's initial schema
V2: Ribalta's data
Migrations and continuous deployment
Callbacks and Java migrations
Migrations per environment
Validating consistency with the entities
Common Mistakes and Tips
Exercises

Why `ddl-auto` will not do in production

Let's revisit the five values from 04-02 with the judgement of a real environment:

Value	Risk in production
`create` / `create-drop`	It wipes the whole database. Catastrophic
`update`	Unreviewed, incomplete and unrecorded changes
`validate`	None: it only checks. The right one
`none`	None: it does nothing

The problem with update runs deeper than "it might delete data" —in fact it does not drop columns—. There are five structural limitations: it cannot modify what exists (changing varchar(80) to varchar(40), adding a NOT NULL to a table with rows or altering a column's type); it deletes nothing, so renaming capacity to total_docks produces both columns, with data in the old one and nulls in the new one; it leaves no record of what was applied, when or who reviewed it; it is not reproducible, because the schema depends on the historical order of startups rather than on a declared state; and it cannot migrate data, such as filling a new column from another one.

And there is a human problem worse than the five technical ones: the schema change stops being a decision and becomes a side effect. Somebody adds a field to an entity for a feature and, on deployment, the production database changes without anyone having reviewed that ALTER TABLE.

The comparison sums up the module:

	`ddl-auto: update`	Flyway
Who decides the SQL	Hibernate	You
Reviewable in Git	No	Yes
Record of what was applied	No	A history table
Reproducible	No	Yes, deterministic
Data migration	No	Yes
Renaming columns	No	Yes
Reversible	No	With undo scripts

The schema as versioned code

The core idea is simple: each schema change is a numbered SQL file that is applied exactly once and in order.

src/main/resources/db/migration/
├── V1__create_initial_schema.sql        ├── V3__create_incidents_table.sql
├── V2__load_ribalta_stations.sql        └── V4__rentals_date_index.sql

Flyway keeps a table in the database itself with the migrations already applied. At startup it compares, runs only the new ones in version order and records the result.

graph TD
    A["The application starts"] --> B["Flyway reads flyway_schema_history"]
    B --> C["It scans db/migration"]
    C --> D{"Are there unapplied versions?"}
    D -->|No| E["It validates checksums and carries on"]
    D -->|Yes| F["It runs them in order: V1, V2, V3..."]
    F --> G["It records each one with its checksum"]
    G --> E
    E --> H["Hibernate validates entities against the schema"]
    H --> I["Application ready"]

The advantages this unlocks: any environment can be rebuilt from scratch by running the migrations in order; the schema change goes through code review, like anything else; Git history explains the evolution of the data model; and development and production converge, because they run the same SQL.

Flyway versus Liquibase

They are the two reference tools in the Java ecosystem, and Spring Boot autoconfigures both.

Aspect	Flyway	Liquibase
Format	Native SQL (or Java)	XML, YAML, JSON or SQL
Learning curve	Low: it is SQL	Medium: you have to learn its language
Engine abstraction	None: you write the engine's SQL	High: it generates SQL per engine
Undo	Paid, in the Pro edition	Free
Predefined refactorings	No	Yes (`renameColumn`, etc.)
Philosophy	Explicit and minimalist	Declarative and comprehensive
Suitable when	A single engine and a team comfortable with SQL	Several engines or a need to undo

CicloUrbana uses Flyway for three concrete reasons: the database is PostgreSQL and it is not going to change, so Liquibase's abstraction adds nothing; writing SQL directly makes the file exactly what gets executed, with no intermediate translation; and anyone who knows SQL can review a migration without learning a new format. If your context is different —a product installed on Oracle, SQL Server or PostgreSQL depending on the customer—, Liquibase is probably better.

Integration with Spring Boot

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>
<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-database-postgresql</artifactId>
</dependency>

The second dependency is mandatory as of Flyway 10, which split support for each engine into its own module. Forgetting it produces a very characteristic error:

Unsupported Database: PostgreSQL 16.2

With the dependencies on the classpath, autoconfiguration does the rest: it creates a Flyway bean, points it at the DataSource and runs the migrations before Hibernate initialises the EntityManagerFactory. That order is essential: by the time Hibernate validates the entities, the tables already exist.

CicloUrbana's configuration:

spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: false
    validate-on-migrate: true
    clean-disabled: true
    table: flyway_schema_history
  jpa:
    hibernate:
      ddl-auto: validate      # no longer update!

Property	What it does	Recommended value
`enabled`	Turns Flyway on	`true`
`locations`	Where to look for migrations	`classpath:db/migration`
`baseline-on-migrate`	Marks an existing DB as the baseline	`false`, except when adopting it in an existing project
`baseline-version`	Version of that baseline	`1`
`validate-on-migrate`	Checks the checksums at startup	`true`
`clean-disabled`	Prevents `flyway clean`	`true`. Never set it to `false`
`out-of-order`	Allows applying earlier versions late	`false`
`table`	Name of the history table	`flyway_schema_history`

clean-disabled: true is non-negotiable. The clean command deletes every object in the schema: tables, data and indexes. Running it by mistake against production is one of those stories that get told for years; since Flyway 9 it is disabled by default, and it is worth leaving it that way explicitly.

The move to ddl-auto: validate is the other key moment: from here on Hibernate does not touch the schema, it only checks that it matches the entities and fails at startup if it does not. It is the safety net that stops migrations and Java classes from drifting apart.

The script naming convention

V2__load_ribalta_stations.sql
│ │ ││
│ │ │└── Description (the _ become spaces)
│ │ └─── Separator: TWO underscores, mandatory
│ └───── Version
└─────── Prefix

Prefix	Type	When it runs
`V`	Versioned	Exactly once, in version order
`R`	Repeatable	Every time its checksum changes, after the versioned ones
`U`	Undo	Only with `flyway undo` (paid edition)

Versioned migrations (V). They are 95% of the work: creating tables, adding columns, inserting reference data. Versions can be 1, 2, 2.1 or 20260901.1; in a large team, a date-and-time scheme prevents two branches from claiming the same version.

Repeatable migrations (R). They carry no version (R__station_occupancy_view.sql) and are re-run whenever their content changes, which makes them ideal for objects that get redefined wholesale: views, functions and procedures. Instead of V5__create_view, V9__modify_view and V14__modify_view_again, there is a single file whose Git history is the view's history.

-- R__station_occupancy_view.sql
CREATE OR REPLACE VIEW station_occupancy_view AS
SELECT s.id, s.name, s.capacity,
       COUNT(b.id) FILTER (WHERE b.status = 'AVAILABLE') AS available_bikes
  FROM stations s
  LEFT JOIN bikes b ON b.station_id = s.id
 WHERE s.active = true
 GROUP BY s.id, s.name, s.capacity;

Name the description well: it appears in the history table and in error messages. V7__add_battery_level_column_to_bikes.sql is informative; V7__changes.sql says nothing.

The `flyway_schema_history` table

Flyway creates this table on its first run: it is the record of everything applied.

Column	Content
`installed_rank`	Order of application
`version`	Version (`NULL` for repeatable ones)
`description`	Readable description
`type`	`SQL`, `JDBC`, `BASELINE`
`script`	File name
`checksum`	Fingerprint of the script's content
`installed_by`	Database user
`installed_on`	Moment of application
`execution_time`	Milliseconds it took
`success`	Whether it finished correctly

SELECT version, description, success, installed_on, execution_time
  FROM flyway_schema_history ORDER BY installed_rank;

 version |      description          | success |    installed_on     | execution_time
---------+---------------------------+---------+---------------------+----------------
 1       | create initial schema     | t       | 2026-09-01 09:14:22 |           184
 2       | load ribalta stations     | t       | 2026-09-01 09:14:22 |            12

The checksum is the central mechanism. At startup, Flyway recomputes each script's fingerprint and compares it with the recorded one; if a script that was already applied has changed, startup fails:

FlywayValidateException: Validate failed: Migrations have failed validation
Migration checksum mismatch for migration version 1
-> Applied to database : 1554682513
-> Resolved locally    : 987654321

It is a deliberate and valuable failure: it means somebody edited an already applied migration, breaking the fundamental premise that migrations are immutable. The file says one thing and the databases where it was applied say another: nobody knows any more what the real schema is.

What to do if it happens to you. If the script was only applied in your local environment, drop the database and migrate again. If it reached a shared environment, there is only one answer: create a new migration with the additional change and revert the previous file to its original content. flyway repair rewrites the checksums, but it is a last resort: it masks the problem instead of solving it.

`V1`: CicloUrbana's initial schema

This is the complete schema, consistent with the entities from 04-03 and the relationships from 04-04.

-- V1__create_initial_schema.sql
-- Initial schema for CicloUrbana: Ribalta's electric bike network.

-- ============================================================
-- Sequences (allocationSize = 50 in the entities, INCREMENT BY 50 here)
-- ============================================================
CREATE SEQUENCE stations_id_seq START WITH 1 INCREMENT BY 50;
CREATE SEQUENCE bikes_id_seq    START WITH 1 INCREMENT BY 50;
CREATE SEQUENCE users_id_seq    START WITH 1 INCREMENT BY 50;
CREATE SEQUENCE rentals_id_seq  START WITH 1 INCREMENT BY 50;

-- ============================================================
-- stations
-- ============================================================
CREATE TABLE stations (
    id          BIGINT       NOT NULL,
    name        VARCHAR(80)  NOT NULL,
    address     VARCHAR(200) NOT NULL,
    capacity    INTEGER      NOT NULL,
    latitude    NUMERIC(9,6) NOT NULL,
    longitude   NUMERIC(9,6) NOT NULL,
    active      BOOLEAN      NOT NULL DEFAULT TRUE,
    version     BIGINT       NOT NULL DEFAULT 0,
    created_at  TIMESTAMPTZ  NOT NULL,
    updated_at  TIMESTAMPTZ  NOT NULL,

    CONSTRAINT pk_stations           PRIMARY KEY (id),
    CONSTRAINT uk_stations_name      UNIQUE (name),
    CONSTRAINT ck_stations_capacity  CHECK (capacity > 0 AND capacity <= 200),
    CONSTRAINT ck_stations_latitude  CHECK (latitude BETWEEN -90 AND 90),
    CONSTRAINT ck_stations_longitude CHECK (longitude BETWEEN -180 AND 180)
);

CREATE INDEX idx_stations_active   ON stations (active);
CREATE INDEX idx_stations_location ON stations (latitude, longitude);

-- ============================================================
-- bikes
-- ============================================================
CREATE TABLE bikes (
    id             BIGINT      NOT NULL,
    plate          VARCHAR(10) NOT NULL,
    status         VARCHAR(20) NOT NULL,
    battery_level  INTEGER     NOT NULL,
    station_id     BIGINT,
    version        BIGINT      NOT NULL DEFAULT 0,
    created_at     TIMESTAMPTZ NOT NULL,
    updated_at     TIMESTAMPTZ NOT NULL,

    CONSTRAINT pk_bikes          PRIMARY KEY (id),
    CONSTRAINT uk_bikes_plate    UNIQUE (plate),
    CONSTRAINT fk_bikes_station  FOREIGN KEY (station_id)
        REFERENCES stations (id) ON DELETE SET NULL,
    CONSTRAINT ck_bikes_status   CHECK (status IN
        ('AVAILABLE', 'IN_USE', 'MAINTENANCE', 'RETIRED')),
    CONSTRAINT ck_bikes_battery  CHECK (battery_level BETWEEN 0 AND 100),
    CONSTRAINT ck_bikes_plate    CHECK (plate ~ '^RB-[0-9]{4}$')
);

CREATE INDEX idx_bikes_status  ON bikes (status);
CREATE INDEX idx_bikes_station ON bikes (station_id);

-- ============================================================
-- users
-- ============================================================
CREATE TABLE users (
    id           BIGINT       NOT NULL,
    email        VARCHAR(120) NOT NULL,
    name         VARCHAR(120) NOT NULL,
    fare_type    VARCHAR(20)  NOT NULL DEFAULT 'STANDARD',
    signup_date  DATE         NOT NULL,
    active       BOOLEAN      NOT NULL DEFAULT TRUE,
    version      BIGINT       NOT NULL DEFAULT 0,
    created_at   TIMESTAMPTZ  NOT NULL,
    updated_at   TIMESTAMPTZ  NOT NULL,

    CONSTRAINT pk_users       PRIMARY KEY (id),
    CONSTRAINT uk_users_email UNIQUE (email),
    CONSTRAINT ck_users_fare  CHECK (fare_type IN
        ('STANDARD', 'STUDENT', 'SENIOR'))
);

-- ============================================================
-- rentals
-- ============================================================
CREATE TABLE rentals (
    id                     BIGINT      NOT NULL,
    user_id                BIGINT      NOT NULL,
    bike_id                BIGINT      NOT NULL,
    origin_station_id      BIGINT      NOT NULL,
    destination_station_id BIGINT,
    started_at             TIMESTAMPTZ NOT NULL,
    ended_at               TIMESTAMPTZ,
    total_amount           NUMERIC(8,2),
    status                 VARCHAR(20) NOT NULL DEFAULT 'IN_PROGRESS',
    version                BIGINT      NOT NULL DEFAULT 0,
    created_at             TIMESTAMPTZ NOT NULL,
    updated_at             TIMESTAMPTZ NOT NULL,

    CONSTRAINT pk_rentals PRIMARY KEY (id),
    CONSTRAINT fk_rentals_user FOREIGN KEY (user_id)
        REFERENCES users (id),
    CONSTRAINT fk_rentals_bike FOREIGN KEY (bike_id)
        REFERENCES bikes (id),
    CONSTRAINT fk_rentals_origin_station FOREIGN KEY (origin_station_id)
        REFERENCES stations (id),
    CONSTRAINT fk_rentals_destination_station FOREIGN KEY (destination_station_id)
        REFERENCES stations (id),
    CONSTRAINT ck_rentals_status CHECK (status IN
        ('IN_PROGRESS', 'FINISHED', 'EXPIRED', 'CANCELLED')),
    CONSTRAINT ck_rentals_ended_at CHECK (ended_at IS NULL OR ended_at >= started_at),
    CONSTRAINT ck_rentals_amount   CHECK (total_amount IS NULL OR total_amount >= 0)
);

CREATE INDEX idx_rentals_user       ON rentals (user_id);
CREATE INDEX idx_rentals_bike       ON rentals (bike_id);
CREATE INDEX idx_rentals_started_at ON rentals (started_at DESC);

-- A user cannot have two rentals in progress at the same time
CREATE UNIQUE INDEX uk_rentals_user_in_progress
    ON rentals (user_id) WHERE ended_at IS NULL;

Five decisions in the script deserve comment:

INCREMENT BY 50 on the sequences, which must match exactly the allocationSize = 50 of the entities (04-03); if they do not match, Hibernate generates colliding ids.

CHECK constraints on the enums. ck_bikes_status guarantees in the database what @Enumerated(EnumType.STRING) guarantees in Java, and defends against writes that do not go through the application. Its trade-off: adding a value to the enum requires a migration that updates the constraint.

ON DELETE SET NULL on bikes.station_id, which reflects the domain decision from 04-04: deleting a station does not delete its bikes, it leaves them with no station assigned.

Indexes on the foreign keys, because PostgreSQL does not create them automatically unlike MySQL: without idx_rentals_user, the "my rentals" query would scan the whole table sequentially.

The partial unique index uk_rentals_user_in_progress. It is the jewel of the script. WHERE ended_at IS NULL makes the uniqueness apply only to rentals in progress: a user can have hundreds of finished rentals, but at most one open. It is CicloUrbana's central business rule guaranteed by the database, immune to race conditions and to any failure of the application logic. No check in Java offers that guarantee.

`V2`: Ribalta's data

This migration retires module 1's DemoStationLoader, which had been recreating the four stations on every startup since 01-05.

-- V2__load_ribalta_stations.sql
-- Initial stations of Ribalta's municipal network.

INSERT INTO stations (id, name, address, capacity, latitude, longitude,
                      active, version, created_at, updated_at)
VALUES
    (1, 'Main Square',   'Main Square 1',        24, 40.416775, -3.703790,
     TRUE, 0, NOW(), NOW()),
    (2, 'North Station', 'Station Avenue 3',     30, 40.428900, -3.698120,
     TRUE, 0, NOW(), NOW()),
    (3, 'River Park',    'Riverside Walk 12',    18, 40.409330, -3.712450,
     TRUE, 0, NOW(), NOW()),
    (4, 'University',    'South Campus, Gate B', 36, 40.435210, -3.689870,
     TRUE, 0, NOW(), NOW());

-- The sequence must end up above the ids inserted by hand
SELECT setval('stations_id_seq', 100, false);

-- The council's fares
INSERT INTO fares (code, description, price_per_minute, minimum_amount)
VALUES ('STANDARD', 'General fare',            0.15, 0.50),
       ('STUDENT',  'Student fare (-40%)',     0.09, 0.30),
       ('SENIOR',   'Senior fare (-50%)',      0.075, 0.25);

Two critical points:

setval on the sequence. When explicit ids are inserted, the sequence does not advance. If you do not adjust it, the first record created through the API will ask for id 1 and collide with "Main Square". setval(..., 100, false) leaves it starting at 100, with plenty of headroom.

Idempotency. A V migration runs exactly once, so it does not need to be idempotent; but writing it with ON CONFLICT (id) DO NOTHING lets the same SQL be reused in other contexts without risk.

Which data belongs in a migration and which does not. Reference data —fares, incident types, the initial network of stations— is part of the functional schema and belongs in migrations. Test data —fictional users, sample rentals— must not appear in production: it goes in migrations separated by environment (section 11).

With this, DemoStationLoader is removed from the project: its data no longer depends on the application starting, it lives in the database and survives restarts. Ribalta's four stations have survived a restart.

Migrations and continuous deployment

Here is the part that separates a small project from one in real production. During a zero-downtime deployment, two versions of the application coexist against a single database:

graph TD
    A["v1.4 on 3 replicas"] --> B["Migration V8"]
    B --> C["v1.4 (2 replicas) + v1.5 (1 replica)"]
    C --> D["v1.5 on 3 replicas"]
    style C fill:#ffe6cc

During the intermediate phase, the old version keeps running queries against the already migrated schema. Hence the golden rule: every migration must be backwards compatible with the previous version of the application.

Change	Compatible?	Why
Adding a table	Yes	The old version ignores it
Adding a nullable column	Yes	The old `INSERT`s leave it null
Adding an index	Yes	Transparent (with `CONCURRENTLY`)
Adding a `NOT NULL` column with a `DEFAULT`	Yes	The old `INSERT`s take the default value
Adding a `NOT NULL` column without a `DEFAULT`	No	The old version's `INSERT`s fail
Dropping a column	No	The old version still reads it
Renaming a column	No	It amounts to dropping and adding
Reducing a `VARCHAR`'s length	No	The existing data may not fit
Adding a `NOT NULL` constraint	No	It breaks the `INSERT`s that omitted it

The expand/contract pattern is the technique for making an incompatible change in compatible steps. Renaming capacity to total_docks without stopping CicloUrbana:

Phase 1 — Expand (V8): add the new column and copy the data.

-- V8__expand_total_docks.sql
ALTER TABLE stations ADD COLUMN total_docks INTEGER;
UPDATE stations SET total_docks = capacity;

-- A trigger keeps both columns in sync during the transition
CREATE OR REPLACE FUNCTION sync_docks() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.total_docks IS DISTINCT FROM OLD.total_docks THEN
        NEW.capacity := NEW.total_docks;
    ELSE
        NEW.total_docks := NEW.capacity;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_docks
    BEFORE INSERT OR UPDATE ON stations
    FOR EACH ROW EXECUTE FUNCTION sync_docks();

Phase 2 — Deploy the version that uses total_docks. Both columns exist and the trigger keeps them consistent, so the old version and the new one coexist without trouble.

Phase 3 — Contract (V9), once the old version is confirmed to be no longer deployed:

-- V9__contract_drop_capacity.sql
DROP TRIGGER trg_sync_docks ON stations;
DROP FUNCTION sync_docks();
ALTER TABLE stations DROP COLUMN capacity;
ALTER TABLE stations ALTER COLUMN total_docks SET NOT NULL;

That is three deployments for one rename. It looks excessive, and it is exactly what separates a system deployable at any hour from one that needs a maintenance window.

The inviolable rule: an already applied migration is never edited. If V5 has a bug, it is fixed with V6; editing it breaks the checksum in every environment and leaves the real schema in an unknown state.

Two operational PostgreSQL tips: use CREATE INDEX CONCURRENTLY on large tables, because a normal CREATE INDEX blocks writes for the whole build (it needs its own migration, since it cannot run inside a transaction); and remember that ADD COLUMN ... DEFAULT has been instantaneous since PostgreSQL 11, but ALTER COLUMN ... TYPE rewrites the entire table.

Callbacks and Java migrations

Callbacks. Flyway runs SQL files at specific moments of the lifecycle if they follow the naming convention: beforeMigrate.sql (before all of them), afterMigrate.sql (after all of them), beforeEachMigrate.sql (before each one) and afterMigrateError.sql (if any fails). An afterMigrate.sql with ANALYZE stations; ANALYZE bikes; ANALYZE rentals; recomputes PostgreSQL's planner statistics, especially useful after a migration that has moved a lot of data.

Java migrations. When the transformation does not fit into SQL —values have to be decrypted, a service called or batches processed with complex logic—, you write a class extending BaseJavaMigration:

package db.migration;   // mandatory package

public class V7__normalise_plates extends BaseJavaMigration {

    @Override
    public void migrate(Context context) throws Exception {
        try (Statement read = context.getConnection().createStatement();
             ResultSet rows = read.executeQuery(
                     "SELECT id, plate FROM bikes WHERE plate !~ '^RB-[0-9]{4}$'")) {

            try (PreparedStatement write = context.getConnection()
                    .prepareStatement("UPDATE bikes SET plate = ? WHERE id = ?")) {
                while (rows.next()) {
                    write.setString(1, normalise(rows.getString("plate")));
                    write.setLong(2, rows.getLong("id"));
                    write.addBatch();
                }
                write.executeBatch();
            }
        }
    }

    private String normalise(String original) {   // "rb142" -> "RB-0142"
        return "RB-" + String.format("%04d",
                Integer.parseInt(original.replaceAll("\\D", "")));
    }
}

Four requirements: the class must live in the db.migration package; its name follows the same convention (V7__description); it uses plain JDBC, never the application's repositories, which are not initialised yet; and it must not manage the transaction, which Flyway already handles. Use them sparingly: SQL is more transparent and reviewable, and a Java migration is only justified when the logic is inexpressible in SQL.

Migrations per environment

Test data cannot reach production. spring.flyway.locations accepts several paths:

src/main/resources/db/
├── migration/                    # schema and reference data: ALL environments
│   ├── V1__create_initial_schema.sql
│   └── V2__load_ribalta_stations.sql
└── data-dev/                     # test data: development ONLY
    └── V900__test_users_and_rentals.sql

# application-dev.yml
spring:
  flyway:
    locations: classpath:db/migration,classpath:db/data-dev

# application-prod.yml
spring:
  flyway:
    locations: classpath:db/migration

High version numbers (V900) keep the test data always at the end and avoid collisions with the real schema migrations.

An important warning: each environment has its own history table, so there is no conflict between them. What does happen is that if a developer runs the prod profile against their local database, Flyway will detect that V900 is applied but no longer appears in locations and will warn with Detected applied migration not resolved locally: it is a warning, not an error. Profiles are studied in depth in 07-02.

Validating consistency with the entities

With Flyway managing the schema, ddl-auto: validate comes fully into its own: Hibernate compares each entity with the real tables and fails at startup if something does not add up.

Schema-validation: missing column [battery_level] in table [bikes]

That message means somebody added a field to the entity and forgot the migration. It is exactly the failure you want, and it arrives before any request suffers it.

validate checks the existence of tables and columns, their types and nullability, and the sequences. It does not check indexes, CHECK constraints or foreign keys, so it is no substitute for reviewing the SQL.

The complete workflow when adding a field to CicloUrbana: add the field to the entity with its annotations; write the corresponding V<n>__...sql migration; start up, with Flyway applying and Hibernate validating; and if validate fails, fix the discrepancy with another new migration, never by editing the previous one.

The Flyway commands from Maven, useful outside the application's startup:

./mvnw flyway:info      # status of each migration: applied, pending, failed
./mvnw flyway:validate  # checks the checksums without applying anything
./mvnw flyway:migrate   # applies the pending ones
./mvnw flyway:baseline  # marks an existing DB as the baseline
./mvnw flyway:repair    # repairs checksums and clears failed entries

They require configuring the plugin in the pom.xml with the URL, user and password taken from environment variables, as in 04-02. flyway:info is especially useful in continuous integration, to check an environment's state before deploying (module 8).

Common Mistakes and Tips

Editing an already applied migration. It breaks the checksum and startup fails in every environment where it was applied. Always fix with a new migration.

Forgetting flyway-database-postgresql. Since Flyway 10 it is mandatory: Unsupported Database: PostgreSQL 16.

Leaving ddl-auto: update with Flyway active. The two compete for the schema and the result is unpredictable. With Flyway, always validate or none.

Forgetting setval after inserting explicit ids. The first record created through the API collides with the preloaded data.

Putting test data in db/migration. It will end up in production. Separate it with locations.

Adding a NOT NULL column without a DEFAULT in a continuous deployment. The old version's INSERTs fail during the transition.

Enabling flyway clean. It wipes the whole schema. clean-disabled: true, always.

Not indexing the foreign keys. PostgreSQL does not do it on its own, and queries by relationship end up as sequential scans.

Tip: one migration, one logical change. It is easier to review and, if it fails, easier to diagnose. And test every migration against a copy of production before deploying: an ALTER TABLE that takes 2 seconds with 4 stations can take 20 minutes with 2 million rentals.

Tip: use constraints and partial unique indexes for business rules. uk_rentals_user_in_progress guarantees in the database something that no check in Java can ensure against race conditions.

Exercises

Exercise 1: the incidents migration

Write V3__create_incidents_table.sql for the SINGLE_TABLE inheritance model from 04-04: an incidents table with an id from a sequence, a foreign key to bikes with cascading delete, a type discriminator column, a mandatory description, a report date, a status, auditing columns, and the specific fields detected_level (battery) and police_report (vandalism). Include CHECK constraints and indexes, and justify why the specific columns accept nulls.

Exercise 2: dropping a column without stopping the service

CicloUrbana has a phone VARCHAR(20) NOT NULL column on users that is no longer used. There are 3 replicas in production with a rolling deployment. Describe the complete sequence of migrations and deployments to remove it without interrupting the service, and explain what would happen if ALTER TABLE users DROP COLUMN phone were run directly.

Exercise 3: diagnose three incidents

Diagnose and resolve each situation.

On startup in pre-production: Migration checksum mismatch for migration version 3.
A developer runs V4 locally and it works; in continuous integration it fails with column "active" of relation "stations" already exists.
After deploying, the application starts but fails when creating stations: duplicate key value violates unique constraint "pk_stations".

Solutions

Solution 1.

-- V3__create_incidents_table.sql
CREATE SEQUENCE incidents_id_seq START WITH 1 INCREMENT BY 50;

CREATE TABLE incidents (
    id              BIGINT       NOT NULL,
    type            VARCHAR(20)  NOT NULL,
    bike_id         BIGINT       NOT NULL,
    description     VARCHAR(500) NOT NULL,
    status          VARCHAR(20)  NOT NULL DEFAULT 'OPEN',
    reported_at     TIMESTAMPTZ  NOT NULL,
    closed_at       TIMESTAMPTZ,
    -- Subclass-specific fields: NULLABLE out of necessity
    detected_level  INTEGER,
    police_report   VARCHAR(40),
    version         BIGINT       NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ  NOT NULL,
    updated_at      TIMESTAMPTZ  NOT NULL,

    CONSTRAINT pk_incidents PRIMARY KEY (id),
    CONSTRAINT fk_incidents_bike FOREIGN KEY (bike_id)
        REFERENCES bikes (id) ON DELETE CASCADE,
    CONSTRAINT ck_incidents_type   CHECK (type IN ('BATTERY', 'VANDALISM', 'BREAKDOWN')),
    CONSTRAINT ck_incidents_status CHECK (status IN ('OPEN', 'IN_REVIEW', 'CLOSED')),
    CONSTRAINT ck_incidents_level  CHECK (detected_level IS NULL
                                          OR detected_level BETWEEN 0 AND 100),
    CONSTRAINT ck_incidents_closed CHECK (closed_at IS NULL
                                          OR closed_at >= reported_at),
    -- Consistency between the discriminator and its own fields
    CONSTRAINT ck_incidents_battery CHECK (
        type <> 'BATTERY' OR detected_level IS NOT NULL)
);

CREATE INDEX idx_incidents_bike   ON incidents (bike_id);
CREATE INDEX idx_incidents_status ON incidents (status)
    WHERE status <> 'CLOSED';

Why the specific columns accept nulls. It is the unavoidable trade-off of SINGLE_TABLE: every subclass shares one table, and a vandalism incident has no detected_level. Declaring them NOT NULL would make it impossible to insert any type that did not have every field.

The ck_incidents_battery constraint recovers part of that lost integrity: if the type is BATTERY, the level is mandatory. It is the technique that compensates for SINGLE_TABLE's main weakness, and one more reason to have chosen JOINED if the own fields were many (exercise 3 of 04-04).

The partial index on status is a deliberate optimisation: it indexes only the incidents that are not closed, which are the ones queried daily, keeping the index small even as the history grows without limit.

Solution 2. The correct sequence has three steps:

Step 1 — V10: relax the constraint.

-- V10__users_phone_nullable.sql
ALTER TABLE users ALTER COLUMN phone DROP NOT NULL;

It is a backwards compatible change: the old version keeps sending the phone and works; the new one will be able to omit it.

Step 2 — Deploy the version that no longer uses phone, removing the field from the User entity, from the DTO and from the mapper. During the rolling deployment, old replicas —which write the phone— and new ones —which do not— coexist, and both work because the column exists and accepts nulls.

Step 3 — V11: drop the column, in a later deployment, once no old replica is confirmed to be alive.

-- V11__drop_users_phone.sql
ALTER TABLE users DROP COLUMN phone;

What would happen with a direct DROP COLUMN. During the intermediate phase, the old replicas keep running INSERT INTO users (..., phone, ...) and SELECT ... phone ..., and every one of those queries would fail with column "phone" does not exist. Worse still, with ddl-auto: validate any old replica that restarted would not start at all: a partial outage of CicloUrbana during the deployment, exactly what the rolling deployment was meant to avoid. And one further consideration: step 3 is irreversible, so it is worth archiving the data before deleting it, because no undo migration recovers information that no longer exists.

Solution 3.

1. Migration checksum mismatch on version 3. Somebody edited V3__...sql after it had been applied in pre-production. The file's checksum no longer matches the recorded one. Diagnosis: SELECT version, checksum, installed_on FROM flyway_schema_history WHERE version = '3'; and compare with the file's Git history. Fix: revert V3 to its original content —the one that was applied— and create V6 with the change that was meant to be introduced. Only if the change was purely cosmetic (a comment, a space) and the effective SQL has been verified to be identical, flyway:repair recomputes the checksums. It is not the default option: it masks the problem.

2. column "active" already exists only in continuous integration. The CI database is not clean: it keeps the schema from an earlier run in which the column was already created, probably by a ddl-auto: update that was left active or by an earlier migration that already added it; locally it worked because the database was created from scratch. Fix: make CI start from an ephemeral database —Testcontainers (06-05) is exactly that—, check that no earlier migration already creates that column and confirm that ddl-auto is at validate in every environment.

3. duplicate key value violates unique constraint "pk_stations". The setval from V2 is missing. The four stations were inserted with explicit ids 1-4, but the stations_id_seq sequence is still at its initial value, so the first station created through the API asks for id 1 and collides with "Main Square". Fix: a new migration that adjusts the sequence above the real maximum.

-- V12__adjust_stations_sequence.sql
SELECT setval('stations_id_seq',
              GREATEST((SELECT COALESCE(MAX(id), 0) FROM stations) + 50, 100),
              false);

GREATEST with the real maximum makes it safe whatever the environment's state, and the margin of 50 respects the allocationSize. The general lesson: whenever a migration inserts explicit ids into a table with a sequence, it must adjust the sequence in the same script.

Conclusion

Module 4 closes with CicloUrbana running on real persistence and with the schema under control. You know why ddl-auto: update cannot reach production —it does not modify what exists, it does not delete, it leaves no record, it is not reproducible and it does not migrate data— and, above all, why its worst defect is a human one: it turns the schema change into a side effect instead of a reviewed decision. You have compared Flyway with Liquibase and you understand why this project chooses native SQL over a single engine. You have integrated Flyway with its two dependencies, configured spring.flyway.* with clean-disabled: true as a red line, and made the module's decisive change: ddl-auto goes from update to validate, so Hibernate no longer touches the schema and limits itself to checking that entities and tables match, failing at startup when they do not. You have mastered the V/R/U convention, you know what a repeatable migration is for and you understand the flyway_schema_history table and its checksum, the fingerprint that turns startup into a guardian against editing already applied migrations.

You have written Ribalta's complete initial schema in PostgreSQL SQL: four sequences with INCREMENT BY 50 matching the allocationSize from 04-03, four tables with their primary keys, named foreign keys, CHECK constraints that replicate in the database what the enums guarantee in Java, explicit indexes on the foreign keys —because PostgreSQL does not create them on its own— and auditing and version columns. And with them the partial unique index uk_rentals_user_in_progress, which turns CicloUrbana's central business rule into a guarantee from the engine, immune to race conditions. The V2 migration loaded the four stations and the three fares, adjusted the sequence with setval and finally retired module 1's DemoStationLoader. You know the expand/contract pattern for renaming a column across three deployments without interrupting the service, the table of compatible and incompatible changes, callbacks, Java migrations with BaseJavaMigration and the separation of test data through locations.

Look at where CicloUrbana is. It started out as a main printing a message. It has a thirteen-endpoint REST API designed around REST's constraints, with correct verbs and status codes, declarative validation, DTOs that separate domain from contract, uniform RFC 7807 errors and a publishable OpenAPI contract. And now, on top of that, a persistent data model on PostgreSQL 16, with well-mapped entities, lazy relationships, Spring Data repositories, queries that do not suffer the N+1 problem, transactions in the service layer and a schema versioned in Git that anyone can rebuild from scratch. Ribalta's four stations survive a restart, and so do the rentals, the bikes and the users.

And precisely because of that there is now a problem that did not matter before: the API is completely open. Anyone who knows the URL can create stations, decommission bikes, look up the personal data of Ribalta's citizens or finish somebody else's rental. While everything lived in memory and was lost on restart, it was a demo; now there is real, persistent data belonging to a municipal network, and there is not a single check on who is at the other end. Module 5, Security in Spring Boot, solves it: we will see what Spring Security is and how its filter chain is inserted in front of the DispatcherServlet we met in 03-01; we will configure it with SecurityFilterChain, replacing the default generated password; we will distinguish authentication from authorisation and model CicloUrbana's roles —citizen, operator, administrator— on the User entity we have just created; we will implement stateless authentication with JWT, appropriate for an API consumed by a mobile application; and we will take security down to method level with @PreAuthorize so that a citizen can only finish their own rentals. The Ribalta network is about to get doors.

Schema Migrations with Flyway

Contents

Why `ddl-auto` will not do in production

The schema as versioned code

Flyway versus Liquibase

Integration with Spring Boot

The script naming convention

The `flyway_schema_history` table

`V1`: CicloUrbana's initial schema

`V2`: Ribalta's data

Migrations and continuous deployment

Callbacks and Java migrations

Migrations per environment

Validating consistency with the entities

Common Mistakes and Tips

Exercises

Exercise 1: the incidents migration

Exercise 2: dropping a column without stopping the service

Exercise 3: diagnose three incidents

Solutions

Conclusion

Spring Boot Course

Module 1: Introduction to Spring Boot

Module 2: Spring Boot Core Concepts

Module 3: Building RESTful Web Services

Module 4: Data Access with Spring Boot

Module 5: Security in Spring Boot

Module 6: Testing in Spring Boot

Module 7: Advanced Spring Boot Features

Module 8: Deploying Spring Boot Applications

Module 9: Performance and Monitoring

Module 10: Best Practices and Tips

Schema Migrations with Flyway

Contents

Why ddl-auto will not do in production

The schema as versioned code

Flyway versus Liquibase

Integration with Spring Boot

The script naming convention

The flyway_schema_history table

V1: CicloUrbana's initial schema

V2: Ribalta's data

Migrations and continuous deployment

Callbacks and Java migrations

Migrations per environment

Validating consistency with the entities

Common Mistakes and Tips

Exercises

Exercise 1: the incidents migration

Exercise 2: dropping a column without stopping the service

Exercise 3: diagnose three incidents

Solutions

Conclusion

Spring Boot Course

Module 1: Introduction to Spring Boot

Module 2: Spring Boot Core Concepts

Module 3: Building RESTful Web Services

Module 4: Data Access with Spring Boot

Module 5: Security in Spring Boot

Module 6: Testing in Spring Boot

Module 7: Advanced Spring Boot Features

Module 8: Deploying Spring Boot Applications

Module 9: Performance and Monitoring

Module 10: Best Practices and Tips

Why `ddl-auto` will not do in production

The `flyway_schema_history` table

`V1`: CicloUrbana's initial schema

`V2`: Ribalta's data