conditional application of SQL

nutmix

I cant find any documentation on what can and cant go into a DB change script, but coming from liquibase, I am sure there is a way to conditionally apply SQL based on params or env prams, I just cant find it.

E.g. for stage and  prod (which is on RDS) we need:

CREATE EXTENSION IF NOT EXISTS aws_s3 WITH SCHEMA public;

and 

...             aws_commons.create_s3_uri(bucket_name, zip_filename, region),

but on dev (local postgres)

we must NOT do that stuff.

Any pointers on how to conditionally include specific lines of the script?  Or maybe liquibase has conditional script inclusion, so we need to separate into multiple scripts?

nutmix

Unfortunately enterprise version is completely out of reach, but this does seem to work on free version:

do $$ BEGIN

if (select 1 from pg_catalog.pg_user p where p.usename = 'rdsadmin') then

CREATE EXTENSION IF NOT EXISTS aws_s3 WITH SCHEMA public;

end if;

end $$;

Piers_Williams

If you are a Teams or Enterprise user, there is the `shouldExecute` option in a Script Config File: https://documentation.red-gate.com/fd/script-config-files-224003083.html

This should let you skip a migration on dev

AlistairW

Flyway is trying to maintain a set of steps that would allow you to recreate your database from the applied migrations, it also won't alter your SQL (apart from placeholder replacement) and having conditionals all over the place works against that so it's not easy to do.

One possibility is to collect all the environment-specific things into a limited set of migrations and then use the shouldExecute functionality to allow the migration(s) to execute only for the appropriate environments.  It's a Flyway Teams feature though. https://documentation.red-gate.com/fd/script-config-files-224003083.html

 Alternatively, you might be able to do something with SQL Placeholders (https://documentation.red-gate.com/fd/placeholders-configuration-224003082.html) but it will depend on how significant the changes you need to make are.