Schema and migrations

At startup, before any repository or JdbcTemplate can use a database, the starter sets up its schema. It runs your migrations (lib/resources/db/migration/V1__books.sql, Flyway's file naming) or, without migrations, your SQL scripts (schema.sql, data.sql). Then it checks the entities against the tables (spring.data.jdbc.schema=validate, the default), or creates the tables from the entities (create).

Why

A schema changes with the application: a new table, a new column, a renamed one. Migrations record each change as a numbered file, run each one once on every database, and remember what ran. The check against the entities catches a mapping that does not fit the schema at startup, with a clear message, instead of at the first query in production.

Example

lib/resources/
  application.properties
  db/migration/
    V1__books.sql          # run once, first
    V2__add_year.sql       # run once, after V1
    V2_1__index_title.sql  # version 2.1
    R__views.sql           # run again whenever it changes, after the versioned ones
-- V1__books.sql
create table books (
  id integer primary key autoincrement,
  title text not null,
  version integer not null
);

-- V2__add_year.sql
alter table books add column year integer;

-- R__views.sql
drop view if exists recent_books;
create view recent_books as select * from books where year > ${recent-year};
spring.datasource.url=sqlite:file:library.db
spring.sql.migration.placeholders.recent-year=2000
# The default: check every entity's table and columns against the database.
spring.data.jdbc.schema=validate

At startup the log (logger spring.sql.migration) names the versions applied, and a books table missing the year column that Book declares would stop the start, naming both.

The order at startup

For each data source, in one step:

  1. One owner. create and create-drop refuse to run with migration files or with spring.sql.init scripts to run, and migrations refuse to run with those scripts, as in Spring Boot.
  2. The connection, retried spring.sql.migration.connect-retries times, connect-retries-interval apart.
  3. The migrations or, when there are none, the SQL scripts.
  4. The tables from the entities, for create and create-drop.
  5. The check of the entities, for validate.

The data source holds back every connection until this is done.

Migrations

File Runs
V<version>__<description>.sql Once, in the order of the versions. A version is whole numbers separated by . or _ (1, 1.1, 1_1, 2.10), compared number by number: 1.10 is after 1.9, and 1 equals 1.0.
R__<description>.sql Again whenever its checksum changed, after the versioned ones.

A run:

  1. finds the files and reads the history table, schema_history (created when missing);
  2. validates the history against the files (validate-on-migrate): an applied migration whose file changed, or is gone, stops the start, naming the file. Applied versions newer than every file are ignored by default (*:future), so a database from a later version of the application still starts;
  3. applies each pending migration in its own transaction (or all in one with group), after replacing the placeholders. A failed migration is rolled back, not recorded, and stops the start naming the file, the number and line of the statement, and the database's message;
  4. holds the migration lock for the whole run, so several instances starting together migrate once: on SQLite the write lock, retried until lock-timeout; on PostgreSQL an advisory lock. The other instances wait, then find nothing to do.

There is no clean command: the runner never drops anything. Log lines name versions, descriptions and counts, never values.

Migration settings

Key Default Meaning
spring.sql.migration.enabled true Whether migrations run. With no files nothing runs anyway.
spring.sql.migration.locations resources:db/migration Where the files are. {vendor} is replaced by the dialect's name: resources:db/migration/{vendor} reads db/migration/sqlite or db/migration/postgresql.
spring.sql.migration.table schema_history The history table.
spring.sql.migration.validate-on-migrate true A changed or missing applied file, or a never-applied file older than the applied ones, stops the start.
spring.sql.migration.target latest The version to migrate to. Repeatable migrations run only for latest.
spring.sql.migration.out-of-order false Whether a file with a version below the latest applied one still runs.
spring.sql.migration.ignore-migration-patterns *:future Problems of the history to ignore, as type:status.
spring.sql.migration.baseline-on-migrate false A database with tables and no history is taken as being at baseline-version, instead of stopping the start.
spring.sql.migration.baseline-version 1 The baseline's version.
spring.sql.migration.baseline-description << Baseline >> The baseline's description.
spring.sql.migration.placeholders.<name> none The value of ${name} in the files.
spring.sql.migration.placeholder-replacement true Whether placeholders are replaced.
spring.sql.migration.placeholder-prefix ${ What a placeholder starts with; must not be empty.
spring.sql.migration.placeholder-suffix } What a placeholder ends with; must not be empty.
spring.sql.migration.group false All pending migrations in one transaction.
spring.sql.migration.connect-retries 0 How many times a failed connection is retried at startup.
spring.sql.migration.connect-retries-interval 10s The wait between two attempts.
spring.sql.migration.lock-timeout 1m How long to wait for the migration lock another process holds.

ignore-migration-patterns entries are type:status, with the type versioned, repeatable or *, and the status missing (applied, not in the files, below the latest file), future (applied, newer than every file), ignored (a file below the latest applied, never applied) or *.

An existing database

To start the history on a database that already has tables:

spring.sql.migration.baseline-on-migrate=true
spring.sql.migration.baseline-version=3

The history records a baseline at version 3; files up to V3__... are taken as applied, and V4__... and later run.

Spring Boot's names

The runner is the starter's own, with Flyway's file naming and history. Spring Boot's spring.flyway.* names are not used, and a key under them stops the start, naming the spring.sql.migration.* key to use instead.

SQL scripts

Without migrations, schema.sql and data.sql of lib/resources/ run at startup, as in Spring Boot: for simple cases and tests.

Key Default Meaning
spring.sql.init.mode embedded When the scripts run: embedded (only on an in-memory database, sqlite::memory:), always, never.
spring.sql.init.schema-locations resources:schema.sql, resources:schema-<platform>.sql The schema scripts.
spring.sql.init.data-locations resources:data.sql, resources:data-<platform>.sql The data scripts, run after the schema scripts.
spring.sql.init.platform all The platform in the names of the platform scripts.
spring.sql.init.continue-on-error false Whether a failed statement is logged and skipped instead of stopping the start.
spring.sql.init.separator ; The separator of the statements.
spring.sql.init.encoding utf-8 The encoding of the files.

spring.data.jdbc.schema

Value What the starter does
none Nothing.
validate (default) Checks that the tables and columns of the entities exist and fit: types, nullability, foreign keys with their ON DELETE, unique keys. A reference without a foreign key is a warning.
create Creates the missing tables from the entities, with foreign keys, indexes on references and unique constraints.
create-drop Creates the tables, and drops them when the context closes.

create and create-drop are not for use with migrations or scripts. There is no update mode (Hibernate's), which never drops or renames and lets the schema drift: write a migration.

The entities checked or created are those of the repositories on that data source, with their children.

Named databases

Each spring.datasources.<name> has its own migrations, scripts and schema mode:

spring.datasources.stats.url=sqlite:file:stats.db
spring.datasources.stats.sql.migration.locations=resources:db/stats
spring.datasources.stats.data.jdbc.schema=validate

The build reads the schema too

The build reads the migrations and schema.sql of lib/resources/ to check the table and column names of your @Query texts (see @Query). It follows create table, alter table (add, rename, drop a column, rename the table), drop table and create view, in the order the files run.

In Spring (Java)

spring.flyway.locations=classpath:db/migration
spring.jpa.hibernate.ddl-auto=validate

Here the migrations are spring.sql.migration.* (Flyway's naming, the starter's runner) and the schema mode is spring.data.jdbc.schema. Spring Data JDBC itself has no schema check; Hibernate's ddl-auto is the closest.

See also