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/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 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: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.
