godfish

godfish is a database migration manager, similar to the very good dogfish, but written in golang. It is a CLI and a library.

goals

• use the native query language in the migration files, no other high-level DSLs
• interface with many DBs
• light on dependencies
• not terrible error messages

installation

There are multiple ways to get the CLI.

The Releases page of the GitHub repository has pre-built artifacts for supported platforms. Each archive file contains an executable binary per driver. Each executable binary will only work for the targeted DB. Pick the one(s) you need.

Alternatively, install via the Homebrew tap:

brew tap rafaelespinoza/godfish

# install the binary for your DB
brew install godfish-cassandra
brew install godfish-mysql
brew install godfish-postgres
brew install godfish-sqlite3
brew install godfish-sqlserver

# or install all of them
brew install godfish

For Unix-like environments without Homebrew, there is an installation script at scripts/install.sh. Check it out.

build

An alternative to using a pre-built release to is to build your own. NOTE: these require just and go.

Make a CLI binary for the DB you want to use. This tool comes with some driver implementations. Build one like so:

just build-cassandra
just build-mysql
just build-postgres
just build-sqlite3
just build-sqlserver

From there you could move it to $GOPATH/bin, move it to your project or whatever else you need to do.

concepts

Like any db migration tool, this helps you and your team manage the shape of your application's database throughout time. Migrations are written in the native language of the database, and exist as files in a directory. All migrations to consider should live in the same directory. A migration is one of these files, which may have metadata components as part of the filename:

• "direction": Migrations that introduce new changes to the DB shape are considered to have a "forward" direction. A migration intended as the inverse of a corresponding forward migration is considered to have a "reverse" direction.
• "version": Describe where a migration exists relative to the other migrations with the same direction. By default, it's a timestamp in the layout YYYYMMDDHHmmss.
• "name": A label you give to describe the migration's contents.

The delimiter of each part is a -. Each migration filename has this format:

${direction}-${version}-${name}.sql

There is an implicit ordering to migrations, denoted by the "version", where subsequent migrations may build upon the shape created by preceding migrations. When a migration is successfully applied, a new row is inserted into the schema migrations table. By default its name is schema_migrations. A migration that has not yet been applied will be a file in the directory, but without a corresponding entry in the DB table.

The shape of the schema migrations table is roughly:

column	type	description
migration_id	varchar	primary key timestamp, derived from filename version
label	varchar	describes the migration, also derived from filename
executed_at	integer	unix epoch of when migration was applied

usage

Not only is this tool a CLI, it's also a database migration library. Most of the time, you'll probably want to use it as a CLI.

command line usage

This section describes basic usage of a CLI binary. For details on getting a CLI binary, see the installation section. Golang is not required here.

execution modes for command line

There are 2 execution modes to pick from, depending on your workflow.

delegator script, godfish

The first mode is using a delegator script. If you installed godfish via Homebrew or you copy scripts/godfish, then godfish is a wrapper script that dynamically routes commands to a DB driver binary, which would be installed nearby; such as the same directory as the script, or in a libexec directory.

When using this mode, you must pass in the driver name as the first argument, like so:

godfish <driver> [...arguments]
# Where `driver` is one of cassandra, mysql, postgres, sqlite3, sqlserver.

# More examples of the delegator script.
godfish cassandra version
godfish mysql init -conf godfish_mysql.json
godfish postgres -dsn "${DB_DSN}" -files testdata/default -loglevel debug info
godfish sqlite3 -dsn path/to/db/file create-migration -name create_foos_table
godfish sqlserver --help

direct to DB driver, godfish-<driver>

The second mode is running a driver binary directly. Use this if you prefer to work with a single, self-container executable without any wrapper scripts. As mentioned earlier, each release contains a binary per DB driver. Some usage examples of this mode:

godfish-<driver> [...arguments]
# Where `driver` is one of cassandra, mysql, postgres, sqlite3, sqlserver.

# More examples of direct driver. Notice, you're invoking a different
# executable.
godfish-cassandra version
godfish-mysql init -conf godfish_mysql.json
godfish-postgres -dsn "${DB_DSN}" -files testdata/default -loglevel debug info
godfish-sqlite3 -dsn path/to/db/file create-migration -name create_foos_table
godfish-sqlserver --help

getting help

