Repository: supabase/cli
Pull request #1069 (2023-05-05 06:59:38)

feat: implement migration up command
## What kind of change does this PR introduce?

feature

## What is the current behavior?

follow up https://github.com/supabase/cli/issues/1055#issuecomment-1535019708

## What is the new behavior?

```bash
$ supabase migration up
Applying migration 20220730063745_full.sql...
Local database is up to date.
```

## Additional context

Add any other context or screenshots.


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

* **40** of **61**   **(65.57%)**  changed or added relevant lines in **4** files are covered.
* **1** unchanged line in **1** file lost coverage.
* Overall coverage decreased (**-0.2%**) to **60.914%**

---

|  Changes Missing Coverage | Covered Lines | Changed/Added Lines | % |
| :-----|--------------|--------|---: |
| [cmd/migration.go](https://coveralls.io/builds/59726334/source?filename=cmd%2Fmigration.go#L76) | 6 | 13 | 46.15%
| [internal/migration/up/up.go](https://coveralls.io/builds/59726334/source?filename=internal%2Fmigration%2Fup%2Fup.go#L17) | 20 | 34 | 58.82%
<!-- | **Total:** | **40** | **61** | **65.57%** | -->

|  Files with Coverage Reduction | New Missed Lines | % |
| :-----|--------------|--: |
| [internal/testing/fstest/open.go](https://coveralls.io/builds/59726334/source?filename=internal%2Ftesting%2Ffstest%2Fopen.go#L27) | 1 | 80.0% |
<!-- | **Total:** | **1** |  | -->

|  Totals | [![Coverage Status](https://coveralls.io/builds/59726334/badge)](https://coveralls.io/builds/59726334) |
| :-- | --: |
| Change from base [Build 4914802403](https://coveralls.io/builds/59726040): |  -0.2% |
| Covered Lines: | 4398 |
| Relevant Lines: | 7220 |

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


Patch:
diff --git a/cmd/migration.go b/cmd/migration.go
index e5db5514..510a07bb 100644
--- a/cmd/migration.go
+++ b/cmd/migration.go
@@ -1,6 +1,7 @@
 package cmd
 
 import (
+	"fmt"
 	"os"
 	"os/signal"
 
@@ -12,6 +13,7 @@ import (
 	"github.com/supabase/cli/internal/migration/list"
 	"github.com/supabase/cli/internal/migration/new"
 	"github.com/supabase/cli/internal/migration/repair"
+	"github.com/supabase/cli/internal/migration/up"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/credentials"
 )
@@ -56,7 +58,7 @@ var (
 
 	migrationRepairCmd = &cobra.Command{
 		Use:   "repair <version>",
-		Short: "Repairs the migration history table",
+		Short: "Repair the migration history table",
 		Args:  cobra.ExactArgs(1),
 		RunE: func(cmd *cobra.Command, args []string) error {
 			fsys := afero.NewOsFs()
@@ -67,22 +69,39 @@ var (
 			return repair.Run(ctx, dbConfig, args[0], targetStatus.Value)
 		},
 	}
+
+	migrationUpCmd = &cobra.Command{
+		Use:   "up",
+		Short: "Apply pending migrations to local database",
+		RunE: func(cmd *cobra.Command, args []string) error {
+			ctx, _ := signal.NotifyContext(cmd.Context(), os.Interrupt)
+			return up.Run(ctx, afero.NewOsFs())
+		},
+		PostRun: func(cmd *cobra.Command, args []string) {
+			fmt.Println("Local database is up to date.")
+		},
+	}
 )
 
 func init() {
-	migrationCmd.PersistentFlags().StringVar(&dbUrl, "db-url", "", "connect using the specified database url")
 	// Build list command
 	listFlags := migrationListCmd.Flags()
+	listFlags.StringVar(&dbUrl, "db-url", "", "connect using the specified database url")
 	listFlags.StringVarP(&dbPassword, "password", "p", "", "Password to your remote Postgres database.")
 	cobra.CheckErr(viper.BindPFlag("DB_PASSWORD", listFlags.Lookup("password")))
+	migrationListCmd.MarkFlagsMutuallyExclusive("db-url", "password")
 	migrationCmd.AddCommand(migrationListCmd)
 	// Build repair command
 	repairFlags := migrationRepairCmd.Flags()
 	repairFlags.Var(&targetStatus, "status", "Version status to update.")
 	cobra.CheckErr(migrationRepairCmd.MarkFlagRequired("status"))
+	repairFlags.StringVar(&dbUrl, "db-url", "", "connect using the specified database url")
 	repairFlags.StringVarP(&dbPassword, "password", "p", "", "Password to your remote Postgres database.")
 	cobra.CheckErr(viper.BindPFlag("DB_PASSWORD", repairFlags.Lookup("password")))
+	migrationRepairCmd.MarkFlagsMutuallyExclusive("db-url", "password")
 	migrationCmd.AddCommand(migrationRepairCmd)
+	// Build up command
+	migrationCmd.AddCommand(migrationUpCmd)
 	// Build new command
 	migrationCmd.AddCommand(migrationNewCmd)
 	rootCmd.AddCommand(migrationCmd)
diff --git a/internal/db/push/push.go b/internal/db/push/push.go
index ca402812..0d502431 100644
--- a/internal/db/push/push.go
+++ b/internal/db/push/push.go
@@ -2,24 +2,17 @@ package push
 
 import (
 	"context"
-	"errors"
 	"fmt"
 	"os"
-	"path/filepath"
 
 	"github.com/jackc/pgconn"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
 	"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/migration/up"
 	"github.com/supabase/cli/internal/utils"
 )
 
-var (
-	errConflict = errors.New("supabase_migrations.schema_migrations table conflicts with the contents of " + utils.Bold(utils.MigrationsDir) + ".")
-)
-
 func Run(ctx context.Context, dryRun bool, config pgconn.Config, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {
 	if dryRun {
 		fmt.Fprintln(os.Stderr, "DRY RUN: migrations will *not* be pushed to the database.")
@@ -29,7 +22,7 @@ func Run(ctx context.Context, dryRun bool, config pgconn.Config, fsys afero.Fs,
 		return err
 	}
 	defer conn.Close(context.Background())
-	pending, err := getPendingMigrations(ctx, conn, fsys)
+	pending, err := up.GetPendingMigrations(ctx, conn, fsys)
 	if err != nil {
 		return err
 	}
@@ -38,54 +31,15 @@ func Run(ctx context.Context, dryRun bool, config pgconn.Config, fsys afero.Fs,
 		return nil
 	}
 	// Push pending migrations
-	if !dryRun {
-		if err := repair.CreateMigrationTable(ctx, conn); err != nil {
-			return err
-		}
-	}
-	for _, filename := range pending {
-		if dryRun {
+	if dryRun {
+		for _, filename := range pending {
 			fmt.Fprintln(os.Stderr, "Would push migration "+utils.Bold(filename)+"...")
-			continue
 		}
-		if err := pushMigration(ctx, conn, filename, fsys); err != nil {
+	} else {
+		if err := apply.MigrateUp(ctx, conn, pending, fsys); err != nil {
 			return err
 		}
 	}
 	fmt.Println("Finished " + utils.Aqua("supabase db push") + ".")
 	return nil
 }
-
-func getPendingMigrations(ctx context.Context, conn *pgx.Conn, fsys afero.Fs) ([]string, error) {
-	remoteMigrations, err := list.LoadRemoteMigrations(ctx, conn)
-	if err != nil {
-		return nil, err
-	}
-	localMigrations, err := list.LoadLocalMigrations(fsys)
-	if err != nil {
-		return nil, err
-	}
-	// Check remote is in-sync or behind local
-	if len(remoteMigrations) > len(localMigrations) {
-		return nil, fmt.Errorf("%w; Found %d versions and %d migrations.", errConflict, len(remoteMigrations), len(localMigrations))
-	}
-	for i, remote := range remoteMigrations {
-		filename := localMigrations[i]
-		// LoadLocalMigrations guarantees we always have a match
-		local := utils.MigrateFilePattern.FindStringSubmatch(filename)[1]
-		if remote != local {
-			return nil, fmt.Errorf("%w; Expected version %s but found migration %s at index %d.", errConflict, remote, filename, i)
-		}
-	}
-	return localMigrations[len(remoteMigrations):], nil
-}
-
-func pushMigration(ctx context.Context, conn *pgx.Conn, filename string, fsys afero.Fs) error {
-	fmt.Fprintln(os.Stderr, "Pushing migration "+utils.Bold(filename)+"...")
-	path := filepath.Join(utils.MigrationsDir, filename)
-	migration, err := apply.NewMigrationFromFile(path, fsys)
-	if err != nil {
-		return err
-	}
-	return migration.ExecBatch(ctx, conn)
-}
diff --git a/internal/migration/apply/apply.go b/internal/migration/apply/apply.go
index 2e838a75..a0a28bac 100644
--- a/internal/migration/apply/apply.go
+++ b/internal/migration/apply/apply.go
@@ -22,24 +22,30 @@ func MigrateDatabase(ctx context.Context, conn *pgx.Conn, fsys afero.Fs) error {
 	if err != nil {
 		return err
 	}
-	// Apply migrations
-	for _, filename := range migrations {
-		if err := migrateUp(ctx, conn, filename, fsys); err != nil {
+	return MigrateUp(ctx, conn, migrations, fsys)
+}
+
+func MigrateUp(ctx context.Context, conn *pgx.Conn, pending []string, fsys afero.Fs) error {
+	if len(pending) > 0 {
+		if err := repair.CreateMigrationTable(ctx, conn); err != nil {
+			return err
+		}
+	}
+	for _, filename := range pending {
+		if err := applyMigration(ctx, conn, filename, fsys); err != nil {
 			return err
 		}
 	}
 	return nil
 }
 
-func migrateUp(ctx context.Context, conn *pgx.Conn, filename string, fsys afero.Fs) error {
+func applyMigration(ctx context.Context, conn *pgx.Conn, filename string, fsys afero.Fs) error {
 	fmt.Fprintln(os.Stderr, "Applying migration "+utils.Bold(filename)+"...")
 	path := filepath.Join(utils.MigrationsDir, filename)
 	migration, err := NewMigrationFromFile(path, fsys)
 	if err != nil {
 		return err
 	}
-	// Skip inserting to migration history table
-	migration.version = ""
 	return migration.ExecBatch(ctx, conn)
 }
 
diff --git a/internal/migration/up/up.go b/internal/migration/up/up.go
new file mode 100644
index 00000000..2399cfbf
--- /dev/null
+++ b/internal/migration/up/up.go
@@ -0,0 +1,55 @@
+package up
+
+import (
+	"context"
+	"errors"
+	"fmt"
+
+	"github.com/jackc/pgx/v4"
+	"github.com/spf13/afero"
+	"github.com/supabase/cli/internal/migration/apply"
+	"github.com/supabase/cli/internal/migration/list"
+	"github.com/supabase/cli/internal/utils"
+)
+
+var errConflict = errors.New("supabase_migrations.schema_migrations table conflicts with the contents of " + utils.Bold(utils.MigrationsDir) + ".")
+
+func Run(ctx context.Context, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {
+	if err := utils.LoadConfigFS(fsys); err != nil {
+		return err
+	}
+	conn, err := utils.ConnectLocalPostgres(ctx, "localhost", utils.Config.Db.Port, "postgres", options...)
+	if err != nil {
+		return err
+	}
+	defer conn.Close(context.Background())
+	pending, err := GetPendingMigrations(ctx, conn, fsys)
+	if err != nil {
+		return err
+	}
+	return apply.MigrateUp(ctx, conn, pending, fsys)
+}
+
+func GetPendingMigrations(ctx context.Context, conn *pgx.Conn, fsys afero.Fs) ([]string, error) {
+	remoteMigrations, err := list.LoadRemoteMigrations(ctx, conn)
+	if err != nil {
+		return nil, err
+	}
+	localMigrations, err := list.LoadLocalMigrations(fsys)
+	if err != nil {
+		return nil, err
+	}
+	// Check remote is in-sync or behind local
+	if len(remoteMigrations) > len(localMigrations) {
+		return nil, fmt.Errorf("%w; Found %d versions and %d migrations.", errConflict, len(remoteMigrations), len(localMigrations))
+	}
+	for i, remote := range remoteMigrations {
+		filename := localMigrations[i]
+		// LoadLocalMigrations guarantees we always have a match
+		local := utils.MigrateFilePattern.FindStringSubmatch(filename)[1]
+		if remote != local {
+			return nil, fmt.Errorf("%w; Expected version %s but found migration %s at index %d.", errConflict, remote, filename, i)
+		}
+	}
+	return localMigrations[len(remoteMigrations):], nil
+}


Test patch:
diff --git a/internal/db/push/push_test.go b/internal/db/push/push_test.go
index 952947ca..ed08b727 100644
--- a/internal/db/push/push_test.go
+++ b/internal/db/push/push_test.go
@@ -1,7 +1,6 @@
 package push
 
 import (
-	"bytes"
 	"context"
 	"path/filepath"
 	"testing"
@@ -15,7 +14,6 @@ import (
 	"github.com/supabase/cli/internal/migration/repair"
 	"github.com/supabase/cli/internal/testing/pgtest"
 	"github.com/supabase/cli/internal/utils"
-	"github.com/supabase/cli/internal/utils/parser"
 )
 
 var dbConfig = pgconn.Config{
@@ -123,163 +121,3 @@ func TestMigrationPush(t *testing.T) {
 		assert.ErrorContains(t, err, "At statement 0: "+repair.INSERT_MIGRATION_VERSION)
 	})
 }
-
-func TestPendingMigrations(t *testing.T) {
-	t.Run("finds pending migrations", func(t *testing.T) {
-		// Setup in-memory fs
-		fsys := afero.NewMemMapFs()
-		files := []string{
-			"20221201000000_test.sql",
-			"20221201000001_test.sql",
-			"20221201000002_test.sql",
-			"20221201000003_test.sql",
-		}
-		for _, name := range files {
-			path := filepath.Join(utils.MigrationsDir, name)
-			require.NoError(t, afero.WriteFile(fsys, path, []byte(""), 0644))
-		}
-		// Setup mock postgres
-		conn := pgtest.NewConn()
-		defer conn.Close(t)
-		conn.Query(list.LIST_MIGRATION_VERSION).
-			Reply("SELECT 2", []interface{}{"20221201000000"}, []interface{}{"20221201000001"})
-		// Connect to mock
-		ctx := context.Background()
-		mock, err := utils.ConnectRemotePostgres(ctx, dbConfig, conn.Intercept)
-		require.NoError(t, err)
-		defer mock.Close(ctx)
-		// Run test
-		pending, err := getPendingMigrations(ctx, mock, fsys)
-		// Check error
-		assert.NoError(t, err)
-		assert.ElementsMatch(t, files[2:], pending)
-	})
-
-	t.Run("throws error on local load failure", func(t *testing.T) {
-		// Setup in-memory fs
-		fsys := afero.NewReadOnlyFs(afero.NewMemMapFs())
-		// Setup mock postgres
-		conn := pgtest.NewConn()
-		defer conn.Close(t)
-		conn.Query(list.LIST_MIGRATION_VERSION).
-			Reply("SELECT 0")
-		// Connect to mock
-		ctx := context.Background()
-		mock, err := utils.ConnectRemotePostgres(ctx, dbConfig, conn.Intercept)
-		require.NoError(t, err)
-		defer mock.Close(ctx)
-		// Run test
-		_, err = getPendingMigrations(ctx, mock, fsys)
-		// Check error
-		assert.ErrorContains(t, err, "operation not permitted")
-	})
-
-	t.Run("throws error on missing migration", func(t *testing.T) {
-		// Setup in-memory fs
-		fsys := afero.NewMemMapFs()
-		// Setup mock postgres
-		conn := pgtest.NewConn()
-		defer conn.Close(t)
-		conn.Query(list.LIST_MIGRATION_VERSION).
-			Reply("SELECT 1", []interface{}{"0"})
-		// Connect to mock
-		ctx := context.Background()
-		mock, err := utils.ConnectRemotePostgres(ctx, dbConfig, conn.Intercept)
-		require.NoError(t, err)
-		defer mock.Close(ctx)
-		// Run test
-		_, err = getPendingMigrations(ctx, mock, fsys)
-		// Check error
-		assert.ErrorContains(t, err, "Found 1 versions and 0 migrations.")
-	})
-
-	t.Run("throws error on version mismatch", func(t *testing.T) {
-		// Setup in-memory fs
-		fsys := afero.NewMemMapFs()
-		path := filepath.Join(utils.MigrationsDir, "1_test.sql")
-		require.NoError(t, afero.WriteFile(fsys, path, []byte(""), 0644))
-		// Setup mock postgres
-		conn := pgtest.NewConn()
-		defer conn.Close(t)
-		conn.Query(list.LIST_MIGRATION_VERSION).
-			Reply("SELECT 1", []interface{}{"0"})
-		// Connect to mock
-		ctx := context.Background()
-		mock, err := utils.ConnectRemotePostgres(ctx, dbConfig, conn.Intercept)
-		require.NoError(t, err)
-		defer mock.Close(ctx)
-		// Run test
-		_, err = getPendingMigrations(ctx, mock, fsys)
-		// Check error
-		assert.ErrorContains(t, err, "Expected version 0 but found migration 1_test.sql at index 0.")
-	})
-}
-
-func TestPushLocal(t *testing.T) {
-	t.Run("pushes local migration", func(t *testing.T) {
-		// Setup in-memory fs
-		fsys := afero.NewMemMapFs()
-		path := filepath.Join(utils.MigrationsDir, "0_test.sql")
-		sql := "create schema public"
-		require.NoError(t, afero.WriteFile(fsys, path, []byte(sql), 0644))
-		// Setup mock postgres
-		conn := pgtest.NewConn()
-		defer conn.Close(t)
-		conn.Query(sql).
-			Reply("CREATE SCHEMA").
-			Query(repair.INSERT_MIGRATION_VERSION, "0").
-			Reply("INSERT 0 1")
-		// Connect to mock
-		ctx := context.Background()
-		mock, err := utils.ConnectRemotePostgres(ctx, dbConfig, conn.Intercept)
-		require.NoError(t, err)
-		defer mock.Close(ctx)
-		// Run test
-		err = pushMigration(ctx, mock, "0_test.sql", fsys)
-		// Check error
-		assert.NoError(t, err)
-	})
-
-	t.Run("throws error on missing file", func(t *testing.T) {
-		// Setup in-memory fs
-		fsys := afero.NewMemMapFs()
-		// Run test
-		err := pushMigration(context.Background(), nil, "0_test.sql", fsys)
-		// Check error
-		assert.ErrorContains(t, err, "open supabase/migrations/0_test.sql: file does not exist")
-	})
-
-	t.Run("throws error on split failure", func(t *testing.T) {
-		// Setup in-memory fs
-		fsys := afero.NewMemMapFs()
-		path := filepath.Join(utils.MigrationsDir, "0_test.sql")
-		sql := bytes.Repeat([]byte{'a'}, parser.MaxScannerCapacity)
-		require.NoError(t, afero.WriteFile(fsys, path, sql, 0644))
-		// Run test
-		err := pushMigration(context.Background(), nil, "0_test.sql", fsys)
-		// Check error
-		assert.ErrorContains(t, err, "bufio.Scanner: token too long\nAfter statement 0: ")
-	})
-
-	t.Run("throws error on exec failure", func(t *testing.T) {
-		// Setup in-memory fs
-		fsys := afero.NewMemMapFs()
-		path := filepath.Join(utils.MigrationsDir, "0_test.sql")
-		require.NoError(t, afero.WriteFile(fsys, path, []byte(""), 0644))
-		// Setup mock postgres
-		conn := pgtest.NewConn()
-		defer conn.Close(t)
-		conn.Query(repair.INSERT_MIGRATION_VERSION, "0").
-			ReplyError(pgerrcode.NotNullViolation, `null value in column "version" of relation "schema_migrations"`)
-		// Connect to mock
-		ctx := context.Background()
-		mock, err := utils.ConnectRemotePostgres(ctx, dbConfig, conn.Intercept)
-		require.NoError(t, err)
-		defer mock.Close(ctx)
-		// Run test
-		err = pushMigration(ctx, mock, "0_test.sql", fsys)
-		// Check error
-		assert.ErrorContains(t, err, `ERROR: null value in column "version" of relation "schema_migrations" (SQLSTATE 23502)`)
-		assert.ErrorContains(t, err, "At statement 0: "+repair.INSERT_MIGRATION_VERSION)
-	})
-}
diff --git a/internal/db/reset/reset_test.go b/internal/db/reset/reset_test.go
index 223a24a3..976476d0 100644
--- a/internal/db/reset/reset_test.go
+++ b/internal/db/reset/reset_test.go
@@ -7,7 +7,6 @@ import (
 	"io"
 	"net/http"
 	"os"
-	"path/filepath"
 	"testing"
 	"time"
 
@@ -137,23 +136,18 @@ func TestInitDatabase(t *testing.T) {
 		utils.Config.Db.Port = 54322
 		utils.InitialSchemaSql = "CREATE SCHEMA public"
 		// Setup in-memory fs
-		fsys := afero.NewMemMapFs()
-		path := filepath.Join(utils.MigrationsDir, "0_table.sql")
-		sql := "CREATE TABLE example()"
-		require.NoError(t, afero.WriteFile(fsys, path, []byte(sql), 0644))
+		fsys := &fstest.OpenErrorFs{DenyPath: utils.MigrationsDir}
 		// Setup mock postgres
 		conn := pgtest.NewConn()
 		defer conn.Close(t)
 		conn.Query(utils.InitialSchemaSql).
 			Reply("CREATE SCHEMA").
 			Query(SET_POSTGRES_ROLE).
-			Reply("SET ROLE").
-			Query(sql).
-			ReplyError(pgerrcode.DuplicateObject, `table "example" already exists`)
+			Reply("SET ROLE")
 		// Run test
 		err := initDatabase(context.Background(), fsys, conn.Intercept)
 		// Check error
-		assert.ErrorContains(t, err, `ERROR: table "example" already exists (SQLSTATE 42710)`)
+		assert.ErrorIs(t, err, os.ErrPermission)
 	})
 }
 
diff --git a/internal/migration/apply/apply_test.go b/internal/migration/apply/apply_test.go
index c405ae18..ca3fe8d8 100644
--- a/internal/migration/apply/apply_test.go
+++ b/internal/migration/apply/apply_test.go
@@ -14,7 +14,6 @@ import (
 	"github.com/stretchr/testify/assert"
 	"github.com/stretchr/testify/require"
 	"github.com/supabase/cli/internal/migration/repair"
-	"github.com/supabase/cli/internal/testing/fstest"
 	"github.com/supabase/cli/internal/testing/pgtest"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/internal/utils/parser"
@@ -30,8 +29,14 @@ func TestMigrateDatabase(t *testing.T) {
 		// Setup mock postgres
 		conn := pgtest.NewConn()
 		defer conn.Close(t)
-		conn.Query(sql).
-			Reply("CREATE SCHEMA")
+		conn.Query(repair.CREATE_VERSION_SCHEMA).
+			Reply("CREATE SCHEMA").
+			Query(repair.CREATE_VERSION_TABLE).
+			Reply("CREATE TABLE").
+			Query(sql).
+			Reply("CREATE SCHEMA").
+			Query(repair.INSERT_MIGRATION_VERSION, "0").
+			Reply("INSERT 1")
 		// Connect to mock
 		ctx := context.Background()
 		mock, err := utils.ConnectLocalPostgres(ctx, "localhost", 5432, "postgres", conn.Intercept)
@@ -47,39 +52,58 @@ func TestMigrateDatabase(t *testing.T) {
 		assert.NoError(t, MigrateDatabase(context.Background(), nil, afero.NewMemMapFs()))
 	})
 
-	t.Run("ignores outdated migrations", func(t *testing.T) {
+	t.Run("throws error on write failure", func(t *testing.T) {
 		// Setup in-memory fs
 		fsys := afero.NewMemMapFs()
-		// Setup initial migration
-		name := "20211208000000_init.sql"
-		path := filepath.Join(utils.MigrationsDir, name)
-		query := "create table test"
-		require.NoError(t, afero.WriteFile(fsys, path, []byte(query), 0644))
 		// Run test
-		err := MigrateDatabase(context.Background(), nil, fsys)
+		err := MigrateDatabase(context.Background(), nil, afero.NewReadOnlyFs(fsys))
 		// Check error
-		assert.NoError(t, err)
+		assert.ErrorIs(t, err, os.ErrPermission)
 	})
 
-	t.Run("throws error on write failure", func(t *testing.T) {
+}
+
+func TestMigrateUp(t *testing.T) {
+	t.Run("throws error on exec failure", func(t *testing.T) {
 		// Setup in-memory fs
 		fsys := afero.NewMemMapFs()
+		// Setup mock postgres
+		conn := pgtest.NewConn()
+		defer conn.Close(t)
+		conn.Query(repair.CREATE_VERSION_SCHEMA).
+			Reply("CREATE SCHEMA").
+			Query(repair.CREATE_VERSION_TABLE).
+			ReplyError(pgerrcode.InsufficientPrivilege, "permission denied for relation supabase_migrations")
+		// Connect to mock
+		ctx := context.Background()
+		mock, err := utils.ConnectLocalPostgres(ctx, "localhost", 5432, "postgres", conn.Intercept)
+		require.NoError(t, err)
+		defer mock.Close(ctx)
 		// Run test
-		err := MigrateDatabase(context.Background(), nil, afero.NewReadOnlyFs(fsys))
+		err = MigrateUp(context.Background(), mock, []string{"20220727064247_employees.sql"}, fsys)
 		// Check error
-		assert.ErrorIs(t, err, os.ErrPermission)
+		assert.ErrorContains(t, err, "ERROR: permission denied for relation supabase_migrations (SQLSTATE 42501)")
 	})
 
-	t.Run("throws error on open failure", func(t *testing.T) {
-		path := filepath.Join(utils.MigrationsDir, "20220727064247_create_table.sql")
+	t.Run("throws error on missing file", func(t *testing.T) {
 		// Setup in-memory fs
-		fsys := &fstest.OpenErrorFs{DenyPath: path}
-		_, err := fsys.MemMapFs.Create(path)
+		fsys := afero.NewMemMapFs()
+		// Setup mock postgres
+		conn := pgtest.NewConn()
+		defer conn.Close(t)
+		conn.Query(repair.CREATE_VERSION_SCHEMA).
+			Reply("CREATE SCHEMA").
+			Query(repair.CREATE_VERSION_TABLE).
+			Reply("CREATE TABLE")
+		// Connect to mock
+		ctx := context.Background()
+		mock, err := utils.ConnectLocalPostgres(ctx, "localhost", 5432, "postgres", conn.Intercept)
 		require.NoError(t, err)
+		defer mock.Close(ctx)
 		// Run test
-		err = MigrateDatabase(context.Background(), nil, fsys)
+		err = MigrateUp(context.Background(), mock, []string{"20220727064247_missing.sql"}, fsys)
 		// Check error
-		assert.ErrorIs(t, err, os.ErrPermission)
+		assert.ErrorIs(t, err, os.ErrNotExist)
 	})
 }
 
diff --git a/internal/migration/up/up_test.go b/internal/migration/up/up_test.go
new file mode 100644
index 00000000..40d9b6f3
--- /dev/null
+++ b/internal/migration/up/up_test.go
@@ -0,0 +1,105 @@
+package up
+
+import (
+	"context"
+	"path/filepath"
+	"testing"
+
+	"github.com/spf13/afero"
+	"github.com/stretchr/testify/assert"
+	"github.com/stretchr/testify/require"
+	"github.com/supabase/cli/internal/migration/list"
+	"github.com/supabase/cli/internal/testing/pgtest"
+	"github.com/supabase/cli/internal/utils"
+)
+
+func TestPendingMigrations(t *testing.T) {
+	t.Run("finds pending migrations", func(t *testing.T) {
+		// Setup in-memory fs
+		fsys := afero.NewMemMapFs()
+		files := []string{
+			"20221201000000_test.sql",
+			"20221201000001_test.sql",
+			"20221201000002_test.sql",
+			"20221201000003_test.sql",
+		}
+		for _, name := range files {
+			path := filepath.Join(utils.MigrationsDir, name)
+			require.NoError(t, afero.WriteFile(fsys, path, []byte(""), 0644))
+		}
+		// Setup mock postgres
+		conn := pgtest.NewConn()
+		defer conn.Close(t)
+		conn.Query(list.LIST_MIGRATION_VERSION).
+			Reply("SELECT 2", []interface{}{"20221201000000"}, []interface{}{"20221201000001"})
+		// Connect to mock
+		ctx := context.Background()
+		mock, err := utils.ConnectLocalPostgres(ctx, "localhost", 5432, "postgres", conn.Intercept)
+		require.NoError(t, err)
+		defer mock.Close(ctx)
+		// Run test
+		pending, err := GetPendingMigrations(ctx, mock, fsys)
+		// Check error
+		assert.NoError(t, err)
+		assert.ElementsMatch(t, files[2:], pending)
+	})
+
+	t.Run("throws error on local load failure", func(t *testing.T) {
+		// Setup in-memory fs
+		fsys := afero.NewReadOnlyFs(afero.NewMemMapFs())
+		// Setup mock postgres
+		conn := pgtest.NewConn()
+		defer conn.Close(t)
+		conn.Query(list.LIST_MIGRATION_VERSION).
+			Reply("SELECT 0")
+		// Connect to mock
+		ctx := context.Background()
+		mock, err := utils.ConnectLocalPostgres(ctx, "localhost", 5432, "postgres", conn.Intercept)
+		require.NoError(t, err)
+		defer mock.Close(ctx)
+		// Run test
+		_, err = GetPendingMigrations(ctx, mock, fsys)
+		// Check error
+		assert.ErrorContains(t, err, "operation not permitted")
+	})
+
+	t.Run("throws error on missing migration", func(t *testing.T) {
+		// Setup in-memory fs
+		fsys := afero.NewMemMapFs()
+		// Setup mock postgres
+		conn := pgtest.NewConn()
+		defer conn.Close(t)
+		conn.Query(list.LIST_MIGRATION_VERSION).
+			Reply("SELECT 1", []interface{}{"0"})
+		// Connect to mock
+		ctx := context.Background()
+		mock, err := utils.ConnectLocalPostgres(ctx, "localhost", 5432, "postgres", conn.Intercept)
+		require.NoError(t, err)
+		defer mock.Close(ctx)
+		// Run test
+		_, err = GetPendingMigrations(ctx, mock, fsys)
+		// Check error
+		assert.ErrorContains(t, err, "Found 1 versions and 0 migrations.")
+	})
+
+	t.Run("throws error on version mismatch", func(t *testing.T) {
+		// Setup in-memory fs
+		fsys := afero.NewMemMapFs()
+		path := filepath.Join(utils.MigrationsDir, "1_test.sql")
+		require.NoError(t, afero.WriteFile(fsys, path, []byte(""), 0644))
+		// Setup mock postgres
+		conn := pgtest.NewConn()
+		defer conn.Close(t)
+		conn.Query(list.LIST_MIGRATION_VERSION).
+			Reply("SELECT 1", []interface{}{"0"})
+		// Connect to mock
+		ctx := context.Background()
+		mock, err := utils.ConnectLocalPostgres(ctx, "localhost", 5432, "postgres", conn.Intercept)
+		require.NoError(t, err)
+		defer mock.Close(ctx)
+		// Run test
+		_, err = GetPendingMigrations(ctx, mock, fsys)
+		// Check error
+		assert.ErrorContains(t, err, "Expected version 0 but found migration 1_test.sql at index 0.")
+	})
+}