Re: Feature request: schema diff tool

On 24/04/2024 09:44, Neszt Tibor wrote:
> Hello,
>
> A diff tool that would generate create, drop, alter, etc. commands from
> the differences between 2 specified schemes would be very useful. The
> diff could even manage data, so there would be insert, delete update
> command outputs, although I think the schema diff management is much
> more important and necessary.
>
> Today, all modern applications are version-tracked, including the sql
> scheme. Now the schema changes must be handled twice: on the one hand,
> the schema must be modified, and on the other hand, the schema
> modification commands must also be written for the upgrade process. A
> good diff tool would allow only the schema to be modified.
>
> Such a tool already exists because the community needed it, e.g.
> apgdiff. I think the problem with this is that the concept isn't even
> good. I think this tool should be part of postgresql, because postgresql
> always knows what the 100% sql syntax is current, an external program,
> for example apgdiff can only follow changes afterwards, generating
> continuous problems.

Does a schema diff tool need / want to actually parse the SQL syntax?

On the other hand, an external tool can be developed independently of
the PostgreSQL release schedule. And you'd want the same tool to work
with different PostgreSQL versions. Those are reasons to *not* bundle it
with PostgreSQL itself. PostgreSQL has a rich ecosystem of tools with
different approaches and tradeoffs, and that's a good thing.

On the whole, -1 from me. I could be convinced otherwise if there's a
technical reason it would need to be part of PostgreSQL itself, but
otherwise it's better to not bundle it.

> Not to mention that an external application can
> stop, e.g. apgdiff is also no longer actively developed, so users who
> built on a diff tool are now in trouble.
>
> Furthermore, it is the least amount of work to do this on the postgresql
> development side, you have the expertise, the sql language processor, etc.

That's just asking for Someone Else to do the work. There are many other
schema diff tools out there. And you can get pretty far by just running
'pg_dump --schema-only' on both databases and comparing them with 'diff'.

--
Heikki Linnakangas
Neon (https://neon.tech)