Handbooks / Spring Boot / Chapter 6
Database Migrations
7 pages · ~5 min✓ Reviewed
Builds on Spring Data JPA. Next up: Spring Security.
Schema Migrations: The Mental Model
1. What a Migration Is
- Migration = versioned, ordered, scripted change to the schema
- Lives in source control beside the application code
- The tool logs applied scripts in a history table
- Dev, CI and prod all converge to the same schema state
2. Core Workflow
- Write scriptnew file
- Commitwith the code
- Run toolapp startup or CI deploy
- Apply pendingin order
| Script | In history table? | Tool does |
|---|---|---|
| V1 | yes | skip |
| V2 | yes | skip |
| V3 | no | apply, then record |
3. Rules and Payoff
| Without migrations | With migrations | |
|---|---|---|
| Environments | manual SQL drift | same state |
| History | no audit trail | git + history table |
| Deploys | unrepeatable | repeatable |
Applied file = frozen
Wrong schema change already applied? Add the next migration. Never edit the old file.
Flyway: Versioned and Repeatable Migrations
1. Versioned vs repeatable
| Versioned | Repeatable | |
|---|---|---|
| File | V2__add_orders_table.sql | R__order_views.sql |
| Runs | once, in version order | when checksum changes |
| Order | by version | after all V, by description |
| Use for | tables, data changes | views, procedures, functions (CREATE OR REPLACE) |
2. Naming and location
- Pattern: V or R, version, two underscores, description with
_for spaces
Flyway Ordering, Commands and Pitfalls
1. Version Ordering
- Versions compare numerically by segment:
V1.2runs beforeV1.10 - No zero-padding needed; it is not a string sort
- A lower version added after a higher one was applied is rejected by default
flyway.outOfOrder=trueallows it, but history may turn inconsistent
2. Core Commands
| Command | Does |
|---|---|
migrate | Apply pending migrations |
info | Show status of each migration |
validate | Check applied checksums |
repair |
Liquibase Changelogs and Comparison with Flyway
1. Changelog and changeSet
- Changelog = master file (YAML, XML, JSON or SQL) of ordered changeSets
- changeSet identity = id + author + file path
- Each applied one is stored in
DATABASECHANGELOGwith an MD5 checksum
yaml
databaseChangeLog:
- changeSet:
id: 1
author: dev
changes:
- createTable:
tableName: usersone changeSet in a YAML changelog
createTable,addColumn: database-agnostic, rollback is generated- Raw SQL changeSet: you must write an explicit
rollback
2. Attributes and the lock
runOnChange: re-run when the changeSet is edited
ddl-auto and Why Not in Production
1. The five values
| Value | On startup | Data |
|---|---|---|
none | does nothing | untouched |
validate | checks entities vs schema | untouched |
update | adds missing tables, columns | kept |
create | drops, recreates schema | destroyed |
create-drop | as create, drops at shutdown | destroyed |
2. Why not in production
HikariCP Connection Pooling and Cheat Sheet
1. Why Pool
- Pooling reuses open connections: opening one per request is slow
- HikariCP is the Spring Boot default pool
| Property | Default | Meaning |
|---|---|---|
maximumPoolSize | 10 | max connections |
minimumIdle | = max size | fixed-size pool |
connectionTimeout | 30s | wait for a connection |
idleTimeout | 10min | retire idle extras |
Check yourself
V2__add_orders.sql already ran in prod. Someone adds a column to that file and redeploys. What happens?
- Startup fails: the checksum no longer matches
flyway_schema_history. - Fix: put the change in a new
V3__...file and restore V2.
Files V1.2__a.sql and V1.10__b.sql are both pending. Which runs first?
V1.2runs first.- Versions compare numerically by segment (2 < 10), not as strings.
With ddl-auto=update, you rename an entity field name to fullName. What does prod end up with?
- A new empty
full_namecolumn is added. - The old
namecolumn stays and its data is not copied. - Use a reviewed migration instead.