Recursive Migration Directory Support (for sqlc compatibility)

[Dev-cmyser]

Hello!
Currently, Goose only supports a flat migration directory. However, tools like sqlc allow (and encourage) organizing migrations and schemas in subfolders (e.g., migrations/schema1/, migrations/schema2/). This is very useful for large projects with multiple schemas or logical separation of migrations.

Please add an option (or default behavior) to allow Goose to recursively search for migration files in all subdirectories of the specified GOOSE_MIGRATION_DIR (or -dir). This would make Goose compatible with sqlc and other tools that support/require such structure.

Example folder structure
migrations/
schema1/
001_init.sql
002_add_table.sql
schema2/
001_init.sql
002_add_table.sql

[mfridman]

goose is already compatible with sqlc. There's a post we put together here:

https://pressly.github.io/goose/blog/2024/goose-sqlc/

Duplicate of #349. Question though, in the example suppose we found all these files and flattened them:

001_init.sql
001_another.sql
002_add_users.sql
002_add_repos.sql

What order would you apply migrations? Or would this be a failure? Presumably, you'd want a flattened list like:

001_init.sql
002_another.sql
003_add_users.sql
004_add_repos.sql

But then it becomes hard to track all the versions, unless you're using goose to create the files in which case it always knows the next version. Could also use timestamps, but we have to support both timestamp and versions.

[mfridman]

Fwiw I'm not opposed to adding this functionality via an opt-in flag like --recursive, would just need some sane behavior to prevent duplicate versions.

[pcfreak30]

I just ran into this as im using goose as a library, and it has to combine migrations from many plugins. Im going to have to rewrite my fs.FS abstraction to handle this... I have been using dbmate and i can pass it a slice of paths in the set fs reference.

Having this would have been useful.

@mfridman

[mfridman]

Can you give an example of your file structure(s)?

[pcfreak30]

Can you give an example of your file structure(s)?

They are dynamic.The filesystem provided to the migration lib is an abstraction that is tracking many embed.FS

an example plugin has

// Package migrations provides database migration support for the abuse plugin
// It embeds and serves SQL migration files for both MySQL and SQLite databases
package migrations

import (
	"embed"
	"io/fs"
)

//go:embed mysql/*.sql
var mysqlFS embed.FS // Embedded MySQL migration files

//go:embed sqlite/*.sql
var sqliteFS embed.FS // Embedded SQLite migration files

// GetMySQL returns the filesystem containing MySQL migration files
// These migrations are used when the plugin is running with a MySQL database
func GetMySQL() fs.FS {
	sub, err := fs.Sub(mysqlFS, "mysql")
	if err != nil {
		panic(err)
	}
	return sub
}

// GetSQLite returns the filesystem containing SQLite migration files
// These migrations are used when the plugin is running with a SQLite database
func GetSQLite() fs.FS {
	sub, err := fs.Sub(sqliteFS, "sqlite")
	if err != nil {
		panic(err)
	}
	return sub
}

		Migrations: core.DBMigration{
			core.DB_TYPE_MYSQL:  migrations.GetMySQL(),
			core.DB_TYPE_SQLITE: migrations.GetSQLite(),
		},

I then aggregate everything and give a virtual FS view for the migration tool, and every plugin gets a folder with its name, and prefix using a number index to keep a controlled order.

So there is no true real fs directly, just abstractions based on the FS interface to allow the migrator to transparently read everything.