
# 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

```text
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
```

```sql
-- 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};
```

```properties
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:

```properties
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:

```properties
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](query.md)). 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)

```properties
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

- [Data sources](data-sources.md)
- [Entities](entities.md)
- [All properties](properties.md)
