$ 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, stubTo 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.
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!
$ 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.sqlTIMESTAMP 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.
Several columns in the database represent enums (they are integer fields).
Their definitions exist in the quicka.common repo.
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.