How to use Migrate (incl sqlc.yaml too!)

migrate-readme.md

Golang Migrations Made Easy

Prerequisite / Getting Started

$ go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest
$ 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.

Getting Started

migrate -path ./migrations -database "postgres://local:asecurepassword@localhost:5003/mydatabase?sslmode=disable" up
20190722135348/u initial (72.22076ms)
20190722170451/u bankdetails (107.416199ms)
20190724092453/u audittrail (142.729672ms)
20190724123500/u invoices (195.704312ms)
20190729090912/u enums (282.903295ms)
20190729150351/u add-shivani (312.146635ms)
20190731131150/u add-user-company-link (352.487406ms)

You shouldn't need to worry too much about it other than that!

Using Docker

$ docker run -e POSTGRES_USER=local -e POSTGRES_PASSWORD=asecurepassword -e POSTGRES_DB=mydatabase -p 5003:5432 postgres:11.10-alpine
...
$ pgcli "postgres://local:asecurepassword@localhost:5003/mydatabase?sslmode=disable"
# run local dev script
 \i ./scripts/sql/__localdev_script.sql

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 quicka.user;
+---------------------+--------------------------+-------------------------+
| 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.

sqlc.yaml

version: "1"
packages:
  - name: "model"
    path: "./model"
    queries: "./model/queries/"
    schema: "./migrations/"
    engine: "postgresql"
    emit_json_tags: false
    emit_prepared_queries: false
    emit_interface: true
    emit_exact_table_names: false
    emit_empty_slices: false
overrides:
  - go_type: "github.com/gofrs/uuid.UUID"
    db_type: "uuid"
    nullable: false
  - go_type: "github.com/gofrs/uuid.NullUUID"
    db_type: "uuid"
    nullable: true
  - go_type: "github.com/shopspring/decimal.Decimal"
    db_type: "pg_catalog.numeric"