How to Keep Track of Database Changes?

As a programmer, we use software such as git to keep track of changes we made in our code, to ease our review process, etc.

Similarly, we can keep track of changes made to our production databases using database migrations.

As these migrations are part of our source code, we can review these changes before applying them to our production system.

In this post, I will show you:

How to use database migrations?
How to make the most out of these migrations?

Flyway — Tool used for database migrations

I am using flyway [1] for demonstrating database migrations. Let’s see how it works first.


flyway — How does it work?

Suppose we have an empty database named My-DB. We need to connect the flyway to our target database and apply migrations. As we apply our migrations, it will first create a table called flyway_schema_history before applying our migrations. This table is used for tracking the state of our database.

Flyway manages database changes using versioned or repeatable migrations [2]. As per the official guideline.

Versioned migrations have a version, a description and a checksum…They are applied in order exactly once.

Repeatable migrations have a description and a checksum, but no version. Instead of being run just once, they are (re-)applied every time their checksum changes.

My focus is on versioned migrations as it’s the most common use case.

Naming conventions for versioned migrations

Flyway under the hood follows a naming convention for migrations [3]. The naming convention is as follows.

Migration name: V2__some_description.sql  
where  
V = prefix  
2 = version number. Please note that this version should be unique for all migrations.  
__ = separator  
some_description = description separated by underscore  
.sql = suffix

Useful commands for migrations

You can find all the commands here [4], however, I have only used the following command so far.

migrate: Apply the migrations.

info: Show the status of the migrations.

validate: Validate applied migrations.

clean: Cleans target database.

Rule of thumb for applying migration on a production system

⚠️ Never touch migrations that have already been applied to a production system. Instead, add a new one.

Let’s suppose you are adding a new feature. For this purpose, you might need a new table. Hence you added a migration. During the development process, you can adjust this migration any number of times.

Once you apply your migration to the production system, never touch this migration again. Add a new migration instead.

Proof of concept

You can check out the complete source code here [5].

Let’s create an empty database my-appusing the following command.

docker-compose up -d my-app

As you can see, the database is empty (no tables at all).


Empty database

I have added a migration to create a users table here [6]. Let’s check the status first before applying our migration using the following command.

docker-compose up flyway-my-app-schema-status


Database migration status

As we haven’t applied our migration yet, hence it’s in pendingstate. Now, let’s apply the migration using the following command.

docker-compose up flyway-my-app-schema-migrate


Output — Database changes applied successfully through migrations

Let’s the status once again.


Successful migration status

As mentioned earlier, flyway creates flyway_schema_history table additionally to keeping track of migrations.

How to make the most out of these migrations?

You can use the flyway:

To automate database migrations using the CICD pipeline.
To facilitate unit-testing. For each unit test — create a new database, apply migrations, populate required data, and execute a test.

Thanks for reading.