Background
Near the beginning of my pairing tour, I had a chance to work with Tom M and his team on a relatively young project. The client wanted the team to create a simple app that could be used as a proof-of-concept for a new business idea. The app in question was near the end of the first phase of development at 8th Light, so our work involved polishing the UI and completing a few final features. Tom and I worked on a number of items including expanding the database, hooking the backend up to the front-end (Typescript and React), and fixing minor bugs.
During this time another member of the team recommended that we refactor our existing code to make use of the new architecture that wrapped the database. Thus, we spent a lot of time working on database migrations. Before pairing with Tom, I thought that migrations were a Rails-specific idea that could only be implemented using the magic of Active Record. Afterwards, I learned that database migrations are common among all languages and are relatively easy to implement. In this blog post, I will explain the advantages of using migrations to build a database and then provide a small example of hand-rolled migrations.
Why Use Migrations?
Database migrations are a set of incremental and reversible changes to the schema of a relational database. Rather than having one (potentially complicated) schema that is committed to version control, a developer can build the schema up through a series of SQL scripts. Each migration consists of an “up” script (i.e. how to add new features to a database) and a “down” script (i.e. how to go back to the previous database structure). A schema migration tool (or database migration tool) can be used to systematically select the scripts needed for a certain action and execute them in the correct order.
It is important to note that database migrations are no guarantee that existing data will not become outdated, corrupt, or go missing. For example, the “up” and “down” scripts might contain irreversible changes such as delete a table or column. Thus, migrations are built to preserve the structure of a database – not the data within it. Due to the fact that a developer can quickly and easily alter a database by creating a new pair of SQL scripts (rather than muddling through a full schema), migrations have become popular in the Agile community.
A developer needs to ensure that the up and down migrations truly are inverse functions with no side effects. If side effects are introduced at any point in the migration process, the database structure might become corrupted. Additionally, if a team is using database migrations to manage a schema, developers cannot directly change the structure of the database without writing a new pair of SQL scripts. A migration might crash or produce unexpected output if a developer manually changes the schema. Thus, discipline is required throughout the development process.
A (Very) Simple Example
Before I paired with Tom, my only exposure to database migrations was through Ruby on Rails. I naively thought that migrations were only possible through Active Record. However, as described above, migrations really are just a series of SQL scripts that are executed in a certain order. A program keeps track of the current state of the database (i.e. which migration just happened) as well as some metadata about the environment and current data.
I decided that the best way to learn how migrations worked was to roll out my own. In order to keep things simple, I decided that I wanted to develop a system that would reference an arbitrary number of SQL scripts, filter out the scripts needed for a certain migration (i.e. “up” vs. “down”), and then run them in the correct order. At the end of the day, an “up” command should take a database from nothing to some final state while a “down” command should take the final state back down to nothing. At this point in time, I was not concerned with stepping up and down the migration incrementally.
This GitHub repo contains a series of SQL scripts that detail the up and down actions of the migration as well as a Bash script that loops through them and runs them all. The scripts value “convention over configuration” in the tradition of Ruby – the script sorts through them using the “up” or “down” keyword that is appended to the end of each filename. Each filename can certainly be ordered using timestamps, but I decided to just use a four digit number for the sake of simplicity.
Upon reflection of my code, the migration system that I wrote is quite underwhelming. I do not mean that the Bash script or this exercise was bad in any way. I simply did not realize that such a simple script would work for my intended goal. The program itself is only twelve lines long (and probably less if I took some time to refactor it). I do understand that it does not do nearly as much as the sophisticated migration system of Ruby on Rails, but it is still impressive to me that the core of such a powerful idea can be contained in one tiny package.
Conclusions
When applications grow large in size, managing their corresponding databases can become very difficult. This is especially true if the app is distributed across many different systems or running on a few different versions. Database migrations can help control the complexity of databases by building a schema in a series of small and organized steps rather than committing one large schema to version control. Migrations also allow the flexibility to build the database to an intermediate state rather than the “all or nothing” format of a single schema.
Rolling your own migrations is surprisingly simple – it really just involves reading through a list of SQL scripts and executing the scripts in a certain order. Of course, my hand-written script is not nearly as powerful as a dedicated migration package, but it does demonstrate how big ideas can come in small packages. Given the choice, I would turn to an existing library for migrations, but it is empowering to know that I don’t necessarily need Active Record in my app 🙂
Cyrus Vandrevala 