pms

README

pms

Personal Migration System. CLI and GO package.

Homebrew

Pull Request

To make it easier to install with Homebrew, this package should be more notable to deploy it(>30 forks, >=30 watchers and >=75 stars). Please support this package!

Supported drivers

• MySQL (4.1+)
• MariaDB
• Percona Server
• Google CloudSQL or Sphinx (2.2.3+)
• PostgreSQL

How to use

Install package

go get github.com/Moranilt/pms

Make folder

First of all you should create a folder where to store migration-files:

mkdir migrations

Make files

To create a migration file you should follow template {version}_{any_name}.{action}.sql where:

• version - version of migrations
• any_name - name which associated with queries inside(whatever you want)
• action - only up or down

Example:

- 1_users.up.sql
- 1_users.down.sql
- 2_posts.up.sql
- 2_posts.down.sql

1_users.up.sql:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL
);

1_users.down.sql:

DROP TABLE users;

2_posts.up.sql:

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

2_posts.down.sql:

DROP TABLE posts;

Run

After first run it'll create migrations table in your DB. Do not delete or update it!

Inside of GO application

Up

To run all migrations you should use Up method which will run all files with up action in file.

If current version 2 and you have files with version up to 5, Up method will run all files from 3 to 5 versions.

Example:

package main

import (
	"log"

	"github.com/Moranilt/pms"
	"github.com/jmoiron/sqlx"
)

func main() {
	conn := "host=localhost dbname=postgres user=root password=1234 port=5432 sslmode=disable"
	db, err := sqlx.Connect("postgres", conn)
	if err != nil {
		log.Fatal("error while connecting to db", err)
	}

	migrator, err := pms.New(db, "./migrations")
	if err != nil {
		log.Fatal(err)
	}

	err = migrator.Up()
	if err != nil {
		log.Fatal("failed to run migrations: ", err)
	}
}

Down

To run all files with down action you should use Down method.

It will run all files with down actions with descending order starts from the latest version of migration.

package main

import (
	"log"

	"github.com/Moranilt/pms"
	"github.com/jmoiron/sqlx"
)

func main() {
	conn := "host=localhost dbname=postgres user=root password=1234 port=5432 sslmode=disable"
	db, err := sqlx.Connect("postgres", conn)
	if err != nil {
		log.Fatal("error while connecting to db", err)
	}

	migrator, err := pms.New(db, "./migrations")
	if err != nil {
		log.Fatal(err)
	}

	err = migrator.Down()
	if err != nil {
		log.Fatal("failed to run down migrations: ", err)
	}
}

Version

You can chose the version to jump to with Version method. Works like checkout to specific version.

If current version is 5 and you will run migrator.Version(7), it will execute all migration files with up action from 6 to 7 versions.

If current version is 5 and you will run migrator.Version(2), it will execute all migration files with down action from 5 to 3 versions.

package main

import (
	"log"

	"github.com/Moranilt/pms"
	"github.com/jmoiron/sqlx"
)

func main() {
	conn := "host=localhost dbname=postgres user=root password=1234 port=5432 sslmode=disable"
	db, err := sqlx.Connect("postgres", conn)
	if err != nil {
		log.Fatal("error while connecting to db", err)
	}

	migrator, err := pms.New(db, "./migrations")
	if err != nil {
		log.Fatal(err)
	}

	err = migrator.Version(3)
	if err != nil {
		log.Fatal("failed to run version migrations: ", err)
	}

  	err = migrator.Version(5)
	if err != nil {
		log.Fatal("failed to run version migrations: ", err)
	}
}

CMD

You can find binaries for your system in releases.

Allowed flags:

--help - display all available flags
-db string - Database name
-down - Run all down migrations from provided path
-host string - Database host (default "localhost")
-pass string - Database password
-port int - Database port (default 5432)
-source string - Source of migration files. For example './migrations' (default "migrations")
-up - Run all migrations from provided path
-user string - Database user (default "root")
-v int - Select version of migrations (default -1)
-sslMode string - Set ssl mode (default "disable")
-driver string - Set MySQL driver (default "mysql")
-url string - Connection URL. [driver]://[user]:[pass]@[host]:[port]/[db_name]?[flag_name]=[flag_value]

Example Up:

pms -driver postgres -db postgres -host localhost -pass secret_pass -source migrations -user root -up

Example Down:

pms -db postgres -host localhost -pass secret_pass -source migrations -user root -down

Example Version:

pms -db postgres -host localhost -pass secret_pass -source migrations -user root -v 5

Example URL:

pms -driver postgres -url "postgres://root:123456@localhost:5432/authentication?sslmode=disable" -up

Documentation

Index

• Constants
• type DB
• type Direction
• type LogType
• type Logger
• type Migration
  • func (m *Migration) Down() error
  • func (m *Migration) Up() error
  • func (m *Migration) Version(version int) error
• type Migrator
  • func New(db DB, path string) (Migrator, error)

Constants

const (
	TABLE_NAME         = "migrations"
	QUERY_CREATE_TABLE = `CREATE TABLE %s (
		version VARCHAR(255) NOT NULL DEFAULT 0
	);
	INSERT INTO migrations (version) VALUES (0);`
	QUERY_UPDATE_VERSION = "UPDATE %s SET version=%d"

	ERROR_EQUAL_VERSION = "current version %d equals current"
	ERROR_UP_TO_DATE    = "migrations is up to date"
)

const (
	SELECT_VERSION = "SELECT version FROM migrations"
)

Types

type DB

type DB interface {
	Begin() (*sql.Tx, error)
	Close() error
	Exec(query string, args ...any) (sql.Result, error)
	Ping() error
	Query(query string, args ...any) (*sql.Rows, error)
	QueryRow(query string, args ...any) *sql.Row
}

type Direction

type Direction string

const (
	DIRECTION_UP   Direction = "up"
	DIRECTION_DOWN Direction = "down"
)

type LogType

type LogType string

const (
	LOG_INFO  LogType = "info"
	LOG_ERROR LogType = "error"
	LOG_WARN  LogType = "warn"
)

type Logger

type Logger interface {
	Info(message ...string)
	Error(message ...string)
	Warn(message ...string)
}

type Migration

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

func (*Migration) Down

func (m *Migration) Down() error

Run all queries from files with `down` action.

func (*Migration) Up

func (m *Migration) Up() error

Run all queries from files with `up` action.

func (*Migration) Version

func (m *Migration) Version(version int) error

Switch to the specified version.

If specified version greater than current it'll run queries with `up` action.

If specified version lower that current it'll run queries with `down` action.

Otherwise it'll return an error.

type Migrator

type Migrator interface {
	Up() error
	Down() error
	Version(int) error
}

func New

func New(db DB, path string) (Migrator, error)

Create new instance of Migration structure