Subject: CLI for Schema Diff

From: Steve Chavez <[email-masked]>
Date: Wed, 10 Mar 2021 13:42:23 -0500

Hey all,

The Schema Diff tool is amazing, works much better than all the current OSS
alternatives.

However, it's only possible to use it from the GUI and thus it cannot be
used programmatically, which would be really useful for CI/CD
environments(deployment automation).

Would you guys be open to make the Schema Diff work through a CLI?

Best regards,
Steve

---

From: Steve Chavez <[email-masked]>
Date: Mon, 29 Mar 2021 18:41:20 -0500

Hey all,

In case anyone is interested, I've managed to enable a CLI mode for the
Schema Diff on this repo:

https://github.com/steve-chavez/pgadmin4/blob/cli/web/cli.py

It basically works by using a Flask test client that interacts with the
Schema Diff endpoints.
It's a single isolated file, I haven't patched any of the existing modules.

For a quickstart, there's also a docker image that can be used like:

docker run supabase/pgadmin-schema-diff \
  'postgres://postgres@host/diff_source' \
  'postgres://postgres@host/diff_target' \
  > diff.sql
## the stderr output shows the same messages as the Schema Diff GUI:

Starting schema diff...
Comparision started......0%
Comparing FTS Dictionaries ...35%
Comparing Functions ...50%
Comparing Trigger Functions ...60%
Comparing Sequences ...70%
Comparing Tables ...80%
Comparing Views ...90%
Done.

On Wed, 10 Mar 2021 at 13:42, Steve Chavez <[email-masked]> wrote:

> Hey all,
>
> The Schema Diff tool is amazing, works much better than all the current
> OSS alternatives.
>

---

From: Dave Page <[email-masked]>
Date: Wed, 31 Mar 2021 08:08:36 +0000

Hi

> Comparing Sequences ...70%
That's an interesting approach! Obviously the code is just a proof of
concept at the moment (redirecting stdout is masking errors for example),
but is this something you'd be interested in working on to become a more
fully featured and production quality CLI?

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: http://www.enterprisedb.com

---

From: Steve Chavez <[email-masked]>
Date: Wed, 31 Mar 2021 18:37:57 -0500

Yes, absolutely! What would be the next step? I'm all for doing a CLI that
covers pgAdmin quality standards.

---

From: Dave Page <[email-masked]>
Date: Thu, 1 Apr 2021 09:44:36 +0100

Hi

On Thu, Apr 1, 2021 at 12:38 AM Steve Chavez <[email-masked]> wrote:

> Yes, absolutely!

Cool!

> What would be the next step? I'm all for doing a CLI that covers pgAdmin
> quality standards.
>

I think there are a few things we'd need to do:

1) Agree a standard for command line syntax. CLIs can easily become very
messy - I've spent time designing such standards in the past
(unfortunately, I can't just share that), but some thoughts might be:

  - have a consistent <command> <command options> <module> <action> <action
options> syntax. For example;

        pgacli --output-format=json schema-diff generate-diff --source=...
--target=...
        pgacli core register-server --host=... --maintenance-db=... ...
        pgacli grant-wizard grant-acl --select --type=tables
--objects=schema.tb_*

    (I've assumed a module name of 'core' for things directly related to
the heart of pgAdmin)

    - Actions are in the form "verb-noun"
    - Every common option has both a short and a long form
    - Uncommon options have a long form only
    - Support various help options, showing appropriate info;

    pgacli -h
    pgacli --help
    pgacli core -h
    pgacli schema-diff --help

2) Figure out how to avoid the hacks in your existing code to do things
like:

    - Hide initialisation output
    - Setup the dummy SQLite database

3) Make the code modular and extensible; perhaps using a Python module per
CLI module.

4) Allow the use of saved connections as well as arbitrary connection
strings.

I think 2 is the hardest issue. It may be that we add a flag to the main
application code that tells it it's running under the CLI, so that it can
avoid doing things it doesn't need to in those cases. One example is the
module loading; we might want to skip that entirely under the CLI, and have
the CLI module that's being called load just the modules it needs.

---

From: Steve Chavez <[email-masked]>
Date: Mon, 19 Apr 2021 00:47:13 -0500

Hi Dave,

Thanks for the thoughtful reply. I got sidetracked but now I'm able to
continue work on the CLI.

> 1) Agree a standard for command line syntax.
> 3) Make the code modular and extensible; perhaps using a Python module
per CLI module.

Cool, I agree with the standard. From my understanding, the "core" CLI
module would be in charge of registering the servers, and thus it'll be
needed by all the other CLI modules right?

Regarding the grant wizard, I'm not familiar with the module so I can't be
of much help in implementing a CLI mode for it.

However, I can definitely help with both core and schema diff. Would you
agree if I scope my contribution to only those 2 modules?

like:

Absolutely. I've been meaning to speed up the CLI start-up time, my
implementation currently loads many unneeded modules. I'll also avoid the
hack to hide stdout.

strings.

Agree. I think saved connections will come with the "core" CLI module
implementation(the register-server command).

On Thu, 1 Apr 2021 at 03:44, Dave Page <[email-masked]> wrote:

>> > but is this something you'd be interested in working on to become a
>> more fully featured and production quality CLI?
>>
>> Yes, absolutely!
>>
>
> Cool!
>
>
>   - have a consistent <command> <command options> <module> <action>
> <action options> syntax. For example;
>

---

From: Dave Page <[email-masked]>
Date: Mon, 19 Apr 2021 09:22:44 +0100

Hi

> needed by all the other CLI modules right?

'needed' in the sense that the CLI would be quite incomplete without at
least the ability to add/delete servers and users I think. I don't think it
would be absolutely required from a technical perspective.

BTW; I'm open to better names than 'core'. 'pgadmin' might be one option.

> agree if I scope my contribution to only those 2 modules?

Oh, definitely. I certainly wasn't suggesting you write everything! Your
help on the CLI framework, and core/schema diff modules would certainly be
appreciated.

> implementation(the register-server command).

Right, that's my thinking.

Thanks!

>>         pgacli --output-format=json schema-diff generate-diff
>> --source=... --target=...
>>>>> In case anyone is interested, I've managed to enable a CLI mode for
>>>>> the Schema Diff on this repo:
>>>>>
>>>>> It basically works by using a Flask test client that interacts with
>>>>> the Schema Diff endpoints.

-- 
Dave Page
Blog: https://pgsnake.blogspot.com
Twitter: @pgsnake

EDB: https://www.enterprisedb.com