dbmigrate

README

dbmigrate

Overview

dbmigrate is a sql database migration tool.

It can be used both as a CLI application and as a Go package, does not use any DSL for migrations, just plain old SQL we all know and love so it is compatible with any framework and programming language.

This readme covers the CLI app, for the Go package documentation please look at https://godoc.org/github.com/dafanasev/dbmigrate

Features

• Can be used as a CLI app and as a Go package
• Support for PostgreSQL, MySQL and SQLite
• Migrations generator
• Up and down migrations in different files
• Database specific migrations (e.g. ones that executed only on Postgres)
• Uses timestamps as migration version
• Migrates all the way up or by specified number of steps
• Applies migrations in batches, that can be rolled back/reapplied at once
• View migrations status and other information such as if database is up to date or not, last applied migration, etc
• Gets database connection settings from command line flags, environment variables, file in JSON, TOML, YAML, HCL, or Java properties format, consul or etcd.
• Support for different environments, e.g. for tests

Installation

• using homebrew: brew tap dafanasev/homebrew-tap, brew install dbmigrate
• using go get: go get -u github.com/dafanasev/dbmigrate/cmd/dbmigrate
• using binaries: https://github.com/dafanasev/dbmigrate/releases

Usage

dbmigrate command can be called from any subdirectory of a directory containing the dbmigrations directory, where migrations are stored.

Database settings

In order to use dbmigrate you should provide database settings. Mandatory settings include database engine (postgres, mysql or sqlite), database name and user (except for sqlite). Host defaults to localhost and port has a specific default value for each database engine.

Settings can be read from the following sources, sorted by precedence:

• Command line flags
• Environment variables
• Configuration files (in JSON, TOML, YAML, HCL, or Java properties format)
• Key-value store (consul or etcd)

So, if both the --engine flag and the engine entry in the configuration file are provided the flag value will be used.

Environments

When using environment variables, configuration files or key-value store the --environment (-e) command line flag can be provided to specify alternative database settings if your project uses more than one database, e.g. for tests.

Command line flags

Database settings related command line flags are:

• -n, --engine: database engine (postgres, mysql or sqlite)
• -d, --database: database name
• -u, --user: database user
• -p, --password: database password
• -b, --host: database host, default is localhost
• -o, --port: database port, default is specific for each database engine
• -t, --table: migrations table, default is migrations

The full list of command line flags can be obtained by running dbmigrate --help.

Environment variables

Environment variables names should be in the following format: uppercased project dir name (the one holding dbmigrations dir) followed by correspnding flag name, joined by underscore and uppercased. For example, the environment variable name for the database engine for the project that located in the directory named theservice would be THESERVICE_ENGINE.

If the --prefix (-x) flag is provided, it would be used instead of project dir as environment variables prefix.

If the --env (-e) flag is provided, it would be used as a second part of the variable name, e.g. when the --env flag set to 'test' and the project is in the directory theservice, variable name for engine would be THESERVICE_TEST_ENGINE

Configuration files

Configuration files could be in JSON, TOML, YAML, HCL, or Java properties format. Default file name is dbmigrate, so dbmigrate looks for dbmigrate.yml, dbmigrate.yaml, dbmigrate.json, etc. The alternative configuration file name (without extension) can be set using the --config (-c) flag.

Example configuration file in yaml format:

engine: postgres
database: blog
user: author
password: topsecret

test:
  engine: sqlite
  database: blog.db

Key-value stores

dbmigrate can use consul or etcd to store configuration. In order to use it the --kvsparams (-k) flag should be provided, with a value in the following format: provider://host(:port)/path.type where provider is consul or etcd, host is key-value store host without scheme part, port can be omitted and defaults to 8500 for consul and 2379 for etcd, path is the key path and type is the file format used to encode the config. File formats are the same ones as used for configuration files.

crypt can be used to put configurations in the key-value store, e.g.: crypt set -endpoint http://localhost:2379 -plaintext /configs/theservice theservice.json

