migration

README

DB Migration

DB Migration is a database migration engine, designed to allow developers to write and execute migration scripts for various SQL database systems.

Features

• Supports SQLite, PostgreSQL &MySQL databases
• Supports both upgrading and downgrading databases
• Can output the migration script to a file instead of executing it
• Supports the following actions:
  • Create a new table
  • Add a new column to an existing table
  • Insert a new row to a table
  • Rename an existing table
  • Drop an existing table
  • Rename an existing column
  • Change an exising column's type
  • Drop an existing column
  • Swap the values of 2 columns
• Also allows making custom commands & queries with the standard Query, QueryRow & Exec functions.

⚠ WARNING ⚠ While the library does support MySQL, it is not recommended to use it. DDL operations on MySQL cause an implicit commit, which means there is no way to roll back the transaction after one of these operations. As a result, if an error occurs during the migration, the database could be left in an unusable state. USE AT YOUR OWN RISK !!!

Getting started

To retrieve the library, simply run:

go get code.waarp.fr/lib/migration

Then import it in your project

import "code.waarp.fr/lib/migration"

Basic usage

A migration consists of a list of Script. A script defines an Up and a Down function. Up makes the desired database changes, and Down undoes them. Scripts are written using methods from the Action interface, which provides utilities for common operations (such as creating tables, adding columns...) as well as functions for writing custom SQL commands.

To execute a migration, the first step is to connect to the database using the standard database/sql library. Once connected, the migration engine can be initialized with the NewEngine function using the *sql.DB connector.

Once the engine initialized, the migration can be executed using the Upgrade function or undone using the Downgrade function. In both case, if any of the script fails, the whole migration is rolled back.

Run test

To run tests, you must first clone the repository.

git clone https://code.waarp.fr/lib/migration.git

Once cloned, simply run go test to execute the tests. This will only run the tests for SQLite. To execute the tests for PostgreSQL and MySQL, you must set the appropriate environment variables with the URL to your test databases.

To run tests for PostgreSQL, the MIGRATION_PGSQL_TEST_DATABASE environment variable must be set to the address of your test database. It can either a URL, or a keyword/value string (see the LIBPQ documentation for more details).

To run tests for MySQL, the MIGRATION_MYSQL_TEST_DATABASE environment variable must be set to the address of your test database. The must be URL in the following format:

[user[:password]@][net[(addr)]]/dbname[?param1=value1&paramN=valueN]

License

Migration is licensed under the terms of the MIT License which are detailed in the LICENSE file.

Documentation

Overview

Package migration contains the elements for writing and running database migration operations.

Index

• Constants
• Variables
• type Actions
• type Column
  • func Col(name string, typ sqlType, constraints ...Constraint) Column
• type Constraint
• type DATE
• type DATETIME
• type Definition
• type Engine
  • func NewEngine(db *sql.DB, dialect string, logger Logger, out io.Writer) (*Engine, error)
  • func (e *Engine) Downgrade(migration Migration) error
  • func (e *Engine) Upgrade(migration Migration) error
• type Logger
• type Migration
• type Script
• type TS
• type TSZ
• type TableConstraint
• type Values

Constants

const (
	NoAction   refOption = "NO ACTION"
	SetNull    refOption = "SET NULL"
	SetDefault refOption = "SET DEFAULT"
	Cascade    refOption = "CASCADE"
	Restrict   refOption = "RESTRICT"
)

const MySQL = "mysql"

MySQL is the constant name of the MySQL dialect translator.

const PostgreSQL = "postgresql"

PostgreSQL is the constant name of the PostgreSQL dialect translator.

const SQLite = "sqlite"

SQLite is the constant name of the SQLite dialect translator.

Variables

var (
	// PrimaryKey declares the column as the table's primary key. Only 1 primary
	// key is allowed per table. For multi-column primary keys, use table constraints
	// instead.
	PrimaryKey = &pk{}

	// ForeignKey declares the column as a foreign key referencing the given table.
	ForeignKey = fkFn

	// NotNull adds a 'not null' constraint to the column.
	NotNull = &notNull{}

	// AutoIncr adds an auto-increment to the column. Only works on columns with
	// type TinyInt, SmallInt, Integer & BigInt.
	AutoIncr = &autoIncr{}

	// Unique adds a 'unique' constraint to the column. To place a 'unique'
	// constraint on multiple columns, use table constraints instead.
	Unique = &unique{}

	// Default adds a default value to the column. The value should be given as
	// parameter of the constraint (ex: Default(0)).
	Default = defaultFn
)

