godfish

godfish is a database migration manager. It is a CLI and a golang library. The name is an homage to dogfish.

features

• Use the native query language in the migration files, no other high-level DSLs.
• Interface with many DBs: cassandra, mysql, postgres, sqlite3, sqlserver.
• Light on dependencies. Release pipeline builds small binaries targeted for one DB driver.
• Release binaries are standalone and statically compiled, go is not necessary. Great if you have projects written in different languages and want a consistent experience.
• CLI with shell completion for bash, fish, zsh.
• Library usage supports embedding migrations.
• 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 which targets that DB. As of v0.16.0, releases also include a binary compiled with all drivers. 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.

To install as a golang library, see library usage

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

# want to work with many different kinds of DBs and use the same binary?
just build

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}

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 command, godfish

The first mode is using a delegator command, where godfish is an executable compiled with all supported drivers in one command. It dynamically routes commands to a DB driver. 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 command.
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 dependencies on other DB clients you know you won't use. 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

shell completion

Pre-generated shell completion scripts for bash, fish, zsh are available in .assets/completions, as well as the brew tap (see installation). At this time it's only for godfish the delegator command.

To generate for driver binaries, see the hidden subcommand completion:

godfish-<driver> completion [shell]

Its usage message may guide you through the rest.

getting help

The remaining usage examples are written as if you are directly running a driver binary. However, if you're using the delegator command 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

See the go doc page for more.

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 more.

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.just and the .ci/ directory.

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

just --set CONTAINER_TOOL podman -f ci.just

Build environments and run tests

just -f ci.just cassandra4-up
just -f ci.just cassandra5-up

just -f ci.just sqlserver-up

just -f ci.just mariadb-up

just -f ci.just postgres15-up
just -f ci.just postgres16-up

just -f ci.just sqlite3-up

Teardown

just -f ci.just cassandra4-down
just -f ci.just cassandra5-down

just -f ci.just sqlserver-down

just -f ci.just mariadb-down

just -f ci.just postgres15-down
just -f ci.just postgres16-down

just -f ci.just sqlite3-down