migrate

package module
v0.0.0-...-233afc8 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: May 28, 2024 License: MIT Imports: 16 Imported by: 3

README

sql-migrate

This is a fork of the sql-migrate SQL Schema migration tool for Go.

The motivation for this fork is to remove the go-gorp dependency, simplifying the codebase but making it work with the pgx db driver exclusively.

Build Status GoDoc

Features

  • Can embed migrations into your application
  • Migrations are defined with SQL for full flexibility
  • Atomic migrations
  • Up/down migrations to allow rollback

Installation

go get -v github.com/heroiclabs/nakama/sql-migrate/

Usage

Import sql-migrate into your application:

import "github.com/heroiclabs/nakama/sql-migrate"

Set up a source of migrations, this can be from memory, from a set of files, from bindata (more on that later), or from any library that implements http.FileSystem:

// Hardcoded strings in memory:
migrations := &migrate.MemoryMigrationSource{
    Migrations: []*migrate.Migration{
        &migrate.Migration{
            Id:   "123",
            Up:   []string{"CREATE TABLE people (id int)"},
            Down: []string{"DROP TABLE people"},
        },
    },
}

// OR: Read migrations from a folder:
migrations := &migrate.FileMigrationSource{
    Dir: "db/migrations",
}

// OR: Use migrations from a packr box
migrations := &migrate.PackrMigrationSource{
    Box: packr.New("migrations", "./migrations"),
}

// OR: Use pkger which implements `http.FileSystem`
migrationSource := &migrate.HttpFileSystemMigrationSource{
    FileSystem: pkger.Dir("/db/migrations"),
}

// OR: Use migrations from bindata:
migrations := &migrate.AssetMigrationSource{
    Asset:    Asset,
    AssetDir: AssetDir,
    Dir:      "migrations",
}

// OR: Read migrations from a `http.FileSystem`
migrationSource := &migrate.HttpFileSystemMigrationSource{
    FileSystem: httpFS,
}

Then use the Exec function to upgrade your database:

n, err := migrate.Exec(db, migrations, migrate.Up)
if err != nil {
    // Handle errors!
}
fmt.Printf("Applied %d migrations!\n", n)

Note that n can be greater than 0 even if there is an error: any migration that succeeded will remain applied even if a later one fails.

Writing migrations

Migrations are defined in SQL files, which contain a set of SQL statements. Special comments are used to distinguish up and down migrations.

-- +migrate Up
-- SQL in section 'Up' is executed when this migration is applied
CREATE TABLE people (id int);


-- +migrate Down
-- SQL section 'Down' is executed when this migration is rolled back
DROP TABLE people;

You can put multiple statements in each block, as long as you end them with a semicolon (;).

You can alternatively set up a separator string that matches an entire line by setting sqlparse.LineSeparator. This can be used to imitate, for example, MS SQL Query Analyzer functionality where commands can be separated by a line with contents of GO. If sqlparse.LineSeparator is matched, it will not be included in the resulting migration scripts.

If you have complex statements which contain semicolons, use StatementBegin and StatementEnd to indicate boundaries:

-- +migrate Up
CREATE TABLE people (id int);

-- +migrate StatementBegin
CREATE OR REPLACE FUNCTION do_something()
returns void AS $$
DECLARE
  create_query text;
BEGIN
  -- Do something here
END;
$$
language plpgsql;
-- +migrate StatementEnd

-- +migrate Down
DROP FUNCTION do_something();
DROP TABLE people;

The order in which migrations are applied is defined through the filename: sql-migrate will sort migrations based on their name. It's recommended to use an increasing version number or a timestamp as the first part of the filename.

Normally each migration is run within a transaction in order to guarantee that it is fully atomic. However some SQL commands (for example creating an index concurrently in PostgreSQL) cannot be executed inside a transaction. In order to execute such a command in a migration, the migration can be run using the notransaction option:

-- +migrate Up notransaction
CREATE UNIQUE INDEX CONCURRENTLY people_unique_id_idx ON people (id);

-- +migrate Down
DROP INDEX people_unique_id_idx;

Embedding migrations with libraries that implement http.FileSystem

