Repository: supabase/cli
Pull request #2728 (2024-10-04 15:33:26)

feat: track seeded files in migration table
## What kind of change does this PR introduce?

feature

## What is the new behavior?

Track seed files in a history table.

- `path` resolves to local seed file and is also they primary key
- `hash` is the sha256 checksum of the seed file

When loading seed files, we check the history table for path and hash match. Any unmatched files will be seeded next.

If file path exists in history but checksum doesn't match, the seed file will be marked dirty. Dirty files are not seeded, only their hash column will be updated.

If both path and hash match, the seed file is skipped, as it has been applied before.

```
$ supabase db push --local --include-seed
Connecting to local database...
Schema migrations are up to date.
Do you want to seed the remote database with these files?
 • supabase/seeds/buckets.sql
 • supabase/seeds/users.sql

 [Y/n] n
context canceled
exit status 1
```

## Additional context

Also prompts before creating custom roles.
```
$ supabase db push --local --include-seed --include-roles
Connecting to local database...
Do you want to create custom roles in the database cluster? [Y/n]
Seeding globals from roles.sql...
Schema migrations are up to date.
Seed files are up to date.
Finished supabase db push.
```


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


### Details

* **22** of **37**   **(59.46%)**  changed or added relevant lines in **5** files are covered.
* **8** unchanged lines in **3** files lost coverage.
* Overall coverage decreased (**-0.02%**) to **60.013%**

---

