Database migrations. CLI and Golang library.

Overview

CircleCI - Build Status GoDoc Coverage Status packagecloud.io Docker Pulls Supported Go Versions GitHub Release Go Report Card

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 a database.
  • Drivers are "dumb", migrate glues everything together and makes sure the logic is bulletproof. (Keeps the drivers lightweight, too.)
  • Database drivers don't assume things or try to correct user input. When in doubt, fail.

Forked from mattes/migrate

Databases

Database drivers run migrations. Add a new database?

Database URLs

Database connection strings are specified via URLs. The URL format is driver dependent but generally has the form: dbdriver://username:[email protected]:port/dbname?param1=true&param2=false

Any reserved URL characters need to be escaped. Note, the % character also needs to be escaped

Explicitly, the following characters need to be escaped: !, #, $, %, &, ', (, ), *, +, ,, /, :, ;, =, ?, @, [, ]

It's easiest to always run the URL parts of your DB connection URL (e.g. username, password, etc) through an URL encoder. See the example Python snippets below:

$ python3 -c 'import urllib.parse; print(urllib.parse.quote(input("String to encode: "), ""))'
String to encode: FAKEpassword!#$%&'()*+,/:;=?@[]
FAKEpassword%21%23%24%25%26%27%28%29%2A%2B%2C%2F%3A%3B%3D%3F%40%5B%5D
$ python2 -c 'import urllib; print urllib.quote(raw_input("String to encode: "), "")'
String to encode: FAKEpassword!#$%&'()*+,/:;=?@[]
FAKEpassword%21%23%24%25%26%27%28%29%2A%2B%2C%2F%3A%3B%3D%3F%40%5B%5D
$

Migration Sources

Source drivers read migrations from local or remote sources. Add a new source?

CLI usage

  • Simple wrapper around this library.
  • Handles ctrl+c (SIGINT) gracefully.
  • No config search paths, no config files, no magic ENV var injections.

CLI Documentation

Basic usage

$ migrate -source file://path/to/migrations -database postgres://localhost:5432/database up 2

Docker usage

$ docker run -v {{ migration dir }}:/migrations --network host migrate/migrate
    -path=/migrations/ -database postgres://localhost:5432/database up 2

Use in your Go project

  • API is stable and frozen for this release (v3 & v4).
  • Uses Go modules to manage dependencies.
  • To help prevent database corruptions, it supports graceful stops via GracefulStop chan bool.
  • Bring your own logger.
  • Uses io.Reader streams internally for low memory overhead.
  • Thread-safe and no goroutine leaks.

Go Documentation

import (
    "github.com/golang-migrate/migrate/v4"
    _ "github.com/golang-migrate/migrate/v4/database/postgres"
    _ "github.com/golang-migrate/migrate/v4/source/github"
)

func main() {
    m, err := migrate.New(
        "github://mattes:[email protected]/migrate_test",
        "postgres://localhost:5432/database?sslmode=enable")
    m.Steps(2)
}

Want to use an existing database client?

import (
    "database/sql"
    _ "github.com/lib/pq"
    "github.com/golang-migrate/migrate/v4"
    "github.com/golang-migrate/migrate/v4/database/postgres"
    _ "github.com/golang-migrate/migrate/v4/source/file"
)

func main() {
    db, err := sql.Open("postgres", "postgres://localhost:5432/database?sslmode=enable")
    driver, err := postgres.WithInstance(db, &postgres.Config{})
    m, err := migrate.NewWithDatabaseInstance(
        "file:///migrations",
        "postgres", driver)
    m.Steps(2)
}

Getting started

Go to getting started

Tutorials

(more tutorials to come)

Migration files

Each migration has an up and down migration. Why?

1481574547_create_users_table.up.sql
1481574547_create_users_table.down.sql

Best practices: How to write migrations.

Versions

