ADR 11: Sql migration script naming convention¶
Status¶
accepted
Context¶
Sequence problem¶
Some DbUp migrations currently have a naming scheme like this:
49. Alter EndpointProfile.sql
50. Alter Settlement Account.sql
51. Alter CoreProfiles.sql
52. Add EI Reporting.sql
This will have unintended consequences once we hit 100 as the order is based on alpha:
- If files 99 and 100 are added in the same deployment, 100 will run first.
- On a fresh db migration, the 100 series will run before 11, 12 etc.
Developer flow problem¶
In addition, simple number usage requires developers to:
- Increment the new script based on the prior.
- Coordinate or fix conflicts during merges if another PR uses the same number.
A timestamp pattern avoids this issue.
Option 1¶
Add a prefix and padding like S00100. Foo script.sql. This does not help with the developer flow issue.
Option 2¶
Add only padding like 0100. Foo script.sql
The drawback is that we would have to rename all the existing files and modify the schemaversions table. Not really in the spirit of DbUp.
This does not help with the developer flow issue.
Option 3¶
Prefix with a date format:
202301302153-DoTheThing.sql
I'd prefer we avoid spaces and extra periods.
This example uses folders, but basically the same idea: gist
This article suggests this as an option but isn't necessary to avoid collisions: Paul Stovell blog
If non-datestamped files are already journaled, a prefix such as "S-" is needed to avoid a cleanup process: S-202301302153-DoTheThing.sql
In scope of this ADR¶
The naming convention of migration scripts.
Not in scope of this ADR¶
Unnumbered post-deploy scripts, such as stored procedures.
Decision¶
Pattern 1 - datestamp¶
Determine a timestamp during file creation. Avoid spaces and extra periods.
Note
The timestamp is only used for sequencing and does not need to be exactly derived. A general date is the idea.
Get-Date -Format "yyyyMMddHHmm" -AsUTC
For journaled schema migration scripts¶
S-202301302153-DoTheThing.sql
For master data scripts¶
M-202301302153-DoTheThing.sql
Note
If the project is new, the prefix character is not necessary as there would not be other files in sequence to contend with.
Pattern 2 - zero padding¶
Pad migration file names with zeros to ensure ordering. This the current pattern followed by WSO2 migrations:
/Scripts/Migrations/00013-Create REG_RESOURCE_RATING.sql
/Scripts/Migrations/00014-Create REG_TAG.sql
Consequences¶
The timestamp provides sequencing with a lower risk of conflicts by other developers.
A data cleanup process is not needed if a character prefix is added to projects that used a number-prefix pattern.