Combine tern and pg-schema-diff

[igorgatis]

Over time, multiple migrations makes life hard to get a clear view of the actual database schema. I was wondering whether the schema could keept as the "desired schema" and use pg-schema-diff to generate the diffs meant to be applied by tern.

For example, at the very beggining, the schema.sql looks like this:

create table t1(
  id serial primary key
);

Given that the schema is empty, the auto-generated migration should look like this:

create table t1(
  id serial primary key
);
---- create above / drop below ----
drop table t1;

Then, someone adds a column to t1 byediting schema.sql file:

create table t1(
  id serial primary key,
  placebo varchar not null
);

The auto-generated migration should look like this:

alter table t1 add column placebo varchar not null;
---- create above / drop below ----
alter table t1 drop column placebo;

This way, schema.sql reprensets the desired state and each migration is automatically generated using pg-schema-diff. Notice that both "create above" and "drop below" can be generated using pg-schema-diff by inverting from/to flags.

To make this very robust, an pglite or similar instance could be used.

Any thoughts?

[jackc]

Could this be handled with a shell script? My guess is a small script could connect these projects in the way you are requesting. I'd prefer to avoid adding any dependencies.

---

Issue #52 is slightly related to this as it entails "flattening" migrations.