if the --secretkeyring (-r) flag is provided, which should point to the path of a secret key ring path, the configuration will be stored encrypted and will be automatically decrypted when retrieved.

Other settings

The --missingdowns (-m) boolean flag, the {APP}_MISSINGDOWNS environment variable or the corresponding entry in the configuration file specifies if it is ok to have missing or empty down migrations. Default is false which means that dbmigrate will exit with an error if this happens.

Commands

dbmigrate has the following commands: generate, migrate (the root, default command), rollback, reapply and status.

Generate

The generate command generates up and down migrations. It uses command line arguments to build migration name, e.g. dbmigrate generate Posts table will create TIMESTAMP_posts_table.up.sql and TIMESTAMP_posts_table.down.sql migrations.

If the --engines (-g) flag is set then migrations will be created only for the specified engines, e.g. dbmigrate -g=postgres,sqlite generate Posts table will generate TIMESTAMP_posts_table.up.postgres.sql, TIMESTAMP_posts_table.up.sqlite.sql and corresponding down migrations.

If the --engines flag is set without value, the database engine specified in connection settings will be used, e.g. dbmigrate -n=sqlite -d=test.db generate Posts table will generate TIMESTAMP_posts_table.up.sqlite.sql and TIMESTAMP_posts_table.down.sqlite.sql files.

Migrate

The migrate command applies all unapplied migrations or, if the --steps (-s) flag is set, only -s migrations. Migrate is the root command, so running it as simple as calling dbmigrate without any subcommand.

Rollback

The rollback command rolls back the latest migration operation, e.g. if 3 migrations were applied during the last operation, then exactly these 3 migrations would be rolled back. If the --steps (-s) flag is set, exactly -s migrations will be rolled back.

Reapply

The reapply command rolls back and applies again migrations applied during the latest migration operation. If the --steps (-s) flag is set, exactly -s migrations will be reapplied.

Status

The status command shows migrations list with names, versions and applied at times, if the migration was applied. It also shows the latest version migration, the last applied migrations (they are not necessarily the same ones), number of applied migrations and if the database schema is up to date or not.

Todo

• Embed migrations into binary or get them from zip/tar archives, http, ssh, s3 or github

License

dbmigrate is distributed under the MIT license.

Documentation

Overview

Package dbmigrate is a sql database migration tool.

dbmigrate can be used both as a CLI application and as a Go package, does not use any DSL for migrations, just plain old SQL we all know and love so it is compatible with any framework and programming language.

Index

• Constants
• func DirExists(dirpath string) bool
• func Engines() []string
• func FileExists(fpath string) bool
• func FindProjectDir(fromDir string) (string, error)
• type Direction
  • func DirectionFromString(s string) (Direction, error)
  • func (d Direction) String() string
• type Migration
  • func (m *Migration) FileName() string
  • func (m *Migration) HumanName() string
• type Migrator
  • func NewMigrator(settings *Settings) (*Migrator, error)
  • func (m *Migrator) Close() error
  • func (m *Migrator) GenerateMigration(descr string, engines ...string) ([]string, error)
  • func (m *Migrator) LastAppliedMigration() (*Migration, error)
  • func (m *Migrator) LatestVersionMigration() (*Migration, error)
  • func (m *Migrator) Migrate() (int, error)
  • func (m *Migrator) MigrateSteps(steps int) (int, error)
  • func (m *Migrator) Rollback() (int, error)
  • func (m *Migrator) RollbackSteps(steps int) (int, error)
  • func (m *Migrator) Status() ([]*Migration, error)
• type Settings

Constants

const (
	// TimestampFormat defines format for migration versioning
	// and is used in migrations file names and db table
	TimestampFormat = "20060102150405"
	// PrintTimestampFormat defines format for printing timestamps
	PrintTimestampFormat = "2006.01.02 15:04:05"
)

