migration

package module
v0.3.0 Latest Latest
Warning

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

 Go to latest Published: Nov 22, 2022 License: MIT Imports: 10 Imported by: 2

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

View Source 
const MySQL = "mysql"

MySQL is the constant name of the MySQL dialect translator.

View Source 
const PostgreSQL = "postgresql"

PostgreSQL is the constant name of the PostgreSQL dialect translator.

View Source 
const SQLite = "sqlite"

SQLite is the constant name of the SQLite dialect translator.

Variables

View Source 
var (
	ErrUnsupportedGoType  = errors.New("unsupported Go type")
	ErrUnwrappedTimeValue = errors.New("time values must be formatted using TimeValue")
)
View Source 
var (
	ErrNoColumns          = errors.New("table has no columns")
	ErrNonIntegerAutoIncr = errors.New("auto-increment can only be set on integer columns")
)
View Source 
var (
	ErrMissingColumnName = errors.New("missing column name")
	ErrMissingColumnType = errors.New("missing column type")
)
View Source 
var (
	ErrMissingConstraintName = errors.New("missing constraint name")
	ErrMissingConstraintCols = errors.New("missing constraint columns")
	ErrMissingExpression     = errors.New("missing expression")
	ErrMissingRefTable       = errors.New("missing reference table")
	ErrMissingRefCols        = errors.New("missing reference columns")
)
View Source 
var ErrMissingNewColumnName = errors.New("missing new column name")
View Source 
var ErrSqliteAutoIncrPK = errors.New(`the sqlite "AUTOINCREMENT" attribute can ` +
	`only be set on "INTEGER PRIMARY KEY" columns`)
View Source 
var ErrSqliteTableParse = errors.New("could not parse the sqlite table schema")
View Source 
var ErrUnknownDataType = errors.New("unknown SQL data type")
View Source 
var ErrUnknownDialect = errors.New("unknown SQL dialect")
View Source 
var ErrUnknownModification = errors.New("unknown table modification type")

Functions

func TimeValue added in v0.3.0 

func TimeValue(value time.Time, dataType DataType) any

TimeValue converts the given time.Time value into it's formatted SQL value of the given SQL DataType.

Types

type Actions 

type Actions interface {
	// GetDialect returns the database's dialect.
	GetDialect() string

	// 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

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

	// CreateTable creates a new table with the given table definition.
	CreateTable(name string, table *Table) error

	// RenameTable changes the name of the given table to a new one.
	RenameTable(oldName, newName string) error

	// AlterTable changes the definition of the given table by applying the given
	// modifications to it. Note that the modifications will be run in the order
	// they are given.
	AlterTable(table string, modifications ...AlterStatement) error

	// DropTable drops the given table. WARNING: the table's data will be lost.
	DropTable(name string) 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

	// 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(table, indexName string) error
}

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

type AddCheck added in v0.3.0 

type AddCheck Check

AddCheck adds a CHECK constraint to the table.

type AddColumn added in v0.3.0 

type AddColumn Column

AddColumn adds a new column to the table with the given definition.

type AddForeignKey added in v0.3.0 

type AddForeignKey ForeignKey

AddForeignKey adds a new foreign key constraint to the table.

type AddPrimaryKey added in v0.3.0 

type AddPrimaryKey PrimaryKey

AddPrimaryKey adds a new primary key constraint to the table (note that a table can only have 1 primary key).

type AddUnique added in v0.3.0 

type AddUnique Unique

AddUnique adds a new unique constraint to the table.

type AlterColumn added in v0.3.0 

type AlterColumn struct {
	Name    string
	NewName string // Optional
	Type    DataType
	NotNull bool
	Default any
}

AlterColumn replaces the column's definition with a new one. All properties must be specified (even those who do not change) otherwise they will be reset to their default value.

NewName is optional and can be used to rename the column.

type AlterStatement added in v0.3.0 

type AlterStatement interface {
	// contains filtered or unexported methods
}

type AutoIncr 

type AutoIncr struct{}

AutoIncr can be set as the default value of a Column to specify that the column uses an auto-increment to generate its default values.

type BigInt 

type BigInt struct{}

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.

func (BigInt) ToMysqlType added in v0.3.0 

func (t BigInt) ToMysqlType() string

func (BigInt) ToPostgresType added in v0.3.0 

func (t BigInt) ToPostgresType() string

func (BigInt) ToSqliteType added in v0.3.0 

func (t BigInt) ToSqliteType() string

type Blob 

type Blob struct{}

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.

func (Blob) ToMysqlType added in v0.3.0 

func (t Blob) ToMysqlType() string

func (Blob) ToPostgresType added in v0.3.0 

func (t Blob) ToPostgresType() string

func (Blob) ToSqliteType added in v0.3.0 

func (t Blob) ToSqliteType() string

type Boolean 

type Boolean struct{}

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.

func (Boolean) ToMysqlType added in v0.3.0 

func (t Boolean) ToMysqlType() string

func (Boolean) ToPostgresType added in v0.3.0 

func (t Boolean) ToPostgresType() string

func (Boolean) ToSqliteType added in v0.3.0 

func (t Boolean) ToSqliteType() string

type Check 

type Check struct {
	Name string // The constraint's name.
	Expr string // The check expression.
}

Check represents a "CHECK" constraint.

type Column 

type Column struct {
	Name string   // The column's name.
	Type DataType // The column's type.

	NotNull bool // Set true to make the column non-nullable.
	Default any  // Set to the column's default value.
}

Column represents a table column's schema.

type CurrentTimestamp 

type CurrentTimestamp struct{}

type DataType added in v0.3.0 

type DataType interface {
	ToSqliteType() string
	ToPostgresType() string
	ToMysqlType() string
}

type Date 

type Date struct{}

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.