The different types of column constraints usable in a CREATE TABLE statement.

var (
	// MultiPrimaryKey adds a primary-key constraint to the given columns.
	MultiPrimaryKey = pkFn

	// MultiUnique adds a 'unique' constraint to the given columns.
	MultiUnique = uniqueFn

	// MultiIndex creates an index on the given columns.
	MultiIndex = indexFn

	// Check add a check constraint to the table.
	Check = checkFn
)

The different types of table constraints usable in a CREATE TABLE statement.

var (
	Boolean = sqlType{/* contains filtered or unexported fields */}

	TinyInt  = sqlType{/* contains filtered or unexported fields */}
	SmallInt = sqlType{/* contains filtered or unexported fields */}
	Integer  = sqlType{/* contains filtered or unexported fields */}
	BigInt   = sqlType{/* contains filtered or unexported fields */}

	Float  = sqlType{/* contains filtered or unexported fields */}
	Double = sqlType{/* contains filtered or unexported fields */}

	Varchar = func(s uint64) sqlType { return sqlType{code: varchar, size: s} }
	Text    = sqlType{/* contains filtered or unexported fields */}

	Date       = sqlType{/* contains filtered or unexported fields */}
	DateTime   = sqlType{/* contains filtered or unexported fields */}
	Timestamp  = sqlType{/* contains filtered or unexported fields */}
	Timestampz = sqlType{/* contains filtered or unexported fields */}

	Binary = func(s uint64) sqlType { return sqlType{code: binary, size: s} }
	Blob   = sqlType{/* contains filtered or unexported fields */}
)

The SQL types supported by the migration engine. These values should be used when declaring a column or when adding a row to a table. If a database RDBMS does not support a specific type, it will be converted to the closest supported equivalent.

var CurrentTimestamp = &curTimestamp{}

var ErrUnknownDialect = errors.New("unknown SQL dialect")

Types

type Actions

type Actions interface {
	// Query executes a query that returns rows, typically a SELECT.
	// Query uses '?' as placeholder for arguments, no matter the database backend.
	Query(query string, args ...any) (*sql.Rows, error)

	// QueryRow executes a query that is expected to return at most one row.
	// QueryRow always returns a non-nil value. Errors are deferred until
	// Row's Scan method is called.
	// If the query selects no rows, the *Row's Scan will return ErrNoRows.
	// Otherwise, the *Row's Scan scans the first selected row and discards
	// the rest.
	// QueryRow uses '?' as placeholder for arguments, no matter the database backend.
	QueryRow(query string, args ...any) *sql.Row

	// Exec executes a prepared statement with the given arguments. The statement
	// should not return anything. If it does, use Query or QueryRow instead.
	// Exec uses '?' as placeholder for arguments, no matter the database backend.
	Exec(query string, args ...any) error

	// GetDialect returns the database's dialect.
	GetDialect() string

	// FormatValue takes a Go value and returns the corresponding SQL value in
	// the given SQL type. If the given value cannot be formatted in the given
	// type, an error is returned.
	FormatValue(val any) (string, error)

	// CreateTable creates a new table with the given definitions. A definition
	// can be either Column or a TableConstraint.
	CreateTable(name string, defs ...Definition) error

	// AddColumn adds a new column to the table with the given type and constraints.
	// It is up to the migration script to then fill that column if necessary.
	AddColumn(table, column string, dataType sqlType, constraints ...Constraint) error

	// AddRow inserts a new row into the given table.
	// Deprecated: simply use Exec with an 'INSERT INTO' command.
	AddRow(table string, values Values) error

	// RenameTable changes the name of the given table to a new one. This is a
	// destructive (non retro-compatible) operation.
	RenameTable(oldName, newName string) error

	// DropTable drops the given table. This is a destructive (non
	// retro-compatible) operation.
	DropTable(name string) error

	// RenameColumn changes the name of the given column. This is a destructive
	// (non retro-compatible) operation.
	RenameColumn(table, oldName, newName string) error

	// ChangeColumnType changes the type of the given column. This is a
	// destructive (non retro-compatible) operation.
	ChangeColumnType(table, col string, to sqlType) error

	// SwapColumns swaps all the values of col1 and col2 for the rows fulfilling
	// the given conditions. The columns' types MUST be compatible for this
	// operation to work.
	SwapColumns(table, col1, col2, cond string, condArgs ...any) error

	// DropColumn drops the given column. This is a destructive (non
	// retro-compatible) operation.
	DropColumn(table, name string) error

	// CreateIndex adds an index (unique or not) on the given columns of the given
	// table.
	CreateIndex(isUnique bool, table, indexName string, cols ...string) error

	// DropIndex drops the index (unique or not) on the given columns of the given
	// table if it exists.
	DropIndex(isUnique bool, table, indexName string) error
}

