migrations

README

SQL migrations for Golang and PostgreSQL

This package allows you to run migrations on your PostgreSQL database using Golang Postgres client. See example for details.

You may also want to check go-pg-migrations before making a decision.

Installation

go-pg/migrations requires a Go version with Modules support and uses import path versioning. So please make sure to initialize a Go module:

go mod init github.com/my/repo
go get github.com/go-pg/migrations/v8

Usage

To run migrations on your project you should fulfill the following steps:

1. define the migration list;
2. implement an executable app that calls migration tool;
3. run migrations.

Define Migrations

Migration Files

You can save SQL migration files at the same directory as your main.go file, they should have proper file extensions (more about migration files).

Registered Migrations

Migrations can be registered in the code using migrations.RegisterTx and migrations.MustRegisterTx functions. More details about migration registering.

Implement app to run the tool

You can run migrations from any place of your app or ecosystem. It can be a standalone application of a part of a big program, or maybe an HTTP handler, etc. Check example for some helpful information about practical usage.

Run Migrations

Run migration tool by providing CLI arguments to the migrations.Run function.

Currently, the following arguments are supported:

• up - runs all available migrations;
• up [target] - runs available migrations up to the target one;
• down - reverts last migration;
• reset - reverts all migrations;
• version - prints current db version;
• set_version [version] - sets db version without running migrations.

Example

You need to create database pg_migrations_example before running the example.

> cd example

> psql -c "CREATE DATABASE pg_migrations_example"
CREATE DATABASE

> go run *.go init
version is 0

> go run *.go version
version is 0

> go run *.go
creating table my_table...
adding id column...
seeding my_table...
migrated from version 0 to 4

> go run *.go version
version is 4

> go run *.go reset
truncating my_table...
dropping id column...
dropping table my_table...
migrated from version 4 to 0

> go run *.go up 2
creating table my_table...
adding id column...
migrated from version 0 to 2

> go run *.go
seeding my_table...
migrated from version 2 to 4

> go run *.go down
truncating my_table...
migrated from version 4 to 3

> go run *.go version
version is 3

> go run *.go set_version 1
migrated from version 3 to 1

> go run *.go create add email to users
created migration 5_add_email_to_users.go

Registering Migrations

migrations.RegisterTx and migrations.MustRegisterTx

Registers migrations to be executed inside transactions.

migrations.Register and migrations.MustRegister

Registers migrations to be executed without any transaction.

SQL migrations

SQL migrations are automatically picked up if placed in the same folder with main.go or Go migrations. SQL migrations may be manually registered using DiscoverSQLMigrations (from OS directory) or DiscoverSQLMigrationsFromFilesystem. It may be used with new go 1.16 embedding feature. Example:

//go:embed migrations/*.sql
var migrations embed.FS

collection := migrations.NewCollection()
collection.DiscoverSQLMigrationsFromFilesystem(http.FS(migrations), "migrations")

SQL migrations must have one of the following extensions:

• .up.sql - up migration;
• .down.sql - down migration;
• .tx.up.sql - transactional up migration;
• .tx.down.sql - transactional down migration.

By default SQL migrations are executed as single PostgreSQL statement. --gopg:split directive can be used to split migration into several statements:

SET statement_timeout = 60000;
SET lock_timeout = 60000;

--gopg:split

CREATE INDEX CONCURRENTLY ...;

Transactions

By default, the migrations are executed outside without any transactions. Individual migrations can however be marked to be executed inside transactions by using the RegisterTx function instead of Register.

Global Transactions

var oldVersion, newVersion int64

err := db.RunInTransaction(func(tx *pg.Tx) (err error) {
    oldVersion, newVersion, err = migrations.Run(tx, flag.Args()...)
    return
})
if err != nil {
    exitf(err.Error())
}

Documentation

Index

• Variables
• func MustRegister(fns ...func(DB) error)
• func MustRegisterTx(fns ...func(DB) error)
• func Register(fns ...func(DB) error) error
• func RegisterTx(fns ...func(DB) error) error
• func Run(db DB, a ...string) (oldVersion, newVersion int64, err error)
• func SetTableName(name string)
• func SetVersion(db DB, version int64) error
• func Version(db DB) (int64, error)
• type Collection
  • func NewCollection(migrations ...*Migration) *Collection
  • func (c *Collection) DisableSQLAutodiscover(flag bool) *Collection
  • func (c *Collection) DiscoverSQLMigrations(dir string) error
  • func (c *Collection) DiscoverSQLMigrationsFromFilesystem(fs http.FileSystem, dir string) error
  • func (c *Collection) Migrations() []*Migration
  • func (c *Collection) MustRegister(fns ...func(DB) error)
  • func (c *Collection) MustRegisterTx(fns ...func(DB) error)
  • func (c *Collection) Register(fns ...func(DB) error) error
  • func (c *Collection) RegisterTx(fns ...func(DB) error) error
  • func (c *Collection) Run(db DB, a ...string) (oldVersion, newVersion int64, err error)
  • func (c *Collection) SetTableName(tableName string) *Collection
  • func (c *Collection) SetVersion(db DB, version int64) error
  • func (c *Collection) Version(db DB) (int64, error)
