Golang Database Migration Tutorial

golang database migrations tutorial

Getting Started #

In this tutorial, you will learn how to use the migrate tool (written in Go and quite popular in Golang 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.

Setup 

Installation 

migrate is a cli tool that is used to run migrations, it can also be used programmatically, but in this tutorial we are going to 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' github.com/golang-migrate/migrate/v4/cmd/[email protected]

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 Golang Migrations 

You will create a posts table which will have to 2 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 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 

In the <timestamp>_create_posts_table.up.sql migration file write 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 write the sql to drop posts table.

DROP TABLE IF EXISTS posts;

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:

postgres://<username>:<password>@localhost:<port>/<db_name>?sslmode=disable

If you are using the database using the docker-compose.yaml method specified above, the connection string will look like this:

postgres://postgres:[email protected]lhost:5454/postgres?sslmode=disable

To run migrations use migrate up command with the appropriate options.

export DB_URL='postgres://postgres:[email protected]:5454/postgres?sslmode=disable'

# Run migrations
migrate -database ${DB_URL} -path db/migrations up

Rolling back migrations 

To roll back all the migrations i.e. execte all the *.down.sql files you use the migration down command.