Actions is an interface regrouping all the database actions available inside a migration script.

type Column

type Column struct {
	Name        string
	Type        sqlType
	Constraints []Constraint
}

Column is a type representing a column declaration in a CREATE TABLE statement. It contains the column's name, type and its constraints (if it has some).

func Col

func Col(name string, typ sqlType, constraints ...Constraint) Column

Col is a shortcut function for instantiating a column without having to declare the attributes' names.

type Constraint

type Constraint any

Constraint represents a single SQL column constraint to be used when declaring a column. Valid constraints are: PrimaryKey, ForeignKey, NotNull, AutoIncr, Unique and Default.

type DATE

type DATE time.Time

type DATETIME

type DATETIME time.Time

type Definition

type Definition any

Definition represents one part of a column definition. It can be a column or a table constraint.

type Engine

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

Engine is an object which can execute a series of script, using the Up and Down functions. It requires an *sql.DB and an SQL dialect to initiate.

func NewEngine

func NewEngine(db *sql.DB, dialect string, logger Logger, out io.Writer) (*Engine, error)

NewEngine returns a new Engine for the given sql.DB, using the given SQL dialect. Errors and requests will be logged on the given Logger.

If the io.Writer argument is not nil, the commands (except for queries) will be written to it instead of being sent to the database. This can be useful if one wishes to execute the migration directly, instead of leaving it to the engine itself.

func (*Engine) Downgrade

func (e *Engine) Downgrade(migration Migration) error

Downgrade takes a slice of migrations, and "reverts" them by calling all their Down functions in reverse order, starting from the last one. All the migrations are run inside a single transaction, and if any of them fails, the whole transaction will be canceled.

func (*Engine) Upgrade

func (e *Engine) Upgrade(migration Migration) error

Upgrade takes Migration, and executes all its scripts sequentially by calling their Up functions in order. All the scripts are run inside a single transaction, and if any of them fails, the whole transaction will be canceled.

type Logger

type Logger interface {
	Trace(msg string, args ...any)
	Debug(msg string, args ...any)
	Info(msg string, args ...any)
	Warning(msg string, args ...any)
	Error(msg string, args ...any)
	Critical(msg string, args ...any)
	Alert(msg string, args ...any)
}

Logger is the interface which must be implemented by loggers.

type Migration

type Migration []Script

A Migration is a list of Script which can be executed by the Engine.

type Script

type Script struct {
	// A short description of what the script does.
	Description string
	// Up applies the migration.
	Up func(db Actions) error
	// Down undoes migration.
	Down func(db Actions) error
}

Script represents a collection of database changes. It is recommended to keep scripts short, and not to run many operation in a single script.

type TS

type TS time.Time

type TSZ

type TSZ time.Time

type TableConstraint

type TableConstraint any

TableConstraint represents a constraint put on an SQL table when declaring said table. Valid table constraints are: MultiPrimaryKey, MultiUnique and Check.

type Values

type Values map[string]any

Values is a map type representing a table row in a INSERT INTO statement. It associates each column's name with its value for that row.