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!

[rtrad89]

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!

Hello, are there any updates regarding Schema Diff CLI upstreaming? Migra #242 issue is blocking me, so would be nice to have another option