Comparison with pgAdmin Schema Diff tool

[tad-lispy]

Hey! Once again thank you for developing Migra. I absolutely adore the idea and design of it. I read "Your migrations are bad, and you should feel bad" and became an instant fan 😊

Since yesterday I'm experimenting with Migra and so far it looks very promising. Though as you know there are some limitations (types, somehow I can't get privileges to work, etc.).

Today I discovered that it is possible to generate a diff using new pgAdmin 4.25 Schema Diff tool. After few minutes of playing with it I have the impression it is more complete with regards to Postgres features.

One big advantage of Migra is that it's a well scoped command line utility. I want a tool that I can integrate with my build / deployment automation. With relatively small effort I integrated Migra with my hodgepodge of Makefile + Git + Docker Swarm + who knows what else lives there. As far as I can tell pgAdmin can only be used in point-and-click manner which doesn't lend itself very well to such workflow.

So I wonder what are your thoughts about the pgAdmin solution. Maybe there is some other aspect that I should consider. And given that both projects are free software and implemented in Python, maybe there is an opportunity to reuse parts of their system?

---

Edit: fixed the link after after @rattrayalex's comment

[djrobstep]

Hey, thanks for the good words! Much appreciated. I'm glad you enjoy migra (and enjoyed the talk too).

I'm yet to have a chance to play around with the new pgadmin diff tool, or check out the code - so I am not sure what is powering it all.

I'd be interested to compare its output to migra's - being an officially sanctioned postgres product I imagine it's pretty rigorous. It would be great if some of their code could be reused - I would have to look deeper at the specifics of it to see what's possible.

I'm not sure who is developing this exactly - I wasn't aware this was being developed at all until very recent. It would be cool if their code could be separated out so it could be reused.

[rattrayalex]

Fixed link to pg-admin schema diff docs: https://www.pgadmin.org/docs/pgadmin4/latest/schema_diff.html

[rattrayalex]

It looks like the code for this tool lives here, written in python: https://git.postgresql.org/gitweb/?p=pgadmin4.git;a=tree;f=web/pgadmin/tools/schema_diff

EDIT: It looks like the logic is tightly integrated to their webserver code and sprawls across the repo, so I doubt you'd be able to extract it. I was unable to directly import it, either; the pip package does not have an __init__.py, so nothing is importable, and even then it errors if you try to invoke ddl_compare outside of an http context.

I think the best bet would be to fire up the webserver and send an HTTP request to the '/ddl_compare/... endpoint.

That might be heavyweight, but it also might be worth it since it seems the ddl diff logic builds on quite a lot of work to model pg's internals in python.

[rattrayalex]

Update: I was able to directly import relevant pgadmin code, though I haven't yet tried to execute an actual comparison as I don't have an example db lying around.

import pgadmin4
import sys

# Must add pgadmin to the path for it to be importable.
sys.path.append(pgadmin4.__path__._path[0])

import pgadmin
import config
from pgadmin.tools.schema_diff.node_registry import SchemaDiffRegistry

# Minimal app initialization so that we can recursively import relevant files,
# since nodes like "table" are only registered with the SchemaDiffRegistry when the modules are imported.
# Initialization could perhaps be avoided by extracting more of the logic from `find_submodules` and `register_blueprint` here.
app = pgadmin.PgAdmin('pgadmin')
app.config.from_object(config)
for module in app.find_submodules('pgadmin'):
  try:
    app.register_blueprint(module)
  except:
    pass

# This should be populated with all desired db nodes (eg; `table`, `function`, `view`, `type`, etc).
print(SchemaDiffRegistry.get_registered_nodes())

# not yet tested, not sure what all these kwargs are for, but this should return the ddl diffs:
SchemaDiffRegistry.get_node_view('table').ddl_compare(
  source_sid=source_sid, source_did=source_did,
  source_scid=source_scid, target_sid=target_sid,
  target_did=target_did, target_scid=target_scid,
  source_oid=source_oid, target_oid=target_oid,
  comp_status=comp_status)

[djrobstep]

Nice. Would be very interested to hear how usable it is and how well it handles ordering dependencies and such.

[rattrayalex]

Same!

[bard]

Also very curious about this, @rattrayalex please share if you make new findings!

[steve-chavez]

Hey all!

Just wanted to let you know that I've managed to extract the pgadmin diff into a CLI tool here: https://github.com/supabase/pgadmin4/blob/cli/web/cli.py

There's also a docker container for trying out the CLI, it basically works like this:

docker run supabase/pgadmin-schema-diff \
  'postgres://user:pass@local:5432/diff_source' \
  'postgres://user:pass@production:5432/diff_target' \
  > diff_demo.sql

Starting schema diff...
Comparision started......0%
Comparing Event Triggers...2%
Comparing Extensions...4%
Comparing Languages...8%
Comparing Foreign Servers...14%
Comparing Foreign Tables of schema 'public'...28%
Comparing Tables of schema 'public'...50%
Comparing Domains of schema 'test_schema_diff'...66%
Comparing Foreign Tables of schema 'test_schema_diff'...68%
Comparing FTS Templates of schema 'test_schema_diff'...76%
Comparing Functions of schema 'test_schema_diff'...78%
Comparing Procedures of schema 'test_schema_diff'...80%
Comparing Tables of schema 'test_schema_diff'...90%
Comparing Types of schema 'test_schema_diff'...92%
Comparing Materialized Views of schema 'test_schema_diff'...96%
Done.

This was done in March(sorry for not reporting here earlier!) as part of Supabase launch week, you can see more details about this in the blog post.

I also chatted with one of the pgadmin devs, and they're very open about upstreaming the CLI in pgadmin4(thread)

If you have any feedback, just let me know!

[rattrayalex]

Amazing, great work @steve-chavez ! Supabase is doing some exciting things. Great to hear there's a chance of upstreaming as well!