Add option to run `pg_dump` into `migrations/schema.sql` on commit by default

[rattrayalex]

Feature description

The README currently recommends doing this yourself, and provides a straightforward example, but IMO this should be built-in and ideally enabled by default once proven out.

My thinking is that reviewing the final schema.sql should be core to the workflow of using graphile-migrate – this is an opinionated tool, and it should be okay to enforce this opinion (especially because some of its other opinions don't work so well without it, like the fact that you can accidentally leave a rnd() function lying around). I don't think a tool like this should be used without dumping and reviewing the schema.sql on every commit.

Ideally, any issues people have with this workflow should also be reported and fixes shared, rather than worked around in each user's bash scripts.

Disclaimer that I am a prospective user, not an active user – this is more a suggestion than a request.

Motivating example

Breaking changes

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 ❤️ (patreon)
• have an active support or consultancy contract with Graphile

[benjie]

I did consider it, but I’m not sure that pg_dump is the optimal experience for all users. Some people have other equally valid strategies (for example one person has set up a series of pg_dump scripts that dump different schemas and different resources into different files), so I don’t think enforcing a particular flow (e.g. single pg_dump) is best. Further, people may want to only dump schemas that graphile migrate is used with; I know migrate users that have per-tenant custom schemas that aren’t managed via migrations that they don’t want dumped via pg_dump, so they must pass custom options. Building it in seems like decreased flexibility and greater maintenance burden; though I do think it may be worth enabling (rather than commenting) in the default gmrc.

[rattrayalex]

Right – that's why it should be an option!

And some of these enhancements users have developed for themselves also sound useful to bake in, if possible. For example, a single migrations/schema.sql by default but an option to break it out by table etc would be pretty fantastic. Similarly, an option to pass extra options to pg_dump would make sense.

In sum:

pgDump: boolean, // true by default?
pgDumpOptions: []string,
pgDumpSplitFilesByTable: boolean, // false by default.

The reason I think this is important is that if I want to convince a team to use graphile-migrate, it shouldn't feel like core elements of the workflow are bolted-on by me, but baked in to the tool. Plus, maintaining shell scripts (embedded into a string or otherwise) is no fun 😅

[rattrayalex]

I'd also be interested in being able to edit the pg_dump and having a tool (not necessarily this one) generate an editable migration file. This might be accomplishable by calling into pgadmin's code for their "schema diff" feature: djrobstep/migra#148

This might help get closer to the more declarative workflows developers tend to like

[zimme]

It would be interesting if during development I could just edit the database using a GUI like TablePlus, have something like migra/pgadmin4 cli diff running in watch mode watching the dev database and the shadow database and generate sql migration ddl into current.sql or whatever for every change.

It would also be nice if this same process was watching pg_dump:ed files schema.sql and schema.current.sql or something so that one could choose to just change the dump of the schema instead of usinga GUI for it.

Alternatively, have something watch for changes to the database schema and dump the updated structure to schema.current.sql, then have something like pgadmin2 cli diff watcing the 2 schema files to generate the migration sql.

Then in the end, you would check on the auto generated sql for the migration and commit it once you're happy.

For some scenarios, you would just handwrite the migration sql yourself.

[benjie]

These are all great ideas, and why this functionality is not baked in. Had we baked it in, someone would think “this is the way” and there’d be less space for innovation. Graphile Migrate is only the foundation of what is to come, but what is to come has not yet fully formed. One day it will govern an excellent flow that will be comfortable and productive, but for now it’s up to users to figure out what works best for them. I’m also perfectly happy (in fact I frequently encourage!) for people to use other migration frameworks; I built this tool primarily for myself.