Repository: supabase/cli
Pull request #1029 (2023-04-21 05:01:30)

fix: handle missing migration history table as empty
## What kind of change does this PR introduce?

Bug fix

## What is the current behavior?

When running `supabase migration list --db-url` on fresh self-hosted projects, a missing table error is thrown.

## What is the new behavior?

```bash
$ supabase migration list --db-url 'postgres://self-hosted'


        LOCAL      │ REMOTE │     TIME (UTC)
  ─────────────────┼────────┼──────────────────────
    20230410135622 │        │ 2023-04-10 13:56:22
```

## Additional context

Add any other context or screenshots.


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

* **9** of **9**   **(100.0%)**  changed or added relevant lines in **2** files are covered.
* No unchanged relevant lines lost coverage.
* Overall coverage increased (+**0.1%**) to **60.564%**

---



|  Totals | [![Coverage Status](https://coveralls.io/builds/59165340/badge)](https://coveralls.io/builds/59165340) |
| :-- | --: |
| Change from base [Build 4755982824](https://coveralls.io/builds/59143519): |  0.1% |
| Covered Lines: | 4191 |
| Relevant Lines: | 6920 |

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


Patch:
diff --git a/internal/db/push/push.go b/internal/db/push/push.go
index 03d033b1..ba32b7d2 100644
--- a/internal/db/push/push.go
+++ b/internal/db/push/push.go
@@ -38,6 +38,11 @@ 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 {
 			fmt.Fprintln(os.Stderr, "Would push migration "+utils.Bold(filename)+"...")
diff --git a/internal/migration/list/list.go b/internal/migration/list/list.go
index 28f221cc..0464c273 100644
--- a/internal/migration/list/list.go
+++ b/internal/migration/list/list.go
@@ -2,6 +2,7 @@ package list
 
 import (
 	"context"
+	"errors"
 	"fmt"
 	"math"
 	"os"
@@ -11,6 +12,7 @@ import (
 
 	"github.com/charmbracelet/glamour"
 	"github.com/jackc/pgconn"
+	"github.com/jackc/pgerrcode"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
 	"github.com/supabase/cli/internal/utils"
@@ -44,6 +46,11 @@ func loadRemoteVersions(ctx context.Context, config pgconn.Config, options ...fu
 func LoadRemoteMigrations(ctx context.Context, conn *pgx.Conn) ([]string, error) {
 	rows, err := conn.Query(ctx, LIST_MIGRATION_VERSION)
 	if err != nil {
+		var pgErr *pgconn.PgError
+		if errors.As(err, &pgErr) && pgErr.Code == pgerrcode.UndefinedTable {
+			// If migration history table is undefined, the remote project has no migrations
+			return nil, nil
+		}
 		return nil, err
 	}
 	versions := []string{}


Test patch:
diff --git a/internal/db/push/push_test.go b/internal/db/push/push_test.go
index f0cca532..952947ca 100644
--- a/internal/db/push/push_test.go
+++ b/internal/db/push/push_test.go
@@ -73,11 +73,31 @@ func TestMigrationPush(t *testing.T) {
 		conn := pgtest.NewConn()
 		defer conn.Close(t)
 		conn.Query(list.LIST_MIGRATION_VERSION).
-			ReplyError(pgerrcode.UndefinedTable, `relation "supabase_migrations.schema_migrations" does not exist`)
+			ReplyError(pgerrcode.InvalidCatalogName, `database "target" does not exist`)
 		// Run test
 		err := Run(context.Background(), false, dbConfig, fsys, conn.Intercept)
 		// Check error
-		assert.ErrorContains(t, err, `ERROR: relation "supabase_migrations.schema_migrations" does not exist (SQLSTATE 42P01)`)
+		assert.ErrorContains(t, err, `ERROR: database "target" does not exist (SQLSTATE 3D000)`)
+	})
+
+	t.Run("throws error on schema create 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(list.LIST_MIGRATION_VERSION).
+			Reply("SELECT 0").
+			Query(repair.CREATE_VERSION_SCHEMA).
+			Reply("CREATE SCHEMA").
+			Query(repair.CREATE_VERSION_TABLE).
+			ReplyError(pgerrcode.InsufficientPrivilege, "permission denied for relation supabase_migrations")
+		// Run test
+		err := Run(context.Background(), false, dbConfig, fsys, conn.Intercept)
+		// Check error
+		assert.ErrorContains(t, err, `ERROR: permission denied for relation supabase_migrations (SQLSTATE 42501)`)
 	})
 
 	t.Run("throws error on push failure", func(t *testing.T) {
@@ -90,6 +110,10 @@ func TestMigrationPush(t *testing.T) {
 		defer conn.Close(t)
 		conn.Query(list.LIST_MIGRATION_VERSION).
 			Reply("SELECT 0").
+			Query(repair.CREATE_VERSION_SCHEMA).
+			Reply("CREATE SCHEMA").
+			Query(repair.CREATE_VERSION_TABLE).
+			Reply("CREATE TABLE").
 			Query(repair.INSERT_MIGRATION_VERSION, "0").
 			ReplyError(pgerrcode.NotNullViolation, `null value in column "version" of relation "schema_migrations"`)
 		// Run test
diff --git a/internal/migration/list/list_test.go b/internal/migration/list/list_test.go
index 17499d7b..5b1a57c4 100644
--- a/internal/migration/list/list_test.go
+++ b/internal/migration/list/list_test.go
@@ -84,16 +84,17 @@ func TestRemoteMigrations(t *testing.T) {
 		assert.ErrorContains(t, err, "invalid port (outside range)")
 	})
 
-	t.Run("throws error on missing schema", func(t *testing.T) {
+	t.Run("loads empty migrations on missing table", func(t *testing.T) {
 		// Setup mock postgres
 		conn := pgtest.NewConn()
 		defer conn.Close(t)
 		conn.Query(LIST_MIGRATION_VERSION).
 			ReplyError(pgerrcode.UndefinedTable, "relation \"supabase_migrations.schema_migrations\" does not exist")
 		// Run test
-		_, err := loadRemoteVersions(context.Background(), dbConfig, conn.Intercept)
+		versions, err := loadRemoteVersions(context.Background(), dbConfig, conn.Intercept)
 		// Check error
-		assert.ErrorContains(t, err, `ERROR: relation "supabase_migrations.schema_migrations" does not exist (SQLSTATE 42P01)`)
+		assert.NoError(t, err)
+		assert.Empty(t, versions)
 	})
 
 	t.Run("throws error on invalid row", func(t *testing.T) {