Goose Migrations for Clean Database Adjustments

Hi there, mate!

As we speak, let’s speak about what database migrations are and why they’re so essential. In right now’s world, it’s no shock that any modifications to a database must be accomplished rigorously and based on a selected course of. Ideally, these steps can be built-in into our CI/CD pipeline in order that all the pieces runs robotically.

Right here’s our agenda:

What’s the issue?
How will we repair it?
A easy instance
A extra advanced instance
Suggestions
Outcomes
Conclusion

What’s the Drawback?

In case your workforce has by no means handled database migrations and also you’re not fully positive why they’re wanted, let’s kind that out. In the event you already know the fundamentals, be happy to skip forward.

Fundamental Problem

After we make “deliberate” and “easy” modifications to the database, we have to keep service availability and meet SLA necessities (in order that customers don’t undergo from downtime or lag). Think about you wish to change a column sort in a desk with 5 million customers. In the event you do that “head-on” (e.g., merely run ALTER TABLE with out prep), the desk may get locked for a major period of time — and your customers can be left with out service.

To keep away from such complications, observe two guidelines:

Apply migrations in a means that doesn’t lock the desk (or no less than minimizes locks).
If you could change a column sort, it’s usually simpler to create a brand new column with the proper sort first after which drop the outdated one afterward.

One other Drawback: Model Management and Rollbacks

Typically you could roll again a migration.

Doing this manually — going into the manufacturing database and twiddling with information — is just not solely dangerous but additionally possible inconceivable for those who don’t have direct entry. That’s the place devoted migration instruments come in useful. They allow you to apply modifications cleanly and revert them if obligatory.

How Do We Repair It? Use the Proper Instruments

Every language and ecosystem has its personal migration instruments:

For Java, Liquibase or Flyway are widespread.
For Go, a well-liked alternative is goose (the one we’ll take a look at right here).
And so forth.

Goose: What It Is and Why It’s Helpful

Goose is a light-weight Go utility that helps you handle migrations robotically. It gives:

Simplicity. Minimal dependencies and a clear file construction for migrations.
Versatility. Helps varied DB drivers (PostgreSQL, MySQL, SQLite, and so forth.).
Flexibility. Write migrations in SQL or Go code.

Putting in Goose

go set up github.com/pressly/goose/v3/cmd/goose@newest

How It Works: Migration Construction

By default, Goose appears to be like for migration information in db/migrations. Every migration follows this format:

NNN_migration_name.(sql|go)

NNN is the migration quantity (e.g., 001, 002, and so forth.).
After that, you possibly can have any descriptive identify, for instance init_schema.
The extension could be .sql or .go.

Instance of an SQL Migration

File: 001_init_schema.sql:

-- +goose Up
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

-- +goose Down
DROP TABLE customers;

Our First Instance

Altering a Column Sort (String → Int)

Suppose we’ve a customers desk with a column age of sort VARCHAR(255). Now we wish to change it to INTEGER. Right here’s what the migration may seem like (file 005_change_column_type.sql):

-- +goose Up
ALTER TABLE customers ALTER COLUMN age TYPE INTEGER USING (age::INTEGER);

-- +goose Down
ALTER TABLE customers ALTER COLUMN age TYPE VARCHAR(255) USING (age::TEXT);

What’s occurring right here:

Up migration
- We alter the age column to INTEGER. The USING (age::INTEGER) clause tells PostgreSQL convert present information to the brand new sort.
- Notice that this migration will fail if there’s any information in age that isn’t numeric. In that case, you’ll want a extra advanced technique (see beneath).
Down migration
- If we roll again, we return age to VARCHAR(255).
- We once more use USING (age::TEXT) to transform from INTEGER again to textual content.

The Second and Advanced Circumstances: Multi-Step Migrations

If the age column may include messy information (not simply numbers), it’s safer to do that in a number of steps:

Add a brand new column (age_int) of sort INTEGER.
Copy legitimate information into the brand new column, coping with or eradicating invalid entries.
Drop the outdated column.

-- +goose Up
-- Step 1: Add a brand new column
ALTER TABLE customers ADD COLUMN age_int INTEGER;

