Managing Postgre With Goose

Goose is a Database migration tool written in go. It allows you to make sequential migrations that you can step forward and backward through your db versions.

This allows your migrations to follow Gitops.

Setup

Since we are going to manage a postgres database, we need to deploy one. Lets make it simple and deploy it locally in docker.

If you dont have docker installed, install it now

Folder Config

Lets create a new project directory nad create a docker-compose.yml file as well as a migrations folder.

mkdir goose-demo 
cd goose-demo 
touch docker-compose.yml 
mkdir migrations 

Postgres Setup

Now that our structure is setup, lets create the docker-compose for Postgres.

Im going to attach these to a docker network named proxy

docker network create proxy 

docker-compose.yml

networks:
    proxy:
        external: true 

volumes:
    pg_data: 

services:
    postgres:
        container_name: postgres
        hostname: postgres
        restart: unless-stopped
        image: postgres:16.2-bookworm
        networks:
        - proxy
        environment:
        - POSTGRES_PASSWORD=goose # Always use a better password this is for demo
        - POSTGRES_USER=goose
        - POSTGRES_DB=goose
        ports:
        - 5432:5432
        volumes:
        - pg_data:/var/lib/postgresql/data

2. Start postgres DB.

docker compose up -d 

Install Goose

Mac

brew install goose 

Go install

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

This will install the goose binary to your $GOPATH/bin directory.

Create a Goose Config Script.

This is just to manage Goose Config so you can source it before you run goose commands in your terminal.

goose_config.sh

#!/usr/bin/env bash

export GOOSE_DRIVER=postgres # The database driver to use
export GOOSE_DBSTRING="postgres://goose:goose@localhost:5432/goose?sslmode=disable" # The database connection string
export GOOSE_MIGRATION_DIR="./migrations" The directory containing the migration files (default: .)

Now source the script in your terminal to load the variables

. goose_config.sh 

can also use

source goose_config.sh 

Now we are ready to start using goose

Fisrt Goose Migration

After the goose_config.sh is sourced we are ready to start.

Lets start by making a user

goose create add_vault_user sql
goose fix # creates sequential ids 

this should create a file in the migrations

migrations/00001_add_vault_user.sql

-- +goose Up
-- +goose StatementBegin
SELECT 'up SQL query';
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
SELECT 'down SQL query';
-- +goose StatementEnd

Now lets modify the goose script to create a user and a drop user for the down

migrations/00001_add_vault_user.sql

-- +goose Up
-- +goose StatementBegin
CREATE USER vaultcontroller WITH SUPERUSER PASSWORD 'Password12345';
ALTER USER vaultcontroller WITH LOGIN CREATEDB CREATEROLE;
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
DROP USER vaultcontroller;
-- +goose StatementEnd

warning

; is required after every SQL statement

Now lets run goose status to make sure we can connect to our db and our migration should show up as pending

goose status

Result

2024/10/17 15:23:56     Applied At                  Migration
2024/10/17 15:23:56     =======================================
2024/10/17 15:23:56     Pending                  -- 00001_add_vault_user.sql

Run Migrtion

Now simply run

goose up 

2024/10/17 15:30:18 OK   00001_add_vault_user.sql (12.81ms)
2024/10/17 15:30:18 goose: successfully migrated database to version: 1

Now if we do a status we should see it is no longer pending

goose status 

Result

2024/10/17 15:31:04     Applied At                  Migration
2024/10/17 15:31:04     =======================================
2024/10/17 15:31:04     Thu Oct 17 22:30:18 2024 -- 00001_add_vault_user.sql

Rollback

When you run goose up it will run all migrations in order, i like to always add goose fix after creating a new migration to make the ids for each migration more human readable.

When you need to roll back you can check to see which version of the database you are on.

goose version

Result

2024/10/17 15:33:52 goose: version 1

To Decrement the version you can run goose down. This will run the -- +goose Down part of the migration and decrement the version

goose down

Result

2024/10/17 15:36:21 OK   00001_add_vault_user.sql (3.54ms)

Check Status

goose status

Result

2024/10/17 15:36:46     Applied At                  Migration
2024/10/17 15:36:46     =======================================
2024/10/17 15:36:46     Pending                  -- 00001_add_vault_user.sql

goose version

Result

2024/10/17 15:36:55 goose: version 0

Conclusion

Goose is a great way to manage your sql migrations and keep them stored in git.

This will allow you to keep a very detailed changed record

• Code changes recorded in Git
• Migrations manifest in goose table on db
• Ability to rollback migrations.

warning

goose down can delete data so be careful with your sql scripts in production

Destroy

We wouldnt be Dev Build Destory without cleaning up our environment.

Very simple, to stop postgres and keep data

docker compose down 

To stop and delete data

docker compose down -v