sqlxmigrate

README

SqlxMigrate

sqlxmigrate is a minimalistic database schema migration helper for sqlx.

This project was inspired by github.com/GuiaBolso/darwin as it provides simple approach for handling database schema. This package however only supports direct SQL statements for each migration and thus unable to handle complex migrations and does not support persisting which migrations have been executed.

The project gormigrate github.com/go-gormigrate/gormigrate provides schema migration for the Gorm ORM. While GORM has is place, there was an opportunity to replace GORM with the sqlx database package to provide a more flexible schema migration tool. This project was original forked from gormigrate and then Gorm was replaced with sqlx.

Supported databases

It supports any of the [databases sqlx supports]:

• PostgreSQL

Additional database support:

Need to determine a plan to abstract the schema logic currently defined in sqlxmigrate_test.go on a driver basis. The sql statement for creating a table in postgres, differs from MySql, etc.

Installing

go get -u github.com/geeks-accelerator/sqlxmigrate

Usage

package main

import (
	"database/sql"
	"log"

	"github.com/geeks-accelerator/sqlxmigrate"
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
)

func main() {
	// this Pings the database trying to connect, panics on error
	// use sqlx.Open() for sql.Open() semantics
	db, err := sqlx.Connect("postgres", "host=127.0.0.1 user=postgres dbname=sqlxmigrate_test port=5433 sslmode=disable password=postgres")
	if err != nil {
		log.Fatalf("main : Register DB : %v", err)
	}
	defer db.Close()

	m := sqlxmigrate.New(db, sqlxmigrate.DefaultOptions, []*sqlxmigrate.Migration{
		// create persons table
		{
			ID: "201608301400",
			Migrate: func(tx *sql.Tx) error {
				q := `CREATE TABLE "people" (
						"id" serial,
						"created_at" timestamp with time zone,
						"updated_at" timestamp with time zone,
						"deleted_at" timestamp with time zone,
						"name" text , 
						PRIMARY KEY ("id")
					)`
				_, err = tx.Exec(q)
				return err
			},
			Rollback: func(tx *sql.Tx) error {
				q := `DROP TABLE IF EXISTS people`
				_, err = tx.Exec(q)
				return err
			},
		},
		// add age column to persons
		{
			ID: "201608301415",
			Migrate: func(tx *sql.Tx) error {
				q := `ALTER TABLE people 
						ADD column age int 
					`
				_, err = tx.Exec(q)
				return err
			},
			Rollback: func(tx *sql.Tx) error {
				q := `ALTER TABLE people 
						DROP column age`
				_, err = tx.Exec(q)
				return err
			},
		},
	})

	if err = m.Migrate(); err != nil {
		log.Fatalf("Could not migrate: %v", err)
	}
	log.Printf("Migration did run successfully")
}

Having a separated function for initializing the schema

If you have a lot of migrations, it can be a pain to run all them, as example, when you are deploying a new instance of the app, in a clean database. To prevent this, you can set a function that will run if no migration was run before (in a new clean database). Remember to create everything here, all tables, foreign keys and what more you need in your app.

m := sqlxmigrate.New(db, sqlxmigrate.DefaultOptions, []*sqlxmigrate.Migration{
    // you migrations here
})

m.InitSchema(func(*sqlx.DB) error {
    // seed initial tables
    return nil
})

Options

This is the options struct, in case you don't want the defaults:

type Options struct {
	// Migrations table name. Default to "migrations".
	TableName string
	// The name of the column that stores the ID of migrations. Defaults to "id".
	IDColumnName string
}

Contributing

To run tests, first copy .sample.env as sample.env and edit the connection string of the database you want to run tests against. Then, run tests like below:

# running tests for PostgreSQL
export PG_CONN_STRING="host=127.0.0.1 user=postgres dbname=sqlxmigrate_test port=5433 sslmode=disable password=postgres"
go test -tags postgresql

# running test for MySQL
go test -tags mysql

