MySqlMigrate

MySqlMigrate

This package manages database migrations and versioning for MySQL.

It's best used in conjunction with a command-line programme; see this example.

Usage

Create migration files

package main

import (
	"log"

	"github.com/blainemoser/MySqlDB/database"
	"github.com/blainemoser/MySqlMigrate/migrate"
)

func main() {
	configs := &database.Configs{
		Host:     "127.0.0.1",
		Port:     "3306",
		Username: "root",
		Password: "secret",
		Driver:   "mysql",
		Database: "name_of_schema",
	}
	db, err := database.Make(configs)
	if err != nil {
		log.Fatal(err)
	}
	m := migrate.Make(&db, "/path/to/migrations/folder")
	_, err = m.Create("name_of_migration_eg_create_users_table")
	if err != nil {
		log.Fatal(err)
	}
}

To create a migration file, first create a pointer to migrate.Migration using migrate.Make(db *database.Database, path string) *migrate.Migration.

The first argument (*database.Database) provides the connection to MySQL. The path string indicates the directory to which to save the migration files. If the directory does not exist it will be created, provided the base directory exists.

Second, call Create(name string) (string, error) on the pointer. Provide the name of the migration as the sole argument.

This will not create the relavant schema. This can be achieved by using the function database.MakeSchemaless(configs *Configs) (Database, error), followed by database.Exec("create schema name_of_schema", nil).

This will create a .sql file in the specified directory that looks as follows:

-- add your UP SQL here

-- [DIRECTION] -- do not alter this line!
-- add your DOWN SQL here

Alter the SQL appropriately, for instance:

-- add your UP SQL here

[STATEMENT] CREATE TABLE users (
	id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	password VARCHAR(255) NOT NULL, 
    	role int(10) NOT NULL,
	name VARCHAR(1000) NOT NULL,
    	email VARCHAR(1000) NOT NULL,
    	phone BIGINT,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- [DIRECTION] -- do not alter this line!
-- add your DOWN SQL here

[STATEMENT] DROP TABLE users;	

Execute multiple statements in one migration by using [STATEMENT] to separate the queries.

Do not delete the line -- [DIRECTION] -- do not alter this line!, it delineates between the "up" SQL and the "down" SQL (which will be run if and when the migration is reversed).

Run and reverse migrations

err := migrate.Make(&db, "/path/to/migrations/folder").MigrateUp()

Use the function *migrate.Migration.MigrateUp() error to run any migrations that have yet to be executed.

err := migrate.Make(&db, "/path/to/migrations/folder").MigrateDown()

Use the function *migrate.Migration.MigrateDown() error to reverse the migrations; this will execute the "down" SQL specified in the migration files.

Note that migrations are reversed in batches (groupings of migrations that were run "up" at the same time). It will not reverse all migrations unless all their "up" statements were executed during the same runtime.