Pre-migration scripts
Pre-migration scripts prepare the database before one concrete flyway migration is executed. They exist for one situation: a change script, which is released already (so its content and checksum must not change), cannot be executed on a database, which contains the change already. This typically happens when the same change is released in two branches under two different versions - for example in 14.19.x and again in 15.15.x.
The change scripts themselves are described in Database scripts (Flyway).
When to use a pre-migration script
- New change script, not released yet - do not write a pre-migration script. Write the change script itself so that it can be executed repeatedly (ADD COLUMN IF NOT EXISTS, CREATE TABLE IF NOT EXISTS, DROP COLUMN IF EXISTS, CREATE INDEX IF NOT EXISTS). FlywayScriptIdempotencyUnitTest checks all V15 and V16 scripts and fails the build otherwise. The content of a change script has to comply with the database design convention.
- Released change script - the content must not be touched, because a changed checksum breaks validation on every installation, where the migration is applied already. A pre-migration script is the way out.
- Data migration - be careful. Marking a migration as applied means the data is never converted. In that case let the pre-migration script prepare the database (for example drop a leftover index) and let flyway execute the migration as usual.
How it works
The engine (DefaultIdmFlywayPreMigrationExecutor) does the following for each module, before the module migrations start:
- Looks for pre-migration scripts of the module (classpath locations only). When there is none, it ends without
a single database call.
Scripts found more than once (module classes and module jar on the classpath) are deduplicated and ordered by the
version of the related migration.
2. Ends as well, when the module has no migration applied yet. Flyway creates the schema history table during the
migration itself, so there is nothing to prepare (and nothing to write into) on a clean installation.
3. Reads the migration version from the script name.
4. Finds the migration with this version in flyway.info(). Description, script name and checksum are taken from there, so they are always the values flyway resolved in the same run.
5. Executes the script only when the migration is going to be executed by the following migration run (state PENDING, or a failed migration). When the migration is applied already, the script is skipped.
6. Applies the migrations with a lower version first. The record written by the script raises the last applied
version of the module, so flyway would evaluate them as ignored and would never execute them.
7. Resolves the placeholders and executes the whole script as one statement, in an own transaction and with the
schema history table of the module locked. The script does not take the lock itself - a script of a module must not
be able to forget it. Database notices and warnings are written into the IdM log.
The engine knows nothing about the tables of the module. The condition ("is the change in the database already?") is a part of the script, together with the decision what to do about it. Thanks to that, the next case is a new file, not a code change.
Location and naming
Pre-migration scripts are placed in a sibling folder of the module change scripts:
{module}/src/main/resources/eu/bcvsolutions/idm/{module}/sql/premigrate/{dbName}/ <- pre-migration scripts
The sibling folder is used by purpose - flyway must not scan the pre-migration scripts and must not resolve them as migrations.
Naming is P{version}__{description}.sql, for example P15_15_001__vs-request-owner.sql for the migration V15_15_001__vs-request-owner.sql. Only the version is used, it says before which migration the script is executed and it has to match the migration exactly. The description behind the separator is for people only.
One module can contain several pre-migration scripts, each of them for its own migration. They are independent, every one of them checks its own condition.
Placeholders
The placeholders are resolved by IdM (they are not flyway placeholders, so they do not collide with ${dbName} used in locations). Never fill the values in by hand - a hard coded checksum becomes wrong as soon as anybody touches the migration file.
| Placeholder | Value | Example |
|---|---|---|
| @schemaHistoryTable@ | flyway schema history table of the module | idm_schema_version_vs |
| @version@ | version of the target migration | 15.15.001 |
| @description@ | description of the target migration | vs-request-owner |
| @script@ | file name of the target migration | V15_15_001__vs-request-owner.sql |
| @checksum@ | checksum flyway calculated for the target migration | 813344787 |
When an unresolved placeholder is left in the script (a typo in the placeholder name), the script is not executed at all and the application start fails with CoreException.
Template
Change the condition and the messages, keep the placeholders and the four branches. Keep the script in one DO block - it is the tested shape and only inside a block you can use branches and RAISE NOTICE.
DECLARE
v_inserted integer := 0;
BEGIN
-- The schema history table is locked by IdM for the transaction of this script, so the script does not
-- have to take care of concurrently starting nodes of a cluster itself.
-- CHANGE HERE: condition, which recognizes that the change is in the database already.
-- When the migration changes more objects (e.g. a table and its audit table), check all of them - marking
-- the migration as applied with a part of the change missing means a broken module, which flyway cannot
-- repair any more. Solve the partial state by an own branch with RAISE EXCEPTION, missing indexes are
-- better created by the script (they can be dropped on the project intentionally).
-- See P15_15_001__vs-request-owner.sql.
-- examples:
-- column : SELECT 1 FROM information_schema.columns WHERE table_schema = current_schema() AND table_name = 'x' AND column_name = 'y'
-- table : SELECT 1 FROM information_schema.tables WHERE table_schema = current_schema() AND table_name = 'x'
-- index : SELECT 1 FROM pg_indexes WHERE schemaname = current_schema() AND indexname = 'idx_x'
IF NOT EXISTS (
SELECT 1 FROM information_schema.columns
WHERE table_schema = current_schema()
AND table_name = 'table_to_change'
AND column_name = 'column_to_change')
THEN
IF EXISTS (SELECT 1 FROM @schemaHistoryTable@ WHERE version = '@version@') THEN
RAISE WARNING 'Schema history [@schemaHistoryTable@] contains migration [@version@], but the change is not in the database. Migration will not be executed again, manual check is needed.';
ELSE
RAISE NOTICE 'Change of migration [@version@] is not in the database, it will be executed as usual.';
END IF;
ELSIF EXISTS (SELECT 1 FROM @schemaHistoryTable@ WHERE version = '@version@' AND success) THEN
RAISE NOTICE 'Migration [@version@] is applied already, nothing to do.';
ELSIF EXISTS (SELECT 1 FROM @schemaHistoryTable@ WHERE version = '@version@' AND NOT success) THEN
UPDATE @schemaHistoryTable@
SET description = '@description@',
type = 'SQL',
script = '@script@',
checksum = @checksum@,
execution_time = 0,
success = true
WHERE version = '@version@';
RAISE WARNING 'Failed record of migration [@version@] was repaired, the change is in the database already.';
ELSE
INSERT INTO @schemaHistoryTable@
(installed_rank, version, description, type, script, checksum, installed_by, installed_on, execution_time, success)
SELECT
coalesce(max(installed_rank), 0) + 1,
'@version@',
'@description@',
'SQL',
'@script@',
@checksum@,
'czechidm',
now(),
0,
true
FROM @schemaHistoryTable@;
GET DIAGNOSTICS v_inserted = ROW_COUNT;
RAISE WARNING 'Change of migration [@version@] is in the database already, migration was marked as applied (inserted rows: %).', v_inserted;
END IF;
END $$;
Behaviour during upgrade
The four branches of the template cover all states, in which the database can be. The table below uses the first released pre-migration script, P15_15_001__vs-request-owner.sql, as an example.
| Database state | What the script does | Result |
|---|---|---|
| Clean installation, the module has no migration applied yet | Script is not executed at all | Flyway creates the schema history table and executes all migrations as usual |
| The change is not in the database, the module has other migrations applied | Nothing, it only writes a notice into the log | Flyway executes the migration as usual |
| Upgrade from 14.19.x, the change is in the database | Writes the migration into the schema history | Flyway skips the migration and continues with the newer ones |
| Migration is applied already | Nothing, the script is not even opened | No duplicate record is created |
| Failed record of the migration in the history | Repairs the record to success with the correct checksum | Flyway continues (this state does not happen on PostgreSQL, see below) |
| The history contains the migration, but the change is not in the database | Nothing, it writes a warning | Manual check is needed, flyway will not execute the migration again |
| The change is in the database partially (e.g. table without its audit table) | Ends with an error | Application start is stopped, the state has to be checked manually |
Log of a successful upgrade from 14.19.x looks like this:
WARN DefaultIdmFlywayPreMigrationExecutor - Pre-migration script [P15_15_001__vs-request-owner.sql]: Columns [vs_request.owner_type, vs_request.owner_id] exist in the database already (upgrade from 14.19.x), migration [15.15.001] was marked as applied (inserted rows: 1).
INFO DbValidate - Successfully validated 15 migrations
INFO DbMigrate - Schema "public" is up to date. No migration necessary.
Newer migrations of the module are executed normally after the pre-migration script, the mechanism does not stop the migration run.
Example - vs-request-owner
The columns owner_type and owner_id of the table vs_request were released twice: as V14_19_001__vs-request-owner.sql in the 14.19.x branch and as V15_15_001__vs-request-owner.sql in the 15 branch. The scripts are identical and neither of them uses IF NOT EXISTS, so an upgrade of a database, which went through 14.19.x, failed:
Message : ERROR: column "owner_type" of relation "vs_request" already exists
Both change scripts stay as they are. The pre-migration script P15_15_001__vs-request-owner.sql in the vs module solves the upgrade.
How to test a pre-migration script
Prepare the database state, which the script is written for, and start the application. For the case above it means: delete the record of the migration from the schema history, insert a record of the migration from the older branch and keep the change in the database.
DELETE FROM idm_schema_version_vs WHERE version = '15.15.001';
INSERT INTO idm_schema_version_vs
(installed_rank, version, description, type, script, checksum, installed_by, installed_on, execution_time, success)
SELECT coalesce(max(installed_rank), 0) + 1, '14.19.001', 'vs-request-owner', 'SQL',
'V14_19_001__vs-request-owner.sql', 813344787, 'czechidm', now(), 17, true
FROM idm_schema_version_vs;
Before the change write the original record down, so that the database can be put back. Test both directions - the state with the change and a clean installation, where the migration has to be executed as usual.
Manual fallback for older builds
On a build without the mechanism the same result is reached by executing the body of the pre-migration script by hand, with the placeholders filled in. The checksum of V15_15_001__vs-request-owner.sql is 813344787.
Never write the record with a plain INSERT outside of such a guarded script:
- When the migration is applied already, a second record for the same version is created. Flyway survives it (the states are SUCCESS and OUT_OF_ORDER, validation passes), but the duplicity stays in the history forever.
- When the checksum is wrong, validation ends with Migration checksum mismatch and the application does not start. Flyway compares against the manually written record, so the correct original record does not help.
Limitations
- The mechanism is verified on PostgreSQL 13, 14, 15, 16 and 17. The IdM 15 product contains change scripts for PostgreSQL only.
- Every module has its own folder of pre-migration scripts. A script of one module cannot prepare the database for a migration of another module.
- Pre-migration scripts of one module are executed in the order of the versions of their migrations. Do not write scripts, which depend on each other.
- The very first migration of a module cannot be prepared - the module has no schema history table at that moment, so the scripts are skipped.
- Pre-migration scripts are resolved on the classpath only, a location of another type (filesystem:) is skipped with a warning.
- The script works with the default schema of the datasource connection. A module with a configured flyway schema (spring.flyway.schemas) is not supported.
- Migrations with a lower version are applied before the script, which also executes the pending repeatable (R__) migrations of the module. A module with a repeatable migration, which depends on a newer change script, is not supported.
- Migrations, which are in the state IGNORED already (the database was upgraded before this mechanism existed), are not repaired by the mechanism.