Database version control

Acronym

sig-api-database-version-control

Belongs to

SIG APIs

Responsible

TheBuro und FalahSama

History

(v1)   2021-02-06 - created initially

Why is there need for such a decision?

We need a tool that helps us to migrate. Migrations from database systems or individual tables. We need a version control for our database.

Additional sources for better understanding the background

• Liquibase
• Flyway

Viable Options

• liquibase
• Flyway

Alternatives not seriously considered

• IRI NextForm
• DBConvert Studio
• Xplenty

How is this decision evaluated?

• Tables migration
• Database migration

Resolution Details

In the context of our project, we chose Flyway. Flyway and Liquibase offer the same functionalities. Whereby Liquibase is a real powerhouse. Flyway is a ligth version and easier to use. Migrations are written with SQL. Liquibase, on the other hand, offers several ways to migrate a database, for example SQL, XML, YAML and more. However, SQL is completely sufficient

Developing with feature branches

To avoid problems with a database versioning with multiple branches, there are two possibilities.

Possibility 1: Add a timestamp to the versioning number.

Example for our project: V3.13.145 .2020.07.08.11.10 __fix_column_lengths.sql

• The first digits (3) means the major release.
• The second digits (13) means minor changes.
• The third digits (145) means the patches or mere bug fixes.
• The further digits are part of the timestamp. Format: Year.Month.Day.Hour.Minute.
• At the end of the description a comment of the version is needed.

You can read here the meaning of the version naming. Only numeric characters are allowed: Only 0..9 and . are allowed.

“This will prevent version number conflicts from happening (I’m assuming including hours and minutes suffices for this) but creates another problem: versions will not be guaranteed to be merged back to master in order.

That means that Flyway could run against a database to apply a migration like above, but then in a later release finds that there’s now a new migration 2017.7.6.12.12 that hasn’t been applied yet but has a version that’s lower than the latest migration that has been applied already.

By default Flyway will consider this an error and your migration will fail. However, you can simply tell Flyway that this is all expected and that it should simply execute all migrations that haven’t been applied yet by setting its outOfOrder property to true.”

Source: Come Fly With Me: Flyway usage patterns part I

Possibility 2: Add a team number to the versioning number.

Each team has a fixed version number.

Impact: 1
Analysis: 2
Requirements: 3
Variants: 4

Example for our project: V3.13.145 .[Team number] __fix_column_lengths.sql

• The first digits (3) means the major release.
• The second digits (13) means minor changes.
• The third digits (145) means the patches or mere bug fixes.
• The other digits are part of the team number.
• At the end of the description a comment of the version is needed.

Here in the wiki is a guide on how to proceed in the flyway: Database version controlling with Flyway.

Reasons for the resolution

We chose Flyway as version control tool for our database.