-- Step 2: Attempt to transfer information over
UPDATE customers 
SET age_int = CASE
    WHEN age ~ '^[0-9]+$' THEN age::INTEGER
    ELSE NULL
END;

-- (elective) take away rows the place information couldn’t be transformed
-- DELETE FROM customers WHERE age_int IS NULL;

-- Step 3: Drop the outdated column
ALTER TABLE customers DROP COLUMN age;

-- +goose Down
-- Step 1: Recreate the outdated column
ALTER TABLE customers ADD COLUMN age VARCHAR(255);

-- Step 2: Copy information again
UPDATE customers 
SET age = age_int::TEXT;

-- Step 3: Drop the brand new column
ALTER TABLE customers DROP COLUMN age_int;

To permit a correct rollback, the Down part simply mirrors the actions in reverse.

Automation is Key

To avoid wasting time, it’s actually handy so as to add migration instructions to a Makefile (or another construct system). Beneath is an instance Makefile with the primary Goose instructions for PostgreSQL.

Let’s assume:

The DSN for the database is postgres://person:password@localhost:5432/dbname?sslmode=disable.
Migration information are in db/migrations.

# File: Makefile

DB_DSN = "postgres://person:password@localhost:5432/dbname?sslmode=disable"
MIGRATIONS_DIR = db/migrations

# Set up Goose (run as soon as)
install-goose:
	go set up github.com/pressly/goose/v3/cmd/goose@newest

# Create a brand new SQL migration file
new-migration:
ifndef NAME
	$(error Utilization: make new-migration NAME=your_migration_name)
endif
	goose -dir $(MIGRATIONS_DIR) create $(NAME) sql

# Apply all pending migrations
migrate-up:
	goose -dir $(MIGRATIONS_DIR) postgres $(DB_DSN) up

# Roll again the final migration
migrate-down:
	goose -dir $(MIGRATIONS_DIR) postgres $(DB_DSN) down

# Roll again all migrations (watch out in manufacturing!)
migrate-reset:
	goose -dir $(MIGRATIONS_DIR) postgres $(DB_DSN) reset

# Verify migration standing
migrate-status:
	goose -dir $(MIGRATIONS_DIR) postgres $(DB_DSN) standing

Find out how to Use It?

1. Create a brand new migration (SQL file). This generates a file db/migrations/002_add_orders_table.sql.

make new-migration NAME=add_orders_table

2. Apply all migrations. Goose will create a schema_migrations desk in your database (if it doesn’t exist already) and apply any new migrations in ascending order.

3. Roll again the final migration. Simply down the final one.

4. Roll again all migrations (use warning in manufacturing). Full reset.

5. Verify migration standing.

Output instance:

$ goose standing
$   Utilized At                  Migration
$   =======================================
$   Solar Jan  6 11:25:03 2013 -- 001_basics.sql
$   Solar Jan  6 11:25:03 2013 -- 002_next.sql
$   Pending                  -- 003_and_again.go

Abstract

By utilizing migration instruments and a Makefile, we will:

Prohibit direct entry to the manufacturing database, making modifications solely by means of migrations.
Simply monitor database variations and roll them again if one thing goes flawed.
Keep a single, constant historical past of database modifications.
Carry out “easy” migrations that received’t break a working manufacturing atmosphere in a microservices world.
Achieve additional validation — each change will undergo a PR and code assessment course of (assuming you have got these settings in place).

One other benefit is that it’s straightforward to combine all these instructions into your CI/CD pipeline. And keep in mind — safety above all else.

For example:

jobs:
  migrate:
    runs-on: ubuntu-latest

    steps:
      - identify: Set up Goose
        run: |
          make install-goose

      - identify: Run database migrations
        env:
          DB_DSN: ${{ secrets and techniques.DATABASE_URL }}
        run: |
          make migrate-up

Conclusion and Suggestions

The principle concepts are so easy:

Preserve your migrations small and frequent. They’re simpler to assessment, check, and revert if wanted.
Use the identical software throughout all environments so dev, stage, and prod are in sync.
Combine migrations into CI/CD so that you’re not depending on anyone individual manually working them.

On this means, you’ll have a dependable and managed course of for altering your database construction — one which doesn’t break manufacturing and allows you to reply rapidly if one thing goes flawed.

Good luck together with your migrations!

Thanks for studying!