Using golang-migrate AKA mattes/migrate with Docker, Postgres and creating new schemas.

readme.md

Using golang-migrate AKA mattes/migrate with Docker, Postgres and creating new schemas.

Prerequisites

We use the golang-migrate tool (formerly known as mattes/migrate) - build it with postgres support.

$ go get -v -u -tags 'postgres' github.com/golang-migrate/migrate/cli
$ migrate -h
Usage: migrate OPTIONS COMMAND [arg...]
       migrate [ -version | -help ]

Options:
  -source          Location of the migrations (driver://url)
  -path            Shorthand for -source=file://path
  -database        Run migrations against this database (driver://url)
  -prefetch N      Number of migrations to load in advance before executing (default 10)
  -lock-timeout N  Allow N seconds to acquire database lock (default 15)
  -verbose         Print verbose logging
  -version         Print version
  -help            Print usage

Commands:
  create [-ext E] [-dir D] [-seq] [-digits N] [-format] NAME
    Create a set of timestamped up/down migrations titled NAME, in directory D with extension E.
    Use -seq option to generate sequential up/down migrations with N digits.
    Use -format option to specify a Go time format string.
  goto V       Migrate to version V
  up [N]       Apply all or N up migrations
  down [N]     Apply all or N down migrations
  drop         Drop everyting inside database
  force V      Set version V but dont run migration (ignores dirty state)
  version      Print current migration version

Source drivers: file
Database drivers: postgres, postgresql, stub

Creating a new entry

To create a new migration you can do the following (assumes that you have the migrate tool installed):

$ migrate create -ext sql -dir migrations <name-of-migration>
...

This will create, two files called migrations/<timestamp>_<name-of-migration>.[up|down].sql. You can fill these in as required.

Applying Schemas

migrate -path ./migrations -database postgres://dev:secret@localhost:5432/postgres?sslmode=disable up
1516166452/u initial (19.666454ms)
... etc...

Using Docker

$ docker run -e POSTGRES_USER=local -e POSTGRES_PASSWORD=asecurepassword -e POSTGRES_DB=quicka_main -p 5003:5432 postgres
...
$ pgcli "postgres://local:asecurepassword@localhost:5003/quicka_main?sslmode=disable"

Technical Notes about the Schema

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE is used instead of TIMESTAMP (which is equivalent to TIMESTAMP WITHOUT TIME ZONE).

This is to allow easier integration between Postgres and Go. When a timezone is absent, Postgres assumes it is in UTC whereas Go assumes that it is in the local timezone (they don't play nicely together). Having an explicit timezone makes the integration easier.

Enums

Several columns in the database represent enums (they are integer fields). Their definitions exist in the quicka.common repo.

pgcli

I would recommend using the awesome pgcli tool.

$ pgcli "postgres://local:asecurepassword@localhost:5454/quicka_main?sslmode=disable"
local@localhost:quicka_main> \d saver.accounts;
+---------------------+--------------------------+-------------------------+
| Column              | Type                     | Modifiers               |
|---------------------+--------------------------+-------------------------|
| account_id          | uuid                     |  not null               |
| created_at          | timestamp with time zone |  not null default now() |
| last_updated_at     | timestamp with time zone |  not null default now() |
+---------------------+--------------------------+-------------------------+
    ...

That's it. Good to go.