|  Changes Missing Coverage | Covered Lines | Changed/Added Lines | % |
| :-----|--------------|--------|---: |
| [internal/db/reset/reset.go](https://coveralls.io/builds/70172662/source?filename=internal%2Fdb%2Freset%2Freset.go#L130) | 0 | 1 | 0.0%
| [internal/migration/apply/apply.go](https://coveralls.io/builds/70172662/source?filename=internal%2Fmigration%2Fapply%2Fapply.go#L32) | 5 | 7 | 71.43%
| [internal/db/push/push.go](https://coveralls.io/builds/70172662/source?filename=internal%2Fdb%2Fpush%2Fpush.go#L36) | 11 | 23 | 47.83%
<!-- | **Total:** | **22** | **37** | **59.46%** | -->

|  Files with Coverage Reduction | New Missed Lines | % |
| :-----|--------------|--: |
| [internal/db/push/push.go](https://coveralls.io/builds/70172662/source?filename=internal%2Fdb%2Fpush%2Fpush.go#L55) | 1 | 79.71% |
| [internal/storage/rm/rm.go](https://coveralls.io/builds/70172662/source?filename=internal%2Fstorage%2Frm%2Frm.go#L124) | 2 | 89.53% |
| [internal/gen/keys/keys.go](https://coveralls.io/builds/70172662/source?filename=internal%2Fgen%2Fkeys%2Fkeys.go#L87) | 5 | 12.9% |
<!-- | **Total:** | **8** |  | -->

|  Totals | [![Coverage Status](https://coveralls.io/builds/70172662/badge)](https://coveralls.io/builds/70172662) |
| :-- | --: |
| Change from base [Build 11183330771](https://coveralls.io/builds/70171759): |  -0.02% |
| Covered Lines: | 6458 |
| Relevant Lines: | 10761 |

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


Patch:
diff --git a/internal/db/push/push.go b/internal/db/push/push.go
index 5439f16c..f52d7897 100644
--- a/internal/db/push/push.go
+++ b/internal/db/push/push.go
@@ -10,7 +10,6 @@ import (
 	"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/up"
 	"github.com/supabase/cli/internal/utils"
 	"github.com/supabase/cli/pkg/migration"
@@ -29,39 +28,75 @@ func Run(ctx context.Context, dryRun, ignoreVersionMismatch bool, includeRoles,
 	if err != nil {
 		return err
 	}
-	if len(pending) == 0 {
+	var seeds []migration.SeedFile
+	if includeSeed {
+		seeds, err = migration.GetPendingSeeds(ctx, utils.Config.Db.Seed.SqlPaths, conn, afero.NewIOFS(fsys))
+		if err != nil {
+			return err
+		}
+	}
+	var globals []string
+	if includeRoles {
+		if exists, err := afero.Exists(fsys, utils.CustomRolesPath); err != nil {
+			return errors.Errorf("failed to find custom roles: %w", err)
+		} else if exists {
+			globals = append(globals, utils.CustomRolesPath)
+		}
+	}
+	if len(pending) == 0 && len(seeds) == 0 && len(globals) == 0 {
 		fmt.Println("Remote database is up to date.")
 		return nil
 	}
 	// Push pending migrations
 	if dryRun {
-		if includeRoles {
-			fmt.Fprintln(os.Stderr, "Would create custom roles "+utils.Bold(utils.CustomRolesPath)+"...")
+		if len(globals) > 0 {
+			fmt.Fprintln(os.Stderr, "Would create custom roles "+utils.Bold(globals[0])+"...")
 		}
-		fmt.Fprintln(os.Stderr, "Would push these migrations:")
-		fmt.Fprint(os.Stderr, utils.Bold(confirmPushAll(pending)))
-		if includeSeed {
-			fmt.Fprintf(os.Stderr, "Would seed data %v...\n", utils.Config.Db.Seed.SqlPaths)
+		if len(pending) > 0 {
+			fmt.Fprintln(os.Stderr, "Would push these migrations:")
+			fmt.Fprint(os.Stderr, confirmPushAll(pending))
 		}
-	} else {
-		msg := fmt.Sprintf("Do you want to push these migrations to the remote database?\n%s\n", confirmPushAll(pending))
-		if shouldPush, err := utils.NewConsole().PromptYesNo(ctx, msg, true); err != nil {
-			return err
-		} else if !shouldPush {
-			return errors.New(context.Canceled)
+		if len(seeds) > 0 {
+			fmt.Fprintln(os.Stderr, "Would seed these files:")
+			fmt.Fprint(os.Stderr, confirmSeedAll(seeds))
 		}
-		if includeRoles {
-			if err := apply.CreateCustomRoles(ctx, conn, fsys); err != nil {
+	} else {
+		if len(globals) > 0 {
+			msg := "Do you want to create custom roles in the database cluster?"
+			if shouldPush, err := utils.NewConsole().PromptYesNo(ctx, msg, true); err != nil {
+				return err
+			} else if !shouldPush {
+				return errors.New(context.Canceled)
+			}
+			if err := migration.SeedGlobals(ctx, globals, conn, afero.NewIOFS(fsys)); err != nil {
 				return err
 			}
 		}
-		if err := migration.ApplyMigrations(ctx, pending, conn, afero.NewIOFS(fsys)); err != nil {
-			return err
+		if len(pending) > 0 {
+			msg := fmt.Sprintf("Do you want to push these migrations to the remote database?\n%s\n", confirmPushAll(pending))
+			if shouldPush, err := utils.NewConsole().PromptYesNo(ctx, msg, true); err != nil {
+				return err
+			} else if !shouldPush {
+				return errors.New(context.Canceled)
+			}
+			if err := migration.ApplyMigrations(ctx, pending, conn, afero.NewIOFS(fsys)); err != nil {
+				return err
+			}
+		} else {
+			fmt.Fprintln(os.Stderr, "Schema migrations are up to date.")
 		}
-		if includeSeed {
-			if err := apply.SeedDatabase(ctx, conn, fsys); err != nil {
+		if len(seeds) > 0 {
+			msg := fmt.Sprintf("Do you want to seed the remote database with these files?\n%s\n", confirmSeedAll(seeds))
+			if shouldPush, err := utils.NewConsole().PromptYesNo(ctx, msg, true); err != nil {
+				return err
+			} else if !shouldPush {
+				return errors.New(context.Canceled)
+			}
+			if err := migration.SeedData(ctx, seeds, conn, afero.NewIOFS(fsys)); err != nil {
 				return err
 			}
+		} else if includeSeed {
+			fmt.Fprintln(os.Stderr, "Seed files are up to date.")
 		}
 	}
 	fmt.Println("Finished " + utils.Aqua("supabase db push") + ".")
@@ -71,7 +106,18 @@ func Run(ctx context.Context, dryRun, ignoreVersionMismatch bool, includeRoles,
 func confirmPushAll(pending []string) (msg string) {
 	for _, path := range pending {
 		filename := filepath.Base(path)
-		msg += fmt.Sprintf(" • %s\n", filename)
+		msg += fmt.Sprintf(" • %s\n", utils.Bold(filename))
+	}
+	return msg
+}
+
+func confirmSeedAll(pending []migration.SeedFile) (msg string) {
+	for _, seed := range pending {
+		notice := seed.Path
+		if seed.Dirty {
+			notice += " (hash update)"
+		}
+		msg += fmt.Sprintf(" • %s\n", utils.Bold(notice))
 	}
 	return msg
 }
diff --git a/internal/db/reset/reset.go b/internal/db/reset/reset.go
index 88e59504..a9d2f4f4 100644
--- a/internal/db/reset/reset.go
+++ b/internal/db/reset/reset.go
@@ -100,11 +100,6 @@ func resetDatabase14(ctx context.Context, version string, fsys afero.Fs, options
 		return err
 	}
 	defer conn.Close(context.Background())
-	if utils.Config.Db.MajorVersion > 14 {
-		if err := start.SetupDatabase(ctx, conn, utils.DbId, os.Stderr, fsys); err != nil {
-			return err
-		}
-	}
 	return apply.MigrateAndSeed(ctx, version, conn, fsys)
 }
 
@@ -131,15 +126,7 @@ func resetDatabase15(ctx context.Context, version string, fsys afero.Fs, options
 	if err := start.WaitForHealthyService(ctx, start.HealthTimeout, utils.DbId); 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, utils.DbId, os.Stderr, fsys); err != nil {
-		return err
-	}
-	if err := apply.MigrateAndSeed(ctx, version, conn, fsys); err != nil {
+	if err := start.SetupLocalDatabase(ctx, version, fsys, os.Stderr, options...); err != nil {
 		return err
 	}
 	fmt.Fprintln(os.Stderr, "Restarting containers...")
diff --git a/internal/db/start/start.go b/internal/db/start/start.go
index 912f845d..822e9aff 100644
--- a/internal/db/start/start.go
+++ b/internal/db/start/start.go
@@ -147,7 +147,7 @@ EOF
 	}
 	// Initialize if we are on PG14 and there's no existing db volume
 	if utils.NoBackupVolume {
-		if err := setupDatabase(ctx, fsys, w, options...); err != nil {
+		if err := SetupLocalDatabase(ctx, "", fsys, w, options...); err != nil {
 			return err
 		}
 	}
@@ -308,7 +308,7 @@ func initSchema15(ctx context.Context, host string) error {
 	return nil
 }
 
-func setupDatabase(ctx context.Context, fsys afero.Fs, w io.Writer, options ...func(*pgx.ConnConfig)) error {
+func SetupLocalDatabase(ctx context.Context, version string, fsys afero.Fs, w io.Writer, options ...func(*pgx.ConnConfig)) error {
 	conn, err := utils.ConnectLocalPostgres(ctx, pgconn.Config{}, options...)
 	if err != nil {
 		return err
@@ -317,12 +317,16 @@ func setupDatabase(ctx context.Context, fsys afero.Fs, w io.Writer, options ...f
 	if err := SetupDatabase(ctx, conn, utils.DbId, w, fsys); err != nil {
 		return err
 	}
-	return apply.MigrateAndSeed(ctx, "", conn, fsys)
+	return apply.MigrateAndSeed(ctx, version, conn, fsys)
 }
 
 func SetupDatabase(ctx context.Context, conn *pgx.Conn, host string, w io.Writer, fsys afero.Fs) error {
 	if err := initSchema(ctx, conn, host, w); err != nil {
 		return err
 	}
-	return apply.CreateCustomRoles(ctx, conn, fsys)
+	err := migration.SeedGlobals(ctx, []string{utils.CustomRolesPath}, conn, afero.NewIOFS(fsys))
+	if errors.Is(err, os.ErrNotExist) {
+		return nil
+	}
+	return err
 }
diff --git a/internal/link/link.go b/internal/link/link.go
index 3a283107..1d3824b8 100644
--- a/internal/link/link.go
+++ b/internal/link/link.go
@@ -183,7 +183,10 @@ func linkDatabase(ctx context.Context, config pgconn.Config, options ...func(*pg
 	defer conn.Close(context.Background())
 	updatePostgresConfig(conn)
 	// If `schema_migrations` doesn't exist on the remote database, create it.
-	return migration.CreateMigrationTable(ctx, conn)
+	if err := migration.CreateMigrationTable(ctx, conn); err != nil {
+		return err
+	}
+	return migration.CreateSeedTable(ctx, conn)
 }
 
 func linkDatabaseVersion(ctx context.Context, projectRef string, fsys afero.Fs) error {
diff --git a/internal/migration/apply/apply.go b/internal/migration/apply/apply.go
index 9ead81dd..224b342f 100644
--- a/internal/migration/apply/apply.go
+++ b/internal/migration/apply/apply.go
@@ -2,9 +2,7 @@ package apply
 
 import (
 	"context"
-	"os"
 
-	"github.com/go-errors/errors"
 	"github.com/jackc/pgx/v4"
 	"github.com/spf13/afero"
 	"github.com/supabase/cli/internal/migration/list"
@@ -20,20 +18,16 @@ func MigrateAndSeed(ctx context.Context, version string, conn *pgx.Conn, fsys af
 	if err := migration.ApplyMigrations(ctx, migrations, conn, afero.NewIOFS(fsys)); err != nil {
 		return err
 	}
-	if !utils.Config.Db.Seed.Enabled {
-		return nil
-	}
-	return SeedDatabase(ctx, conn, fsys)
-}
-
-func SeedDatabase(ctx context.Context, conn *pgx.Conn, fsys afero.Fs) error {
-	return migration.SeedData(ctx, utils.Config.Db.Seed.SqlPaths, conn, afero.NewIOFS(fsys))
+	return applySeedFiles(ctx, conn, fsys)
 }
 
-func CreateCustomRoles(ctx context.Context, conn *pgx.Conn, fsys afero.Fs) error {
-	err := migration.SeedGlobals(ctx, []string{utils.CustomRolesPath}, conn, afero.NewIOFS(fsys))
-	if errors.Is(err, os.ErrNotExist) {
+func applySeedFiles(ctx context.Context, conn *pgx.Conn, fsys afero.Fs) error {
+	if !utils.Config.Db.Seed.Enabled {
 		return nil
 	}
-	return err
+	seeds, err := migration.GetPendingSeeds(ctx, utils.Config.Db.Seed.SqlPaths, conn, afero.NewIOFS(fsys))
+	if err != nil {
+		return err
+	}
+	return migration.SeedData(ctx, seeds, conn, afero.NewIOFS(fsys))
 }
diff --git a/internal/utils/flags/db_url.go b/internal/utils/flags/db_url.go
index 6fc91217..d089f2d1 100644
--- a/internal/utils/flags/db_url.go
+++ b/internal/utils/flags/db_url.go
@@ -50,6 +50,11 @@ func ParseDatabaseConfig(flagSet *pflag.FlagSet, fsys afero.Fs) error {
 	// Update connection config
 	switch connType {
 	case direct:
+		if err := utils.Config.Load("", utils.NewRootFS(fsys)); err != nil {
+			if !errors.Is(err, os.ErrNotExist) {
+				return err
+			}
+		}
 		if flag := flagSet.Lookup("db-url"); flag != nil {
 			config, err := pgconn.ParseConfig(flag.Value.String())
 			if err != nil {
diff --git a/pkg/migration/file.go b/pkg/migration/file.go
index 04a94556..79da1c86 100644
--- a/pkg/migration/file.go
+++ b/pkg/migration/file.go
@@ -2,6 +2,8 @@ package migration
 
 import (
 	"context"
+	"crypto/sha256"
+	"encoding/hex"
 	"io"
 	"io/fs"
 	"path/filepath"
@@ -24,6 +26,22 @@ type MigrationFile struct {
 var migrateFilePattern = regexp.MustCompile(`^([0-9]+)_(.*)\.sql$`)
 
 func NewMigrationFromFile(path string, fsys fs.FS) (*MigrationFile, error) {
+	lines, err := parseFile(path, fsys)
+	if err != nil {
+		return nil, err
+	}
+	file := MigrationFile{Statements: lines}
+	// Parse version from file name
+	filename := filepath.Base(path)
+	matches := migrateFilePattern.FindStringSubmatch(filename)
+	if len(matches) > 2 {
+		file.Version = matches[1]
+		file.Name = matches[2]
+	}
+	return &file, nil
+}
+
+func parseFile(path string, fsys fs.FS) ([]string, error) {
 	sql, err := fsys.Open(path)
 	if err != nil {
 		return nil, errors.Errorf("failed to open migration file: %w", err)
@@ -37,17 +55,7 @@ func NewMigrationFromFile(path string, fsys fs.FS) (*MigrationFile, error) {
 			}
 		}
 	}
-	file, err := NewMigrationFromReader(sql)
-	if err == nil {
-		// Parse version from file name
-		filename := filepath.Base(path)
-		matches := migrateFilePattern.FindStringSubmatch(filename)
-		if len(matches) > 2 {
-			file.Version = matches[1]
-			file.Name = matches[2]
-		}
-	}
-	return file, err
+	return parser.SplitAndTrim(sql)
 }
 
 func NewMigrationFromReader(sql io.Reader) (*MigrationFile, error) {
@@ -112,12 +120,40 @@ func (m *MigrationFile) insertVersionSQL(conn *pgx.Conn, batch *pgconn.Batch) er
 	return nil
 }
 
-func (m *MigrationFile) ExecBatchWithCache(ctx context.Context, conn *pgx.Conn) error {
+type SeedFile struct {
+	Path  string
+	Hash  string
+	Dirty bool `db:"-"`
+}
+
+func NewSeedFile(path string, fsys fs.FS) (*SeedFile, error) {
+	sql, err := fsys.Open(path)
+	if err != nil {
+		return nil, errors.Errorf("failed to open seed file: %w", err)
+	}
+	defer sql.Close()
+	hash := sha256.New()
+	if _, err := io.Copy(hash, sql); err != nil {
+		return nil, errors.Errorf("failed to hash file: %w", err)
+	}
+	digest := hex.EncodeToString(hash.Sum(nil))
+	return &SeedFile{Path: path, Hash: digest}, nil
+}
+
+func (m *SeedFile) ExecBatchWithCache(ctx context.Context, conn *pgx.Conn, fsys fs.FS) error {
+	// Parse each file individually to reduce memory usage
+	lines, err := parseFile(m.Path, fsys)
+	if err != nil {
+		return err
+	}
 	// Data statements don't mutate schemas, safe to use statement cache
 	batch := pgx.Batch{}
-	for _, line := range m.Statements {
-		batch.Queue(line)
+	if !m.Dirty {
+		for _, line := range lines {
+			batch.Queue(line)
+		}
 	}
+	batch.Queue(UPSERT_SEED_FILE, m.Path, m.Hash)
 	// No need to track version here because there are no schema changes
 	if err := conn.SendBatch(ctx, &batch).Close(); err != nil {
 		return errors.Errorf("failed to send batch: %w", err)
diff --git a/pkg/migration/history.go b/pkg/migration/history.go
index 6dc8b5fc..5ff77ae6 100644
--- a/pkg/migration/history.go
+++ b/pkg/migration/history.go
@@ -21,6 +21,9 @@ const (
 	TRUNCATE_VERSION_TABLE   = "TRUNCATE supabase_migrations.schema_migrations"
 	SELECT_VERSION_TABLE     = "SELECT * FROM supabase_migrations.schema_migrations"
 	LIST_MIGRATION_VERSION   = "SELECT version FROM supabase_migrations.schema_migrations ORDER BY version"
+	CREATE_SEED_TABLE        = "CREATE TABLE IF NOT EXISTS supabase_migrations.seed_files (path text NOT NULL PRIMARY KEY, hash text NOT NULL)"
+	UPSERT_SEED_FILE         = "INSERT INTO supabase_migrations.seed_files(path, hash) VALUES($1, $2) ON CONFLICT (path) DO UPDATE SET hash = EXCLUDED.hash"
+	SELECT_SEED_TABLE        = "SELECT path, hash FROM supabase_migrations.seed_files"
 )
 
 // TODO: support overriding `supabase_migrations.schema_migrations` with user defined <schema>.<table>
@@ -46,3 +49,24 @@ func ReadMigrationTable(ctx context.Context, conn *pgx.Conn) ([]MigrationFile, e
 	}
 	return pgxv5.CollectRows[MigrationFile](rows)
 }
+
+func CreateSeedTable(ctx context.Context, conn *pgx.Conn) error {
+	// This must be run without prepared statements because each statement in the batch depends on
+	// the previous schema change. The lock timeout will be reset when implicit transaction ends.
+	batch := pgconn.Batch{}
+	batch.ExecParams(SET_LOCK_TIMEOUT, nil, nil, nil, nil)
+	batch.ExecParams(CREATE_VERSION_SCHEMA, nil, nil, nil, nil)
+	batch.ExecParams(CREATE_SEED_TABLE, nil, nil, nil, nil)
+	if _, err := conn.PgConn().ExecBatch(ctx, &batch).ReadAll(); err != nil {
+		return errors.Errorf("failed to create migration table: %w", err)
+	}
+	return nil
+}
+
+func ReadSeedTable(ctx context.Context, conn *pgx.Conn) ([]SeedFile, error) {
+	rows, err := conn.Query(ctx, SELECT_SEED_TABLE)
+	if err != nil {
+		return nil, errors.Errorf("failed to read seed table: %w", err)
+	}
+	return pgxv5.CollectRows[SeedFile](rows)
+}
diff --git a/pkg/migration/seed.go b/pkg/migration/seed.go
index 62a2c875..988e8d4b 100644
--- a/pkg/migration/seed.go
+++ b/pkg/migration/seed.go
@@ -7,16 +7,70 @@ import (
 	"os"
 	"path/filepath"
 
+	"github.com/go-errors/errors"
+	"github.com/jackc/pgconn"
+	"github.com/jackc/pgerrcode"
 	"github.com/jackc/pgx/v4"
 )
 
-func SeedData(ctx context.Context, pending []string, conn *pgx.Conn, fsys fs.FS) error {
-	for _, path := range pending {
-		fmt.Fprintf(os.Stderr, "Seeding data from %s...\n", path)
-		// Batch seed commands, safe to use statement cache
-		if seed, err := NewMigrationFromFile(path, fsys); err != nil {
+func getRemoteSeeds(ctx context.Context, conn *pgx.Conn) (map[string]string, error) {
+	remotes, err := ReadSeedTable(ctx, conn)
+	if err != nil {
+		var pgErr *pgconn.PgError
+		if errors.As(err, &pgErr) && pgErr.Code == pgerrcode.UndefinedTable {
+			// If seed table is undefined, the remote project has no migrations
+			return nil, nil
+		}
+		return nil, err
+	}
+	applied := make(map[string]string, len(remotes))
+	for _, seed := range remotes {
+		applied[seed.Path] = seed.Hash
+	}
+	return applied, nil
+}
+
+func GetPendingSeeds(ctx context.Context, locals []string, conn *pgx.Conn, fsys fs.FS) ([]SeedFile, error) {
+	if len(locals) == 0 {
+		return nil, nil
+	}
+	applied, err := getRemoteSeeds(ctx, conn)
+	if err != nil {
+		return nil, err
+	}
+	var pending []SeedFile
+	for _, path := range locals {
+		seed, err := NewSeedFile(path, fsys)
+		if err != nil {
+			return nil, err
+		}
+		if hash, exists := applied[seed.Path]; exists {
+			// Skip seed files that already exist
+			if hash == seed.Hash {
+				continue
+			}
+			// Mark seed file as dirty
+			seed.Dirty = true
+		}
+		pending = append(pending, *seed)
+	}
+	return pending, nil
+}
+
+func SeedData(ctx context.Context, pending []SeedFile, conn *pgx.Conn, fsys fs.FS) error {
+	if len(pending) > 0 {
+		if err := CreateSeedTable(ctx, conn); err != nil {
 			return err
-		} else if err := seed.ExecBatchWithCache(ctx, conn); err != nil {
+		}
+	}
+	for _, seed := range pending {
+		if seed.Dirty {
+			fmt.Fprintf(os.Stderr, "Updating seed hash to %s...\n", seed.Path)
+		} else {
+			fmt.Fprintf(os.Stderr, "Seeding data from %s...\n", seed.Path)
+		}
+		// Batch seed commands, safe to use statement cache
+		if err := seed.ExecBatchWithCache(ctx, conn, fsys); err != nil {
 			return err
 		}
 	}
diff --git a/pkg/pgxv5/rows.go b/pkg/pgxv5/rows.go
index 299ce868..96aa16f5 100644
--- a/pkg/pgxv5/rows.go
+++ b/pkg/pgxv5/rows.go
@@ -104,18 +104,11 @@ func appendScanTargets(dstElemValue reflect.Value, scanTargets []any, fldDescs [
 				return nil, err
 			}
 		} else {
-			dbTag, dbTagPresent := sf.Tag.Lookup(structTagKey)
-			if dbTagPresent {
-				dbTag = strings.Split(dbTag, ",")[0]
-			}
-			if dbTag == "-" {
+			colName := GetColumnName(sf)
+			if len(colName) == 0 {
 				// Field is ignored, skip it.
 				continue
 			}
-			colName := dbTag
-			if !dbTagPresent {
-				colName = sf.Name
-			}
 			fpos := fieldPosByName(fldDescs, colName)
 			if fpos == -1 || fpos >= len(scanTargets) {
 				return nil, errors.Errorf("cannot find field %s in returned row", colName)
@@ -126,3 +119,14 @@ func appendScanTargets(dstElemValue reflect.Value, scanTargets []any, fldDescs [
 
 	return scanTargets, err
 }
+
+func GetColumnName(sf reflect.StructField) string {
+	dbTag, dbTagPresent := sf.Tag.Lookup(structTagKey)
+	if !dbTagPresent {
+		return sf.Name
+	}
+	if dbTag = strings.Split(dbTag, ",")[0]; dbTag != "-" {
+		return dbTag
+	}
+	return ""
+}


Test patch:
diff --git a/internal/db/push/push_test.go b/internal/db/push/push_test.go
index 1616216d..3a3ff3cd 100644
--- a/internal/db/push/push_test.go
+++ b/internal/db/push/push_test.go
@@ -2,6 +2,8 @@ package push
 
 import (
 	"context"
+	"crypto/sha256"
+	"encoding/hex"
 	"os"
 	"path/filepath"
 	"testing"
@@ -146,7 +148,7 @@ func TestPushAll(t *testing.T) {
 
 	t.Run("throws error on roles failure", func(t *testing.T) {
 		// Setup in-memory fs
-		fsys := &fstest.OpenErrorFs{DenyPath: utils.CustomRolesPath}
+		fsys := &fstest.StatErrorFs{DenyPath: utils.CustomRolesPath}
 		path := filepath.Join(utils.MigrationsDir, "0_test.sql")
 		require.NoError(t, afero.WriteFile(fsys, path, []byte{}, 0644))
 		// Setup mock postgres
@@ -161,24 +163,30 @@ func TestPushAll(t *testing.T) {
 	})
 
 	t.Run("throws error on seed failure", func(t *testing.T) {
+		digest := hex.EncodeToString(sha256.New().Sum(nil))
 		seedPath := filepath.Join(utils.SupabaseDirPath, "seed.sql")
 		utils.Config.Db.Seed.SqlPaths = []string{seedPath}
 		// Setup in-memory fs
-		fsys := &fstest.OpenErrorFs{DenyPath: seedPath}
-		_, _ = fsys.Create(seedPath)
+		fsys := afero.NewMemMapFs()
+		require.NoError(t, afero.WriteFile(fsys, seedPath, []byte{}, 0644))
 		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(migration.LIST_MIGRATION_VERSION).
+			Reply("SELECT 0").
+			Query(migration.SELECT_SEED_TABLE).
 			Reply("SELECT 0")
 		helper.MockMigrationHistory(conn).
 			Query(migration.INSERT_MIGRATION_VERSION, "0", "test", nil).
 			Reply("INSERT 0 1")
+		helper.MockSeedHistory(conn).
+			Query(migration.UPSERT_SEED_FILE, seedPath, digest).
+			ReplyError(pgerrcode.NotNullViolation, `null value in column "hash" of relation "seed_files"`)
 		// Run test
 		err := Run(context.Background(), false, false, false, true, dbConfig, fsys, conn.Intercept)
 		// Check error
-		assert.ErrorIs(t, err, os.ErrPermission)
+		assert.ErrorContains(t, err, `ERROR: null value in column "hash" of relation "seed_files" (SQLSTATE 23502)`)
 	})
 }
diff --git a/internal/db/start/start_test.go b/internal/db/start/start_test.go
index 8d1816b0..96d97da8 100644
--- a/internal/db/start/start_test.go
+++ b/internal/db/start/start_test.go
@@ -6,7 +6,6 @@ import (
 	"io"
 	"net/http"
 	"os"
-	"path/filepath"
 	"testing"
 
 	"github.com/docker/docker/api/types"
@@ -52,8 +51,6 @@ func TestInitBranch(t *testing.T) {
 
 func TestStartDatabase(t *testing.T) {
 	t.Run("initialize main branch", func(t *testing.T) {
-		seedPath := filepath.Join(utils.SupabaseDirPath, "seed.sql")
-		utils.Config.Db.Seed.SqlPaths = []string{seedPath}
 		utils.Config.Db.MajorVersion = 15
 		utils.DbId = "supabase_db_test"
 		utils.ConfigId = "supabase_config_test"
@@ -62,8 +59,6 @@ func TestStartDatabase(t *testing.T) {
 		fsys := afero.NewMemMapFs()
 		roles := "create role test"
 		require.NoError(t, afero.WriteFile(fsys, utils.CustomRolesPath, []byte(roles), 0644))
-		seed := "INSERT INTO employees(name) VALUES ('Alice')"
-		require.NoError(t, afero.WriteFile(fsys, seedPath, []byte(seed), 0644))
 		// Setup mock docker
 		require.NoError(t, apitest.MockDocker(utils.Docker))
 		defer gock.OffAll()
@@ -91,9 +86,7 @@ func TestStartDatabase(t *testing.T) {
 		conn := pgtest.NewConn()
 		defer conn.Close(t)
 		conn.Query(roles).
-			Reply("CREATE ROLE").
-			Query(seed).
-			Reply("INSERT 0 1")
+			Reply("CREATE ROLE")
 		// Run test
 		err := StartDatabase(context.Background(), fsys, io.Discard, conn.Intercept)
 		// Check error
@@ -259,7 +252,7 @@ func TestSetupDatabase(t *testing.T) {
 			Query(roles).
 			Reply("CREATE ROLE")
 		// Run test
-		err := setupDatabase(context.Background(), fsys, io.Discard, conn.Intercept)
+		err := SetupLocalDatabase(context.Background(), "", fsys, io.Discard, conn.Intercept)
 		// Check error
 		assert.NoError(t, err)
 		assert.Empty(t, apitest.ListUnmatchedRequests())
@@ -268,12 +261,13 @@ func TestSetupDatabase(t *testing.T) {
 	t.Run("throws error on connect failure", func(t *testing.T) {
 		utils.Config.Db.Port = 0
 		// Run test
-		err := setupDatabase(context.Background(), nil, io.Discard)
+		err := SetupLocalDatabase(context.Background(), "", nil, io.Discard)
 		// Check error
 		assert.ErrorContains(t, err, "invalid port (outside range)")
 	})
 
 	t.Run("throws error on init failure", func(t *testing.T) {
+		utils.Config.Realtime.Enabled = true
 		utils.Config.Db.Port = 5432
 		// Setup mock docker
 		require.NoError(t, apitest.MockDocker(utils.Docker))
@@ -285,7 +279,7 @@ func TestSetupDatabase(t *testing.T) {
 		conn := pgtest.NewConn()
 		defer conn.Close(t)
 		// Run test
-		err := setupDatabase(context.Background(), nil, io.Discard, conn.Intercept)
+		err := SetupLocalDatabase(context.Background(), "", nil, io.Discard, conn.Intercept)
 		// Check error
 		assert.ErrorContains(t, err, "network error")
 		assert.Empty(t, apitest.ListUnmatchedRequests())
@@ -308,7 +302,7 @@ func TestSetupDatabase(t *testing.T) {
 		conn := pgtest.NewConn()
 		defer conn.Close(t)
 		// Run test
-		err := setupDatabase(context.Background(), fsys, io.Discard, conn.Intercept)
+		err := SetupLocalDatabase(context.Background(), "", fsys, io.Discard, conn.Intercept)
 		// Check error
 		assert.ErrorIs(t, err, os.ErrPermission)
 		assert.Empty(t, apitest.ListUnmatchedRequests())
diff --git a/internal/link/link_test.go b/internal/link/link_test.go
index f3396129..edc95d8f 100644
--- a/internal/link/link_test.go
+++ b/internal/link/link_test.go
@@ -46,6 +46,7 @@ func TestLinkCommand(t *testing.T) {
 		conn := pgtest.NewConn()
 		defer conn.Close(t)
 		helper.MockMigrationHistory(conn)
+		helper.MockSeedHistory(conn)
 		// Flush pending mocks after test execution
 		defer gock.OffAll()
 		gock.New(utils.DefaultApiHost).
@@ -279,6 +280,7 @@ func TestLinkDatabase(t *testing.T) {
 		})
 		defer conn.Close(t)
 		helper.MockMigrationHistory(conn)
+		helper.MockSeedHistory(conn)
 		// Run test
 		err := linkDatabase(context.Background(), dbConfig, conn.Intercept)
 		// Check error
@@ -294,6 +296,7 @@ func TestLinkDatabase(t *testing.T) {
 		})
 		defer conn.Close(t)
 		helper.MockMigrationHistory(conn)
+		helper.MockSeedHistory(conn)
 		// Run test
 		err := linkDatabase(context.Background(), dbConfig, conn.Intercept)
 		// Check error
diff --git a/internal/migration/apply/apply_test.go b/internal/migration/apply/apply_test.go
index 25362c5f..28609374 100644
--- a/internal/migration/apply/apply_test.go
+++ b/internal/migration/apply/apply_test.go
@@ -6,7 +6,6 @@ import (
 	"path/filepath"
 	"testing"
 
-	"github.com/jackc/pgerrcode"
 	"github.com/spf13/afero"
 	"github.com/stretchr/testify/assert"
 	"github.com/stretchr/testify/require"
@@ -75,64 +74,3 @@ func TestMigrateDatabase(t *testing.T) {
 		assert.ErrorIs(t, err, os.ErrPermission)
 	})
 }
-
-func TestSeedDatabase(t *testing.T) {
-	seedPath := filepath.Join(utils.SupabaseDirPath, "seed.sql")
-	utils.Config.Db.Seed.SqlPaths = []string{seedPath}
-
-	t.Run("seeds from file", func(t *testing.T) {
-		// Setup in-memory fs
-		fsys := afero.NewMemMapFs()
-		// Setup seed file
-		sql := "INSERT INTO employees(name) VALUES ('Alice')"
-		require.NoError(t, afero.WriteFile(fsys, seedPath, []byte(sql), 0644))
-		// Setup mock postgres
-		conn := pgtest.NewConn()
-		defer conn.Close(t)
-		conn.Query(sql).
-			Reply("INSERT 0 1")
-		// Run test
-		err := SeedDatabase(context.Background(), conn.MockClient(t), fsys)
-		// Check error
-		assert.NoError(t, err)
-	})
-
-	t.Run("ignores missing seed", func(t *testing.T) {
-		sqlPaths := utils.Config.Db.Seed.SqlPaths
-		utils.Config.Db.Seed.SqlPaths = []string{}
-		t.Cleanup(func() { utils.Config.Db.Seed.SqlPaths = sqlPaths })
-		// Setup in-memory fs
-		fsys := afero.NewMemMapFs()
-		// Run test
-		err := SeedDatabase(context.Background(), nil, fsys)
-		// Check error
-		assert.NoError(t, err)
-	})
-
-	t.Run("throws error on read failure", func(t *testing.T) {
-		// Setup in-memory fs
-		fsys := &fstest.OpenErrorFs{DenyPath: seedPath}
-		_, _ = fsys.Create(seedPath)
-		// Run test
-		err := SeedDatabase(context.Background(), nil, fsys)
-		// Check error
-		assert.ErrorIs(t, err, os.ErrPermission)
-	})
-
-	t.Run("throws error on insert failure", func(t *testing.T) {
-		// Setup in-memory fs
-		fsys := afero.NewMemMapFs()
-		// Setup seed file
-		sql := "INSERT INTO employees(name) VALUES ('Alice')"
-		require.NoError(t, afero.WriteFile(fsys, seedPath, []byte(sql), 0644))
-		// Setup mock postgres
-		conn := pgtest.NewConn()
-		defer conn.Close(t)
-		conn.Query(sql).
-			ReplyError(pgerrcode.NotNullViolation, `null value in column "age" of relation "employees"`)
-		// Run test
-		err := SeedDatabase(context.Background(), conn.MockClient(t), fsys)
-		// Check error
-		assert.ErrorContains(t, err, `ERROR: null value in column "age" of relation "employees" (SQLSTATE 23502)`)
-	})
-}
diff --git a/internal/testing/helper/history.go b/internal/testing/helper/history.go
index b73d267e..95c846b7 100644
--- a/internal/testing/helper/history.go
+++ b/internal/testing/helper/history.go
@@ -17,3 +17,12 @@ func MockMigrationHistory(conn *pgtest.MockConn) *pgtest.MockConn {
 		Reply("ALTER TABLE")
 	return conn
 }
+
+func MockSeedHistory(conn *pgtest.MockConn) *pgtest.MockConn {
+	conn.Query(migration.SET_LOCK_TIMEOUT).
+		Query(migration.CREATE_VERSION_SCHEMA).
+		Reply("CREATE SCHEMA").
+		Query(migration.CREATE_SEED_TABLE).
+		Reply("CREATE TABLE")
+	return conn
+}
diff --git a/pkg/migration/seed_test.go b/pkg/migration/seed_test.go
index e81d814a..7e9cd4ac 100644
--- a/pkg/migration/seed_test.go
+++ b/pkg/migration/seed_test.go
@@ -8,50 +8,138 @@ import (
 	fs "testing/fstest"
 
 	"github.com/jackc/pgerrcode"
+	"github.com/jackc/pgx/v4"
 	"github.com/stretchr/testify/assert"
+	"github.com/stretchr/testify/require"
 	"github.com/supabase/cli/pkg/pgtest"
 )
 
 //go:embed testdata/seed.sql
 var testSeed string
 
-func TestSeedData(t *testing.T) {
+func TestPendingSeeds(t *testing.T) {
 	pending := []string{"testdata/seed.sql"}
 
-	t.Run("seeds from file", func(t *testing.T) {
+	t.Run("finds new seeds", func(t *testing.T) {
 		// Setup mock postgres
 		conn := pgtest.NewConn()
 		defer conn.Close(t)
-		conn.Query(testSeed).
-			Reply("INSERT 0 1")
+		conn.Query(SELECT_SEED_TABLE).
+			Reply("SELECT 0")
+		// Run test
+		seeds, err := GetPendingSeeds(context.Background(), pending, conn.MockClient(t), testMigrations)
+		// Check error
+		assert.NoError(t, err)
+		require.Len(t, seeds, 1)
+		assert.Equal(t, seeds[0].Path, pending[0])
+		assert.Equal(t, seeds[0].Hash, "61868484fc0ddca2a2022217629a9fd9a4cf1ca479432046290797d6d40ffcc3")
+		assert.False(t, seeds[0].Dirty)
+	})
+
+	t.Run("finds dirty seeds", func(t *testing.T) {
+		// Setup mock postgres
+		conn := pgtest.NewConn()
+		defer conn.Close(t)
+		conn.Query(SELECT_SEED_TABLE).
+			Reply("SELECT 1", SeedFile{Path: pending[0], Hash: "outdated"})
+		// Run test
+		seeds, err := GetPendingSeeds(context.Background(), pending, conn.MockClient(t), testMigrations)
+		// Check error
+		assert.NoError(t, err)
+		require.Len(t, seeds, 1)
+		assert.Equal(t, seeds[0].Path, pending[0])
+		assert.Equal(t, seeds[0].Hash, "61868484fc0ddca2a2022217629a9fd9a4cf1ca479432046290797d6d40ffcc3")
+		assert.True(t, seeds[0].Dirty)
+	})
+
+	t.Run("skips applied seed", func(t *testing.T) {
+		// Setup mock postgres
+		conn := pgtest.NewConn()
+		defer conn.Close(t)
+		conn.Query(SELECT_SEED_TABLE).
+			Reply("SELECT 1", SeedFile{Path: pending[0], Hash: "61868484fc0ddca2a2022217629a9fd9a4cf1ca479432046290797d6d40ffcc3"})
+		// Run test
+		seeds, err := GetPendingSeeds(context.Background(), pending, conn.MockClient(t), testMigrations)
+		// Check error
+		assert.NoError(t, err)
+		require.Empty(t, seeds)
+	})
+
+	t.Run("ignores missing seed table", func(t *testing.T) {
+		// Setup mock postgres
+		conn := pgtest.NewConn()
+		defer conn.Close(t)
+		conn.Query(SELECT_SEED_TABLE).
+			ReplyError(pgerrcode.UndefinedTable, `relation "seed_files" does not exist`)
 		// Run test
-		err := SeedData(context.Background(), pending, conn.MockClient(t), testMigrations)
+		_, err := GetPendingSeeds(context.Background(), pending, conn.MockClient(t), testMigrations)
 		// Check error
 		assert.NoError(t, err)
 	})
 
 	t.Run("throws error on missing file", func(t *testing.T) {
+		// Setup mock postgres
+		conn := pgtest.NewConn()
+		defer conn.Close(t)
+		conn.Query(SELECT_SEED_TABLE).
+			Reply("SELECT 0")
 		// Setup in-memory fs
 		fsys := fs.MapFS{}
 		// Run test
-		err := SeedData(context.Background(), pending, nil, fsys)
+		_, err := GetPendingSeeds(context.Background(), pending, conn.MockClient(t), fsys)
 		// Check error
 		assert.ErrorIs(t, err, os.ErrNotExist)
 	})
+}
+
+func TestSeedData(t *testing.T) {
+	t.Run("seeds from file", func(t *testing.T) {
+		seed := SeedFile{
+			Path:  "testdata/seed.sql",
+			Hash:  "61868484fc0ddca2a2022217629a9fd9a4cf1ca479432046290797d6d40ffcc3",
+			Dirty: true,
+		}
+		// Setup mock postgres
+		conn := pgtest.NewConn()
+		defer conn.Close(t)
+		mockSeedHistory(conn).
+			Query(UPSERT_SEED_FILE, seed.Path, seed.Hash).
+			Reply("INSERT 0 1")
+		// Run test
+		err := SeedData(context.Background(), []SeedFile{seed}, conn.MockClient(t), testMigrations)
+		// Check error
+		assert.NoError(t, err)
+	})
 
-	t.Run("throws error on insert failure", func(t *testing.T) {
+	t.Run("throws error on upsert failure", func(t *testing.T) {
+		seed := SeedFile{
+			Path: "testdata/seed.sql",
+			Hash: "61868484fc0ddca2a2022217629a9fd9a4cf1ca479432046290797d6d40ffcc3",
+		}
 		// Setup mock postgres
 		conn := pgtest.NewConn()
 		defer conn.Close(t)
-		conn.Query(testSeed).
+		mockSeedHistory(conn).
+			Query(testSeed+`;INSERT INTO supabase_migrations.seed_files(path, hash) VALUES( 'testdata/seed.sql' ,  '61868484fc0ddca2a2022217629a9fd9a4cf1ca479432046290797d6d40ffcc3' ) ON CONFLICT (path) DO UPDATE SET hash = EXCLUDED.hash`).
 			ReplyError(pgerrcode.NotNullViolation, `null value in column "age" of relation "employees"`)
 		// Run test
-		err := SeedData(context.Background(), pending, conn.MockClient(t), testMigrations)
+		err := SeedData(context.Background(), []SeedFile{seed}, conn.MockClient(t, func(cc *pgx.ConnConfig) {
+			cc.PreferSimpleProtocol = true
+		}), testMigrations)
 		// Check error
 		assert.ErrorContains(t, err, `ERROR: null value in column "age" of relation "employees" (SQLSTATE 23502)`)
 	})
 }
 
+func mockSeedHistory(conn *pgtest.MockConn) *pgtest.MockConn {
+	conn.Query(SET_LOCK_TIMEOUT).
+		Query(CREATE_VERSION_SCHEMA).
+		Reply("CREATE SCHEMA").
+		Query(CREATE_SEED_TABLE).
+		Reply("CREATE TABLE")
+	return conn
+}
+
 //go:embed testdata/1_globals.sql
 var testGlobals string
 
diff --git a/pkg/pgtest/mock.go b/pkg/pgtest/mock.go
index b73ad255..0f5e9bb8 100644
--- a/pkg/pgtest/mock.go
+++ b/pkg/pgtest/mock.go
@@ -153,7 +153,10 @@ func (r *MockConn) Reply(tag string, rows ...interface{}) *MockConn {
 		} else if t := reflect.TypeOf(rows[0]); t.Kind() == reflect.Struct {
 			s := reflect.ValueOf(rows[0])
 			for i := 0; i < s.NumField(); i++ {
-				name := t.Field(i).Name
+				name := pgxv5.GetColumnName(t.Field(i))
+				if len(name) == 0 {
+					continue
+				}
 				v := s.Field(i).Interface()
 				if fd := toFieldDescription(v); fd != nil {
 					fd.Name = []byte(name)
@@ -182,6 +185,9 @@ func (r *MockConn) Reply(tag string, rows ...interface{}) *MockConn {
 		} else if t := reflect.TypeOf(data); t.Kind() == reflect.Struct {
 			s := reflect.ValueOf(rows[0])
 			for i := 0; i < s.NumField(); i++ {
+				if name := pgxv5.GetColumnName(t.Field(i)); len(name) == 0 {
+					continue
+				}
 				v := s.Field(i).Interface()
 				if value, oid := r.encodeValueArg(v); oid > 0 {
 					dr.Values = append(dr.Values, value)