Version Supported? Import Notes
master import "github.com/golang-migrate/migrate/v4" New features and bug fixes arrive here first
v4 import "github.com/golang-migrate/migrate/v4" Used for stable releases
v3 import "github.com/golang-migrate/migrate" (with package manager) or import "gopkg.in/golang-migrate/migrate.v3" (not recommended) DO NOT USE - No longer supported

Development and Contributing

Yes, please! Makefile is your friend, read the development guide.

Also have a look at the FAQ.


Looking for alternatives? https://awesome-go.com/#database.

Issues
  • Getting

    Getting "error: file does not exist"

    We have run the migration successfully, and now i need to run it again in case there are any new migrations.

    This is he command we are using:

    migrate -database 'mysql://xxx:[email protected]/zzzz' -source file://go/src/github.com/me/myproj.org/db/schema up

    I am in the root directory of the user (i.e. /home/myuser), and underneath is the structure: go/src/github.com/me/myproj.org/db/schema which contains files like:

    201802221525_init_schema.down.sql 201805211743_us_states.down.sql 201805291420_countries.down.sql 201806051200_currencies.down.sql 201802221525_init_schema.up.sql 201805211743_us_states.up.sql 201805291420_countries.up.sql 201806051200_currencies.up.sql etc.

    We see that with mattes migrate, there were many similar issues with no obvious resolution.

    in the db in schema_migrations table, I see it says version 201807191200, although i am not sure this has any bearing on the issue.

    If I change the file path to something which doesn't exist, e.g.

    migrate -database 'mysql://xxx:[email protected]/zzzz' -source file://go/src/github.com/me/myproj.org/notexist up

    I get a different error message:

    error: open /home/muyuser/go/src/github.com/me/myproj.org/notexist : no such file or directory

    This is how we installed migrate:

    wget https://github.com/golang-migrate/migrate/releases/download/v3.2.0/migrate.linux-amd64.tar.gz gunzip migrate.linux-amd64.tar.gz tar xvf migrate.linux-amd64.tar mv migrate.linux-amd64 /usr/bin/migrate

    opened by nutmix 22
  • Fix go.sum

    Fix go.sum

    See golang/go#29278. Fix #138.

    opened by fsouza 19
  • feat: embed, io/fs support

    feat: embed, io/fs support

    Is your feature request related to a problem? Please describe.

    The next Go 1.16 will add official file system interface and file embedding.

    https://github.com/golang/go/issues/41190 https://github.com/golang/go/tree/master/src/io/fs

    https://github.com/golang/go/issues/41191 https://github.com/golang/go/tree/master/src/embed

    It would be great if golang-migrate supports these as source.Driver.

    It replaces statik, go-bindata, and pkger. Of course, this does not mean that they cannot be used.

    Describe the solution you'd like

    Describe alternatives you've considered

    Additional context

    We will also need to use the build tag properly.

    opened by johejo 17
  • fix example of install by go toolchain at README

    fix example of install by go toolchain at README

    Currently, result of install by go toolchain as follows

    > go get -tags 'postgres' -u github.com/golang-migrate/migrate/cmd/migrate
    go: finding github.com/golang-migrate/migrate/cmd/migrate latest
    go: finding github.com/golang-migrate/migrate/cmd latest
    go get github.com/golang-migrate/migrate/cmd/migrate: no matching versions for query "latest"
    

    So, I fixed Installation->With Go toolchain.

    opened by stobita 17
  • Dirty database version 1. Fix and force version

    Dirty database version 1. Fix and force version

    the error appears as below when migrate timeout:

    Dirty database version 1. Fix and force version
    

    Steps to Reproduce Steps to reproduce the behavior:

    1. do migrate timeout
    2. migrate again

    Expected Behavior migrate again could automatically solve this problem and do migrate normally.

    wontfix 
    opened by duyanghao 15
  • adding /v3/ to urls is a breaking change when building.

    adding /v3/ to urls is a breaking change when building.

    When building the project there is no such place as

    ```https://github.com/golang-migrate/migrate/v3/``

    so the build cannot import its dependencies and the build fails.

    Steps to Reproduce

    Build the project as below

    go build -tags 'postgres' -o ~/go/bin/migrate github.com/golang-migrate/migrate/cli

    Expected Behavior I expect the binary to be built and deployed to $GOPATH/bin/migrate

    Migrate Version v3.5.2

    opened by hayres 15
  • postgres: Move lock out of ensureVersionTable, for consistency with other SQL operations

    postgres: Move lock out of ensureVersionTable, for consistency with other SQL operations

    Fixes #164 .

    ensureVersionTable seems to be the only SQL operation in the postgres driver which handles locking of database itself, meaning it seems to break the convention of having the caller responsible for locking and not the callee.

    This removes the locking logic from ensureVersionTable and makes sure to properly lock the database before calling ensureVersionTable from the WithInstance method.

    opened by lukaspj 15
  • Postgres - add x-migrations-table-quoted url query option (#95)

    Postgres - add x-migrations-table-quoted url query option (#95)

    Following this comment, this is a new Pull Request.

    By default, gomigrate quote migrations table name, if x-migrations-table-quoted is enabled, then you must to quote migrations table name manually, for instance "gomigrate"."schema_migrations".

    I have used this playground to test this branch.

    opened by stephane-klein 14
  • mysql database registered twice

    mysql database registered twice

    Step to reproduce Execute the sample code of this page: https://github.com/golang-migrate/migrate/tree/master/database/mysql

    Output It gives the following error:

    panic: sql: Register called twice for driver mysql
    
    goroutine 1 [running]:
    database/sql.Register(0x127dc81, 0x5, 0x12a7a20, 0x13d2508)
    	/usr/local/Cellar/go/1.10.3/libexec/src/database/sql/sql.go:50 +0x1a4
    github.com/golang-migrate/migrate/vendor/github.com/go-sql-driver/mysql.init.0()
    	/Users/martin/go/src/github.com/golang-migrate/migrate/vendor/github.com/go-sql-driver/mysql/driver.go:168 +0x5c
    exit status 2
    

    Root cause The migrate/database/mysql package uses github.com/go-sql-driver/mysql which already registers the "mysql" driver. So when migrate/database/mysql tries to register it as well, it creates this error.

    opened by mdestagnol 13
  • Packr Box as migration source.

    Packr Box as migration source.

    Is your feature request related to a problem? Please describe. go-bindata has been left unmaintained, unwillingly or otherwise. See https://github.com/jteeuwen/go-bindata/issues/5

    This presents a problem as we're using go-bindata to embed our migration files into our Go binaries. The next best alternative is https://github.com/gobuffalo/packr

    Describe the solution you'd like Migration source support for packr boxes.

    Describe alternatives you've considered N/A

    Additional context N/A

    opened by chakrit 13
  •  undefined: iofs.PartialDriver error on mysql tag

    undefined: iofs.PartialDriver error on mysql tag

    Describe the Bug I am getting an error when I try to get mysql tag via go get.

    Steps to Reproduce Steps to reproduce the behavior:

    1. I have this command in my Dockerfile RUN go get -tags 'mysql' -u github.com/golang-migrate/migrate/cmd/migrate
    2. When I build it, see the error

    Go Version go version go1.13.15 linux/amd64

    Stacktrace

    > [8/8] RUN go get -tags 'mysql' -u github.com/golang-migrate/migrate/cmd/migrate:                                                                                                                                                     
    #12 6.997 # github.com/golang-migrate/migrate/source/file
    #12 6.997 ../../golang-migrate/migrate/source/file/file.go:17:2: undefined: iofs.PartialDriver
    
    opened by cemezgin 2
  • Split a single migration into multiple files

    Split a single migration into multiple files

    Is your feature request related to a problem? Please describe. Some of our migration files are very large. (E.g. The initial up migration file, defining most of the schema, significant refactors of the data model, collapsing of migrations, etc.)

    Describe the solution you'd like The possibility to split a single migration into multiple ordered files (whilst keeping all files as a single migration version). Essentially this would require modifying the file name format from:

    {version}_{title}.up.{extension}
    {version}_{title}.down.{extension}
    

    to:

    {version}_{part}_{title}.up.{extension}
    {version}_{part}_{title}.down.{extension}
    

    Where part is an optional incrementing integer for its migration version. This would result in something like:

    0001_01_users.up.sql
    0001_01_users.down.sql
    0001_02_base.up.sql
    0001_02_views.down.sql
    0002_triggers.up.sql
    0002_triggers.down.sql
    
    

    From a clean database, running all up migration would run the files in the following orders:

    1. 0001_01_users.up.sql
    2. 0001_02_views.up.sql
    3. 0002_triggers.up.sql

    Note that:

    • It should not be possible to run some of the files for a migration versions. All of them must be run together. (Otherwise, it would just be separate migrations...)
    • Splitting the code across file obviously implies separate transactions (when applicable).
    • If a version specifies parts for up or down, then all files for that migration must specify part in order to have a deterministic order for the files. (I.e. you cannot have 0001_01_users.up.sql and 0001_views.up.sql.)
    • Part numbers can be skipped in one direction with respect to the other direction, similar to migration versions.
    • By keeping file parts optional, existing migration files will continue to work as expected, thus maintaining backwards-compatibility.

    Describe alternatives you've considered

    1. Simply split the large migration into multiple migrations.
      This does not work for me, as there is a risk to leave the DB in an inconsistent state due to user error, as it is unclear which migrations should always be applied in a group.
    2. Use an external tool to generate a single migration file from multiple files (either by simply concatenating multiple files or using templates).
      This does not work either. Besides the overhead of build (and remembering to build) the migration files, this introduces a lot of complexity and undermines the consistency of the migration files.

    Additional context Splitting large migrations into smaller files might mitigate #300.

    opened by 0xjac 0
  • CLI Install instructions are incorrect for Go 1.16+

    CLI Install instructions are incorrect for Go 1.16+

    Describe the Bug The CLI instruction list this step for go 1.16+ https://github.com/golang-migrate/migrate/tree/master/cmd/migrate go install -tags 'postgres' -o $GOPATH/bin/migrate github.com/golang-migrate/migrate/v4/cmd/[email protected]$TAG However the go install command has no -o option.

    Also the initial go get command does not work

    [email protected] bin % go get -u -d github.com/golang-migrate/migrate/cmd/migrate
    go get: module github.com/golang-migrate/[email protected] found (v3.5.4+incompatible), but does not contain package github.com/golang-migrate/migrate/cmd/migrate
    
    

    Steps to Reproduce Steps to reproduce the behavior:

    1. My migrations look like '...'
    2. I ran migrate with the following options '....'
    3. See error

    Expected Behavior A clear and concise description of what you expected to happen.

    Migrate Version e.g. v3.4.0 Obtained by running: migrate -version

    Loaded Source Drivers e.g. s3, github, go-bindata, gcs, file Obtained by running: migrate -help

    Loaded Database Drivers e.g. spanner, stub, clickhouse, cockroachdb, crdb-postgres, postgres, postgresql, pgx, redshift, cassandra, cockroach, mysql Obtained by running: migrate -help

    Go Version e.g. go version go1.11 linux/amd64 Obtained by running: go version

    Stacktrace Please provide if available

    Additional context Add any other context about the problem here.

    opened by aastein 1
  • Add

    Add "always up" capability

    What

    This adds detection of the file pattern *.alwaysup.sql and *.alwaysdown.sql. These files will be executed every time the migration tool is used.

    Why

    This is useful for migrations that setup items like stored procedures, custom data types, and other persistent objects. Here is a sample use case.

    A developer writes a stored procedure and needs to alter its body a month later. Under normal use, the developer would need to write a new migration file to have the changes applied. This makes it difficult for other developers to review what has changed or see the overall history behind the procedure.

    Using the alwaysup filename, the procedure can be developed within the same file allowing for version control systems to do diffs during PR reviews, show the history of the file, etc.

    opened by sprak3000 4
  • Export schema following each action

    Export schema following each action

    Is your feature request related to a problem? Please describe. It would be nice if after each migration action, the tool exported the current schema to a file. This would allow the impact of migrations on a schema to be easily tracked and reviewed in version control

    Describe the solution you'd like An option called --export-schema-path=path/to/schema.sql for each command that executes a migration. When this option is included and has a non zero value, the tool will execute a command against the SQL server to get the current schema of the database. In postgres the pg_dump -s databasename command can be used.

    Describe alternatives you've considered A half-measure that can be used is to duplicate all migration commands in a Makefile which executes a command to export a schema after the golang-migrate commands complete.

    Additional context Add any other context or screenshots about the feature request here.

    opened by aastein 0
  • Getting

    Getting "Cannot achieve consistency level ALL" error

    Describe the Bug I'm attempting to run migrate to populate a database I have, but when I run the command it comes back with the error: error: Cannot achieve consistency level ALL in line 0: SELECT version, dirty FROM "schema_migrations" LIMIT 1

    I've found a number of different posts related to this error message but they all seem to be related to TRUNCATE commands. I'm wondering why this might be happening and if there is a workaround? My keyspace definition is as follows: CREATE KEYSPACE cs_workflow_v2 WITH replication = {'class': 'NetworkTopologyStrategy', 'beta-cassandra-01': '3'} AND durable_writes = true; Is it possible to migrate without changing to SimpleStrategy with 1 node?

    Steps to Reproduce Steps to reproduce the behavior:

    1. My migrations look like 'migrate -database cassandra://{some ip....}:9042/cs_workflow_v2 -path {some path...} -verbose up'
    2. See error: error: Cannot achieve consistency level ALL in line 0: SELECT version, dirty FROM "schema_migrations" LIMIT 1

    Expected Behavior The data would migrate without errors

    Migrate Version v4.14.1

    Loaded Source Drivers file, bitbucket, github, gitlab, go-bindata, godoc-vfs, gcs, s3, github-ee

    Loaded Database Drivers cassandra, cockroachdb, firebird, neo4j, postgres, postgresql, redshift, crdb-postgres, firebirdsql, mongodb, mongodb+srv, spanner, sqlserver, stub, clickhouse, cockroach, mysql

    Go Version go version go1.16.5 darwin/amd64

    opened by Broham 1
  • Better/more explicit error message for trying to run a migration with none defined.

    Better/more explicit error message for trying to run a migration with none defined.

    Is your feature request related to a problem? Please describe. I'm converting one of my projects from using rambler externally to using this as a library, and I was going nuts trying to figure out why the heck I was getting an error of first .: file does not exist -- it turns out my .sql files were named incorrectly, I used 00-foo 01-bar instead of 00_foo and 01_bar

    Describe the solution you'd like It'd be nice if there was a more clear and explicit message for when no migrations are found at all. I'm using the iofs driver on master now, but I also tried the httpfs driver to do it by way of http.FS but that's all I ended up getting

    Describe alternatives you've considered Not sure what to put here. It took me code splunking to find https://github.com/golang-migrate/migrate/blob/master/source/parse.go#L21 to notice what was wrong finally.

    Additional context Add any other context or screenshots about the feature request here.

    opened by AmandaCameron 1
  • pq: unterminated dollar-quoted string at or near

    pq: unterminated dollar-quoted string at or near "$BODY$

    Seems like x-multi-statement=true is causing migrate to fail with error: migration failed: unterminated dollar-quoted string at or near "$BODY$

    Steps to Reproduce

    1. My migrations look like
    CREATE OR REPLACE FUNCTION add_column(target REGCLASS)
      RETURNS VOID AS $BODY$
    DECLARE
    table_name TEXT := QUOTE_IDENT(target :: TEXT);
    BEGIN
    EXECUTE 'ALTER TABLE ' || table_name ||
            ' ADD COLUMN IF NOT EXISTS col_id INT4 NOT NULL';
    END;
    $BODY$
    LANGUAGE plpgsql;
    
    1. I ran migrate with the following options, please note x-multi-statement=true:
    docker run -v /tmp/migrations:/migrations --network host -i 9df23b0b252d -path /migrations/ -database 'postgres://postgres:@localhost:5433/test?sslmode=disable&x-multi-statement=true' up
    

    9df23b0b252d is an image id built at commit https://github.com/golang-migrate/migrate/commit/3dfb0ff538aaebd744c6b8145ee7581f06c7a9ed

    1. See error error: migration failed: unterminated dollar-quoted string at or near "$BODY$ image

    Migrate Version 4.14.1-43-g3dfb0ff, running it as docker container built at commit https://github.com/golang-migrate/migrate/commit/3dfb0ff538aaebd744c6b8145ee7581f06c7a9ed

    Loaded Source Drivers godoc-vfs, gcs, file, s3, github, github-ee, gitlab, go-bindata, bitbucket

    Loaded Database Drivers postgres, clickhouse, cockroachdb, firebird, mongodb, mongodb+srv, sqlserver, postgresql, stub, crdb-postgres, mysql, neo4j, pgx, redshift, spanner, cassandra, cockroach, firebirdsql

    In case if I run the migration without x-multi-statement=true it works fine: image

    Also I can create that function via psql without any issue: image

    Attachments: migrations.zip

    opened by REASY 0
  • ClickHouse insert statement error

    ClickHouse insert statement error

    I've got an error: insert statement supported only in the batch mode (use begin/commit) But ClickHouse doesn't support transaction

            CREATE TABLE IF NOT EXISTS owner_type (
            `id` String,
            `name` String
            )
            ENGINE = MergeTree()
            ORDER BY (id)
    
            INSERT INTO owner_type VALUES
            ('CUSTOMER', 'c'),
            ('ORGANIZATION', 'o')
    
    opened by divanovGH 1
  • Changing TRUNCATE for a DELETE in the SetVersion Method

    Changing TRUNCATE for a DELETE in the SetVersion Method

    Since MySQL does not support transactions for DDLs, I have changed the TRUNCATE statement for a DELETE FROM, which is transactional.

    The details are explained in issue #584

    opened by martinarrieta 1
Releases(v4.15.0-beta.1)
A Go package to help write migrations with go-pg/pg.

go-pg-migrations A Go package to help write migrations with go-pg/pg. Usage Installation Because go-pg now has Go modules support, go-pg-migrations al

Robin Joseph 80 Aug 30, 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. 1.9k Sep 26, 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 116 Jul 26, 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.2k Sep 23, 2021
Database migrations. CLI and Golang library.

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

null 27 May 30, 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 30 Sep 7, 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 674 Sep 23, 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 6 Jul 27, 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 121 Aug 29, 2021
Schema management CLI for MySQL

Skeema is a tool for managing MySQL tables and schema changes in a declarative fashion using pure SQL. It provides a CLI tool allowing you to: Export

Skeema 934 Sep 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 7, 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 10 Sep 20, 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 24 Mar 22, 2019
mini tools handling migrasion database from cli

mini tools handling migrasion database from cli

null 0 Sep 20, 2021
Apache AVRO for go

avro The purpose of this package is to facilitate use of AVRO with go strong typing. Features github.com/khezen/avro Marshal/Unmarshal AVRO schema git

Guillaume Simonneau 25 Aug 2, 2021