# running test for multiple databases at once
go test -tags 'postgresql mysql'

Or alternatively, you could use Docker to easily run tests on all databases at once. To do that, make sure Docker is installed and running in your machine and then run:

task docker

Or Manually execute docker

# Build the test container
docker build -t sqlxmigrate .

# Ensure previous containers have stopped
docker-compose down -v

# Run the tests:
docker-compose run sqlxmigrate go test -v -tags 'postgresql'

Documentation

Overview

Package sqlxmigrate is a migration helper for sqlx (https://github.com/jmoiron/sqlx/). Enables schema versioning and rollback cababilities.

Example:

package main

import (

"database/sql"
"log"

"github.com/geeks-accelerator/sqlxmigrate"
"github.com/jmoiron/sqlx"
_ "github.com/lib/pq"

)

func main() {
	// this Pings the database trying to connect, panics on error
	// use sqlx.Open() for sql.Open() semantics
	db, err := sqlx.Connect("postgres", "host=127.0.0.1 user=postgres dbname=sqlxmigrate_test port=5433 sslmode=disable password=postgres")
	if err != nil {
		log.Fatalf("main : Register DB : %v", err)
	}
	defer db.Close()

	m := sqlxmigrate.New(db, sqlxmigrate.DefaultOptions, []*sqlxmigrate.Migration{
		// create persons table
		{
			ID: "201608301400",
			Migrate: func(tx *sql.Tx) error {
				q := `CREATE TABLE "people" (
						"id" serial,
						"created_at" timestamp with time zone,
						"updated_at" timestamp with time zone,
						"deleted_at" timestamp with time zone,
						"name" text ,
						PRIMARY KEY ("id")
					)`
				_, err = tx.Exec(q)
				return err
			},
			Rollback: func(tx *sql.Tx) error {
				q := `DROP TABLE IF EXISTS people`
				_, err = tx.Exec(q)
				return err
			},
		},
		// add age column to persons
		{
			ID: "201608301415",
			Migrate: func(tx *sql.Tx) error {
				q := `ALTER TABLE people
						ADD column age int
					`
				_, err = tx.Exec(q)
				return err
			},
			Rollback: func(tx *sql.Tx) error {
				q := `ALTER TABLE people
						DROP column age`
				_, err = tx.Exec(q)
				return err
			},
		},
	})

	if err = m.Migrate(); err != nil {
		log.Fatalf("Could not migrate: %v", err)
	}
	log.Printf("Migration did run successfully")
}

Index

• Variables
• type DuplicatedIDError
  • func (e *DuplicatedIDError) Error() string
• type InitSchemaFunc
• type MigrateFunc
• type Migration
• type Options
• type ReservedIDError
  • func (e *ReservedIDError) Error() string
• type RollbackFunc
• type Sqlxmigrate
  • func New(db *sqlx.DB, options *Options, migrations []*Migration) *Sqlxmigrate
  • func (g *Sqlxmigrate) HasTable(tableName string) (bool, error)
  • func (g *Sqlxmigrate) InitSchema(initSchema InitSchemaFunc)
  • func (g *Sqlxmigrate) Migrate() error
  • func (g *Sqlxmigrate) MigrateTo(migrationID string) error
  • func (g *Sqlxmigrate) RollbackLast() error
  • func (g *Sqlxmigrate) RollbackMigration(m *Migration) error
  • func (g *Sqlxmigrate) RollbackTo(migrationID string) error
  • func (g *Sqlxmigrate) SetLogger(logger *log.Logger)

Variables

var (
	// DefaultOptions can be used if you don't want to think about options.
	DefaultOptions = &Options{
		TableName:    "migrations",
		IDColumnName: "id",
		IDColumnSize: 255,
	}

	// ErrRollbackImpossible is returned when trying to rollback a migration
	// that has no rollback function.
	ErrRollbackImpossible = errors.New("sqlxmigrate: It's impossible to rollback this migration")

	// ErrNoMigrationDefined is returned when no migration is defined.
	ErrNoMigrationDefined = errors.New("sqlxmigrate: No migration defined")

	// ErrMissingID is returned when the ID od migration is equal to ""
	ErrMissingID = errors.New("sqlxmigrate: Missing ID in migration")

	// ErrNoRunMigration is returned when any run migration was found while
	// running RollbackLast
	ErrNoRunMigration = errors.New("sqlxmigrate: Could not find last run migration")

	// ErrMigrationIDDoesNotExist is returned when migrating or rolling back to a migration ID that
	// does not exist in the list of migrations
	ErrMigrationIDDoesNotExist = errors.New("sqlxmigrate: Tried to migrate to an ID that doesn't exist")
)

Types

type DuplicatedIDError

type DuplicatedIDError struct {
	ID string
}

DuplicatedIDError is returned when more than one migration have the same ID

func (*DuplicatedIDError) Error

func (e *DuplicatedIDError) Error() string

type InitSchemaFunc

type InitSchemaFunc func(*sqlx.DB) error

InitSchemaFunc is the func signature for initializing the schema.

type MigrateFunc

type MigrateFunc func(*sql.Tx) error

MigrateFunc is the func signature for migrating.

type Migration

type Migration struct {
	// ID is the migration identifier. Usually a timestamp like "201601021504".
	ID string
	// Migrate is a function that will br executed while running this migration.
	Migrate MigrateFunc
	// Rollback will be executed on rollback. Can be nil.
	Rollback RollbackFunc
}

Migration represents a database migration (a modification to be made on the database).

type Options

type Options struct {
	// TableName is the migration table.
	TableName string
	// IDColumnName is the name of column where the migration id will be stored.
	IDColumnName string
	// IDColumnSize is the length of the migration id column
	IDColumnSize int
}

Options define options for all migrations.

type ReservedIDError

type ReservedIDError struct {
	ID string
}

ReservedIDError is returned when a migration is using a reserved ID

func (*ReservedIDError) Error

func (e *ReservedIDError) Error() string

type RollbackFunc

type RollbackFunc func(*sql.Tx) error

RollbackFunc is the func signature for rollbacking.

type Sqlxmigrate

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

Sqlxmigrate represents a collection of all migrations of a database schema.

func New

func New(db *sqlx.DB, options *Options, migrations []*Migration) *Sqlxmigrate

New returns a new Sqlxmigrate.

func (*Sqlxmigrate) HasTable

func (g *Sqlxmigrate) HasTable(tableName string) (bool, error)

func (*Sqlxmigrate) InitSchema

func (g *Sqlxmigrate) InitSchema(initSchema InitSchemaFunc)

InitSchema sets a function that is run if no migration is found. The idea is preventing to run all migrations when a new clean database is being migrating. In this function you should create all tables and foreign key necessary to your application.

func (*Sqlxmigrate) Migrate

func (g *Sqlxmigrate) Migrate() error

Migrate executes all migrations that did not run yet.

func (*Sqlxmigrate) MigrateTo

func (g *Sqlxmigrate) MigrateTo(migrationID string) error

MigrateTo executes all migrations that did not run yet up to the migration that matches `migrationID`.

func (*Sqlxmigrate) RollbackLast

func (g *Sqlxmigrate) RollbackLast() error

RollbackLast undo the last migration

func (*Sqlxmigrate) RollbackMigration

func (g *Sqlxmigrate) RollbackMigration(m *Migration) error

RollbackMigration undo a migration.

func (*Sqlxmigrate) RollbackTo

func (g *Sqlxmigrate) RollbackTo(migrationID string) error

RollbackTo undoes migrations up to the given migration that matches the `migrationID`. Migration with the matching `migrationID` is not rolled back.

func (*Sqlxmigrate) SetLogger

func (g *Sqlxmigrate) SetLogger(logger *log.Logger)

SetLogger allows the default logger to be overwritten