Multiple databases with migrations in go creates entry in all databases

[lanpar]

example: have three databases named
"apple"
"cherry"
"peach"

have a migration say 10393.go for cherry only

result goose_db_version in all databases will have the 10393 entry

for sql no issues

[VojtechVitek]

Sorry, I don't understand the issue.

goose (by default) connects to a single database only:
https://github.com/pressly/goose/blob/master/cmd/goose/main.go#L65

Can you describe the problem?

[shawntoffel]

The go func registry is a global state:

goose/migrate.go

Line 22 in 3c2a65e

registeredGoMigrations = map[int64]*Migration{}

This is problematic for any application which connects to multiple databases and requires separate migrations per database.

In the following db1 will end up with go migrations from both dir1 and dir2, as will db2.

goose.Up(db1, "dir1")
goose.Up(db2, "dir2")

[VojtechVitek]

For now, you can use two separate binaries.

We can consider this feature for goose v3.

[NathanBaulch]

FWIW, I solved this problem by filtering out Go migrations that aren't within the target dir tree.
It's a bit hacky correlating the specified dir path to the executable metadata path (from runtime.Caller) but it works for now.

_, callerFile, _, _ := runtime.Caller(0)
rootPath := filepath.Join(filepath.Dir(callerFile), "../..")
cleanDir := strings.Trim(*dir, "./\\")
if filepath.IsAbs(cleanDir) {
	if relDir, err := filepath.Rel(rootPath, cleanDir); err != nil {
		log.Fatalf("dir must be within package root: %v\n", err)
	} else {
		cleanDir = relDir
	}
}
cleanDir = filepath.ToSlash(cleanDir) + "/"
migrations, err := goose.CollectMigrations(*dir, 0, goose.MaxVersion)
if err != nil {
	log.Fatalf("goose collect migrations: %v", err)
}
for i, m := range migrations {
	if filepath.Ext(m.Source) == ".go" && !strings.HasPrefix(m.Source[len(rootPath)+1:], cleanDir) {
		m.Version = math.MinInt64 + int64(i)
	}
}

[mfridman]

I think a cleaner design for /v4 would be to allow initializing a goose provider and eliminating the globals found throughout the library today.

E.g.,

goose.NewProvider can be initialized with the migrations directory, the db connection, a logger and everything else in-between. And then the methods could hang off the *Provider.

Obviously this would be a breaking change and a good chunk of work, but this would allow goose to be much more flexible.

[mcgaw]

I got bitten by this today. I'm building a custom go binary that embeds a migrations folder which has multiple database sub-folders (MySQL). The SQL migrations worked fine, but then surprisingly goose attempted to run the go migration on a different database as well.

It would be great if this "just worked" but will try fix by @NathanBaulch Where does this code live?

[mcgaw]

The way I approached this, which is perhaps slightly cleaner, was to make each migration folder (representing a MySQL database in my case) a Go package. Instead of registering Go migrations in the module init function, I use logic in the custom Go binary to call into a Register function that is defined in each database package, but only call the register function for the particular database I'm running the binary against. Each package level Register function is responsible for calling each of the registration functions in Go migration files themselves i.e Register12 etc. This solves the problem without too much fuss!

[mfridman]

This functionality has been added in v3.16.0 and higher.

A bit more detail in this blog post:

https://pressly.github.io/goose/blog/2023/goose-provider/#database-locking

Thank you to everyone for the feedback.