Repository: supabase/cli
Pull request #892 (2023-02-28 06:00:14)

fix: exclude temporary schemas from db diff
## What kind of change does this PR introduce?

Bug fix https://gist.github.com/tmcw/6f43e433bae38fffacec6d2aa5c9345a

## What is the new behavior?

- removes all temporary schemas from being diffed by default
- uses `pg_*` wildcard pattern to support both diff and dump

## Additional context

Add any other context or screenshots.



Patch:
diff --git a/internal/db/diff/migra.go b/internal/db/diff/migra.go
index 5a564399..574a8589 100644
--- a/internal/db/diff/migra.go
+++ b/internal/db/diff/migra.go
@@ -23,7 +23,7 @@ import (
 	"github.com/supabase/cli/internal/utils/parser"
 )
 
-const LIST_SCHEMAS = "SELECT schema_name FROM information_schema.schemata WHERE NOT schema_name = ANY($1) ORDER BY schema_name"
+const LIST_SCHEMAS = "SELECT schema_name FROM information_schema.schemata WHERE NOT schema_name LIKE ANY($1) ORDER BY schema_name"
 
 var (
 	//go:embed templates/migra.sh
@@ -107,6 +107,7 @@ func LoadUserSchemas(ctx context.Context, conn *pgx.Conn, exclude ...string) ([]
 			"supabase_functions",
 			"supabase_migrations",
 		}, utils.SystemSchemas...)
+		exclude = likeEscapeSchema(exclude)
 	}
 	rows, err := conn.Query(ctx, LIST_SCHEMAS, exclude)
 	if err != nil {
@@ -123,6 +124,15 @@ func LoadUserSchemas(ctx context.Context, conn *pgx.Conn, exclude ...string) ([]
 	return schemas, nil
 }
 
+func likeEscapeSchema(schemas []string) (result []string) {
+	// Treat _ as literal, * as any character
+	replacer := strings.NewReplacer("_", `\_`, "*", "%")
+	for _, sch := range schemas {
+		result = append(result, replacer.Replace(sch))
+	}
+	return result
+}
+
 func CreateShadowDatabase(ctx context.Context) (string, error) {
 	config := container.Config{
 		Image: utils.DbImage,
diff --git a/internal/utils/misc.go b/internal/utils/misc.go
index 3b5669fe..1fa2e227 100644
--- a/internal/utils/misc.go
+++ b/internal/utils/misc.go
@@ -109,13 +109,10 @@ var (
 	FuncSlugPattern    = regexp.MustCompile(`^[A-Za-z][A-Za-z0-9_-]*$`)
 	ImageNamePattern   = regexp.MustCompile(`\/(.*):`)
 
-	// These schemas are ignored from schema diffs
+	// These schemas are ignored from db diff and db dump
 	SystemSchemas = []string{
 		"information_schema",
-		"pg_catalog",
-		"pg_toast",
-		// "pg_temp_1",
-		// "pg_toast_temp_1",
+		"pg_*", // Wildcard pattern follows pg_dump
 		// Owned by extensions
 		"cron",
 		"graphql",


Test patch:
diff --git a/internal/db/diff/migra_test.go b/internal/db/diff/migra_test.go
index dbe378f7..a20bb185 100644
--- a/internal/db/diff/migra_test.go
+++ b/internal/db/diff/migra_test.go
@@ -108,7 +108,7 @@ func TestRunMigra(t *testing.T) {
 		// Setup mock postgres
 		conn := pgtest.NewConn()
 		defer conn.Close(t)
-		conn.Query("SELECT schema_name FROM information_schema.schemata WHERE NOT schema_name = ANY('{pgbouncer,realtime,_realtime,supabase_functions,supabase_migrations,information_schema,pg_catalog,pg_toast,cron,graphql,graphql_public,net,pgsodium,pgsodium_masks,vault}') ORDER BY schema_name").
+		conn.Query(`SELECT schema_name FROM information_schema.schemata WHERE NOT schema_name LIKE ANY('{pgbouncer,realtime,"\\_realtime","supabase\\_functions","supabase\\_migrations","information\\_schema","pg\\_%",cron,graphql,"graphql\\_public",net,pgsodium,"pgsodium\\_masks",vault}') ORDER BY schema_name`).
 			ReplyError(pgerrcode.DuplicateTable, `relation "test" already exists`)
 		// Run test
 		err := RunMigra(context.Background(), []string{}, "", "password", fsys, conn.Intercept)