You can also embed migrations with any library that implements http.FileSystem, like vfsgen, parcello, or go-resources.

migrationSource := &migrate.HttpFileSystemMigrationSource{
    FileSystem: httpFS,
}

Tests

Run the tests by first starting the Postgres docker container.

docker compose up
go test

Extending

Adding a new migration source means implementing MigrationSource.

type MigrationSource interface {
    FindMigrations() ([]*Migration, error)
}

The resulting slice of migrations will be executed in the given order, so it should usually be sorted by the Id field.

License

This library is distributed under the MIT license.

Documentation

Index

Constants

View Source 
const DefaultMigrationTableName = "migration_info"

Variables

This section is empty.

Functions

func Exec 

func Exec(ctx context.Context, db *pgx.Conn, m MigrationSource, dir MigrationDirection) (int, error)

Execute a set of migrations

Returns the number of applied migrations.

func ExecMax 

func ExecMax(ctx context.Context, db *pgx.Conn, m MigrationSource, dir MigrationDirection, max int) (int, error)

Execute a set of migrations

Will apply at most `max` migrations. Pass 0 for no limit (or use Exec).

Returns the number of applied migrations.

func ExecVersion 

func ExecVersion(ctx context.Context, db *pgx.Conn, m MigrationSource, dir MigrationDirection, version int64) (int, error)

Execute a set of migrations

Will apply at the target `version` of migration. Cannot be a negative value.

Returns the number of applied migrations.

func SetDisableCreateTable 

func SetDisableCreateTable(disable bool)

SetDisableCreateTable sets the boolean to disable the creation of the migration table

func SetIgnoreUnknown 

func SetIgnoreUnknown(v bool)

SetIgnoreUnknown sets the flag that skips database check to see if there is a migration in the database that is not in migration source.

This should be used sparingly as it is removing a safety check.

func SetTable 

func SetTable(name string)

Set the name of the table used to store migration info.

Should be called before any other call such as (Exec, ExecMax, ...).

Types

type AssetMigrationSource 

type AssetMigrationSource struct {
	// Asset should return content of file in path if exists
	Asset func(path string) ([]byte, error)

	// AssetDir should return list of files in the path
	AssetDir func(path string) ([]string, error)

	// Path in the bindata to use.
	Dir string
}

Migrations from a bindata asset set.

func (AssetMigrationSource) FindMigrations 

func (a AssetMigrationSource) FindMigrations() ([]*Migration, error)

type EmbedFileSystemMigrationSource 

type EmbedFileSystemMigrationSource struct {
	FileSystem embed.FS

	Root string
}

func (EmbedFileSystemMigrationSource) FindMigrations 

func (f EmbedFileSystemMigrationSource) FindMigrations() ([]*Migration, error)

type FileMigrationSource 

type FileMigrationSource struct {
	Dir string
}

A set of migrations loaded from a directory.

func (FileMigrationSource) FindMigrations 

func (f FileMigrationSource) FindMigrations() ([]*Migration, error)

type HttpFileSystemMigrationSource 

type HttpFileSystemMigrationSource struct {
	FileSystem http.FileSystem
}

func (HttpFileSystemMigrationSource) FindMigrations 

func (f HttpFileSystemMigrationSource) FindMigrations() ([]*Migration, error)

type MemoryMigrationSource 

type MemoryMigrationSource struct {
	Migrations []*Migration
}

A hardcoded set of migrations, in-memory.

func (MemoryMigrationSource) FindMigrations 

func (m MemoryMigrationSource) FindMigrations() ([]*Migration, error)

type Migration 

type Migration struct {
	Id   string
	Up   []string
	Down []string

	DisableTransactionUp   bool
	DisableTransactionDown bool
}

func ParseMigration 

func ParseMigration(id string, r io.ReadSeeker) (*Migration, error)

Migration parsing

func ToApply 

func ToApply(migrations []*Migration, current string, direction MigrationDirection) []*Migration

Filter a slice of migrations into ones that should be applied.

func (Migration) Less 

func (m Migration) Less(other *Migration) bool

