Recommendation for reducing total migration files (eg. managing renaming across deploys)?

[karlhorky]

Hey @lukeed, hope things are going well with you!

Just wanted to ask for an official recommendation for keeping the total number of migration files low (eg. 1 per table).

Benefits:

1. Fewer files to look through for the original migration code for a particular table field

Drawbacks:

1. Editing existing migrations mean that the updates will need to be performed manually
2. The bigger issue is that renaming (re-sorting) migration files is a bit painstaking to manage across deploys, since the old names are saved in the existing databases

Right now, we're manually doing manual queries to change the migrations names and id (which is used for the order by Ley) in the migrations table on every deploy that changes existing database tables. But I was just wondering whether you would have a recommendation for an easier way.

As a caveat, I do understand that it seems a bit like we're working against the "only create new migrations" aspect that is often recommended about migrations. And I think if we had more tooling around being able to quickly track down the single source of truth for the current configuration of a table field, then that would be potentially an ok way around it. But it's also nice to be able to refer to a single file and trust that it's the latest version of the schema...

Alternatives Considered

Single schema file with diffs

Actually ideally, I would love for there to be only a single schema file and that diffs / morphs would be generated and applied to databases which are not up to date (feeling more like JSX rather than imperative DOM modification). I've been looking at things like pgkit and pg-differ and similar, but haven't found the time to move to this type of architecture yet.

Keywords for search

• move, moving, rename, renaming, order, reorder, reordering, sorting

[lukeed]

Migrations are append-only and will just grow over time. I've thought about looking for a smart auto-diff tool but these things make me very nervous because of the data migration part... everything is dandy when adding new columns or simply renaming, but sometimes you need more than that & I wouldn't want to trust an automatic destructor with that level of data responsibility.

So instead I let /migrations grow (since it should always be used as a source of truth for matching the latest DB schema) and then use my type interfaces as the "heres whats latest" snapshot.

In a past team, we would auto-migrate on deploy and then ran a hook that would auto-print the latest of all the schemas and that was committed into a latest-schemas.sql-like file, so you could see the true shapes in one file (without it overriding or affecting migrations). Supabase essentially does this with their supabase gen types command, but reconstruct it into TS types.

hope that helps~!

[karlhorky]

Great, thanks for the insights! I can see the appeal of append-only, much fewer moving parts 👍 automated morphing of a complex change could be indeed dangerous. If we go in this direction I'll want something bulletproof (or at least refusing to make potentially destructive changes without confirmation)

Codegen to a single schema file may be close enough to the workflow that I'm looking for that it could be helpful, maybe I'll take a look at some options there.

Thanks!