func (Date) ToMysqlType added in v0.3.0 

func (t Date) ToMysqlType() string

func (Date) ToPostgresType added in v0.3.0 

func (t Date) ToPostgresType() string

func (Date) ToSqliteType added in v0.3.0 

func (t Date) ToSqliteType() string

type DateTime added in v0.2.0 

type DateTime struct{}

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.

func (DateTime) ToMysqlType added in v0.3.0 

func (t DateTime) ToMysqlType() string

func (DateTime) ToPostgresType added in v0.3.0 

func (t DateTime) ToPostgresType() string

func (DateTime) ToSqliteType added in v0.3.0 

func (t DateTime) ToSqliteType() string

type DateTimeOffset added in v0.3.0 

type DateTimeOffset struct{}

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.

func (DateTimeOffset) ToMysqlType added in v0.3.0 

func (t DateTimeOffset) ToMysqlType() string

func (DateTimeOffset) ToPostgresType added in v0.3.0 

func (t DateTimeOffset) ToPostgresType() string

func (DateTimeOffset) ToSqliteType added in v0.3.0 

func (t DateTimeOffset) ToSqliteType() string

type Double 

type Double struct{}

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.

func (Double) ToMysqlType added in v0.3.0 

func (t Double) ToMysqlType() string

func (Double) ToPostgresType added in v0.3.0 

func (t Double) ToPostgresType() string

func (Double) ToSqliteType added in v0.3.0 

func (t Double) ToSqliteType() string

type DropColumn added in v0.3.0 

type DropColumn struct{ Name string }

DropColumn drops the given column from the table.

type DropConstraint added in v0.3.0 

type DropConstraint struct{ Name string }

DropConstraint drops the given constraint from the table.

type DropPrimaryKey added in v0.3.0 

type DropPrimaryKey struct{ Name string }

DropPrimaryKey drops the given primary key from the table.

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 Float 

type Float struct{}

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.

func (Float) ToMysqlType added in v0.3.0 

func (t Float) ToMysqlType() string

func (Float) ToPostgresType added in v0.3.0 

func (t Float) ToPostgresType() string

func (Float) ToSqliteType added in v0.3.0 

func (t Float) ToSqliteType() string

type ForeignKey 

type ForeignKey struct {
	Name     string            // The constraint's name.
	Cols     []string          // The columns forming the foreign key.
	RefTbl   string            // The table referenced by the foreign key.
	RefCols  []string          // The columns referenced by the foreign key.
	OnUpdate ReferentialAction // An optional "ON UPDATE" clause.
	OnDelete ReferentialAction // An optional "ON DELETE" clause.
}

ForeignKey represents a "FOREIGN KEY" constraint.

type Integer 

type Integer struct{}

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.

func (Integer) ToMysqlType added in v0.3.0 

func (t Integer) ToMysqlType() string

func (Integer) ToPostgresType added in v0.3.0 

func (t Integer) ToPostgresType() string

func (Integer) ToSqliteType added in v0.3.0 

func (t Integer) ToSqliteType() string

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 PrimaryKey 

type PrimaryKey struct {
	Name string   // The constraint's name.
	Cols []string // The columns forming the primary key.
}

PrimaryKey represents a "PRIMARY KEY" constraint.

type ReferentialAction added in v0.3.0 

type ReferentialAction uint8
const (
	NoAction   ReferentialAction = iota + 1 // NO ACTION
	Restrict                                // RESTRICT
	Cascade                                 // CASCADE
	SetNull                                 // SET NULL
	SetDefault                              // SET DEFAULT
)

func (ReferentialAction) String added in v0.3.0 

func (i ReferentialAction) String() string

type RenameColumn added in v0.3.0 

type RenameColumn struct{ OldName, NewName string }

RenameColumn renames the given column.

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 SmallInt 

type SmallInt struct{}

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.

func (SmallInt) ToMysqlType added in v0.3.0 

func (t SmallInt) ToMysqlType() string

func (SmallInt) ToPostgresType added in v0.3.0 

func (t SmallInt) ToPostgresType() string

func (SmallInt) ToSqliteType added in v0.3.0 

func (t SmallInt) ToSqliteType() string

type Table added in v0.3.0 

type Table struct {
	Columns     []Column     // The table's columns
	PrimaryKey  *PrimaryKey  // The table's primary key.
	ForeignKeys []ForeignKey // The table's foreign keys.
	Uniques     []Unique     // The table's unique constraints.
	Checks      []Check      // The table's check constraints.
}

Table represents an SQL table's schema.

type Text 

type Text struct{}

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.

func (Text) ToMysqlType added in v0.3.0 

func (t Text) ToMysqlType() string

func (Text) ToPostgresType added in v0.3.0 

func (t Text) ToPostgresType() string

func (Text) ToSqliteType added in v0.3.0 

func (t Text) ToSqliteType() string

type TinyInt 

type TinyInt struct{}

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.

func (TinyInt) ToMysqlType added in v0.3.0 

func (t TinyInt) ToMysqlType() string

func (TinyInt) ToPostgresType added in v0.3.0 

func (t TinyInt) ToPostgresType() string

func (TinyInt) ToSqliteType added in v0.3.0 

func (t TinyInt) ToSqliteType() string

type Unique 

type Unique struct {
	Name string   // The constraint's name.
	Cols []string // The columns forming the unique key.
}

Unique represents a "UNIQUE" constraint.

type Varchar 

type Varchar uint64

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.

func (Varchar) ToMysqlType added in v0.3.0 

func (t Varchar) ToMysqlType() string

func (Varchar) ToPostgresType added in v0.3.0 

func (t Varchar) ToPostgresType() string

func (Varchar) ToSqliteType added in v0.3.0 

func (t Varchar) ToSqliteType() string

Source Files

View all Source files

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.