Feature request: Support common migration file formats

[atombender]

At our company, we use Goose and Dbmate for managing and running database migrations. (For historical reasons we need to use both.)

Goose has a format like this:

-- +goose Up
-- +goose StatementBegin
ALTER ...
-- +goose StatementEnd
-- +goose StatementBegin
CREATE ...
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
...

Dbmate has a format like this:

-- migrate:up
ALTER ...;
CREATE ...;

-- migrate:down
...

In addition, both tools have a way to declare whether to wrap statements in a transaction. With Goose:

-- +goose NO TRANSACTION

With Dbmate, it's defined on the up/down phases:

-- migrate:up transaction:false
CREATE INDEX ...

-- migrate:down transaction:false
DROP INDEX ...

Eugene looks like a great tool, but it is not possible to use it as-is with these tools because it assumes the entire file is run as a single transaction, and it does not treat the up/down phases separately.

I'm currently writing a small wrapper that parses the files and then runs Eugene on the separate statements, but it's a bunch of work parsing the statements, sending them to Eugene, then reconstructing the original line numbers, etc., especially since the non-transaction versions have to be split up further so that Eugene is run on each statement separately.

In short, it would be great to support these tools out of the box.

There are many other tools like these, but they generally work on the exact same principle. (Some of these, like Sequelize and ActiveRecord, define the migrations using code, which would not be parseable. I would not worry about those at this stage.) It should therefore be quite easy to make the input format pluggable.

[kaaveland]

We should be able to do this in eugene itself. I'll need to read up a bit on this, and ponder how to structure it, but I already have code that splits files based on comments, so for the linter this should be fairly easy (since it doesn't need to care if something is up/down, or in a transaction). I think it's probably a bit more involved for the tracer. Is there always both an up and a down migration? 🤔

[atombender]

👍 Yes, there is always both up/down, or at least I think it one can sensibly assume there will be.

[kaaveland]

To really support goose, it seems like we're also going to have to do environment variable interpolations, see: https://github.com/pressly/goose?tab=readme-ov-file#sql-migrations

It seems like goose generates timestamped file names that are compatible with the sequence number solution in eugene, and uses sequence number ordering for them. dbmate generates timestamped numbers that sort correctly as numbers as well. It doesn't seem to do interpolations.

I think the right way forward is probably to refactor so that we have special flyway support rather than make a complex model that supports N files -> 1 migration and 1 file -> N migration. Edit: Also have to create a model that can preserve line numbers. 🤔