Question: sql output for plan and excluding tables

[RWOverdijk]

Hi!

I am trying to use pg-schema-diff to create migrations for me. It works well, and I can get it to work with custom code, but I have a couple of questions. They might be dumb, because I am very new to this tool (I am also a bit dumb so there's that) and they might also be because I am trying to use this tool in a way that I shouldn't. I apologize in advance for both.

Output SQL

I was wondering if it's possible to output the plan as sql instead of json (which I now use to make sql) or pretty. So I can just write the output straight into a migration file (to then obviously be manually checked).

Exclude tables

I'm also curious if I can exclude tables. It suggests dropping a bunch of tables that it shouldn't. They're not part of my local schema (project sql files), but the db has them (and needs them for plugins). It is completely right in suggesting I delete them, I just don't know how to tell it not to.

Diff to local

Is it possible to diff 2 local schema files? The idea is to run this as part of feature branches to generate diffs for the migrations of a specific git branch. When the developer is done building the feature, the database will be up to date. So diffing the sql files to it will yield no results. I'd rather store a snapshot of the schema when development starts, and then diff that against the sql files to get the real diff.

Alternatively I could of course make a migration database that stores this snapshot as empty tables at the beginning of development, and diff against that. But that seems like a lot.

Slow?

Lastly I am curious if there's anything I am doing wrong, because it is taking a considerable amount of time to run. I have (besides 2 plugin tables) an empty database and a single .sql file in my schema directory. But running plan takes 16s (8s if I use --disable-plan-validation).

I am running:
time pg-schema-diff plan --schema-dir ./apps/server/src/domain/authors --dsn 'postgresql://xxx' --output-format json --disable-plan-validation

[bplunkett-stripe]

I was wondering if it's possible to output the plan as sql instead of json (which I now use to make sql) or pretty. So I can just write the output straight into a migration file (to then obviously be manually checked).

Can you not use jq? For example,

pg-schema-diff plan --dsn 'postgresql://localhost:5432/postgres' --schema-dir ~/stripe/temp/examplesql --disable-plan-validation --output-format json | jq -r '.statements.[].ddl'

I'm also curious if I can exclude tables. It suggests dropping a bunch of tables that it shouldn't. They're not part of my local schema (project sql files), but the db has them (and needs them for plugins). It is completely right in suggesting I delete them, I just don't know how to tell it not to.

This is not supported, but support could be easily added.

Diff to local

This is available in the library but not the CLI.

Slow?

Out of curiosity, what's the p50 for executing queries against the database? If your database is far away geographically, I might expect it to be slow because we pipe a bunch of sql to it one line at a time.

[RWOverdijk]

Thanks for the replies :)

Out of curiosity, what's the p50 for executing queries against the database?

No clue, I am not monitoring this yet 😅 I wrote that question thinking that 8 to 16s is too long for a single table, even if the server was on the other side of the planet. But you are right, I should test this. Also, this process will only run when rounding up a feature, so even if it is 16s that's completely fine.

Consider me informed!

[bplunkett-stripe]

Support for "diff to local" was added