Goose database migration tool - fork of https://bitbucket.org/liamstask/goose

Overview

goose

Goose is a database migration tool. Manage your database schema by creating incremental SQL changes or Go functions.

GoDoc Widget Travis Widget

Goals of this fork

github.com/pressly/goose is a fork of bitbucket.org/liamstask/goose with the following changes:

  • No config files
  • Default goose binary can migrate SQL files only
  • Go migrations:
    • We don't go build Go migrations functions on-the-fly from within the goose binary
    • Instead, we let you create your own custom goose binary, register your Go migration functions explicitly and run complex migrations with your own *sql.DB connection
    • Go migration functions let you run your code within an SQL transaction, if you use the *sql.Tx argument
  • The goose pkg is decoupled from the binary:
    • goose pkg doesn't register any SQL drivers anymore, thus no driver panic() conflict within your codebase!
    • goose pkg doesn't have any vendor dependencies anymore
  • We use timestamped migrations by default but recommend a hybrid approach of using timestamps in the development process and sequential versions in production.

Install

$ go get -u github.com/pressly/goose/cmd/goose

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

For a lite version of the binary without DB connection dependent commands, use the exclusive build tags:

$ go build -tags='no_postgres no_mysql no_sqlite3' -i -o goose ./cmd/goose

Usage

Usage: goose [OPTIONS] DRIVER DBSTRING COMMAND

Drivers:
    postgres
    mysql
    sqlite3
    mssql
    redshift

Examples:
    goose sqlite3 ./foo.db status
    goose sqlite3 ./foo.db create init sql
    goose sqlite3 ./foo.db create add_some_column sql
    goose sqlite3 ./foo.db create fetch_user_data go
    goose sqlite3 ./foo.db up

    goose postgres "user=postgres dbname=postgres sslmode=disable" status
    goose mysql "user:[email protected]/dbname?parseTime=true" status
    goose redshift "postgres://user:[email protected]:5439/db" status
    goose tidb "user:[email protected]/dbname?parseTime=true" status
    goose mssql "sqlserver://user:[email protected]:1433?database=master" status

Options:

  -dir string
    	directory with migration files (default ".")
  -table string
    	migrations table name (default "goose_db_version")
  -h	print help
  -v	enable verbose mode
  -version
    	print version

Commands:
    up                   Migrate the DB to the most recent version available
    up-by-one            Migrate the DB up by 1
    up-to VERSION        Migrate the DB to a specific VERSION
    down                 Roll back the version by 1
    down-to VERSION      Roll back to a specific VERSION
    redo                 Re-run the latest migration
    reset                Roll back all migrations
    status               Dump the migration status for the current DB
    version              Print the current version of the database
    create NAME [sql|go] Creates new migration file with the current timestamp
    fix                  Apply sequential ordering to migrations

create

Create a new SQL migration.

$ goose create add_some_column sql
$ Created new file: 20170506082420_add_some_column.sql

Edit the newly created file to define the behavior of your migration.

You can also create a Go migration, if you then invoke it with your own goose binary:

$ goose create fetch_user_data go
$ Created new file: 20170506082421_fetch_user_data.go

up

Apply all available migrations.

$ goose up
$ OK    001_basics.sql
$ OK    002_next.sql
$ OK    003_and_again.go

up-to

Migrate up to a specific version.

$ goose up-to 20170506082420
$ OK    20170506082420_create_table.sql

up-by-one

Migrate up a single migration from the current version

$ goose up-by-one
$ OK    20170614145246_change_type.sql

down

Roll back a single migration from the current version.

$ goose down
$ OK    003_and_again.go

down-to

Roll back migrations to a specific version.

$ goose down-to 20170506082527
$ OK    20170506082527_alter_column.sql

redo

Roll back the most recently applied migration, then run it again.

$ goose redo
$ OK    003_and_again.go
$ OK    003_and_again.go

status

Print the status of all migrations:

$ goose status
$   Applied At                  Migration
$   =======================================
$   Sun Jan  6 11:25:03 2013 -- 001_basics.sql
$   Sun Jan  6 11:25:03 2013 -- 002_next.sql
$   Pending                  -- 003_and_again.go

