Databases
How to Keep Track of Database Changes?
Use Database Migrations
As a programmer, we use software such as git to keep track of changes we made in our code, 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 story, I will show you:
- How to use database migrations?
- How to make the most out of these migrations?
- Guidelines for using migrations in a production system.
Flyway — Tool used for database migrations
I am using flyway [1] 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 [1]. 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 the 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 = suffixUseful 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.
Guideline for using migrations in a production system
Never alter already used migrations
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 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.
Migrations should be environment independent
Normally, migrations should not contain any environment-specific details. The idea here is to have migrations that can solely be used for every environment (development, staging, and/or testing).
So, if you’re adding migration(s) that can only be run in say production environment but can’t be run in other ones then you should rethink about it.
For instance, if you want to remove certain rows from the production system only, then this would not be part of the migration. You will do it manually (via some kind of script).
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-appAs you can see, the database is empty (no tables at all).
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
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. I have explained this point in more detail in this post.
Thanks for reading.
Resources
[2] https://flywaydb.org/documentation/concepts/migrations#overview
[3] https://flywaydb.org/documentation/concepts/migrations#sql-based-migrations
[4] https://flywaydb.org/documentation/usage/commandline/
[5] https://github.com/anasanjaria/database-migrations
[6] https://github.com/anasanjaria/database-migrations/blob/main/migrations/V1__create_users_table.sql
If you enjoyed this post, you might also enjoy my programming series:
Want to connect?
http://anasanjaria.bio.link/