Using database migrations to manage schema changes

A guide to manage structural changes to your database

Great Migration — Wildebeest crossing the Mara river | Flickr

The first time I came across the concept of database migrations was while learning to build web apps using Rails. As stated in the Rails documentation;

Migrations are a convenient way for you to alter your database in a structured and organized manner.

While there are a multitude of tools out there to manage migrations in the language of your choice, the core principles remain the same. You could use a generic tool across languages and frameworks, however using one tailored for your current tech stack will give you the most benefit.

Let’s look at the core principles when working with migrations.

Migrate Up or Down

We migrate “UP” to the latest / newer database schema version or migrate “DOWN” to an older / previous database schema version.

It’s considered a best practice to be able to rollback or reverse migration changes. Each migration is usually organised in two files. One to apply the schema change aka “UP” and one to rollback the changes aka “DOWN”.

Ensuring the migrations are idempotent will also keep things simple and robust. This is easily achieved by using a statement like CREATE TABLE IF NOT EXISTS or the reverse DROP TABLE IF EXISTS . Running the same migration multiple times will have the same effect as running it once.

File naming conventions

Migration files need to be prefixed to maintain the order in which the migrations are to be applied. This can be achieved by using:

• Sequence numbers: 0001_migration_name.sql, 0002_migration_name.sql
• Date and time : 20221105151510_migration_name.sql (YYYYMMDDHHMMSS format)
• Timestamps: 1500445949_migration_name.sql

File format

The format of the migration file can vary depending on the library or framework you are using.

Usually it will be a regular SQL statement supported by the database you are using.

CREATE TABLE IF NOT EXISTS "user" 
(
  id serial primary key,
  name text,
  email text not null
)

Or, in the case of Rails, migration files are ruby classes that extend ActiveRecord::Migration

class CreateProducts < ActiveRecord::Migration
  def change
    create_table :products do |t|
      t.string :name
      t.text :description
    
      t.timestamps
    end
  end
end

Version Controlled

All the migrations files should be versioned controlled. One challenge that will surely need to be dealt with in larger teams is having multiple developers making simultaneous changes. This will require some development process in place and co-ordination among team members.

As a rule of thumb, once a migration file has been committed and pushed, modifications should take place via a new migration. Avoid modifying existing migrations. If the migration file only exists on your local machine, you’re free to modify the same file before it’s committed and pushed.

Example with golang-migrate

Now let’s take a look at an example using golang-migrate . We’ll use the CLI approach here.

Step 1: Create a migration for the user table

❯ migrate create -ext sql -dir migrations -seq create_user_table

Creates a migration file with an extension(ext) of sql in the migrations directory (dir) using a sequence (seq).

This creates two files

❯ tree migrations
migrations
├── 000001_create_user_table.down.sql
└── 000001_create_user_table.up.sql

Step 2: Fill in the migration files

------000001_create_user_table.up.sql--------
CREATE TABLE IF NOT EXISTS "user" 
(
  id serial primary key,
  name text,
  email text not null
)
------000001_create_user_table.down.sql--------
DROP TABLE IF EXISTS "user"

Step 3: Run migrate UP

Assuming you have setup the ENV variable POSTGRESQL_URL , run the migrate “up” with command.

❯ migrate -database ${POSTGRESQL_URL} -path migrations up

If your syntax is correct, you’ll see a success message.

1/u create_user_table (29.238127ms)

Here POSTGRESQL_URL is the connection string to connect to the database, e.g.

export POSTGRESQL_URL=postgres://scott:tiger@localhost:5432/example?sslmode=disable&search_path=public

Step 4: Run migrate DOWN

To reverse the migration we just ran, execute

❯ migrate -database ${POSTGRESQL_URL} -path migrations down

Final thoughts

And that’s about it. Database migrations are a simple albeit necessary concept to understand when building web applications. Sometimes the term can sound more complex than it actually is.

Migrations tools & frameworks

• Node: https://github.com/db-migrate/node-db-migrate
• Rails: https://guides.rubyonrails.org/active_record_migrations.html
• Golang: https://github.com/golang-migrate/migrate
• Java: https://www.liquibase.org/

References

• https://github.com/golang-migrate/migrate/blob/master/GETTING_STARTED.md
• https://guides.rubyonrails.org/v3.2/migrations.html
• https://github.com/golang-migrate/migrate
• https://blog.f5.works/best-practices-manage-database-schema-changes-with-database-migration-and-version-control/

Support

If you like reading my content and find it helpful, please consider buying me a coffee!

Oliver Mascarenhas is simplifying software engineering concepts & practices.