migrate

README

norm/migrate

Migrations can be provided as:

• A set of files in a fs.FS (which can be embedded) matching the (\d+)_(\w+)\.(apply|discard)\.sql naming pattern using migrate.NewWithFiles()
• A []*migrate.Migration using using migrate.New()

By default migrations can have multiple SQL statements and are run within database transactions. Transactions can be disabled, limiting each migration to single SQL statement.

Using migration files

Each migration file can contain multiple SQL statements and each individual statement must be terminated with ;.

To disable transactions for a given migration annotate the migration file with the following SQL comment -- migrate: NoTransaction.

Example

Migration files structure

-- versions
	|-- 0001_orders_table.apply.sql
	|-- 0001_orders_table.discard.sql
	|-- 0002_users_table.apply.sql
	|-- 0002_users_table.discard.sql
	|-- 0003_payments_table.apply.sql
	|-- 0003_payments_table.discard.sql

Migration code

	ctx := context.Background()

	db, err := sql.Open("pgx", "postgres://user:pass@postgres:5432/db")
	if err != nil {
		// handle err
	}

	// use a local dir with migrations, could also be a embed.FS
	//go:embed versions/*
	// var versions embed.FS
	m, err := migrate.NewWithFiles(db, log.Printf, os.DirFS("./versions"))
	if err != nil {
		// handle err
	}

	// migrate all the way up
	err = m.Up(ctx)
	if err != nil {
		// handle err
	}

	// get current version
	v, err := m.Version(ctx)
	if err != nil {
		// handle err
	}

	fmt.Println(v.Version)


	// migrate forward or backward to a specific version
	err = m.Apply(ctx, 2)
	if err != nil {
		panic(err)
	}

	// migrate all the way down and remove migration history
	err = m.Down(ctx)
	if err != nil {
		panic(err)
	}

Using migration structs

Each migration struct can contain multiple SQL statements and each individual statement must NOT be terminated with ;.

To disable transactions for a given migration, set the migrate.Migration.NoTx to true.

Example

Migration structs

var versions = []*Migration{
	{
		Version: 1,
		Name:    "users_table",
		Apply: Statements{
			NoTx:       true,
			Statements: []string{"CREATE TABLE IF NOT EXISTS users(id text, name text, email text, role text, PRIMARY KEY (id))"},
		},
		Discard: Statements{
			Statements: []string{"DROP TABLE IF EXISTS users CASCADE"},
		},
	},
	{
		Version: 2,
		Name:    "users_email_index",
		Apply: Statements{
			Statements: []string{"CREATE INDEX IF NOT EXISTS ix_users_email ON users (email)"},
		},
		Discard: Statements{
			Statements: []string{"DROP INDEX IF EXISTS ix_users_email CASCADE"},
		},
	},
	{
		Version: 3,
		Name:    "roles_table",
		Apply: Statements{
			Statements: []string{"CREATE TABLE IF NOT EXISTS roles(id text, name text, properties jsonb NOT NULL DEFAULT '{}'::jsonb, PRIMARY KEY (id))"},
		},
		Discard: Statements{
			Statements: []string{"DROP TABLE IF EXISTS roles CASCADE"},
		},
	},
	{
		Version: 4,
		Name:    "user_roles_fk",
		Apply: Statements{
			Statements: []string{"ALTER TABLE users ADD CONSTRAINT roles_fk FOREIGN KEY (role) REFERENCES roles (id)"},
		},
		Discard: Statements{
			Statements: []string{"ALTER TABLE users DROP CONSTRAINT roles_fk CASCADE"},
		},
	},
}

Migration code

	ctx := context.Background()

	db, err := sql.Open("pgx", "postgres://user:pass@postgres:5432/db")
	if err != nil {
		// handle err
	}

	// use a local dir with migrations, could also be a embed.FS
	//go:embed versions/*
	// var versions embed.FS
	m, err := migrate.New(db, log.Printf, versions)
	if err != nil {
		// handle err
	}

	// migrate all the way up
	err = m.Up(ctx)
	if err != nil {
		// handle err
	}

	// get current version
	v, err := m.Version(ctx)
	if err != nil {
		// handle err
	}

	fmt.Println(v.Version)


	// migrate forward or backward to a specific version
	err = m.Apply(ctx, 2)
	if err != nil {
		panic(err)
	}

	// migrate all the way down and remove migration history
	err = m.Down(ctx)
	if err != nil {
		panic(err)
	}

Documentation

Index

• Variables
• type Executor
• type Logger
• type Migrate
  • func New(db *sql.DB, logger Logger, migrations []*Migration) (m *Migrate, err error)
  • func NewWithFiles(db *sql.DB, logger Logger, files fs.FS) (m *Migrate, err error)
  • func (m *Migrate) Apply(ctx context.Context, version int64) (err error)
  • func (m *Migrate) Down(ctx context.Context) (err error)
  • func (m *Migrate) Up(ctx context.Context) (err error)
  • func (m *Migrate) Version(ctx context.Context) (version *Version, err error)
  • func (m *Migrate) Versions() (versions []Version)
• type Migration
• type Statements
• type Version

Variables

var (
	ErrInvalidNoTx = fmt.Errorf("migrate: migrations that disable transactions must have only one statement")
)

var (
	// StdLog is the log.Printf function from the standard library
	StdLog = log.Printf
)

Types

type Executor

type Executor interface {
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
}

Executor executes statements in a database

type Logger

type Logger func(s string, args ...interface{})

Logger function signature

type Migrate

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

Migrate manages database migrations

func New

func New(db *sql.DB, logger Logger, migrations []*Migration) (m *Migrate, err error)

New creates a new Migrate with the given database and versions.

If the provided logger function is not `nil` additional information will be logged during the migrations apply or discard.

func NewWithFiles

func NewWithFiles(db *sql.DB, logger Logger, files fs.FS) (m *Migrate, err error)

NewWithFiles is like new but takes a fs.Fs as a source for migration files. Only files within the 1st level of the provided path matching the `(\d+)_(\w+)\.(apply|discard)\.sql` pattern will be added to the Migrate catalog.

func (*Migrate) Apply

func (m *Migrate) Apply(ctx context.Context, version int64) (err error)

Apply either rolls forward or backwards the migrations to the specified version

func (*Migrate) Down

func (m *Migrate) Down(ctx context.Context) (err error)

Down discards all existing database migrations and migration history

func (*Migrate) Up

func (m *Migrate) Up(ctx context.Context) (err error)

Up apply all existing migrations to the database

func (*Migrate) Version

func (m *Migrate) Version(ctx context.Context) (version *Version, err error)

Version returns the current database migration version. If the database migrations are not initialized version is -1.

func (*Migrate) Versions

func (m *Migrate) Versions() (versions []Version)

Versions return the list of migration versions available to this migrate instance.

type Migration

type Migration struct {
	Version int64
	Name    string
	Apply   Statements
	Discard Statements
}

Migration represents a database migration apply and discard statements

type Statements

type Statements struct {
	NoTx       bool
	Statements []string
}

Statements are set of SQL statements that either apply or discard a migration

type Version

type Version struct {
	Version int64
	Date    time.Time
	Name    string
}

Version represents a migration version and its metadata