Syntax error in multi statement migration puts you in a bad state

[Bachmann1234]

SQLIte project. Made a creation migration that included multiple statements. One of which had a flaw

~/code/babble-note/babble-backend-src/native master*
❯ migrant apply --all
Applying[Up]: "/Users/bachmann/code/babble-note/babble-backend-src/native/migrations/20200615025736_init/up.sql"
Error: MigrationError: Migration was unsucessful...
MigrationError: near "text": syntax error

I fixed the migration but it looks like the original statements were not rolled back (not running in a transaction maybe?)

❯ migrant apply --all
Applying[Up]: "/Users/bachmann/code/babble-note/babble-backend-src/native/migrations/20200615025736_init/up.sql"
Error: MigrationError: Migration was unsucessful...
MigrationError: table tag already exists

But I cant go down because the original migration is not marked as ... up

~/code/babble-note/babble-backend-src/native master*
❯ migrant apply --down
Error: MigrationComplete: No un-applied `Down` migrations found

[jaemk]

There's a note in the readme that should probably be larger

Note: SQL statements are batch executed as is. If you want your migration to happen atomically in a transaction you must manually wrap your statements in a transaction (begin transaction; ... commit;).

That requirement stems from two things:

1. going with the lowest common denominator, mysql doesn't support migrations in transaction
2. an original "feature" of this tool where it would fallback to using the command line utilities (psql, sqlite, mysqlsh) if the CLI was compiled without the database driver libraries.

Number 2 is no longer a "feature", now it's just tech debt that I need to get rid of - after that, db's that support ddl changes in transactions can run all migration sql in a transaction automatically.

[Bachmann1234]

:doh: thats what I get for not reading the readme close enough