Note: for MySQL parseTime flag must be enabled.

version

Print the current version of the database:

$ goose version
$ goose: version 002

Migrations

goose supports migrations written in SQL or in Go.

SQL Migrations

A sample SQL migration looks like:

-- +goose Up
CREATE TABLE post (
    id int NOT NULL,
    title text,
    body text,
    PRIMARY KEY(id)
);

-- +goose Down
DROP TABLE post;

Notice the annotations in the comments. Any statements following -- +goose Up will be executed as part of a forward migration, and any statements following -- +goose Down will be executed as part of a rollback.

By default, all migrations are run within a transaction. Some statements like CREATE DATABASE, however, cannot be run within a transaction. You may optionally add -- +goose NO TRANSACTION to the top of your migration file in order to skip transactions within that specific migration file. Both Up and Down migrations within this file will be run without transactions.

By default, SQL statements are delimited by semicolons - in fact, query statements must end with a semicolon to be properly recognized by goose.

More complex statements (PL/pgSQL) that have semicolons within them must be annotated with -- +goose StatementBegin and -- +goose StatementEnd to be properly recognized. For example:

-- +goose Up
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION histories_partition_creation( DATE, DATE )
returns void AS $$
DECLARE
  create_query text;
BEGIN
  FOR create_query IN SELECT
      'CREATE TABLE IF NOT EXISTS histories_'
      || TO_CHAR( d, 'YYYY_MM' )
      || ' ( CHECK( created_at >= timestamp '''
      || TO_CHAR( d, 'YYYY-MM-DD 00:00:00' )
      || ''' AND created_at < timestamp '''
      || TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD 00:00:00' )
      || ''' ) ) inherits ( histories );'
    FROM generate_series( $1, $2, '1 month' ) AS d
  LOOP
    EXECUTE create_query;
  END LOOP;  -- LOOP END
END;         -- FUNCTION END
$$
language plpgsql;
-- +goose StatementEnd

Go Migrations

  1. Create your own goose binary, see example
  2. Import github.com/pressly/goose
  3. Register your migration functions
  4. Run goose command, ie. goose.Up(db *sql.DB, dir string)

A sample Go migration 00002_users_add_email.go file looks like:

package migrations

import (
	"database/sql"

	"github.com/pressly/goose"
)

func init() {
	goose.AddMigration(Up, Down)
}

func Up(tx *sql.Tx) error {
	_, err := tx.Exec("UPDATE users SET username='admin' WHERE username='root';")
	if err != nil {
		return err
	}
	return nil
}

func Down(tx *sql.Tx) error {
	_, err := tx.Exec("UPDATE users SET username='root' WHERE username='admin';")
	if err != nil {
		return err
	}
	return nil
}

Hybrid Versioning

Please, read the versioning problem first.

We strongly recommend adopting a hybrid versioning approach, using both timestamps and sequential numbers. Migrations created during the development process are timestamped and sequential versions are ran on production. We believe this method will prevent the problem of conflicting versions when writing software in a team environment.

To help you adopt this approach, create will use the current timestamp as the migration version. When you're ready to deploy your migrations in a production environment, we also provide a helpful fix command to convert your migrations into sequential order, while preserving the timestamp ordering. We recommend running fix in the CI pipeline, and only when the migrations are ready for production.

License

Licensed under MIT License

Issues
  • sequential numbering with postgres or redshift schemas

    sequential numbering with postgres or redshift schemas

    I'm just started using this fork because it has support for Redshift. However, I use schemas to organize tables with databases like redshift and postgres but the new sequential numbering of files, instead of timestamps, means that I have to put all my migration files in the same directory, otherwise the counter will start again at 00001 for each directory.

    Say I have a single database in redshift or postgres, and it has multiple schemas CREATE SCHEMA foo; and CREATE SCHEMA bar;. If I wanted to organize my goose migrations by folders, I might have:

    foo/
    bar/
    

    If i then ran cd foo && goose create add_cars sql then cd ../bar && goose_create add_trains sql, goose will create foo/00001_add_cars.sql and bar/00001_add_trains.sql. So running:

    $ cd foo $ goose postgres "user=rkulla dbname=postgres sslmode=disable" up goose: no migrations to run. current version: 1 $ cd ../bar $ goose postgres "user=rkulla dbname=postgres sslmode=disable" up goose: no migrations to run. current version: 1

    would only apply the migration under foo/ but not the one under bar/, because both migrations start with 00001_ and postgres or redshift only get ONE goose_db_version table to share amongst the different schema names. This makes them harder to organize unless I do goose create add_cars_foo sql' andgoose create add_trains_bar sql, then run commands likels *_foo.sqlandls *_bar.sql`.

    It's not that big of a deal right now I guess, but I'm wondering if there's a better way and if it's really worth not using timestamps. IIRC, Ruby on Rails's migration feature used to use sequential numbers but developers complained because it caused a lot of conflicts when 2 different developers working on the same project made separate changes but both generated a migration with the same number. So Rails switched to UTC timestamps.

    opened by rkulla 28
  • Exit the program when migration can't be parsed

    Exit the program when migration can't be parsed

    Closes #115

    With un-parsable SQL, goose should exit it's current execution.

    Previously, goose would effectively skip over this invalid migration but still mark it as applied on the database.

    This PR will make it so that goose will error out of it's current execution and the migration will not be applied.

    I have also added files created by tests to the .gitignore

    opened by TomasBarry 24
  • Add vendoring support using dep vendor package manager.

    Add vendoring support using dep vendor package manager.

    • Add vendor dependencies for better macos homebrew support
    • Referencing #42
    • Referencing Homebrew/homebrew-core#14724

    Signed-off-by: Mario Kozjak [email protected]

    opened by mkozjak 22
  • Create migration file with next version number

    Create migration file with next version number

    Fixes #25

    opened by VojtechVitek 11
  • goose is no longer able to run with GOOSE_DBSTRING set as env

    goose is no longer able to run with GOOSE_DBSTRING set as env

    I have not added or changed anything but did not pin the version of goose.

    In the latest version I am now unable to set this env variable. Therefore I am unable to get the correct env string for deployments. Which ultimately messes up production issues now.

    goose run: "postgres": no such command
    

    I am running this command:

    ./goose -dir migrations postgres $GOOSE_DBSTRING up 
    
    opened by eleijonmarck 10
  • goose run: no separator found

    goose run: no separator found

    I've installed the binary using go get -u github.com/pressly/goose/cmd/goose. I can generate the migration files in anywhere, but not in my project directory. When i type something like goose postgres <connection_string> status, i got error goose run: no separator found. Is there something i might misconfigure ? or misunderstand the usage ? thanks

    I'm using

    • golang:1.9.2 darwin/amd64
    • macOS High Sierra 10.13
    opened by pjuanda 9
  • Feature Request: Custom migration templates

    Feature Request: Custom migration templates

    Hi,

    If I'm willing to create a PR, would you be open to adding an option to allow us to specify custom templates?

    The reason I ask is that the current sql template produces:

    
    -- +goose Up
    -- SQL in section 'Up' is executed when this migration is applied
    
    
    -- +goose Down
    -- SQL section 'Down' is executed when this migration is rolled back
    
    
    

    but I would like it to produce:

    
    -- +goose Up
    -- +goose StatementBegin
    -- +goose StatementEnd
    
    
    -- +goose Down
    -- +goose StatementBegin
    -- +goose StatementEnd
    
    
    

    I want to always use the begin and end statements since we will be doing some complex migrations including plpgsql functions and I'd rather just always use the same format.

    Allowing me to specify a custom template would avoid me having to replace the default template in the newly-created file after creating a new migration.

    If you're open to this, please give me a suggestion as to how you'd like the template specified and I'd be happy to follow that. I guess I'd see these templates specified in code, but I suppose a command like arg would work as well.

    Thoughts?

    Thanks!

    opened by joelpresence 9
  • Made command more importable

    Made command more importable

    opened by macdabby 9
  • Binary releases

    Binary releases

    Hi all,

    First off - thanks for stepping up and taking over this project!

    I'd love to see binary releases published to your GitHub repo. Doing a go get to grab the CLI at runtime is a little scary, since we can't pin a version (to my knowledge). Even scarier given that this is mucking with databases.

    In terms of implementation, we're big fans of goreleaser - it does all the heavy lifting for you.

    Would be happy to take a crack at a PR if y'all would be open to it.

    opened by rbren 9
  • feature: add additional columns to goose table

    feature: add additional columns to goose table

    The current goose table stores the following pieces of information:

    id
    version_id
    is_applied
    tstamp
    

    To improve goose, we could record a few additional items, such as:

    filename
    checksum
    duration_in_ms
    

    I'm not sure we even want to do this, but figured I'd open this issue as a placeholder.

    Another thing we've considered previously is removing is_applied .. I don't recall why we need this.


    Some previous requests: #213

    opened by mfridman 1
  • improve Status performance; add ability to retrieve latest N migrations

    improve Status performance; add ability to retrieve latest N migrations

    Currently the implementation of the status command is to loop over all the migrations and get the db metadata one-by-one. I think we can do better by retrieving all migrations at once, although to future proof we could just paginate which would be a few round trips.

    This would enable us to list latest N migrations, which has been requested in #254

    Lastly, this will enable us to display migrations based on their applied order, instead of their migration file order. See comment https://github.com/pressly/goose/pull/280#issuecomment-945006038. This is important because in #262 we added out-of-order migrations, so the file order no longer is the source of truth.

    opened by mfridman 0
  • feature: add dry-run option

    feature: add dry-run option

    More details to come..

    But it'd be neat to print (or save to a single file) the migrations goose will apply, but do not actually apply the migrations.

    TBD on the implementation.

    opened by mfridman 0
  • feature: add functionality to dump database schema

    feature: add functionality to dump database schema

    The proposal is to enable goose to drop the current database schema. This is usually done with tools like pg_dump.

    Now, this would require the user to have this executable in their $PATH or we can invoke a lightweight container such as postgres:14-alpine, if neither is available then fail.

    I don't think this should live inside the github.com/pressly/goose/v3 package, and instead should be its own subdir package, such as pgutil or something. I imagine we should expose a few of these mysqlutil. (can't come up with a better name, suggestions welcome to not use {dialect}util)

    In the goose binaries, the behaviour could be to dump a schema file after running a modifying migration. The file could be written to the same folder as -dir as schema.sql

    opened by mfridman 0
  • Add support for seeding the database with test data

    Add support for seeding the database with test data

    I have used goose at pretty much every job to manage my relational databases. It works like a charm, but I always find myself creating some sort of wrapper that allows me to use goose to seed my local and development environments with test data. It would be really nice if this were supported already.

    I propose creating a new command goose seed that will allow users to continue managing their schema migrations with goose and also seed their non-production environments with test data for integration & end-to-end testing. Please note that I am not suggesting actual seed data be managed this way - if your application requires some static, pre-existing data, that can just be inserted along with the regular schema migrations.

    What are your thoughts on this? If we agree that this is useful and decide on an interface, I'd like to take on this work and open a PR to implement it.

    opened by soggycactus 17
  • How do I connect to clickhouse using a username and password.

    How do I connect to clickhouse using a username and password.

    Please add an example on how to use the dsn to connect to clickhouse using a username and password.

    opened by rickydjohn 0
  • Add Add flag to retrieve N most recent migrations

    Add Add flag to retrieve N most recent migrations

    Resolves #254

    This PR adds a flag that allows the user to retrieve the N most recent migrations. This is useful when you have many migrations that you have created and it takes a long time to print them all out and you just want to see the last few to either make sure they have all been applied or to make sure that the new migration timestamp you are working on will still be applied when another collaborator may have created and merged a migration before you.

    usage example: goose -n 10 mysql "[DB_String]" status This would return the 10 most recent migrations

    opened by cmpickle 3
  • Support for last N migrations for the database

    Support for last N migrations for the database

    When using the status command of goose it can take a long time to read and print out all of the years worth of migrations to the terminal. Often times when I run the status command all I care about is looking at the last 10 or so migrations to see if the database that I am currently pointed at has had the latest migrations applied.

    I would love to see support added for a flag along the lines of goose mysql "[DB_CONNECTION_STRING]" status -n 10 That would allow me to have goose retrieve the last 10 migrations and if they have been applied as status typically does.

    opened by cmpickle 0
  • Use UTC for timestamps when creating a migration

    Use UTC for timestamps when creating a migration

    So, I understand that there is prior art here (#38, #50, #63), but my team is using timestamped versions and switching to sequential numbering is not an option at this time. We're split across multiple timezones and in order to keep things a little more sane, UTC would be a welcome change for the timestamps. Maybe in the future we can migrate over to sequential, but this is where we are at.

    opened by linux2647 4
  • clickhouse: Fixed get db version query

    clickhouse: Fixed get db version query

    2 bugs with current query:

    • reset command resets only last applied migrations , because LIMIT 1;
    • not deterministic result for migrations applied with same tstamp, because ORDER BY tstamp;

    @huynguyenh, please take a look)

    opened by chapsuk 0
Releases(v3.4.1)
Owner
Pressly Inc.
Pressly Inc.
Dbmate is a database migration tool, to keep your database schema in sync across multiple developers and your production servers.

Dbmate is a database migration tool, to keep your database schema in sync across multiple developers and your production servers. It is a stand

Adrian Macneil 2.2k Dec 3, 2021
Minimalistic database migration helper for Gorm ORM

Gormigrate Gormigrate is a minimalistic migration helper for Gorm. Gorm already has useful migrate functions, just misses proper schema versioning and

null 698 Dec 3, 2021
Dead simple Go database migration library.

migrator Dead simple Go database migration library. Features Simple code Usage as a library, embeddable and extensible on your behalf Support of any d

David Lobe 115 Nov 8, 2021
Database migration through structures - development

goMigration 基于 Golang 的数据库迁移工具,目前仍在开发中,有兴趣的小伙伴可以联系我一起~ 食用方法 go get https://github.com/DGuang21/goMigration 手动将其安装 可通过 gom gen create_c_user_table 方法生

Daguang 2 Oct 17, 2021
Simple Migration Tool - written in Go

Pravasan Simple Migration tool intend to be used for any languages, for any db. Please feel free to criticize, comment, etc. Currently this is working

null 25 Nov 18, 2021
SQL schema migration tool for Go.

sql-migrate SQL Schema migration tool for Go. Based on gorp and goose. Using modl? Check out modl-migrate. Features Usable as a CLI tool or as a libra

Ruben Vermeersch 2.3k Nov 29, 2021
Simple migration tool for MySQL

prrn Simple migration tool for MySQL This is a CLI that helps you create a DB migration file. There is no need to write up and down files from scratch

kamijin_fanta 7 Nov 10, 2021
A tool to compare if terraform provider migration schema snapshot is equal to schema defined in resource code

migration schema comparer for Terraform When develop Terraform provider sometimes we need do some state migration(not schema migration) via StateUpgra

null 0 Nov 18, 2021
Opinionated tool for database structure management and migrations

trek Requirements At least version 13 of postgres is needed. Installation go install . Setup Create config.yaml: model_name: <model_name> db_name: <db

Stack11 2 Nov 18, 2021
Database schema evolution library for Go

Try browsing the code on Sourcegraph! Darwin Database schema evolution library for Go Example package main import ( "database/sql" "log" "github.

Guiabolso 122 Nov 20, 2021
Django style fixtures for Golang's excellent built-in database/sql library.

go-fixtures Django style fixtures for Golang's excellent built-in database/sql library. Currently only YAML fixtures are supported. There are two rese

Richard Knop 26 Apr 8, 2021
Database migrations. CLI and Golang library.

migrate Database migrations written in Go. Use as CLI or import as library. Migrate reads migrations from sources and applies them in correct order to

null 7.6k Dec 3, 2021
Database migrations. CLI and Golang library.

Database migrations written in Go. Use as CLI or import as library.

null 27 May 30, 2021
goydb, a couchdb compatible embeddable database written in go

goydb, a couchdb compatible embeddable database written in go Getting started (not embedded) Using docker mkdir data docker run -e GOYDB_ADMINS=admin:

goydb 12 Nov 24, 2021
mini tools handling migrasion database from cli

mini tools handling migrasion database from cli

null 1 Nov 23, 2021
entimport is a tool for creating Ent schemas from existing SQL databases.

entimport entimport is a tool for creating Ent schemas from existing SQL databases. Currently, MySQL and PostgreSQL are supported. The tool can import

ariga 33 Nov 27, 2021
Tool to handle versioned migrations with gorm

GORM Migrations About Gorm Migrations Gorm Migrations is a tool designed for go-gorm. Gorm Migration takes the pain out of development of migrations v

null 1 Dec 6, 2021
Goose database migration tool - fork of https://bitbucket.org/liamstask/goose

goose Goose is a database migration tool. Manage your database schema by creating incremental SQL changes or Go functions. Goals of this fork github.c

Pressly Inc. 2k Dec 6, 2021
⚡️ A lightweight service that will build and store your go projects binaries, Integrated with Github, Gitlab, Bitbucket and Bitbucket Server.

Rabbit A lightweight service that will build and store your go projects binaries. Rabbit is a lightweight service that will build and store your go pr

Ahmed 193 Nov 18, 2021
A simple database migration tool using an sql.DB connection and fs.FS for the migration source

A simple database migration tool using an sql.DB connection and fs.FS for the migration source. It has no non-test dependencies.

 maragu 31 Nov 18, 2021
Quickly clone an entire org/users repositories into one directory - Supports GitHub, GitLab, Bitbucket, and more

ghorg ghorg allows you to quickly clone all of an orgs, or users repos into a single directory. This can be useful in many situations including Search

Jay Gabriels 615 Nov 27, 2021
Dbmate is a database migration tool, to keep your database schema in sync across multiple developers and your production servers.

Dbmate is a database migration tool, to keep your database schema in sync across multiple developers and your production servers. It is a stand

Adrian Macneil 2.2k Dec 3, 2021
Minimalistic database migration helper for Gorm ORM

Gormigrate Gormigrate is a minimalistic migration helper for Gorm. Gorm already has useful migrate functions, just misses proper schema versioning and

null 698 Dec 3, 2021
Dead simple Go database migration library.

migrator Dead simple Go database migration library. Features Simple code Usage as a library, embeddable and extensible on your behalf Support of any d

David Lobe 115 Nov 8, 2021
Database migration through structures - development

goMigration 基于 Golang 的数据库迁移工具,目前仍在开发中,有兴趣的小伙伴可以联系我一起~ 食用方法 go get https://github.com/DGuang21/goMigration 手动将其安装 可通过 gom gen create_c_user_table 方法生

Daguang 2 Oct 17, 2021
A Go library for an efficient implementation of a skip list: https://godoc.org/github.com/MauriceGit/skiplist

Fast Skiplist Implementation This Go-library implements a very fast and efficient Skiplist that can be used as direct substitute for a balanced tree o

Maurice Tollmien 179 Dec 1, 2021
:fishing_pole_and_fish: Webhook receiver for GitHub, Bitbucket, GitLab, Gogs

Library webhooks Library webhooks allows for easy receiving and parsing of GitHub, Bitbucket and GitLab Webhook Events Features: Parses the entire pay

Go Playgound 679 Dec 5, 2021
Ah shhgit! Find secrets in your code. Secrets detection for your GitHub, GitLab and Bitbucket repositories: www.shhgit.com

shhgit helps secure forward-thinking development, operations, and security teams by finding secrets across their code before it leads to a security br

Paul 3.3k Dec 4, 2021
BitBucket Majoo With Golang

BitBucket Majoo created by Christian Vergiananta Tamonsang * Database create database db_bitbucket * Running Program go run bitbucket * Usecase diagra

vergiananta 0 Nov 13, 2021