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.
Users
Our API needs to support the standard CRUD operations for "users" - the people logging into and using our application.
Assignment
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.
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: aUUIDthat will serve as the primary keycreated_at: aTIMESTAMPthat can not be nullupdated_at: aTIMESTAMPthat can not be nullemail:TEXTthat can not be null and must be unique
The -- +goose Up and -- +goose Down comments are required. They tell Goose how to run the migration in each direction.
protocol://username:password@host:port/database
Here are examples:
- macOS (no password, your username):
postgres://wagslane:@localhost:5432/chirpy - Linux (password from last lesson, postgres user):
postgres://postgres:postgres@localhost:5432/chirpy
Test your connection string by running psql, for example:
psql "postgres://wagslane:@localhost:5432/chirpy"
It should connect you to the chirpy database directly. If it's working, great. exit out of psql and save the connection string.
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 chirpy
\dt
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 ea036a3f-6fa3-446a-ba20-c04cb913e12a
Submit
bootdev run ea036a3f-6fa3-446a-ba20-c04cb913e12a -s
To run and submit the tests for this lesson, you must have an active Boot.dev membership
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.