Clarification: Does goose no longer pick up migration files from subdirectories?

[preslavrachev]

It has been a while since I last used goose, and now I am trying to integrate it in my current project.

Due to the way we generate our SQL code (we use a generator called sqlc), we have multiple subdirectories with schema files in them. This way is preferred by sqlc, because we can let it generate Go code, based on mixing and matching of some schemas.

Anyway, I tried migrating everything by just using a path like this store/sql/schema/**/, but it will not work. I know that something like this used to work in the past.

What is the presently preferred way?

[mfridman]

I think I know what the issue is, but to confirm, do these subdirectories contain different timestamped migration files, and you're expecting goose to collect all the migrations in those subdirectories?

For this example, running goose -dir='store/sql/schema/*' status is expected to return:

Applied At                  Migration
=======================================
Pending                  -- 20220512230116_create_users.sql
Pending                  -- 20220512230129_create_orgs_table.sql
Pending                  -- 20220512230138_create_repos.sql
Pending                  -- 20220512230156_add_users_index.sql

.
└── sql
    └── schema
        ├── orgs
        │   └── 20220512230129_create_orgs_table.sql
        ├── repos
        │   └── 20220512230138_create_repos.sql
        └── users
            ├── 20220512230116_create_users.sql
            └── 20220512230156_add_users_index.sql

Note, I'm using * here because iirc Go doesn't support **.

[preslavrachev]

@mfridman This is exactly how my directory structure looks like. All I need is a simple syntax that picks up a flat list of all migrations in all subfolders, ordered by the timestamp.

Unfortunately if I execute the following call:

goose -dir store/sql/schema/*  postgres "postgres_url" status

I get

-dbstring="store/sql/schema/orgs": "store/sql/schema/users": unknown dialect

If I add quotation marks around the dir value, the error turns into:

goose run: failed to collect migrations: store/sql/schema/* directory does not exist

[mfridman]

Thanks for clarifying, this functionality hasn't exited in ~5 years.

The command I pasted above worked because I removed the .Stat check here:

goose/migrate.go

Lines 145 to 147 in b44efc3

	if _, err := fs.Stat(fsys, dirpath); errors.Is(err, fs.ErrNotExist) {
	return nil, fmt.Errorf("%s directory does not exist", dirpath)
	}

Need to add the quotes so the shell doesn't expand the path, and with latest goose the error is expected:

goose run: failed to collect migrations: store/sql/schema/* directory does not exist

I'm vague on the details, but this was probably added with the intention that all migration files be placed within a single directory. However, given how popular sqlc and other tools have become this seems worth investigating.

Would it be feasible to place all migration files in a single directory, and then point the individual sqlc packages at the schema file?

Lastly, I presume you're doing it this way because of, arguably, a limitation in sqlc?
sqlc-dev/sqlc#845 (comment)

[mfridman]

I've been going back and forth on this one.

This only really makes sense for timestamped migrations, because splitting sequential migrations across subdirectories will make reasoning about migrations quite difficult.

The way I'd imagine this working is a separate flag -r or -recursive which looks for migration files in all subdirectories from the -dir. These migrations must be timestamped, otherwise error.

The default, however, is to scan a single directory.

cc @VojtechVitek maybe you have some thoughts, but I've been using sqlc more and more lately, and this might be a common enough use case for us to support.

[VojtechVitek]

I didn't have this use case, as I prefer sequential migrations in a single directory. So, I don't really have any strong arguments for/against.

[stanislavprokopov]

We are also using multiple subdirectories, and right now we need to copy them all together into to a separate temp dir before we run the migration, so we would really benefit from a new flag that would handle this use-case automatically. Please consider adding this feature.

[tosone]

Here i add some logic for migrate support for subdir: go-sigma/migrate@2b1fed0. goose looks better than migrate.

[mfridman]

Yeah, alright. This should be too hard to implement. I'm not opposed to it and can see this being useful to folks