rockhopper is an embeddable migration tool written in Go, which can embed your migration files into a package with an easy-to-use API.
REF: a small penguin with a yellowish crest, breeding on subantarctic coastal cliffs which it ascends by hopping from rock to rock.
- Embeddable migration script - you can embed SQL files as go source files and compile them into a binary
- Support multiple drivers
- Modularized migration package structure
- Compatible with Goose https://github.com/pressly/goose
- mysql
- sqlite3
- postgresql
- mssql
go install github.com/c9s/rockhopper/v2/cmd/rockhopper@v2.0.3
Add rockhopper.yaml
with the following content:
---
driver: mysql
dialect: mysql
dsn: "root@tcp(localhost:3306)/rockhopper?parseTime=true"
package: myapp
migrationsDirs:
- migrations/module1
- migrations/module2
And create the directory structure for your migration files (or you can just use migrations/
:
mkdir -p migrations/{module1,module2}
Then create a migration file with the following command:
rockhopper create -t sql --output migrations/module1 add_trades_table
Here is an example of the migration script (SQL format):
-- @package main
-- !txn
-- +up
-- +begin
CREATE TABLE trades
(
`id` BIGINT UNSIGNED,
`order_id` BIGINT UNSIGNED NOT NULL,
`symbol` VARCHAR(20) NOT NULL,
`price` DECIMAL(16, 8) UNSIGNED NOT NULL,
`quantity` DECIMAL(16, 8) UNSIGNED NOT NULL,
`fee` DECIMAL(16, 8) UNSIGNED NOT NULL,
`fee_currency` VARCHAR(10) NOT NULL,
`side` VARCHAR(4) NOT NULL DEFAULT '',
`traded_at` DATETIME(3) NOT NULL,
PRIMARY KEY (`gid`),
UNIQUE KEY `id` (`exchange`, `symbol`, `side`, `id`)
);
-- +end
-- +begin
ALTER TABLE trades ADD COLUMN foo INT DEFAULT 0;
-- +end
-- +down
-- +begin
ALTER TABLE trades DROP COLUMN foo;
-- +end
-- +begin
DROP TABLE trades;
-- +end
After editing, you can check your migration status:
rockhopper status
To upgrade:
rockhopper up
To downgrade:
rockhopper down
Create a directory for your migrations:
mkdir migrations
Add rockhopper.yaml
with the following content:
---
driver: mysql
dialect: mysql
dsn: "root@tcp(localhost:3306)/rockhopper?parseTime=true"
migrationsDir: migrations
To add new migration scripts:
rockhopper create -t sql my_first_migration
Or, more advanced:
rockhopper --config rockhopper_mysql_local.yaml create -o migrations/mysql/app1 -t sql "create table 1"
To check migration script status:
rockhopper status
Apply all available migrations:
rockhopper up
When marking the migration script with @package app1
, the migration scripts will be executed per package,
here is the flow:
- Collect all migration scripts
- Categorize the migration scripts by their package name
- Iterate the migration scripts by package
The default package name is set to main
Roll back a single migration from the current version:
rockhopper down
To redo a migration:
rockhopper redo
You can compile your SQL migrations into a go package:
rockhopper compile -o pkg/migrations
You can change the default config file name by passing the --config
parameter:
rockhooper --config rockhopper_sqlite3.yaml status
$ rockhopper status
+---------+----------------+---------------------------------------------------------+--------------------------+---------+
| PACKAGE | VERSION ID | SOURCE FILE | APPLIED AT | CURRENT |
+---------+----------------+---------------------------------------------------------+--------------------------+---------+
| app1 | 20240116231445 | migrations/mysql/app1/20240116231445_create_table_1.sql | Fri Jan 19 15:34:51 2024 | - |
| app1 | 20240116231513 | migrations/mysql/app1/20240116231513_create_table_2.sql | Fri Jan 19 15:34:51 2024 | * |
+---------+----------------+---------------------------------------------------------+--------------------------+---------+
| app2 | 20240117132418 | migrations/mysql/app2/20240117132418_create_table_1.sql | Fri Jan 19 15:34:51 2024 | - |
| app2 | 20240117132421 | migrations/mysql/app2/20240117132421_create_table_2.sql | Fri Jan 19 15:34:51 2024 | * |
+---------+----------------+---------------------------------------------------------+--------------------------+---------+
| | | MIGRATIONS | 4 | |
+---------+----------------+---------------------------------------------------------+--------------------------+---------+
ROCKHOPPER_DRIVER=mysql
ROCKHOPPER_DIALECT=mysql
ROCKHOPPER_DSN="root:root@unix(/opt/local/var/run/mysql57/mysqld.sock)/bbgo"
ROCKHOPPER_DRIVER
is the db driver name that will be used for the protocol.
ROCKHOPPER_DIALECT
is the dialect name that will be used for generating different kinds of SQL, e.g. mysql, sqlite3, postgresql...
ROCKHOPPER_DSN
is the DSN used for connecting to the database.
rockhopper supports migrations written in SQL or in Go.
A simple SQL migration looks like:
-- +up
CREATE TABLE post (
id int NOT NULL,
title text,
body text,
PRIMARY KEY(id)
);
-- +down
DROP TABLE post;
Each migration file must have exactly one -- +up
annotation.
The -- +down
annotation is optional.
If the file has both annotations, then the -- +up
annotation must come first.
Notice the annotations in the comments.
Any statements following -- +up
will be executed as part of a forward migration,
and any statements following -- +down
will be executed as part of a rollback.
By default, all migrations are run within a transaction.
Some statements like CREATE DATABASE
, however, cannot be run within a transaction,
You may optionally add -- !txn
to the top of your migration file in order to skip transactions within that specific migration file.
Both Up and Down migrations within this file will be run without transactions.
By default, SQL statements are delimited by semicolons - in fact, query statements must end with a semicolon to be properly recognized by rockhopper.
More complex statements (PL/pgSQL) that have semicolons within them must be annotated with -- +begin
and -- +end
to be properly recognized. For example:
-- +up
-- +begin
create or replace procedure prac_transfer(
sender int,
receiver int,
amount dec
)
language plpgsql
as $$
begin
-- subtracting the amount from the sender's account
update accounts
set balance = balance - amount
where id = sender;
-- adding the amount to the receiver's account
update accounts
set balance = balance + amount
where id = receiver;
commit;
end;$$;-- +end
With rockhopper, you can embed the SQL migration files into your application, simply run:
rockhopper compile --output pkg/migrations
the SQL migration files will be compiled as a GO package, you can simply import the package to load these migrations, with the following example:
import (
"context"
"github.com/c9s/rockhopper/v2"
mysqlMigrations "github.com/c9s/bbgo/pkg/migrations/mysql"
)
func Migrate(ctx context.Context, db *sql.DB) error {
dialect, err := rockhopper.LoadDialect("mysql")
if err != nil {
return err
}
rh := rockhopper.New(s.Driver, dialect, db, rockhopper.TableName)
if err := rh.Touch(ctx); err != nil {
return err
}
migrations = mysqlMigrations.Migrations()
migrations = migrations.FilterPackage([]string{"main"}).SortAndConnect()
if len(migrations) == 0 {
return nil
}
_, lastAppliedMigration, err := rh.FindLastAppliedMigration(ctx, migrations)
if err != nil {
return err
}
if lastAppliedMigration != nil {
return rockhopper.Up(ctx, rh, lastAppliedMigration.Next, 0)
}
return rockhopper.Up(ctx, rh, migrations.Head(), 0)
}
If you need to integrate rockhopper API, for example, controlling the upgrade/downgrade process from your application, you can call the rockhopper API to do these things:
With config file:
// load config into the global instance
config, err = rockhopper.LoadConfig(configFile)
if err != nil {
log.Fatal(err)
}
db, err := rockhopper.OpenByConfig(config)
if err != nil {
return err
}
defer db.Close()
currentVersion, err = db.CurrentVersion()
if err != nil {
return err
}
loader := &rockhopper.SqlMigrationLoader{}
migrations, err := loader.Load(config.MigrationsDir)
if err != nil {
return err
}
for _, m := range migrations {
// ....
}
err = rockhopper.Up(ctx, db, migrations, currentVersion, to, func(m *rockhopper.Migration) {
log.Infof("migration %v is applied", m.Version)
})
Without config file:
driver := os.Getenv("DB_DRIVER")
dialect, err := rockhopper.LoadDialect(driver)
if err != nil {
return err
}
var migrations rockhopper.MigrationSlice
switch s.Driver {
case "sqlite3":
migrations = sqlite3Migrations.Migrations()
case "mysql":
migrations = mysqlMigrations.Migrations()
}
// sqlx.DB is different from sql.DB
rh := rockhopper.New(s.Driver, dialect, s.DB.DB)
currentVersion, err := rh.CurrentVersion()
if err != nil {
return err
}
if err := rockhopper.Up(ctx, rh, migrations, currentVersion, 0); err != nil {
return err
}
Thanks to https://github.com/pressly/goose, this project was forked from goose.
MIT License