ClickHouse Migrations

• Migration names
• Migration groups and rollbacks
• Go-based migrations
• SQL-based migrations

You can write migrations to change database schema or data. A migration can be a regular Go function or a text file with SQL commands.

Migration names

You should put each migration into a separate file. A migration file names consists of an unique migration name (20210505110026) and a comment (add_foo_column), for example, 20210505110026_add_foo_column.go.

Migration status

go-clickhouse stores the completed migration names in the ch_migrations table to decide which migrations to run. It also uses that information to rollback migrations.

When a migration fails, go-clickhouse still marks the migration as applied so you can rollback the partially applied migration to cleanup the database and try to run the migration again.

Migration groups and rollbacks

When there are multiple migrations to run, go-clickhouse runs migrations together as a group. During rollbacks, go-clickhouse reverts the last migration group (not a single migration). Usually that is desirable, because it rolls the db back to the last known stable state.

To rollback a single migration, you need to rollback the last group, delete the migration(s) you want to skip, and run migrations again. Alternatively, you can add a new migration with the changes you need.

Go-based migrations

A Go-based migration is a regular Go function that can execute arbitrary code. Each such function must be registered in a migration collection that is created in main.go file:

package migrations

import (
	"github.com/uptrace/go-clickhouse/chmigrate"
)

// A collection of migrations.
var Migrations = chmigrate.NewMigrations()

Then, in a separate files, you should define and register migrations using MustRegister method, for example, in 20210505110026_test_migration.go:

package migrations

import (
	"context"
	"fmt"

	"github.com/uptrace/go-clickhouse/ch"
)

func init() {
	Migrations.MustRegister(func(ctx context.Context, db *ch.DB) error {
		fmt.Print(" [up migration] ")
		return nil
	}, func(ctx context.Context, db *ch.DB) error {
		fmt.Print(" [down migration] ")
		return nil
	})
}

See example for details.

SQL-based migrations

A SQL-based migration is a file with .up.sql extension that contains one or more SQL commands. You can use --migration:split line as a separator to create migrations with multiple statements.

SELECT 1

--migration:split

SELECT 2

You can register such migrations using Discover method:

//go:embed *.sql
var sqlMigrations embed.FS

func init() {
	if err := Migrations.Discover(sqlMigrations); err != nil {
		panic(err)
	}
}

To create a transactional migration, use .tx.up.sql extension.

See example for details.