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:
- One owner.
createandcreate-droprefuse to run with migration files or withspring.sql.initscripts to run, and migrations refuse to run with those scripts, as in Spring Boot. - The connection, retried
spring.sql.migration.connect-retriestimes,connect-retries-intervalapart. - The migrations or, when there are none, the SQL scripts.
- The tables from the entities, for
createandcreate-drop. - 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:
- finds the files and reads the history table,
schema_history(created when missing); - 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; - 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; - 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. |
- A missing default script is skipped. A location you give must exist, unless it starts with
optional:.@@platform@@in a location is replaced by the platform. - A failed statement stops the start naming the file and the line, unless
continue-on-error.
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.