Photo by Blake Connally on Unsplash
How to Keep Track of Database Changes?
Use Database Migrations
3 min read
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  for demonstrating database migrations. Let’s see how it works first.
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 . 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 . 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 , 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 .
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).
I have added a migration to create a
users table here . Let’s check the status first before applying our migration using the following command.
docker-compose up flyway-my-app-schema-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
Let’s the status once again.
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.