> On 19/10/2022 14:25 CEST edi mari <edim2525@xxxxxxxxx> 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