The remaining usage examples are written as if you are directly running a driver binary. However, if you're using the delegator script mentioned above, then you would simply replace godfish-<driver> with godfish <driver>.

godfish-<driver> help
godfish-<driver> -h
godfish-<driver> <command> -h

Configuration options are read from command line flags first. If those are not set, then it checks the configuration file.

connecting to the db

Database connection parameters may be read from an environment variable. Set:

DB_DSN=

When using one of the pre-built binaries, the database may also be specified with a command line flag, -dsn. If specified in both places, then the command line value will have higher precedence than the environment variable.

configure file paths

Manually set path to db migration files.

godfish-<driver> -files db/migrations <command>

Make your life easier by creating a configuration file by invoking the init subcommand. This creates a file at .godfish.json, where configuration may live.

Change the path to the configuration file.

mv .godfish.json foo.json
godfish-<driver> -conf foo.json <command>

everything else

cat .godfish.json
# { "path_to_files": "db/migrations" }

godfish-<driver> create-migration -name alpha
# outputs:
# db/migrations/forward-20200128070010-alpha.sql
# db/migrations/reverse-20200128070010-alpha.sql

godfish-<driver> create-migration -name bravo -reversible=false
# outputs:
# db/migrations/forward-20200128070106-bravo.sql

#
# ... write the sql in those files ...
#

# apply migrations
godfish-<driver> migrate
# apply migrations to up a specific version
godfish-<driver> migrate -version 20060102150405

# show status
godfish-<driver> info

# apply a reverse migration
godfish-<driver> rollback

# rollback and re-apply the last migration
godfish-<driver> remigrate

# show build metadata
godfish-<driver> version
godfish-<driver> version -json

library usage

Though most of the time you'll probably want to use one of the pre-built binaries, you could also use this as a golang library.

go get github.com/rafaelespinoza/godfish

embed migrations

An issue that may arise with deployments is that the migration files must be deployed alongside the godfish binary. Migrations data and the behavior provided by the godfish library can be combined into a single self-contained binary by using the embed package. See the go doc page for an example.

upgrading schema migrations

If you have data created with v0.14.0 or lower and then later on use a newer version, then you may run into an error message like:

schema migrations table is missing columns; run the upgrade command to fix this

A schema migrations table created with versions <= v0.14.0 will lack the label and executed_at columns. The upgrade command adds them.

other minutiae

Here are some notable differences between dogfish and godfish:

Filenames:

• dogfish: migrate-${date}-${name}.sql, or rollback-${date}-${name}.sql
• godfish: forward-${date}-${name}.sql, or reverse-${date}-${name}.sql

Note, dogfish uses the words, "migrate" and "rollback" to describe the migration's direction whereas godfish uses "forward" and "reverse". They are the same in that they are two complementaries.

ls -1 /path/to/db/migrations

forward-20191112050547-init_foos.sql
forward-20221067051242-add_bars.sql
forward-20250805031405-update_more_stuff.sql
reverse-20191112050547-init_foos.sql
reverse-20221067051242-add_bars.sql
reverse-20250805031405-update_more_stuff.sql

project organization

One of the goals of this project is to minimize the amount of dependencies. Driver-specific code is placed in drivers/ so that building a binary for one database does not require building code for another database.

The godfish package defines library functions, interfaces needed to build a driver implementation.

Test infrastructure mostly lives in the .ci/ and .github/ directories. Many integration tests may be run in isolation on your local machine without GitHub actions.

tests

Docker (or equivalent) is used to create environments and run the tests against a live database. Each database has a separate configuration. All of this lives in ci.Justfile and the .ci/ directory.

Using an OCI-compatible tool other than docker (ie: podman)?

just --set CONTAINER_TOOL podman -f ci.Justfile

Build environments and run tests

just -f ci.Justfile cassandra3-up
just -f ci.Justfile cassandra4-up

just -f ci.Justfile sqlserver-up

just -f ci.Justfile mariadb-up

just -f ci.Justfile postgres14-up
just -f ci.Justfile postgres15-up

just -f ci.Justfile sqlite3-up

Teardown

just -f ci.Justfile cassandra3-down
just -f ci.Justfile cassandra4-down

just -f ci.Justfile sqlserver-down

just -f ci.Justfile mariadb-down

just -f ci.Justfile postgres14-down
just -f ci.Justfile postgres15-down

just -f ci.Justfile sqlite3-down