Thread: Database schema changes tools

> On 19/10/2022 14:25 CEST edi mari <edim2525@gmail.com> wrote:
>
> Are you using database schema change tools to manage your Postgres, like Flyway, Liquibase, and DBmaestro ?
> Can you please recommend or disrecommend

I recommend looking into Sqitch[1], having switched to it myself one year ago
after using Flyway for every project.

My gripe with Flyway is the separation of versioned migrations (non-idempotent)
and repeatable migrations (idempotent, e.g. CREATE OR REPLACE VIEW).  Repeatable
migrations always run after *all* versioned migrations.  This makes it impossible
to use views or functions in versioned migrations unless they are created before.
I haven't found an option to change the migration process from "all or nothing"
to something that is more like logical groups or batches of migrations, so that
versioned and repeatable migrations can be interleaved.

Another thing that bugs me with Flyway is that you have to control the order
of migrations via filenames.  Not so difficult for versioned migrations if you
stick to dotted version numbers or leading zeros in filenames.  But your file
system may not list migration files in the same order as Flyway applies them, so
watch out.

Ordering repeatable migrations has the same issue which is relevant for views
with dependencies on other views.  Flyway relies on the filename order in that
case.  Refactoring views may require adding new repeatable migrations between
two existing ones.

That's why I switched to Sqitch because the order of migrations is defined
explicitly in a plan file.  So it's easy to interleave idempotent and non-idempotent
migrations as one sees fit.

Sqitch runs migrations scripts through psql so you can also use meta-commands
and variables.  But beware that every migrations runs as a separate transaction[2]
in contrast to Flyway whose migrate command runs a single transaction with all
pending migrations.

In the end it depends on how complex your schema is (or will be).  Flyway is
okay if it's only for a bunch of tables.  Anything beyond that, with dependencies
between database objects, is tricky IMO.

[1] https://sqitch.org/
[2] https://groups.google.com/g/sqitch-users/c/7lTJXNhS8Ho/m/fyuZmSe8IT0J

--
Erik

edi mari schrieb am 19.10.2022 um 14:25:
> Hi All,
> Are you using database schema change tools to manage your Postgres, like Flyway, Liquibase, and DBmaestro ?
> Can you please recommend or disrecommend

We are using Liquibase exclusively.

Some projects require support for multiple database, which is quite easy to do with Liquibase's XML format.

For single-DBMS projects we use Liquibase's "annotated SQL" format, which means we are writing SQL scripts
that can also be run without Liquibase (which makes testing them easier)

Thomas