Request: additional delimiter in migration scripts to indicate section to be ignored when migrate up/down

[NLKNguyen]

Hi,

Thanks for this awesome utility. One thing I wish it also has is a delimiter like -- migrate:ignore to store testing, troubleshooting queries that shouldn't run in migration.
My current work-around is that I put migration code under -- migrate:up section like usual and store those test code under -- migrate:down section, and I simply don't use dbmate down command because that section is intended for test code only. So the obvious downside is that I can only use dbmate up command, which is not too bad.

However, having something like -- migrate:ignore will allow me to put the test code there, so I get to use both dbmate up & down as intended. I'm sure there are other use cases that -- migrate:ignore will help as well.

Thanks!

[rohitpaulk]

@NLKNguyen have you tried using SQL comments? i.e. something like this:

-- migrate:up
CREATE TABLE foo;

-- migrate:down
DROP TABLE foo;

-- testing
-- CREATE TABLE foobar;

[rohitpaulk]

(Note: I haven't tested the above, but I imagine that'd work)

[NLKNguyen]

@rohitpaulk that would require commenting out manually every time then uncommenting later. Unfortunately, that would be hard to maintain as the number of migration scripts increases.

I'm leaning into the idea of a shell script that will copy all *.sql in my migrations folder into a temporary folder then truncate the testing code in the copied sql scripts, and dbmate will point to that temporary folder instead. That will do it, but a built-in feature in dbmate would be cleaner.

[rohitpaulk]

@NLKNguyen not sure I understand the use case here 🙂 under what circumstances do you want these "testing" scripts to run?

[NLKNguyen]

They run when I work on the script interactively, let say I have some queries that I run manually to check a function in the -- migrate:up section. In other words, the up section is run by dbmate like usual, and the test/ignore section is to be run interactively in a query editor manually therefore not intended to be part of the migration.

One would say just put those test queries out to a separate file and not in the migration script. That's fair, but I find it more convenient to group them in the same script for easy lookup.

[NLKNguyen]

In case anyone is interested in a workaround in the meantime, I use a preprocessing method (Linux/macOS) to create copies of my migration scripts without the pseudo section -- migrate:ignore so that I can use dbmate up|down on those copies.

This way my source scripts still contain both the migration up/down sections and the testing section that will be ignored when copied over to a prepared folder for dbmate to execute.

# prepare the output directory
mkdir ./migrations

# for each SQL script in the working directory, 
# copy all lines above the marker line "-- migrate:ignore" into a new file with the same name in the output directory
ls -1 *.sql | xargs -I % awk -v outfile=./migrations/%  '/^-- migrate:ignore/{ exit }; { print $0 > outfile }' %

# then run migration using those new scripts in the output directory
dbmate --migrations-dir ./migrations up