Database Migration with Golang Tutorial

In this tutorial, you will learn how to use the migrate tool (written in Golang and popular in the Go community) to execute database migrations. As a second part, you will write some Go code to read the data from the database.

You will use PostgreSQL as the database of choice in this article, but migrate is compatible with many more databases check out the list here.

Installation of Golang Migration CLI 

migrate is a CLI tool used to run migrations. It can also be used programmatically, but in this tutorial, we will use it via CLI. There are multiple ways to install the migrate CLI, such as brew, scoop, etc. Take a look at the installation document to see all the available options.

We are going to install it using go install.

go install -tags 'postgres'

Install the Postgres Database 

To run the sample postgres database, I have provided you with a docker-compose.yaml file. Clone the GitHub project and run docker-compose up. This will run a Postgres database on port 5454.

You are free to setup a Postgres DB as you like. There is nothing special in the above setup.

Creating Migrations with Golang

You are going to create a posts table that will have two columns, title and body.

To create a new migration, run the migrate create command with appropriate options.

migrate create -ext sql -dir db/migrations create_posts_table
  • ext specifies the file extension to use when creating migrations file.
  • dir specifies which directory to create the migrations in.

This will create two migrations in db/migrations the folder matching the pattern: <timestamp>_create_posts_table.down.sql and <timestamp>_create_posts_table.up.sql.

  • UP migration will contain the SQL to create the post table.
  • DOWN migration will contain the SQL to revert what has been done in the up migration.

Writing SQL Queries 

In the <timestamp>_create_posts_table.up.sql migration file writes the SQL to create posts table.

CREATE TABLE IF NOT EXISTS posts (title varchar, body varchar);

In the <timestamp>_create_posts_table.down.sql migration file writes the SQL to drop posts table.


Running migrations 

migrate needs a way to connect the database to execute the SQL statements. For this, you will need a valid Postgres connection string following the format: