Repository: supabase/cli
Pull request #3635 (2025-05-30 10:47:56)

feat: alias migration down command as reset
## What kind of change does this PR introduce?

relates to https://github.com/supabase/cli/issues/611

## What is the new behavior?

Adds an alias for `db reset` so that it's easier to revert the last n migrations without inputting the full version string.

```
$ supabase db start
Seeding globals from roles.sql...
Applying migration 20250510162534_table.sql...
Applying migration 20250510162754_index.sql...
NOTICE (42P06): schema "supabase_migrations" already exists, skipping
Seeding data from supabase/seed.sql...
A new version of Supabase CLI is available: v2.23.4 (currently installed v)
We recommend updating regularly for new features and bug fixes: https://supabase.com/docs/guides/cli/getting-started#updating-the-supabase-cli
$ supabase migrations down
Connecting to local database...
Resetting local database to version: 20250510162534
Recreating database...
```

## Additional context

https://x.com/nicolaygerold/status/1928396644241879115


coveralls: ## Pull Request Test Coverage Report for [Build 15439675413](https://coveralls.io/builds/73990908)


### Details

* **17** of **44**   **(38.64%)**  changed or added relevant lines in **4** files are covered.
* **5** unchanged lines in **1** file lost coverage.
* Overall coverage decreased (**-0.06%**) to **54.848%**

---

