Praefect: add sql-migrate-down subcommand

5 files changed, 154 insertions, 2 deletions

diff --git a/changelogs/unreleased/jv-sql-down-migrations.yml b/changelogs/unreleased/jv-sql-down-migrations.yml
new file mode 100644
index 000000000..ccd3655bf
--- /dev/null
+++ b/changelogs/unreleased/jv-sql-down-migrations.yml
@@ -0,0 +1,5 @@
+---
+title: 'Praefect: add sql-migrate-down subcommand'
+merge_request: 1770
+author:
+type: added
diff --git a/cmd/praefect/subcmd_sqldown.go b/cmd/praefect/subcmd_sqldown.go
new file mode 100644
index 000000000..f34395d6c
--- /dev/null
+++ b/cmd/praefect/subcmd_sqldown.go
@@ -0,0 +1,79 @@
+package main
+
+import (
+	"flag"
+	"fmt"
+	"strconv"
+
+	"gitlab.com/gitlab-org/gitaly/internal/praefect/config"
+	"gitlab.com/gitlab-org/gitaly/internal/praefect/datastore"
+)
+
+const subCmdSQLMigrateDown = "sql-migrate-down"
+
+func sqlMigrateDown(conf config.Config, args []string) int {
+	cmd := &sqlMigrateDownCmd{Config: conf}
+	return cmd.Run(args)
+}
+
+type sqlMigrateDownCmd struct{ config.Config }
+
+func (*sqlMigrateDownCmd) prefix() string { return progname + " " + subCmdSQLMigrateDown }
+
+func (*sqlMigrateDownCmd) invocation() string { return invocationPrefix + " " + subCmdSQLMigrateDown }
+
+func (smd *sqlMigrateDownCmd) Run(args []string) int {
+	flagset := flag.NewFlagSet(smd.prefix(), flag.ExitOnError)
+	flagset.Usage = func() {
+		printfErr("usage:  %s [-f] MAX_MIGRATIONS\n", smd.invocation())
+	}
+	force := flagset.Bool("f", false, "apply down-migrations (default is dry run)")
+
+	_ = flagset.Parse(args) // No error check because flagset is set to ExitOnError
+
+	if flagset.NArg() != 1 {
+		flagset.Usage()
+		return 1
+	}
+
+	if err := smd.run(*force, flagset.Arg(0)); err != nil {
+		printfErr("%s: fail: %v\n", smd.prefix(), err)
+		return 1
+	}
+
+	return 0
+}
+
+func (smd *sqlMigrateDownCmd) run(force bool, maxString string) error {
+	maxMigrations, err := strconv.Atoi(maxString)
+	if err != nil {
+		return err
+	}
+
+	if maxMigrations < 1 {
+		return fmt.Errorf("number of migrations to roll back must be 1 or more")
+	}
+
+	if force {
+		n, err := datastore.MigrateDown(smd.Config, maxMigrations)
+		if err != nil {
+			return err
+		}
+
+		fmt.Printf("%s: OK (applied %d \"down\" migrations)\n", smd.prefix(), n)
+		return nil
+	}
+
+	planned, err := datastore.MigrateDownPlan(smd.Config, maxMigrations)
+	if err != nil {
+		return err
+	}
+
+	fmt.Printf("%s: DRY RUN -- would roll back:\n\n", smd.prefix())
+	for _, id := range planned {
+		fmt.Printf("- %s\n", id)
+	}
+	fmt.Printf("\nTo apply these migrations run: %s -f %d\n", smd.invocation(), maxMigrations)
+
+	return nil
+}
diff --git a/cmd/praefect/subcommand.go b/cmd/praefect/subcommand.go
index 74e9d7989..81d516b3c 100644
--- a/cmd/praefect/subcommand.go
+++ b/cmd/praefect/subcommand.go
@@ -9,6 +9,8 @@ import (
 	"gitlab.com/gitlab-org/gitaly/internal/praefect/datastore"
 )
 
