Can Flyway accomodate different SQL dialects in the same migration script?

user_2817141 0
Viewed 68

in my Company we manage a SaaS infrastructure that uses PostgreSQL as DB solution. However, some clients wants us to perform an on-premise deployment, in which they provide MySQL instead.

This of course entails identifying and isolate PG-specific datatypes and functions, find a corresponding MySQL equivalent, and make them run properly.

If we were using Liquibase, that would be easy, since it would require me to write databaseChangeLog(s) with a precondition like this:

<preConditions onFail="HALT">
    <dbms type="mysql"/>
</preConditions>

and include as many as I want within the same databaseChangeLog

With Flyway however, it seems like the only possible way is to define different folders, under which place the SQL files for a particular RDBMS.

This seems quite overkill and error-prone to me, so I would like to know: does Flyway have some specific syntax that I missed, which allows me to write different SQL-dialect-specific queries within the same .sql file? I.E. I would hypothetically expect something like this :

--; if(mysql)
CREATE TABLE test (start_date TIMESTAMP NOT NULL);
--; endif

--; if(postgresql)
CREATE TABLE test (start_date TIMESTAMPTZ NOT NULL);
--; endif

...

thank you in advance!

flyway database
2 Answers

This is not a feature of flyway. SQL scripts in flyway are meant to be purely valid SQL (with some exceptions for variable injection).

However, you may be able to look at Java migrations to do conditional execution but you would not be able to do that with your existing SQL scripts.

user_163987660

Flyway doesn't support conditional blocks within migration files but it is possible for SQL dialects to use the same migration scripts.

I've used flyway placeholders to do this, rather than conditional blocks. I've got a demonstration of the technique here, using PowerShell as the glue. Yours sounds an interesting application, I have the same requirement for an application that can be installed in various environments that use a different brand of RDBMS, and I can see the value of block structures. The reason that I used placeholders was that the difference between SQL dialects us usually fairly trivial. Maybe a combination of techniques would be a winner.

I would guess that you can use SQL conditional blocks together with placeholders. You would just then need a placeholder with the name of your rdbms, taken from the flyway.conf jdbc connection-string.

user_128397940
Related