Re: Database schema changes tools

Hi Erik

wow! I did not know Sqitch. I try it now !!

Thank you very much and best regards

Claudio

Il giorno mer 19 ott 2022 alle ore 15:41 Erik Wienhold <ewie(at)ewie(dot)name> ha
scritto:

> > On 19/10/2022 14:25 CEST edi mari <edim2525(at)gmail(dot)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
>
>
>