|  Changes Missing Coverage | Covered Lines | Changed/Added Lines | % |
| :-----|--------------|--------|---: |
| [cmd/db.go](https://coveralls.io/builds/73990908/source?filename=cmd%2Fdb.go#L198) | 0 | 3 | 0.0%
| [internal/migration/down/down.go](https://coveralls.io/builds/73990908/source?filename=internal%2Fmigration%2Fdown%2Fdown.go#L19) | 15 | 19 | 78.95%
| [cmd/migration.go](https://coveralls.io/builds/73990908/source?filename=cmd%2Fmigration.go#L100) | 0 | 10 | 0.0%
| [internal/db/reset/reset.go](https://coveralls.io/builds/73990908/source?filename=internal%2Fdb%2Freset%2Freset.go#L43) | 2 | 12 | 16.67%
<!-- | **Total:** | **17** | **44** | **38.64%** | -->

|  Files with Coverage Reduction | New Missed Lines | % |
| :-----|--------------|--: |
| [internal/gen/keys/keys.go](https://coveralls.io/builds/73990908/source?filename=internal%2Fgen%2Fkeys%2Fkeys.go#L87) | 5 | 12.9% |
<!-- | **Total:** | **5** |  | -->

|  Totals | [![Coverage Status](https://coveralls.io/builds/73990908/badge)](https://coveralls.io/builds/73990908) |
| :-- | --: |
| Change from base [Build 15438467837](https://coveralls.io/builds/73989809): |  -0.06% |
| Covered Lines: | 5979 |
| Relevant Lines: | 10901 |

---
##### 💛  - [Coveralls](https://coveralls.io)


Patch:
diff --git a/cmd/db.go b/cmd/db.go
index 948f77be..8beec27e 100644
--- a/cmd/db.go
+++ b/cmd/db.go
@@ -195,7 +195,7 @@ var (
 			if noSeed {
 				utils.Config.Db.Seed.Enabled = false
 			}
-			return reset.Run(cmd.Context(), migrationVersion, flags.DbConfig, afero.NewOsFs())
+			return reset.Run(cmd.Context(), migrationVersion, nLastVersion, flags.DbConfig, afero.NewOsFs())
 		},
 	}
 
@@ -319,6 +319,8 @@ func init() {
 	resetFlags.BoolVar(&noSeed, "no-seed", false, "Skip running the seed script after reset.")
 	dbResetCmd.MarkFlagsMutuallyExclusive("db-url", "linked", "local")
 	resetFlags.StringVar(&migrationVersion, "version", "", "Reset up to the specified version.")
+	resetFlags.UintVar(&nLastVersion, "last", 0, "Reset up to the last n migration versions.")
+	dbResetCmd.MarkFlagsMutuallyExclusive("version", "last")
 	dbCmd.AddCommand(dbResetCmd)
 	// Build lint command
 	lintFlags := dbLintCmd.Flags()
diff --git a/cmd/migration.go b/cmd/migration.go
index 30b7716a..fc92e40d 100644
--- a/cmd/migration.go
+++ b/cmd/migration.go
@@ -8,6 +8,7 @@ import (
 	"github.com/spf13/afero"
 	"github.com/spf13/cobra"
 	"github.com/spf13/viper"
+	"github.com/supabase/cli/internal/migration/down"
 	"github.com/supabase/cli/internal/migration/fetch"
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/migration/new"
@@ -90,6 +91,17 @@ var (
 		},
 	}
 
+	nLastVersion uint
+
+	migrationDownCmd = &cobra.Command{
+		Use:   "down",
+		Short: "Resets applied migrations up to the last n versions",
+		Args:  cobra.NoArgs,
+		RunE: func(cmd *cobra.Command, args []string) error {
+			return down.Run(cmd.Context(), nLastVersion, flags.DbConfig, afero.NewOsFs())
+		},
+	}
+
 	migrationFetchCmd = &cobra.Command{
 		Use:   "fetch",
 		Short: "Fetch migration files from history table",
@@ -141,6 +153,13 @@ func init() {
 	upFlags.Bool("local", true, "Applies pending migrations to the local database.")
 	migrationUpCmd.MarkFlagsMutuallyExclusive("db-url", "linked", "local")
 	migrationCmd.AddCommand(migrationUpCmd)
+	downFlags := migrationDownCmd.Flags()
+	downFlags.UintVar(&nLastVersion, "last", 1, "Reset up to the last n migration versions.")
+	downFlags.String("db-url", "", "Resets applied migrations on the database specified by the connection string (must be percent-encoded).")
+	downFlags.Bool("linked", false, "Resets applied migrations on the linked project.")
+	downFlags.Bool("local", true, "Resets applied migrations on the local database.")
+	migrationDownCmd.MarkFlagsMutuallyExclusive("db-url", "linked", "local")
+	migrationCmd.AddCommand(migrationDownCmd)
 	// Build up command
 	fetchFlags := migrationFetchCmd.Flags()
 	fetchFlags.String("db-url", "", "Fetches migrations from the database specified by the connection string (must be percent-encoded).")
diff --git a/internal/db/reset/reset.go b/internal/db/reset/reset.go
index 3455ffbc..5613b64b 100644
--- a/internal/db/reset/reset.go
+++ b/internal/db/reset/reset.go
@@ -23,6 +23,7 @@ import (
 	"github.com/supabase/cli/internal/db/start"
 	"github.com/supabase/cli/internal/gen/keys"
 	"github.com/supabase/cli/internal/migration/apply"
+	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/migration/repair"
 	"github.com/supabase/cli/internal/seed/buckets"
 	"github.com/supabase/cli/internal/utils"
@@ -30,7 +31,7 @@ import (
 	"github.com/supabase/cli/pkg/vault"
 )
 
-func Run(ctx context.Context, version string, config pgconn.Config, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {
+func Run(ctx context.Context, version string, last uint, config pgconn.Config, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {
 	if len(version) > 0 {
 		if _, err := strconv.Atoi(version); err != nil {
 			return errors.New(repair.ErrInvalidVersion)
@@ -38,6 +39,17 @@ func Run(ctx context.Context, version string, config pgconn.Config, fsys afero.F
 		if _, err := repair.GetMigrationFile(version, fsys); err != nil {
 			return err
 		}
+	} else if last > 0 {
+		localMigrations, err := list.LoadLocalVersions(fsys)
+		if err != nil {
+			return err
+		}
+		if total := uint(len(localMigrations)); last < total {
+			version = localMigrations[total-last-1]
+		} else {
+			// Negative skips all migrations
+			version = "-"
+		}
 	}
 	if !utils.IsLocalDatabase(config) {
 		msg := "Do you want to reset the remote database?"
diff --git a/internal/migration/down/down.go b/internal/migration/down/down.go
new file mode 100644
index 00000000..300bb1b6
--- /dev/null
+++ b/internal/migration/down/down.go
@@ -0,0 +1,33 @@
+package down
+
+import (
+	"context"
+	"fmt"
+
+	"github.com/go-errors/errors"
+	"github.com/jackc/pgconn"
+	"github.com/jackc/pgx/v4"
+	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/db/reset"
+	"github.com/supabase/cli/internal/utils"
+	"github.com/supabase/cli/pkg/migration"
+)
+
+func Run(ctx context.Context, last uint, config pgconn.Config, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {
+	conn, err := utils.ConnectByConfig(ctx, config, options...)
+	if err != nil {
+		return err
+	}
+	defer conn.Close(context.Background())
+	remoteMigrations, err := migration.ListRemoteMigrations(ctx, conn)
+	if err != nil {
+		return err
+	}
+	total := uint(len(remoteMigrations))
+	if total <= last {
+		utils.CmdSuggestion = fmt.Sprintf("Try %s if you want to revert all migrations.", utils.Aqua("supabase db reset"))
+		return errors.Errorf("--last must be smaller than total applied migrations: %d", total)
+	}
+	version := remoteMigrations[total-last-1]
+	return reset.Run(ctx, version, 0, config, fsys)
+}


Test patch:
diff --git a/internal/db/reset/reset_test.go b/internal/db/reset/reset_test.go
index bb87bd91..b331ff34 100644
--- a/internal/db/reset/reset_test.go
+++ b/internal/db/reset/reset_test.go
@@ -96,7 +96,7 @@ func TestResetCommand(t *testing.T) {
 			Reply(http.StatusOK).
 			JSON([]storage.BucketResponse{})
 		// Run test
-		err := Run(context.Background(), "", dbConfig, fsys, conn.Intercept)
+		err := Run(context.Background(), "", 0, dbConfig, fsys, conn.Intercept)
 		// Check error
 		assert.NoError(t, err)
 		assert.Empty(t, apitest.ListUnmatchedRequests())
@@ -106,7 +106,7 @@ func TestResetCommand(t *testing.T) {
 		// Setup in-memory fs
 		fsys := afero.NewMemMapFs()
 		// Run test
-		err := Run(context.Background(), "", pgconn.Config{Host: "db.supabase.co"}, fsys)
+		err := Run(context.Background(), "", 0, pgconn.Config{Host: "db.supabase.co"}, fsys)
 		// Check error
 		assert.ErrorIs(t, err, context.Canceled)
 	})
@@ -116,7 +116,7 @@ func TestResetCommand(t *testing.T) {
 		// Setup in-memory fs
 		fsys := afero.NewMemMapFs()
 		// Run test
-		err := Run(context.Background(), "", pgconn.Config{Host: "db.supabase.co"}, fsys)
+		err := Run(context.Background(), "", 0, pgconn.Config{Host: "db.supabase.co"}, fsys)
 		// Check error
 		assert.ErrorContains(t, err, "invalid port (outside range)")
 	})
@@ -131,7 +131,7 @@ func TestResetCommand(t *testing.T) {
 			Get("/v" + utils.Docker.ClientVersion() + "/containers").
 			Reply(http.StatusNotFound)
 		// Run test
-		err := Run(context.Background(), "", dbConfig, fsys)
+		err := Run(context.Background(), "", 0, dbConfig, fsys)
 		// Check error
 		assert.ErrorIs(t, err, utils.ErrNotRunning)
 		assert.Empty(t, apitest.ListUnmatchedRequests())
@@ -153,7 +153,7 @@ func TestResetCommand(t *testing.T) {
 			Delete("/v" + utils.Docker.ClientVersion() + "/containers/" + utils.DbId).
 			ReplyError(errors.New("network error"))
 		// Run test
-		err := Run(context.Background(), "", dbConfig, fsys)
+		err := Run(context.Background(), "", 0, dbConfig, fsys)
 		// Check error
 		assert.ErrorContains(t, err, "network error")
 		assert.Empty(t, apitest.ListUnmatchedRequests())
diff --git a/internal/migration/down/down_test.go b/internal/migration/down/down_test.go
new file mode 100644
index 00000000..b5dfe9bf
--- /dev/null
+++ b/internal/migration/down/down_test.go
@@ -0,0 +1,76 @@
+package down
+
+import (
+	"context"
+	"os"
+	"path/filepath"
+	"testing"
+
+	"github.com/jackc/pgconn"
+	"github.com/spf13/afero"
+	"github.com/stretchr/testify/assert"
+	"github.com/stretchr/testify/require"
+	"github.com/supabase/cli/internal/utils"
+	"github.com/supabase/cli/pkg/migration"
+	"github.com/supabase/cli/pkg/pgtest"
+)
+
+var dbConfig = pgconn.Config{
+	Host:     "127.0.0.1",
+	Port:     5432,
+	User:     "admin",
+	Password: "password",
+	Database: "postgres",
+}
+
+func TestMigrationsDown(t *testing.T) {
+	t.Run("resets last n migrations", func(t *testing.T) {
+		// Setup in-memory fs
+		fsys := afero.NewMemMapFs()
+		files := []string{
+			filepath.Join(utils.MigrationsDir, "20221201000000_test.sql"),
+			filepath.Join(utils.MigrationsDir, "20221201000001_test.sql"),
+			filepath.Join(utils.MigrationsDir, "20221201000002_test.sql"),
+		}
+		for _, path := range files {
+			require.NoError(t, afero.WriteFile(fsys, path, []byte(""), 0644))
+		}
+		// Setup mock postgres
+		conn := pgtest.NewConn()
+		defer conn.Close(t)
+		conn.Query(migration.LIST_MIGRATION_VERSION).
+			Reply("SELECT 2", []interface{}{"20221201000000"}, []interface{}{"20221201000001"})
+		// Run test
+		err := Run(context.Background(), 1, dbConfig, fsys, conn.Intercept)
+		// Check error
+		assert.ErrorIs(t, err, context.Canceled)
+	})
+
+	t.Run("throws error on out of range", func(t *testing.T) {
+		// Setup in-memory fs
+		fsys := afero.NewMemMapFs()
+		// Setup mock postgres
+		conn := pgtest.NewConn()
+		defer conn.Close(t)
+		conn.Query(migration.LIST_MIGRATION_VERSION).
+			Reply("SELECT 2", []interface{}{"20221201000000"}, []interface{}{"20221201000001"})
+		// Run test
+		err := Run(context.Background(), 2, dbConfig, fsys, conn.Intercept)
+		// Check error
+		assert.ErrorContains(t, err, "--last must be smaller than total applied migrations: 2")
+	})
+
+	t.Run("throws error on missing version", func(t *testing.T) {
+		// Setup in-memory fs
+		fsys := afero.NewMemMapFs()
+		// Setup mock postgres
+		conn := pgtest.NewConn()
+		defer conn.Close(t)
+		conn.Query(migration.LIST_MIGRATION_VERSION).
+			Reply("SELECT 2", []interface{}{"20221201000000"}, []interface{}{"20221201000001"})
+		// Run test
+		err := Run(context.Background(), 1, dbConfig, fsys, conn.Intercept)
+		// Check error
+		assert.ErrorIs(t, err, os.ErrNotExist)
+	})
+}