Getting Started #

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

You are going to 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 programtically, 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/migrate@latest

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 #

You are going to 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:postgres@localhost:5454/postgres?sslmode=disable

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

export DB_URL='postgres://postgres:postgres@localhost: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.

export DB_URL='postgres://postgres:postgres@localhost:5454/postgres?sslmode=disable'

migrate -database ${DB_URL} -path db/migrations down

Writing Code #

Now that you have the posts table in the database, you are going to write some Go code to access the data. This is an optional section, feel free to skip it.

The complete code is available in this GitHub repository.

To access a postgres database we need a database driver, let's use the popular pq package.

go get github.com/lib/pq

Making a connection to the database.

package main

import (
	"database/sql"
    "log"
    _ "github.com/lib/pq"
)

func main() {
	// credentials used from the compose setup previously
	db, err := sql.Open("postgres", "postgres://postgres:postgres@localhost:5454/postgres?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}

	defer db.Close()
    
    if err := db.Ping(); err != nil {
		log.Fatal("Failed to ping db", err)
	}
}

Create a struct to represent a row of post table.

type Post struct {
	Title string
	Body  string
}

Selecting all the posts from db and displaying them.

package main

import (
	"database/sql"
    "log"
    "fmt"
    _ "github.com/lib/pq"
)

type Post struct {
	Title string
	Body  string
}

func main() {
	...
    // previous code of db connection setup
    ...
    
    // query all the posts
	result, err := db.Query("SELECT * FROM posts;")
	if err != nil {
		log.Fatal(err)
	}

	defer result.Close()

	// iterate over all the posts and print them
	for result.Next() {
		var p Post

		err := result.Scan(&p.Title, &p.Body)
		if err != nil {
			log.Fatal(err)
		}

		fmt.Printf("%+v\n", p)
	}
}

Thank you for reading this article 🙏🏻 hope you enjoyed it.