Add a "compact" command

[ben-pr-p]

Feature description

Add a command,

graphile-migrate compact

which compresses all of the migrations in committed/ to one migration. I think this can be accomplished by running all the committed migrations on the shadow database and then dumping the output via pg_dump -c, where -c is the "clean" flag that includes statements to drop objects.

Motivating example

Over time, you can accumulate a large number of committed migrations. This is mostly a non-issue, but when it has become an issue is when I'm redefining a function for the more than 2nd time.

I'll search my code for CREATE OR REPLACE FUNCTION my_func_name to find the latest definition, and occasionally I'll find an older version to redefine and accidentally re-introduce a change incidental to my intended change.

Checking a dump mostly solves this, but it would be nice not to encounter it in the first place and to keep the committed migrations clean.

I'm also curious to see if other people have potential uses for this - not 100% committed to it being necessary / good.

Supporting development

I [tick all that apply]:

• am interested in building this feature myself
• am interested in collaborating on building this feature
• am willing to help testing this feature before it's released
• am willing to write a test-driven test suite for this feature (before it exists)
• am a Graphile sponsor ❤️
• have an active support or consultancy contract with Graphile

[benjie]

In general I'm loathe to do anything that encourages referencing the committed migrations; these are not meant for human consumption and you should instead reference the DB directly or a pg_dump or similar. I'm also unsure how this would work with the migration tracking table in the database - especially remote databases such as staging/production, or other developers?

[crubier]

I think that once the migration folder becomes too big to your taste (100 migrations? 1000? 10k?), it's still time to just:

• nuke the migrations folder
• nuke the migration table
• and dump your SQL schema in a clean new migration

It takes 5minutes, not too complicated for something I'd expect to do every man-year of work or so. But hard enough to not let any beginner do it and make a mess.

On the other hand, having this as a command in Graphile migrate looks like a foot gun that could cause problems

[benjie]

I should note that I have wanted a "squash"/"rebase" style feature before. Often you'll want something to end up being one migration, but whilst working on it you might want to commit phases (phase 1: insert new tables; phase 2: copy data over from old tables to new; phase 3: implement security and test it; phase 4: delete the original tables/etc). Ultimately you want this to be one commit, but due to the way you're manipulating the data within the tables you can't really do it idempotently. The ability to squash these 4 commits into one before you merge to master/ship would be neat. I've not evaluated what it'd involve yet, and it opens the same questions I had above (but with a much more obviously constrained use case).

[rattrayalex]

squash/rebase functionality – whether for a periodic compaction or a reconsideration of intentionally-staged commits yet to hit production –  sounds awesome.

One idea for implementation would be for graphile-migrate rebase to add a file migrations/removed-commits which lists the filenames of migrations that had previously been committed. Then, graphile-migrate migrate could inspect the list and remove the migrations from the table? (assuming the files are indeed missing and the migrations are indeed in the table).

Once the migrate has been run on all databases that previously contained these commits, the file could be deleted if desired.

[EyMaddis]

@ben-pr-p in your specific example you could implement a little script in afterAllMigrations or afterCurrent to dump all functions in to a folder than then setup VSCode to ignore the migrations folder completely when searching.
Maybe this is a nice way of doing things :)