Goose Migrations

Goose is a database migration tool written in Go. It runs migrations from a set of SQL files, making it a perfect fit for this project (we wanna stay close to the raw SQL).

What Is a Migration?

A migration is just a set of changes to your database table. You can have as many migrations as needed as your requirements change over time. For example, one migration might create a new table, one might delete a column, and one might add 2 new columns.

An "up" migration moves the state of the database from its current schema to the schema that you want. So, to get a "blank" database to the state it needs to be ready to run your application, you run all the "up" migrations.

If something breaks, you can run one of the "down" migrations to revert the database to a previous state. "Down" migrations are also used if you need to reset a local testing database to a known state.

Assignment

Install Goose

Goose is just a command line tool that happens to be written in Go. I recommend installing it using go install:

go install github.com/pressly/goose/v3/cmd/goose@latest

Run goose -version to make sure it's installed correctly.

Create a users migration in a new sql/schema directory.

A "migration" in Goose is just a .sql file with some SQL queries and some special comments. Our first migration should just create a users table. The simplest format for these files is:

number_name.sql

For example, I created a file in sql/schema called 001_users.sql with the following contents:

-- +goose Up
CREATE TABLE ...

-- +goose Down
DROP TABLE users;

Write out the CREATE TABLE statement in full, I left it blank for you to fill in. A user should have 4 fields:

id: a UUID that will serve as the primary key
created_at: a TIMESTAMP that can not be null
updated_at: a TIMESTAMP that can not be null
name: a unique string that can not be null

The -- +goose Up and -- +goose Down comments are case sensitive and required. They tell Goose how to run the migration in each direction.

Get your connection string. A connection string is just a URL with all of the information needed to connect to a database. The format is:

protocol://username:password@host:port/database

Here are examples:

macOS (no password, your username): postgres://wagslane:@localhost:5432/gator
Linux (password from last lesson, postgres user): postgres://postgres:postgres@localhost:5432/gator

Test your connection string by running psql, for example:

psql "postgres://wagslane:@localhost:5432/gator"

It should connect you to the gator database directly. If it's working, great. exit out of psql and save the connection string.

Run the up migration.

cd into the sql/schema directory and run:

goose postgres <connection_string> up

Run your migration! Make sure it works by using psql to find your newly created users table:

psql gator
\dt

Run the down migration to make sure it works (it should just drop the table). When you're satisified, run the up migration again to recreate the table.
Add the connection string to the .gatorconfig.json file instead of the example string we used earlier. When using it with goose, you'll use it in the format we just used. However, here in the config file it needs an additional sslmode=disable query string:

protocol://username:password@host:port/database?sslmode=disable

Your application code needs to know to not try to use SSL locally.

Run and submit the CLI tests.

The Boot.dev CLI requires you to be signed in to submit your solution!

Copy/paste one of the following commands into your terminal:

Run

bootdev run d049abb2-c733-4986-a33a-73f05e2ea06c

Submit

bootdev run d049abb2-c733-4986-a33a-73f05e2ea06c -s

Run the CLI commands to test your solution.

goose --version
- Expecting exit code: 0
cat ~/.gatorconfig.json
- Expecting exit code: 0
- Expecting stdout to contain all of:
  - postgres://
  - ?sslmode=disable

Using the Bootdev CLI

The Bootdev CLI is the only way to submit your solution for this type of lesson. We need to be able to run commands in your environment to verify your solution.

You can install it here. It's a Go program hosted on GitHub, so you'll need Go installed as well. Instructions are on the GitHub page.