func (Migration) NumberPrefixMatches 

func (m Migration) NumberPrefixMatches() []string

func (Migration) VersionInt 

func (m Migration) VersionInt() int64

type MigrationDirection 

type MigrationDirection int
const (
	Up MigrationDirection = iota
	Down
)

type MigrationRecord 

type MigrationRecord struct {
	Id        string    `db:"id"`
	AppliedAt time.Time `db:"applied_at"`
}

func GetMigrationRecords 

func GetMigrationRecords(ctx context.Context, db *pgx.Conn) ([]*MigrationRecord, error)

type MigrationSet 

type MigrationSet struct {
	// TableName name of the table used to store migration info.
	TableName string
	// IgnoreUnknown skips the check to see if there is a migration
	// ran in the database that is not in MigrationSource.
	//
	// This should be used sparingly as it is removing a safety check.
	IgnoreUnknown bool
	// DisableCreateTable disable the creation of the migration table
	DisableCreateTable bool
}

MigrationSet provides database parameters for a migration execution

func (MigrationSet) Exec 

func (ms MigrationSet) Exec(ctx context.Context, db *pgx.Conn, m MigrationSource, dir MigrationDirection) (int, error)

Returns the number of applied migrations.

func (MigrationSet) ExecMax 

func (ms MigrationSet) ExecMax(ctx context.Context, db *pgx.Conn, m MigrationSource, dir MigrationDirection, max int) (int, error)

Returns the number of applied migrations.

func (MigrationSet) ExecVersion 

func (ms MigrationSet) ExecVersion(ctx context.Context, db *pgx.Conn, m MigrationSource, dir MigrationDirection, version int64) (int, error)

Returns the number of applied migrations.

func (MigrationSet) GetMigrationRecords 

func (ms MigrationSet) GetMigrationRecords(ctx context.Context, db *pgx.Conn) ([]*MigrationRecord, error)

func (MigrationSet) PlanMigration 

func (ms MigrationSet) PlanMigration(ctx context.Context, db *pgx.Conn, m MigrationSource, dir MigrationDirection, max int) ([]*PlannedMigration, error)

Plan a migration.

func (MigrationSet) PlanMigrationToVersion 

func (ms MigrationSet) PlanMigrationToVersion(ctx context.Context, db *pgx.Conn, m MigrationSource, dir MigrationDirection, version int64) ([]*PlannedMigration, error)

Plan a migration to version.

type MigrationSource 

type MigrationSource interface {
	// Finds the migrations.
	//
	// The resulting slice of migrations should be sorted by Id.
	FindMigrations() ([]*Migration, error)
}

type PackrBox 

type PackrBox interface {
	List() []string
	Find(name string) ([]byte, error)
}

Avoids pulling in the packr library for everyone, mimicks the bits of packr.Box that we need.

type PlanError 

type PlanError struct {
	Migration    *Migration
	ErrorMessage string
}

PlanError happens where no migration plan could be created between the sets of already applied migrations and the currently found. For example, when the database contains a migration which is not among the migrations list found for an operation.

func (*PlanError) Error 

func (p *PlanError) Error() string

type PlannedMigration 

type PlannedMigration struct {
	*Migration

	DisableTransaction bool
	Queries            []string
}

func PlanMigration 

func PlanMigration(ctx context.Context, db *pgx.Conn, m MigrationSource, dir MigrationDirection, max int) ([]*PlannedMigration, error)

Plan a migration.

func PlanMigrationToVersion 

func PlanMigrationToVersion(ctx context.Context, db *pgx.Conn, m MigrationSource, dir MigrationDirection, version int64) ([]*PlannedMigration, error)

Plan a migration to version.

func ToCatchup 

func ToCatchup(migrations, existingMigrations []*Migration, lastRun *Migration) []*PlannedMigration

type TxError 

type TxError struct {
	Migration *Migration
	Err       error
}

TxError is returned when any error is encountered during a database transaction. It contains the relevant *Migration and notes it's Id in the Error function output.

func (*TxError) Error 

func (e *TxError) Error() string

Source Files

View all Source files

Directories

Path Synopsis

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.