const (

	// DirectionUp is the direction to migrate schema
	DirectionUp
	// DirectionDown is the direction to rollback schema
	DirectionDown
)

const AllSteps = 0

AllSteps specifies that all migrations should be applied

const MigrationsDir = "dbmigrations"

MigrationsDir is the directory to store migrations

Functions

func DirExists

func DirExists(dirpath string) bool

DirExists checks if directory at path exists

func Engines

func Engines() []string

Engines returns list of supported database engines

func FileExists

func FileExists(fpath string) bool

FileExists checks if file at path exists

func FindProjectDir

func FindProjectDir(fromDir string) (string, error)

FindProjectDir recursively finds project dir (the one that has dbmigrations subdir)

Types

type Direction

type Direction int

Direction specifies if migration is used to migrate or rollback schema

func DirectionFromString

func DirectionFromString(s string) (Direction, error)

DirectionFromString tries to build Direction from string, checking for valid ones and returning an error if check was unsuccessful

func (Direction) String

func (d Direction) String() string

String returns string representation of direction

type Migration

type Migration struct {
	// Version holds the created at timestamp
	Version   time.Time
	Name      string
	AppliedAt time.Time
	Direction Direction
	Engine    string
}

Migration holds metadata of migration

func (*Migration) FileName

func (m *Migration) FileName() string

FileName builds migration file name from metadata

func (*Migration) HumanName

func (m *Migration) HumanName() string

HumanName returns migration's name without underscores

type Migrator

type Migrator struct {
	// Settings used by migrator
	*Settings
	// contains filtered or unexported fields
}

Migrator is the end user interface for all library operations

func NewMigrator

func NewMigrator(settings *Settings) (*Migrator, error)

NewMigrator creates new Migrator instance

func (*Migrator) Close

func (m *Migrator) Close() error

Close frees resources acquired by migrator

func (*Migrator) GenerateMigration

func (m *Migrator) GenerateMigration(descr string, engines ...string) ([]string, error)

GenerateMigration generates up and down migrations with given name for given engine

func (*Migrator) LastAppliedMigration

func (m *Migrator) LastAppliedMigration() (*Migration, error)

LastAppliedMigration returns the migration which was applied last

func (*Migrator) LatestVersionMigration

func (m *Migrator) LatestVersionMigration() (*Migration, error)

LatestVersionMigration returns the migration that has the most recent version (which is not necessarily the last applied one)

func (*Migrator) Migrate

func (m *Migrator) Migrate() (int, error)

Migrate applies all unapplied migrations

func (*Migrator) MigrateSteps

func (m *Migrator) MigrateSteps(steps int) (int, error)

MigrateSteps applies the number of migrations specified by the steps variable, returning number of applied migrations

func (*Migrator) Rollback

func (m *Migrator) Rollback() (int, error)

Rollback rolls back last migration operation

func (*Migrator) RollbackSteps

func (m *Migrator) RollbackSteps(steps int) (int, error)

RollbackSteps rolls back the number of migrations specified by the steps variable

func (*Migrator) Status

func (m *Migrator) Status() ([]*Migration, error)

Status returns applied a timestamp for each migration or nil if it is not set along with the migration's name and version

type Settings

type Settings struct {
	// Engines is the used RDBMS. Currently PostgreSQL, MySQL and SQLite are supported
	Engine   string
	Database string
	User     string
	Password string
	Host     string
	Port     int
	// MigrationsTable is the database table to store applied migrations data
	MigrationsTable string
	// AllowMissingDowns flag specifies if Migrator should allow empty or missing down migrations files
	// which means that there will be no rollback for the corresponding up migrations and that this is ok
	AllowMissingDowns bool
	// MigrationsCh is the channel for applied migrations
	MigrationsCh chan *Migration
	// ErrorsChan is the channel for errors that happened during the work but are not fatal
	ErrorsCh chan error
}

Settings used by Migrator