• type DB
• type Migration
  • func RegisteredMigrations() []*Migration
  • func (m *Migration) String() string

Variables

var DefaultCollection = NewCollection()

Functions

func MustRegister

func MustRegister(fns ...func(DB) error)

func MustRegisterTx

func MustRegisterTx(fns ...func(DB) error)

func Register

func Register(fns ...func(DB) error) error

Register registers new database migration. Must be called from file with name like "1_initialize_db.go", where:

• 1 - migration version;
• initialize_db - comment.

func RegisterTx

func RegisterTx(fns ...func(DB) error) error

RegisterTx is just like Register but marks the migration to be executed inside a transaction.

func Run

func Run(db DB, a ...string) (oldVersion, newVersion int64, err error)

Run runs command on the db. Supported commands are: - up [target] - runs all available migrations by default or up to target one if argument is provided. - down - reverts last migration. - reset - reverts all migrations. - version - prints current db version. - set_version - sets db version without running migrations.

func SetTableName

func SetTableName(name string)

func SetVersion

func SetVersion(db DB, version int64) error

func Version

func Version(db DB) (int64, error)

Types

type Collection

type Collection struct {
	// contains filtered or unexported fields
}

func NewCollection

func NewCollection(migrations ...*Migration) *Collection

func (*Collection) DisableSQLAutodiscover

func (c *Collection) DisableSQLAutodiscover(flag bool) *Collection

func (*Collection) DiscoverSQLMigrations

func (c *Collection) DiscoverSQLMigrations(dir string) error

DiscoverSQLMigrations scan the dir for files with .sql extension and adds discovered SQL migrations to the collection.

func (*Collection) DiscoverSQLMigrationsFromFilesystem

func (c *Collection) DiscoverSQLMigrationsFromFilesystem(fs http.FileSystem, dir string) error

DiscoverSQLMigrations scan the dir from the given filesystem for files with .sql extension and adds discovered SQL migrations to the collection.

func (*Collection) Migrations

func (c *Collection) Migrations() []*Migration

func (*Collection) MustRegister

func (c *Collection) MustRegister(fns ...func(DB) error)

func (*Collection) MustRegisterTx

func (c *Collection) MustRegisterTx(fns ...func(DB) error)

func (*Collection) Register

func (c *Collection) Register(fns ...func(DB) error) error

Register registers new database migration. Must be called from a file with name like "1_initialize_db.go".

func (*Collection) RegisterTx

func (c *Collection) RegisterTx(fns ...func(DB) error) error

RegisterTx is like Register, but migration will be run in a transaction.

func (*Collection) Run

func (c *Collection) Run(db DB, a ...string) (oldVersion, newVersion int64, err error)

func (*Collection) SetTableName

func (c *Collection) SetTableName(tableName string) *Collection

func (*Collection) SetVersion

func (c *Collection) SetVersion(db DB, version int64) error

func (*Collection) Version

func (c *Collection) Version(db DB) (int64, error)

type DB

type DB interface {
	Model(model ...interface{}) *orm.Query

	Exec(query interface{}, params ...interface{}) (orm.Result, error)
	ExecOne(query interface{}, params ...interface{}) (orm.Result, error)
	Query(coll, query interface{}, params ...interface{}) (orm.Result, error)
	QueryOne(model, query interface{}, params ...interface{}) (orm.Result, error)

	Begin() (*pg.Tx, error)

	CopyFrom(r io.Reader, query interface{}, params ...interface{}) (orm.Result, error)
	CopyTo(w io.Writer, query interface{}, params ...interface{}) (orm.Result, error)

	Context() context.Context
}

DB is a common interface for pg.DB and pg.Tx types.

type Migration

type Migration struct {
	Version int64

	UpTx bool
	Up   func(DB) error

	DownTx bool
	Down   func(DB) error
}

func RegisteredMigrations

func RegisteredMigrations() []*Migration

RegisteredMigrations returns currently registered Migrations.

func (*Migration) String

func (m *Migration) String() string