Repository: supabase/cli
Pull request #2099 (2024-03-27 10:59:05)

feat: support declarative schema with db diff
## What kind of change does this PR introduce?

closes https://github.com/supabase/cli/issues/1215

## What is the new behavior?

- Add schema files to `supabase/schemas` (they will be run in lexical order)
  - Easiest way to create one is `supabase db dump --local -f supabase/schemas/base.sql`
- Then run `supabase db diff`

A new local database will be created from declared schemas for diffing.

```
$ supabase db diff
Creating local database from supabase/schemas...
Setting up initial schema...
Creating custom roles supabase/roles.sql...
Applying schema supabase/schemas/base.sql...
Connecting to local database...
Creating shadow database...
Setting up initial schema...
Creating custom roles supabase/roles.sql...
Applying migration 20231204107450_grant.sql...
Diffing schemas: extensions,private,public
Finished supabase db diff on branch dec-schema.

No schema changes found
```

## Additional context

Add any other context or screenshots.


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


### Details

* **7** of **59**   **(11.86%)**  changed or added relevant lines in **4** files are covered.
* **5** unchanged lines in **1** file lost coverage.
* Overall coverage decreased (**-0.3%**) to **57.727%**

---

|  Changes Missing Coverage | Covered Lines | Changed/Added Lines | % |
| :-----|--------------|--------|---: |
| [internal/db/diff/pgadmin.go](https://coveralls.io/builds/66554605/source?filename=internal%2Fdb%2Fdiff%2Fpgadmin.go#L61) | 0 | 1 | 0.0%
| [internal/db/diff/diff.go](https://coveralls.io/builds/66554605/source?filename=internal%2Fdb%2Fdiff%2Fdiff.go#L42) | 4 | 55 | 7.27%
<!-- | **Total:** | **7** | **59** | **11.86%** | -->

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

|  Totals | [![Coverage Status](https://coveralls.io/builds/66554605/badge)](https://coveralls.io/builds/66554605) |
| :-- | --: |
| Change from base [Build 8449117477](https://coveralls.io/builds/66552117): |  -0.3% |
| Covered Lines: | 6365 |
| Relevant Lines: | 11026 |

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


Patch:
diff --git a/internal/db/diff/diff.go b/internal/db/diff/diff.go
index e60c8883..3f15c5fc 100644
--- a/internal/db/diff/diff.go
+++ b/internal/db/diff/diff.go
@@ -5,7 +5,9 @@ import (
 	_ "embed"
 	"fmt"
 	"io"
+	"io/fs"
 	"os"
+	"path/filepath"
 	"regexp"
 	"strconv"
 	"strings"
@@ -24,6 +26,7 @@ import (
 	"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/utils"
 	"github.com/supabase/cli/internal/utils/parser"
 )
@@ -35,6 +38,19 @@ func Run(ctx context.Context, schema []string, file string, config pgconn.Config
 	if err := utils.LoadConfigFS(fsys); err != nil {
 		return err
 	}
+	if utils.IsLocalDatabase(config) {
+		if container, err := createShadowIfNotExists(ctx, fsys); err != nil {
+			return err
+		} else if len(container) > 0 {
+			defer utils.DockerRemove(container)
+			if !start.WaitForHealthyService(ctx, container, start.HealthTimeout) {
+				return errors.New(start.ErrDatabase)
+			}
+			if err := migrateBaseDatabase(ctx, container, fsys, options...); err != nil {
+				return err
+			}
+		}
+	}
 	// 1. Load all user defined schemas
 	if len(schema) == 0 {
 		schema, err = loadSchema(ctx, config, options...)
@@ -60,6 +76,35 @@ func Run(ctx context.Context, schema []string, file string, config pgconn.Config
 	return nil
 }
 
+func createShadowIfNotExists(ctx context.Context, fsys afero.Fs) (string, error) {
+	if exists, err := afero.DirExists(fsys, utils.SchemasDir); err != nil {
+		return "", errors.Errorf("failed to check schemas: %w", err)
+	} else if !exists {
+		return "", nil
+	}
+	if err := utils.AssertSupabaseDbIsRunning(); !errors.Is(err, utils.ErrNotRunning) {
+		return "", err
+	}
+	fmt.Fprintf(os.Stderr, "Creating local database from %s...\n", utils.Bold(utils.SchemasDir))
+	return CreateShadowDatabase(ctx, utils.Config.Db.Port)
+}
+
+func loadDeclaredSchemas(fsys afero.Fs) ([]string, error) {
+	var declared []string
+	if err := afero.Walk(fsys, utils.SchemasDir, func(path string, info fs.FileInfo, err error) error {
+		if err != nil {
+			return err
+		}
+		if info.Mode().IsRegular() && filepath.Ext(info.Name()) == ".sql" {
+			declared = append(declared, path)
+		}
+		return nil
+	}); err != nil {
+		return nil, errors.Errorf("failed to walk dir: %w", err)
+	}
+	return declared, nil
+}
+
 // https://github.com/djrobstep/migra/blob/master/migra/statements.py#L6
 var dropStatementPattern = regexp.MustCompile(`(?i)drop\s+`)
 
@@ -87,9 +132,9 @@ func loadSchema(ctx context.Context, config pgconn.Config, options ...func(*pgx.
 	return reset.LoadUserSchemas(ctx, conn)
 }
 
-func CreateShadowDatabase(ctx context.Context) (string, error) {
+func CreateShadowDatabase(ctx context.Context, port uint16) (string, error) {
 	config := start.NewContainerConfig()
-	hostPort := strconv.FormatUint(uint64(utils.Config.Db.ShadowPort), 10)
+	hostPort := strconv.FormatUint(uint64(port), 10)
 	hostConfig := container.HostConfig{
 		PortBindings: nat.PortMap{"5432/tcp": []nat.PortBinding{{HostPort: hostPort}}},
 		AutoRemove:   true,
@@ -105,7 +150,7 @@ func CreateShadowDatabase(ctx context.Context) (string, error) {
 func ConnectShadowDatabase(ctx context.Context, timeout time.Duration, options ...func(*pgx.ConnConfig)) (conn *pgx.Conn, err error) {
 	// Retry until connected, cancelled, or timeout
 	policy := backoff.WithMaxRetries(backoff.NewConstantBackOff(time.Second), uint64(timeout.Seconds()))
-	config := pgconn.Config{Port: uint16(utils.Config.Db.ShadowPort)}
+	config := pgconn.Config{Port: utils.Config.Db.ShadowPort}
 	connect := func() (*pgx.Conn, error) {
 		return utils.ConnectLocalPostgres(ctx, config, options...)
 	}
@@ -128,9 +173,35 @@ func MigrateShadowDatabase(ctx context.Context, container string, fsys afero.Fs,
 	return apply.MigrateUp(ctx, conn, migrations, fsys)
 }
 
+func migrateBaseDatabase(ctx context.Context, container string, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {
+	migrations, err := loadDeclaredSchemas(fsys)
+	if err != nil {
+		return err
+	}
+	conn, err := utils.ConnectLocalPostgres(ctx, pgconn.Config{}, options...)
+	if err != nil {
+		return err
+	}
+	defer conn.Close(context.Background())
+	if err := start.SetupDatabase(ctx, conn, container[:12], os.Stderr, fsys); err != nil {
+		return err
+	}
+	for _, path := range migrations {
+		fmt.Fprintln(os.Stderr, "Applying schema "+utils.Bold(path)+"...")
+		migration, err := repair.NewMigrationFromFile(path, fsys)
+		if err != nil {
+			return err
+		}
+		if err := migration.ExecBatch(ctx, conn); err != nil {
+			return err
+		}
+	}
+	return nil
+}
+
 func DiffDatabase(ctx context.Context, schema []string, config pgconn.Config, w io.Writer, fsys afero.Fs, differ func(context.Context, string, string, []string) (string, error), options ...func(*pgx.ConnConfig)) (string, error) {
 	fmt.Fprintln(w, "Creating shadow database...")
-	shadow, err := CreateShadowDatabase(ctx)
+	shadow, err := CreateShadowDatabase(ctx, utils.Config.Db.ShadowPort)
 	if err != nil {
 		return "", err
 	}
@@ -144,7 +215,7 @@ func DiffDatabase(ctx context.Context, schema []string, config pgconn.Config, w
 	fmt.Fprintln(w, "Diffing schemas:", strings.Join(schema, ","))
 	source := utils.ToPostgresURL(pgconn.Config{
 		Host:     utils.Config.Hostname,
-		Port:     uint16(utils.Config.Db.ShadowPort),
+		Port:     utils.Config.Db.ShadowPort,
 		User:     "postgres",
 		Password: utils.Config.Db.Password,
 		Database: "postgres",
diff --git a/internal/db/diff/pgadmin.go b/internal/db/diff/pgadmin.go
index 6c94442f..55df354e 100644
--- a/internal/db/diff/pgadmin.go
+++ b/internal/db/diff/pgadmin.go
@@ -58,7 +58,7 @@ func run(p utils.Program, ctx context.Context, schema []string, config pgconn.Co
 	p.Send(utils.StatusMsg("Creating shadow database..."))
 
 	// 1. Create shadow db and run migrations
-	shadow, err := CreateShadowDatabase(ctx)
+	shadow, err := CreateShadowDatabase(ctx, utils.Config.Db.ShadowPort)
 	if err != nil {
 		return err
 	}
diff --git a/internal/functions/new/new.go b/internal/functions/new/new.go
index f9447cf9..7abc3658 100644
--- a/internal/functions/new/new.go
+++ b/internal/functions/new/new.go
@@ -51,7 +51,7 @@ func Run(ctx context.Context, slug string, fsys afero.Fs) error {
 			utils.CmdSuggestion = utils.SuggestDebugFlag
 		}
 		config := indexConfig{
-			Port:  uint16(utils.Config.Api.Port),
+			Port:  utils.Config.Api.Port,
 			Slug:  slug,
 			Token: utils.Config.Auth.AnonKey,
 		}
diff --git a/internal/migration/squash/squash.go b/internal/migration/squash/squash.go
index 557afaff..05576e12 100644
--- a/internal/migration/squash/squash.go
+++ b/internal/migration/squash/squash.go
@@ -80,7 +80,7 @@ func squashToVersion(ctx context.Context, version string, fsys afero.Fs, options
 
 func squashMigrations(ctx context.Context, migrations []string, fsys afero.Fs, options ...func(*pgx.ConnConfig)) error {
 	// 1. Start shadow database
-	shadow, err := diff.CreateShadowDatabase(ctx)
+	shadow, err := diff.CreateShadowDatabase(ctx, utils.Config.Db.ShadowPort)
 	if err != nil {
 		return err
 	}
@@ -100,7 +100,7 @@ func squashMigrations(ctx context.Context, migrations []string, fsys afero.Fs, o
 	schemas := []string{"auth", "storage"}
 	config := pgconn.Config{
 		Host:     utils.Config.Hostname,
-		Port:     uint16(utils.Config.Db.ShadowPort),
+		Port:     utils.Config.Db.ShadowPort,
 		User:     "postgres",
 		Password: utils.Config.Db.Password,
 		Database: "postgres",
diff --git a/internal/start/start.go b/internal/start/start.go
index 09a14c35..ad95e799 100644
--- a/internal/start/start.go
+++ b/internal/start/start.go
@@ -103,7 +103,7 @@ type kongConfig struct {
 	EdgeRuntimeId string
 	LogflareId    string
 	ApiHost       string
-	ApiPort       uint
+	ApiPort       uint16
 }
 
 var (
diff --git a/internal/utils/config.go b/internal/utils/config.go
index 2174fdf6..abb8f390 100644
--- a/internal/utils/config.go
+++ b/internal/utils/config.go
@@ -267,7 +267,7 @@ type (
 	api struct {
 		Enabled         bool     `toml:"enabled"`
 		Image           string   `toml:"-"`
-		Port            uint     `toml:"port"`
+		Port            uint16   `toml:"port"`
 		Schemas         []string `toml:"schemas"`
 		ExtraSearchPath []string `toml:"extra_search_path"`
 		MaxRows         uint     `toml:"max_rows"`
@@ -275,8 +275,8 @@ type (
 
 	db struct {
 		Image        string `toml:"-"`
-		Port         uint   `toml:"port"`
-		ShadowPort   uint   `toml:"shadow_port"`
+		Port         uint16 `toml:"port"`
+		ShadowPort   uint16 `toml:"shadow_port"`
 		MajorVersion uint   `toml:"major_version"`
 		Password     string `toml:"-"`
 		RootKey      string `toml:"-" mapstructure:"root_key"`
@@ -303,16 +303,16 @@ type (
 
 	studio struct {
 		Enabled      bool   `toml:"enabled"`
-		Port         uint   `toml:"port"`
+		Port         uint16 `toml:"port"`
 		ApiUrl       string `toml:"api_url"`
 		OpenaiApiKey string `toml:"openai_api_key"`
 	}
 
 	inbucket struct {
-		Enabled  bool `toml:"enabled"`
-		Port     uint `toml:"port"`
-		SmtpPort uint `toml:"smtp_port"`
-		Pop3Port uint `toml:"pop3_port"`
+		Enabled  bool   `toml:"enabled"`
+		Port     uint16 `toml:"port"`
+		SmtpPort uint16 `toml:"smtp_port"`
+		Pop3Port uint16 `toml:"pop3_port"`
 	}
 
 	storage struct {
diff --git a/internal/utils/connect.go b/internal/utils/connect.go
index cf7c5d9f..b3c44ad4 100644
--- a/internal/utils/connect.go
+++ b/internal/utils/connect.go
@@ -98,7 +98,7 @@ func ConnectLocalPostgres(ctx context.Context, config pgconn.Config, options ...
 		config.Host = Config.Hostname
 	}
 	if config.Port == 0 {
-		config.Port = uint16(Config.Db.Port)
+		config.Port = Config.Db.Port
 	}
 	if len(config.User) == 0 {
 		config.User = "postgres"
@@ -155,5 +155,5 @@ func ConnectByConfig(ctx context.Context, config pgconn.Config, options ...func(
 }
 
 func IsLocalDatabase(config pgconn.Config) bool {
-	return config.Host == Config.Hostname && config.Port == uint16(Config.Db.Port)
+	return config.Host == Config.Hostname && config.Port == Config.Db.Port
 }
diff --git a/internal/utils/flags/db_url.go b/internal/utils/flags/db_url.go
index 602d2974..629e0332 100644
--- a/internal/utils/flags/db_url.go
+++ b/internal/utils/flags/db_url.go
@@ -63,7 +63,7 @@ func ParseDatabaseConfig(flagSet *pflag.FlagSet, fsys afero.Fs) error {
 		}
 		// Ignore other PG settings
 		DbConfig.Host = utils.Config.Hostname
-		DbConfig.Port = uint16(utils.Config.Db.Port)
+		DbConfig.Port = utils.Config.Db.Port
 		DbConfig.User = "postgres"
 		DbConfig.Password = utils.Config.Db.Password
 		DbConfig.Database = "postgres"
diff --git a/internal/utils/misc.go b/internal/utils/misc.go
index 2f468d48..da088c1c 100644
--- a/internal/utils/misc.go
+++ b/internal/utils/misc.go
@@ -188,6 +188,7 @@ var (
 	RestVersionPath       = filepath.Join(TempDir, "rest-version")
 	StorageVersionPath    = filepath.Join(TempDir, "storage-version")
 	CurrBranchPath        = filepath.Join(SupabaseDirPath, ".branches", "_current_branch")
+	SchemasDir            = filepath.Join(SupabaseDirPath, "schemas")
 	MigrationsDir         = filepath.Join(SupabaseDirPath, "migrations")
 	FunctionsDir          = filepath.Join(SupabaseDirPath, "functions")
 	FallbackImportMapPath = filepath.Join(FunctionsDir, "import_map.json")


Test patch:
diff --git a/internal/db/reset/reset_test.go b/internal/db/reset/reset_test.go
index 9728bac4..650c9557 100644
--- a/internal/db/reset/reset_test.go
+++ b/internal/db/reset/reset_test.go
@@ -29,7 +29,7 @@ func TestResetCommand(t *testing.T) {
 
 	var dbConfig = pgconn.Config{
 		Host:     utils.Config.Hostname,
-		Port:     uint16(utils.Config.Db.Port),
+		Port:     utils.Config.Db.Port,
 		User:     "admin",
 		Password: "password",
 		Database: "postgres",
diff --git a/internal/gen/types/typescript/typescript_test.go b/internal/gen/types/typescript/typescript_test.go
index 9e4ed81e..31fec482 100644
--- a/internal/gen/types/typescript/typescript_test.go
+++ b/internal/gen/types/typescript/typescript_test.go
@@ -25,7 +25,7 @@ func TestGenLocalCommand(t *testing.T) {
 
 	dbConfig := pgconn.Config{
 		Host:     utils.Config.Hostname,
-		Port:     uint16(utils.Config.Db.Port),
+		Port:     utils.Config.Db.Port,
 		User:     "admin",
 		Password: "password",
 	}