+const invocationPrefix = progname + " -config CONFIG_TOML"
+
 // subCommand returns an exit code, to be fed into os.Exit.
 func subCommand(conf config.Config, arg0 string, argRest []string) int {
 	interrupt := make(chan os.Signal)
@@ -24,6 +26,8 @@ func subCommand(conf config.Config, arg0 string, argRest []string) int {
 		return sqlPing(conf)
 	case "sql-migrate":
 		return sqlMigrate(conf)
+	case subCmdSQLMigrateDown:
+		return sqlMigrateDown(conf, argRest)
 	case "dial-nodes":
 		return dialNodes(conf)
 	default:
diff --git a/doc/sql_migrations.md b/doc/sql_migrations.md
index a4d9b6fef..617da054a 100644
--- a/doc/sql_migrations.md
+++ b/doc/sql_migrations.md
@@ -11,3 +11,31 @@ Praefect SQL migrations should be applied automatically when you deploy Praefect
 ```
 praefect -config /path/to/config.toml sql-migrate
 ```
+
+## Rolling back migrations
+
+Rolling back SQL migrations in Praefect works a little differently
+from ActiveRecord. It is a three step process.
+
+### 1. Decide how many steps you want to roll back
+
+Count the number of migrations you want to roll back.
+
+### 2. Perform a dry run and verify that the right migrations are getting rolled back
+
+```
+praefect -config /path/to/config.toml sql-migrate-down NUM_ROLLBACK
+```
+
+This will perform a dry run and print the list of migrations that
+would be rolled back. Verify that these are the migrations you want to
+roll back.
+
+### 3. Perform the rollback
+
+We use the same command as before, but we pass `-f` to indicate we
+want destructive changes (the rollbacks) to happen.
+
+```
+praefect -config /path/to/config.toml sql-migrate-down -f NUM_ROLLBACK
+```
diff --git a/internal/praefect/datastore/db.go b/internal/praefect/datastore/db.go
index d84d9b3ee..3ec1e2368 100644
--- a/internal/praefect/datastore/db.go
+++ b/internal/praefect/datastore/db.go
@@ -41,6 +41,8 @@ func CheckPostgresVersion(conf config.Config) error {
 
 func openDB(conf config.Config) (*sql.DB, error) { return sql.Open("postgres", conf.DB.ToPQString()) }
 
+const sqlMigrateDialect = "postgres"
+
 // Migrate will apply all pending SQL migrations
 func Migrate(conf config.Config) (int, error) {
 	db, err := openDB(conf)
@@ -49,7 +51,41 @@ func Migrate(conf config.Config) (int, error) {
 	}
 	defer db.Close()
 
-	migrationSource := &migrate.MemoryMigrationSource{Migrations: migrations.All()}
+	return migrate.Exec(db, sqlMigrateDialect, migrationSource(), migrate.Up)
+}
+
+// MigrateDownPlan does a dry run for rolling back at most max migrations.
+func MigrateDownPlan(conf config.Config, max int) ([]string, error) {
+	db, err := openDB(conf)
+	if err != nil {
+		return nil, fmt.Errorf("sql open: %v", err)
+	}
+	defer db.Close()
+
+	planned, _, err := migrate.PlanMigration(db, sqlMigrateDialect, migrationSource(), migrate.Down, max)
+	if err != nil {
+		return nil, err
+	}
+
+	var result []string
+	for _, m := range planned {
+		result = append(result, m.Id)
+	}
+
+	return result, nil
+}
+
+// MigrateDown rolls back at most max migrations.
+func MigrateDown(conf config.Config, max int) (int, error) {
+	db, err := openDB(conf)
+	if err != nil {
+		return 0, fmt.Errorf("sql open: %v", err)
+	}
+	defer db.Close()
+
+	return migrate.ExecMax(db, sqlMigrateDialect, migrationSource(), migrate.Down, max)
+}
 
-	return migrate.Exec(db, "postgres", migrationSource, migrate.Up)
+func migrationSource() *migrate.MemoryMigrationSource {
+	return &migrate.MemoryMigrationSource{Migrations: migrations.All()}
 }