How can we help you today? How can we help you today?

conditional application of SQL

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
0

Comments

3 comments

Sort by
  • 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
    Piers_Williams
    0
  • 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 $$;

    nutmix
    0
  • 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.


    AlistairW
    0

Add comment

Please sign in to leave a comment.