Installing the migrate tool
To manage SQL migrations in this project we’re going to use the migrate command-line tool (which itself is written in Go).
On Linux and Windows, the easiest method is to download a pre-built binary and move it to a location on your system path.
Before you continue, please check that it’s available and working on your machine by trying to execute the migrate binary with the -version flag. It should output the current version number similar to this:
zzh@ZZHPC:~$ migrate -version 4.18.1
zzh@ZZHPC:/zdata/Github/greenlight$ migrate create -seq -ext=.sql -dir=./migrations create_movie_table /zdata/Github/greenlight/migrations/000001_create_movie_table.up.sql /zdata/Github/greenlight/migrations/000001_create_movie_table.down.sql
In this command:
- The -seq flag indicates that we want to use sequential numbering like 0001, 0002, ... for the migration files (instead of a Unix timestamp, which is the default).
- The -ext flag indicates that we want to give the migration files the extension .sql .
- The -dir flag indicates that we want to store the migration files in the ./migrations directory (which will be created automatically if it doesn’t already exist).
- The name create_movie_table is a descriptive label that we give the migration files to signify their contents.
File: migrations/000001_create_movie_table.up.sql
CREATE TABLE IF NOT EXISTS movie ( id bigserial PRIMARY KEY, created_at timestamp(0) with time zone NOT NULL DEFAULT NOW(), title text NOT NULL, year integer NOT NULL, runtime integer NOT NULL, genres text[] NOT NULL, version integer NOT NULL DEFAULT 1 );
https://www.depesz.com/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/
File: migrations/000001_create_movie_table.down.sql
DROP TABLE IF EXISTS movie;
zzh@ZZHPC:/zdata/Github/greenlight$ migrate create -seq -ext=.sql -dir=./migrations add_movie_check_constraints /zdata/Github/greenlight/migrations/000002_add_movie_check_constraints.up.sql /zdata/Github/greenlight/migrations/000002_add_movie_check_constraints.down.sql
File: migrations/000002_add_movie_check_constraints.up.sql
ALTER TABLE movie ADD CONSTRAINT movie_runtime_check CHECK (runtime >= 0); ALTER TABLE movie ADD CONSTRAINT movie_year_check CHECK (year BETWEEN 1888 AND date_part('year', now())); ALTER TABLE movie ADD CONSTRAINT movie_genres_length_check CHECK (array_length(genres, 1) BETWEEN 1 AND 5);
File: migrations/000002_add_movie_check_constraints.down.sql
ALTER TABLE movie DROP CONSTRAINT IF EXISTS movie_runtime_check; ALTER TABLE movie DROP CONSTRAINT IF EXISTS movie_year_check; ALTER TABLE movie DROP CONSTRAINT IF EXISTS movie_genres_length_check;
Executing the migrations
zzh@ZZHPC:/zdata/Github/greenlight$ migrate -path=./migrations -database=$GREENLIGHT_DB_DSN up 1/u create_movie_table (8.585235ms) 2/u add_movie_check_constraints (13.254208ms)
At this point, it’s worth opening a connection to your database and listing the tables with the \dt meta command:
zzh@ZZHPC:~$ docker exec -it postgres17 psql --dbname=greenlight --username=greenlight
psql (17.1 (Debian 17.1-1.pgdg120+1))
Type "help" for help.
greenlight=> \dt
List of relations
Schema | Name | Type | Owner
--------+-------------------+-------+------------
public | movie | table | greenlight
public | schema_migrations | table | greenlight
(2 rows)
greenlight=>
You should see that the movies table has been created, along with a schema_migrations table, both of which are owned by the greenlight user.
The schema_migrations table is automatically generated by the migrate tool and used to keep track of which migrations have been applied. Let’s take a quick look inside it:
greenlight=> SELECT * FROM schema_migrations;
version | dirty
---------+-------
2 | f
(1 row)
The version column here indicates that our migration files up to (and including) number 2 in the sequence have been executed against the database. The value of the dirty column is false , which indicates that the migration files were cleanly executed without any errors and the SQL statements they contain were successfully applied in full.
If you like, you can also run the \d meta command on the movies table to see the structure of the table and confirm that the CHECK constraints were created correctly. Like so:
greenlight=> \d movie
Table "public.movie"
Column | Type | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+-----------------------------------
id | bigint | | not null | nextval('movie_id_seq'::regclass)
created_at | timestamp(0) with time zone | | not null | now()
title | text | | not null |
year | integer | | not null |
runtime | integer | | not null |
genres | text[] | | not null |
version | integer | | not null | 1
Indexes:
"movie_pkey" PRIMARY KEY, btree (id)
Check constraints:
"movie_genres_length_check" CHECK (array_length(genres, 1) >= 1 AND array_length(genres, 1) <= 5)
"movie_runtime_check" CHECK (runtime >= 0)
"movie_year_check" CHECK (year >= 1888 AND year::double precision <= date_part('year'::text, now()))
greenlight=>
package main import ( "context" "database/sql" "flag" "fmt" "github.com/golang-migrate/migrate/v4" "log" "net/http" "os" "time" // New import "github.com/golang-migrate/migrate/v4/database/postgres" // New import _ "github.com/golang-migrate/migrate/v4/source/file" _ "github.com/lib/pq" ) func main() { ... db, err := openDB(cfg) if err != nil { logger.Error(err.Error()) os.Exit(1) } defer db.Close() logger.Info("database connection pool established") migrationDriver, err := postgres.WithInstance(db, &postgres.Config{}) if err != nil { logger.Error(err.Error()) os.Exit(1) } migrator, err := migrate.NewWithDatabaseInstance("file:///path/to/your/migrations", "postgres", migrationDriver) if err != nil { logger.Error(err.Error()) os.Exit(1) } err = migrator.Up() if err != nil && err != migrate.ErrNoChange { logger.Error(err.Error()) os.Exit(1) } logger.Info("database migrations applied") ... }
