Repository: ariga/atlas
Pull request #932 (2022-07-11 07:59:29)

cmd/atlas/cmdapi: add migrate status command
Adds `atlas migrate status` command to print information about the migration status of a connected database.

![image](https://user-images.githubusercontent.com/12862103/178210554-eeec6003-5341-4c2c-9eaa-d1a3ac76c4c7.png)
![image](https://user-images.githubusercontent.com/12862103/178210585-67aa5c84-0e48-4668-b017-22fc07d28776.png)
![image](https://user-images.githubusercontent.com/12862103/178210630-c4dc8bcd-b352-4fb7-a68f-5fb0478a177c.png)


Patch:
diff --git a/cmd/atlas/go.mod b/cmd/atlas/go.mod
index 55c588b8..afdd308b 100644
--- a/cmd/atlas/go.mod
+++ b/cmd/atlas/go.mod
@@ -3,7 +3,7 @@ module ariga.io/atlas/cmd/atlas
 go 1.18
 
 require (
-	ariga.io/atlas v0.4.3-0.20220710113219-c40b374362d5
+	ariga.io/atlas v0.4.3-0.20220711075326-c3fb0f64fd0e
 	entgo.io/ent v0.10.2-0.20220502113020-4ac82f5bb3f0
 	github.com/fatih/color v1.13.0
 	github.com/go-sql-driver/mysql v1.6.0
diff --git a/cmd/atlas/go.sum b/cmd/atlas/go.sum
index bc292ece..bbcc777b 100644
--- a/cmd/atlas/go.sum
+++ b/cmd/atlas/go.sum
@@ -1,5 +1,5 @@
-ariga.io/atlas v0.4.3-0.20220710113219-c40b374362d5 h1:ShgRQTOV4v7f0Lq8XNwaACj+pCvfNQGBath5+QhR1lY=
-ariga.io/atlas v0.4.3-0.20220710113219-c40b374362d5/go.mod h1:ofVetkJqlaWle3mvYmaS2uyFGFcc7dSq436tmxa/Mzk=
+ariga.io/atlas v0.4.3-0.20220711075326-c3fb0f64fd0e h1:BFNDhM1I7hZrgS1k2UOUm1L9btGzB4L3dHsgDsTad7Q=
+ariga.io/atlas v0.4.3-0.20220711075326-c3fb0f64fd0e/go.mod h1:ofVetkJqlaWle3mvYmaS2uyFGFcc7dSq436tmxa/Mzk=
 entgo.io/ent v0.10.2-0.20220502113020-4ac82f5bb3f0 h1:qHA4+ANAzDj6BcDLxNgZuzKxFre/RI9r5wwsI2O+1M4=
 entgo.io/ent v0.10.2-0.20220502113020-4ac82f5bb3f0/go.mod h1:Zh61BPvB+cL6VWEyN8f1YoDacrMjQf2KDlDeX26xq2k=
 github.com/DATA-DOG/go-sqlmock v1.5.0 h1:Shsta01QNfFxHCfpW6YH2STWB0MudeXXEWMr20OEh60=
diff --git a/cmd/atlas/internal/cmdapi/migrate.go b/cmd/atlas/internal/cmdapi/migrate.go
index 5fa29837..ec212bda 100644
--- a/cmd/atlas/internal/cmdapi/migrate.go
+++ b/cmd/atlas/internal/cmdapi/migrate.go
@@ -19,6 +19,7 @@ import (
 
 	"ariga.io/atlas/cmd/atlas/internal/ci"
 	entmigrate "ariga.io/atlas/cmd/atlas/internal/migrate"
+	"ariga.io/atlas/cmd/atlas/internal/migrate/ent/revision"
 	"ariga.io/atlas/sql/migrate"
 	"ariga.io/atlas/sql/schema"
 	"ariga.io/atlas/sql/sqlcheck"
@@ -159,6 +160,16 @@ This command should be used whenever a manual change in the migration directory
 		PreRunE: migrateFlagsFromEnv,
 		RunE:    CmdMigrateNewRun,
 	}
+	// MigrateStatusCmd represents the 'atlas migrate status' command.
+	MigrateStatusCmd = &cobra.Command{
+		Use:   "status [flags]",
+		Short: "Get information about the current migration status.",
+		Long:  `'atlas migrate status' reports information about the current status of a connected database compared to the migration directory.`,
+		Example: `  atlas migrate status --url mysql://user:pass@localhost:3306/
+  atlas migrate status --url mysql://user:pass@localhost:3306/ --dir file:///path/to/migration/directory`,
+		PreRunE: migrateFlagsFromEnv,
+		RunE:    CmdMigrateStatusRun,
+	}
 	// MigrateValidateCmd represents the 'atlas migrate validate' command.
 	MigrateValidateCmd = &cobra.Command{
 		Use:   "validate [flags]",
@@ -196,6 +207,7 @@ func init() {
 	MigrateCmd.AddCommand(MigrateHashCmd)
 	MigrateCmd.AddCommand(MigrateNewCmd)
 	MigrateCmd.AddCommand(MigrateValidateCmd)
+	MigrateCmd.AddCommand(MigrateStatusCmd)
 	MigrateCmd.AddCommand(MigrateLintCmd)
 	// Reusable flags.
 	urlFlag := func(f *string, name, short string, set *pflag.FlagSet) {
@@ -222,6 +234,8 @@ func init() {
 	cobra.CheckErr(MigrateDiffCmd.MarkFlagRequired(migrateFlagTo))
 	// Validate flags.
 	urlFlag(&MigrateFlags.DevURL, migrateFlagDevURL, "", MigrateValidateCmd.Flags())
+	// Status flags.
+	urlFlag(&MigrateFlags.URL, migrateFlagURL, "u", MigrateStatusCmd.Flags())
 	// Lint flags.
 	urlFlag(&MigrateFlags.DevURL, migrateFlagDevURL, "", MigrateLintCmd.Flags())
 	MigrateLintCmd.PersistentFlags().StringVarP(&MigrateFlags.Lint.Format, migrateFlagLog, "", "", "custom logging using a Go template")
@@ -254,6 +268,7 @@ func CmdMigrateApplyRun(cmd *cobra.Command, args []string) error {
 	if err != nil {
 		return err
 	}
+	defer c.Close()
 	// Get the correct log format and destination. Currently, only os.Stdout is supported.
 	l, err := logFormat(cmd.OutOrStdout())
 	if err != nil {
@@ -396,6 +411,106 @@ func CmdMigrateNewRun(_ *cobra.Command, args []string) error {
 	return migrate.NewPlanner(nil, dir, migrate.WithFormatter(f)).WritePlan(&migrate.Plan{Name: name})
 }
 
+// CmdMigrateStatusRun is the command executed when running the CLI with 'migrate status' args.
+func CmdMigrateStatusRun(cmd *cobra.Command, _ []string) error {
+	// Open the migration directory.
+	dir, err := dir()
+	if err != nil {
+		return err
+	}
+	sc := dir.(migrate.Scanner) // all supported migration directories implement the migrate.Scanner
+	avail, err := sc.Files()
+	if err != nil {
+		return err
+	}
+	// Open a client to the database.
+	client, err := sqlclient.Open(cmd.Context(), MigrateFlags.URL)
+	if err != nil {
+		return err
+	}
+	defer client.Close()
+	if ok, err := revisionsTableExists(cmd.Context(), client); !ok || err != nil {
+		if err != nil {
+			return err
+		}
+		return statusPrint(cmd.OutOrStdout(), sc, avail, nil, nil)
+	}
+	// Currently, only in DB revisions are supported.
+	opts := []entmigrate.Option{entmigrate.WithSchema(MigrateFlags.RevisionSchema)}
+	rrw, err := entmigrate.NewEntRevisions(client, opts...)
+	if err != nil {
+		return err
+	}
+	if err := rrw.Init(cmd.Context()); err != nil {
+		return err
+	}
+	// Executor can give us insights on the revision state.
+	ex, err := migrate.NewExecutor(client.Driver, dir, rrw)
+	if err != nil {
+		return err
+	}
+	dirty := migrate.DirtyError{}
+	pending, err := ex.Pending(context.Background())
+	if err != nil && !errors.As(err, &dirty) && !errors.Is(err, migrate.ErrNoPendingFiles) {
+		return err
+	}
+	if errors.As(err, &dirty) {
+		var c func(string, ...interface{}) string
+		switch dirty.State {
+		case migrate.StateOngoing:
+			c = yellow
+		case migrate.StateError:
+			c = red
+		}
+		s := c(strings.ToUpper(dirty.State))
+		msgPrint(cmd.OutOrStdout(), s, fmt.Sprintf("Dirty migration state: version %s has state %s", dirty.Version, s))
+		return nil
+	}
+	return statusPrint(cmd.OutOrStdout(), sc, avail, avail[:len(pending)+1], pending)
+}
+
+func headerPrint(out io.Writer, state string) {
+	fmt.Fprintf(out, "Migration Status: %s\n", state)
+}
+
+func msgPrint(out io.Writer, state, msg string) {
+	headerPrint(out, state)
+	fmt.Fprintln(out, msg)
+}
+
+func statusPrint(out io.Writer, sc migrate.Scanner, avail, applied, pending []migrate.File) (err error) {
+	var cur, next, state string
+	if len(applied) == 0 {
+		cur = "No version applied yet"
+	} else {
+		cur, err = sc.Version(applied[len(applied)-1])
+		if err != nil {
+			return err
+		}
+	}
+	if len(pending) == 0 {
+		state = green("OK")
+		next = "Already at latest version"
+	} else {
+		state = cyan("PENDING")
+		next, err = sc.Version(pending[0])
+		if err != nil {
+			return err
+		}
+	}
+	headerPrint(out, state)
+	fmt.Fprintf(out, "%s%s Current Version:\t%s\n", indent2, dash, cyan(cur))
+	fmt.Fprintf(out, "%s%s Next Version:\t%s\n", indent2, dash, cyan(next))
+	fmt.Fprintf(out, "%s%s Available:\t\t%s\n", indent2, dash, cyan(strconv.Itoa(len(avail))))
+	fmt.Fprintf(out, "%s%s Executed:\t\t%s\n", indent2, dash, cyan(strconv.Itoa(len(applied))))
+	c := cyan
+	if len(pending) == 0 {
+		c = green
+	}
+	fmt.Fprintf(out, "%s%s Pending:\t\t%s\n", indent2, dash, c(strconv.Itoa(len(pending))))
+	return nil
+}
+
 // CmdMigrateValidateRun is the command executed when running the CLI with 'migrate validate' args.
 func CmdMigrateValidateRun(cmd *cobra.Command, _ []string) error {
 	// Validating the integrity is done by the PersistentPreRun already.
@@ -480,6 +595,36 @@ func CmdMigrateLintRun(cmd *cobra.Command, _ []string) error {
 	return r.Run(cmd.Context())
 }
 
+func revisionsTableExists(ctx context.Context, c *sqlclient.Client) (bool, error) {
+	// Connect to the given schema name.
+	sc, err := sqlclient.Open(ctx, MigrateFlags.URL, sqlclient.OpenSchema(MigrateFlags.RevisionSchema))
+	switch {
+	case err != nil && !errors.Is(err, sqlclient.ErrUnsupported):
+		return false, err
+	case errors.Is(err, sqlclient.ErrUnsupported):
+		// If the driver does not support changing the schema use the existing connection.
+		sc = c
+	case err == nil:
+		// Connecting attempt to the schema was successful, make sure to close it.
+		defer sc.Close()
+	}
+	// Inspect schema and check if the table does already exist.
+	s, err := sc.InspectSchema(ctx, "", &schema.InspectOptions{Tables: []string{revision.Table}})
+	switch {
+	case err != nil && !schema.IsNotExistError(err):
+		return false, err
+	case schema.IsNotExistError(err):
+		// Schema does not exist.
+		return false, nil
+	}
+	if _, ok := s.Table(revision.Table); !ok {
+		// Table does not exist.
+		return false, nil
+	}
+	// Schema and Table are present.
+	return true, nil
+}
+
 // dir returns a migrate.Dir to use as migration directory. For now only local directories are supported.
 func dir() (migrate.Dir, error) {
 	parts := strings.SplitN(MigrateFlags.DirURL, "://", 2)
@@ -612,6 +757,7 @@ const (
 	formatGoose         = "goose"
 	formatFlyway        = "flyway"
 	formatLiquibase     = "liquibase"
+	formatDbmate        = "dbmate"
 )
 
 func formatter() (migrate.Formatter, error) {
@@ -626,6 +772,8 @@ func formatter() (migrate.Formatter, error) {
 		return sqltool.FlywayFormatter, nil
 	case formatLiquibase:
 		return sqltool.LiquibaseFormatter, nil
+	case formatDbmate:
+		return sqltool.DbmateFormatter, nil
 	default:
 		return nil, fmt.Errorf("unknown format %q", MigrateFlags.Format)
 	}
@@ -647,6 +795,8 @@ type LogTTY struct {
 
 var (
 	cyan    = color.CyanString
+	green   = color.HiGreenString
+	red     = color.HiRedString
 	yellow  = color.YellowString
 	dash    = yellow("--")
 	arr     = cyan("->")
diff --git a/sql/sqltool/tool.go b/sql/sqltool/tool.go
index 08d4cda4..5e6fa02c 100644
--- a/sql/sqltool/tool.go
+++ b/sql/sqltool/tool.go
@@ -51,6 +51,14 @@ var (
 {{ with $change.Reverse }}--rollback: {{ . }};{{ end }}
 {{ end }}`,
 	)
+	// DbmateFormatter returns a migrate.Formatter compatible with amacneil/dbmate.
+	DbmateFormatter = templateFormatter(
+		"{{ now }}{{ with .Name }}_{{ . }}{{ end }}.sql",
+		`-- migrate:up
+{{ range .Changes }}{{ with .Comment }}-- {{ println . }}{{ end }}{{ printf "%s;\n" .Cmd }}{{ end }}
+-- migrate:down
+{{ range rev .Changes }}{{ if .Reverse }}{{ with .Comment }}-- reverse: {{ println . }}{{ end }}{{ printf "%s;\n" .Reverse }}{{ end }}{{ end }}`,
+	)
 )
 
 // GolangMigrateDir wraps a migrate.LocalDir and provides a migrate.Scanner


Test patch:
diff --git a/cmd/atlas/internal/cmdapi/migrate_test.go b/cmd/atlas/internal/cmdapi/migrate_test.go
index 10ab7ea8..7489add6 100644
--- a/cmd/atlas/internal/cmdapi/migrate_test.go
+++ b/cmd/atlas/internal/cmdapi/migrate_test.go
@@ -236,6 +236,12 @@ func TestMigrate_New(t *testing.T) {
 	require.FileExists(t, filepath.Join(p, v+"_liquibase.sql"))
 	require.Equal(t, 9, countFiles(t, p))
 
+	s, err = runCmd(Root, "migrate", "new", "dbmate", "--dir", "file://"+p, "--format", formatDbmate)
+	require.Zero(t, s)
+	require.NoError(t, err)
+	require.FileExists(t, filepath.Join(p, v+"_dbmate.sql"))
+	require.Equal(t, 10, countFiles(t, p))
+
 	f := filepath.Join("testdata", "mysql", "new.sql")
 	require.NoError(t, os.WriteFile(f, []byte("contents"), 0600))
 	t.Cleanup(func() { os.Remove(f) })
diff --git a/sql/sqltool/tool_test.go b/sql/sqltool/tool_test.go
index 0f0f245a..abaa7f2a 100644
--- a/sql/sqltool/tool_test.go
+++ b/sql/sqltool/tool_test.go
@@ -98,6 +98,24 @@ CREATE TABLE t2(c int);
 `, v, v),
 			},
 		},
+		{
+			"amacneil/dbmate",
+			sqltool.DbmateFormatter,
+			map[string]string{
+				v + "_tooling-plan.sql": `-- migrate:up
+-- create table t1
+CREATE TABLE t1(c int);
+-- create table t2
+CREATE TABLE t2(c int);
+
+-- migrate:down
+-- reverse: create table t2
+DROP TABLE t2;
+-- reverse: create table t1
+DROP TABLE t1 IF EXISTS;
+`,
+			},
+		},
 	} {
 		t.Run(tt.name, func(t *testing.T) {
 			d := dir(t)