What is a Database Migration?

In simple terms, a database migration is the management of incremental, reversible changes to a relational database. Migrations are organized into separate script files, providing version control for changes made to the database structure.

Goose

Goose is a database migration tool written in Go. It can run migrations from the same SQL files that SQLC (another excellent Go tool, which I’ll discuss in a future article!) uses, making Goose and SQLC a perfect pairing for database management in Go.

Installation

To install Goose, make sure Go is installed on your machine. Then, simply run:

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

After installation, confirm it’s installed correctly by running:

goose -version

Now you’re all set with a powerful tool for managing database migrations in your Go projects! Let’s dive into using it.

Writing Database Migrations

With Goose, setting up your migration system is straightforward:

Step 1: Create a Database Migration Folder

Create a folder named sql/schema for your migrations (though you can use any folder structure you prefer). Organizing migrations under clearly named folders helps maintain a tidy codebase.

Step 2: Create a Migration File

Migration files follow a specific naming convention:

number_name.sql

For example, if you’re creating a users table, name the file something like:

001_create_users.sql

Step 3: Writing Migrations

Inside each migration file, structure your SQL commands with the following format:

-- +goose Up
CREATE TABLE ...

-- +goose Down
DROP TABLE users;

The -- +goose Up and -- +goose Down comments are required. They signal to Goose how to handle the migration. An “up” migration moves your database to a new state, while a “down” migration reverts it to its previous state.

Step 4: Running the Migrations

goose postgres CONN up

To run your migrations, navigate to the migration file directory in your terminal and execute:

• Replace postgres with your database driver of choice.
• Use up to apply migrations and down to roll them back.
• Replace CONN with your database connection string.

The connection string is a URL containing the information needed to connect to your database. Here’s the general format:

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

Examples:

• On Mac OS (no password, using your username): postgres://mahmoudajam:@localhost:5432/blogator
• On Linux (using the postgres user): postgres://postgres:postgres@localhost:5432/blogator

To test your connection, you can run:

psql "postgres://mahmoudajam:@localhost:5432/blogator"

This command should connect you directly to the database, blogator in this example.

Conclusion

Using Goose for database migrations in Go provides a simple, organized way to manage changes in your database over time. With its straightforward commands and compatibility with tools like SQLC, Goose can help streamline your database workflows, making it easier to track, apply, and revert changes as your project evolves. By setting up a structured migration system, you ensure that your database is as adaptable and well-maintained as your codebase, enabling seamless updates and reliable version control. Give Goose a try in your next Go project and experience the ease of organized database management!