Schema Migration Tool Preference

[robertsosinski]

I'm just interested in what schema migration tools y'all have used with Postgres and hope to discover anything new out there.

I've used Flyway because I like writing migrations in SQL and I also like being able to define repeatable migrations for views and functions. Wonder what else offers this or what other features are out there.

Thanks!

[wolfgangwalther]

I use the PostgreSQL extension system. My application code is packaged as a SQL extension and updated via ALTER EXTENSION ... etc.

[steve-chavez]

@wolfgangwalther That's an awesome idea. It also gives versioning for free, and things like ALTER EXTENSION.. UPDATE. WDYT about documenting the approach?

[robertsosinski]

I would also like to learn more about how this works in both development and production deployment. Using PostgREST more and more means I have to deploy more functions and views than ever before, so if doing it via an extension is easier then with Flyway, that would be a huge productivity boost.

Even any docs that showcase high-level how you organize such code and perform deployments would be a big bonus.

Thanks!

[wolfgangwalther]

On a high level:

I am lazy, so I want to write the least amount of code as possible. The application code is split into three layers, each corresponding to their own schema and own extension:

• data: This only contains tables and some enums/domains that are needed for those tables. No functions, triggers, RLS, views. The code for the data layer is written through migrations. So the extension upgrade files are written by hand here - treating the migration code as "the source of truth". I also have some dump-scripts to dump the whole layer into some CREATE TABLE ... statements, to make it easier to see at first glance how the schema looks like - even when reviewing PRs. Those dumps are checked in git, too.
• logic: This contains the business logic, but is - in theory - PostgREST-agnostic. This contains all the triggers and RLS to make the schema consistent, trigger cascading actions when anything happens. pg_cron lives here, too, to send out emails after certain things. RLS is managed here. Nothing in this layer needs migration. In this layer we just write CREATE FUNCTION .., CREATE TRIGGER ...` etc.
• api: This actually consists of three schemas api (internal), extra (for db-extra-search-path) and exposed (db-schemas). All the VIEWs and RPCs live here. A lot of PostgREST specific code, too. Same thing as in the logic layer: No migrations, just CREATE ....

The update tool runs as a kubernetes job triggered by a helm hook and basically does the following:

DROP EXTENSION api CASCADE;
DROP EXTENSION logic CASCADE;
ALTER EXTENSION data UPDATE TO VERSION ...;
CREATE EXTENSION logic;
CREATE EXTENSION api;

Yeah, in reality it's slightly more sophisticated, but that's the idea. The whole api and logic layer are just dropped and recreated entirely. This way I don't need to replace any functions or views, the migration code is kept to an absolute minimum. It also does not require any "schema diff tool" as in other approaches at all.

[j0lvera]

I use agnostic. Just like the name says, it's ORM, web framework agnostic, so you can use it with PostgREST.

I'm using it right now to migrate a codebase from fastAPI to PostgREST and it's working fine.

Another good one is Alembic, but you have to use SQLAlchemy tools, idioms to build the migrations.