Let'sGoFurther - Chapter 6: SQL Migrations

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=>