Repository: supabase/cli
Pull request #3652 (2025-06-04 10:26:14)

fix: update default postgres major version to 17
## What kind of change does this PR introduce?

Bug fix

## What is the new behavior?

Bump default postgres version to 17.

## Additional context

Add any other context or screenshots.



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)
+}
diff --git a/pkg/config/templates/config.toml b/pkg/config/templates/config.toml
index c8aa68ad..b1dfdfe6 100644
--- a/pkg/config/templates/config.toml
+++ b/pkg/config/templates/config.toml
@@ -28,7 +28,7 @@ port = 54322
 shadow_port = 54320
 # The database major version to use. This has to be the same as your remote database's. Run `SHOW
 # server_version;` on the remote database to check.
-major_version = 15
+major_version = 17
 
 [db.pooler]
 enabled = false


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)
+	})
+}
diff --git a/pkg/config/testdata/config.toml b/pkg/config/testdata/config.toml
index 98ad26ec..6f1d814c 100644
--- a/pkg/config/testdata/config.toml
+++ b/pkg/config/testdata/config.toml
@@ -28,7 +28,7 @@ port = 54322
 shadow_port = 54320
 # The database major version to use. This has to be the same as your remote database's. Run `SHOW
 # server_version;` on the remote database to check.
-major_version = 15
+major_version = 17
 
 [db.migrations]
 # Specifies an ordered list of schema files that describe your database.