"after" in migration configuration should use VCS history if VCS enabled

[mplanchard]

Right now, it seems like the after configuration for migrations is closely coupled with a file format used by certain ORMs (e.g. prisma).

In our project, the files are named like 0001_whatever.sql, with no timestamp.

If after is going to refer to a timestamp rather than a migration number, it would be nice to do it in a more generalized way. The first thing that comes to mind is using git to determine the file's last modification time, if VCS integration is enabled.

Alternatively, a configuration option to use migration numbers would be nice, which could potentially take some kind of DSL to convert filenames to migration numbers (perhaps a sed command).

[mplanchard]

As it stands, we just use this make target, which works well enough:

# Only lint migrations after 172, since we can't change old ones, and
# 172-forward lint without errors currently.
LINTED_MIGRATIONS := $(shell fd . 'data/db/proxy/migrations' \
								--extension sql \
								--max-depth 1 \
								--format {/} \
								--exec bash -c '[[ "{/}" > "0172" ]] && echo {}')
# Lint db migrations. Note that the DB must be running for this check.
db-lint: .make/db-lint
.PHONY: db-lint

.make/db-lint: $(LINTED_MIGRATIONS) | .make
	if [[ "$?" ]]; then
		postgrestools check $?
	fi
	touch .make/db-lint

[psteinroe]

Thanks for the feedback! Did you see this part in the docs?

For pre-commit hooks and when working locally, use --staged to only lint files that have been staged. In CI environments, you most likely want to use --changed to only lint files that have been changed compared to your vcs.defaultBranch configuration. If defaultBranch is not set in your postgrestools.jsonc, use --since=REF to specify the base branch to compare against.

Stil, I will test this on the weekend, because I am quite sure we don't care about timestamps and simply parse the number. So maybe there is just a bug here and your case should work.

[psteinroe]

@mplanchard in the next release, after will work with numeric prefixes. So 0001_whatever.sql will work.

[mplanchard]

That's great, thanks!

Re: the --staged option, I did see that, but we generally like to be able to run totally equivalent checks in our local environment and CI, without needing to worry about the state of the git repository or whatever, although using --staged for a precommit hook is definitely something we might do.