Customizing SQL executed per environment

Question

We use flyway to promote our application from dev -> uat -> prod. It would be very useful to use flyway to also deploy the application to developer desktops using a lighter-weight sql database (e.g. Derby or H2). For various reasons Oracle is not a good option for some of our developers.

A similar question pointed out a capability in flyway to maintain seperate sets of scripts per target database.

I'm reluctant to rely on manual synchronization of two sets of scripts. The SQL difference between Oracle and Derby are minor. Is it possible in flyway for a developer to insert some java code to do some light-weight dynamic stripping of the SQL at runtime based on the environment (e.g. remove tablespace designation)?

Answer 1

For minor differences like this, Flyway's placeholder replacement does wonders. Simple and bridges common minor differences easily. http://flywaydb.org/documentation/faq.html#placeholders

Answer 2

In my experience, placeholders were not adequate replacement in a long run (oracle v HSQLDB w. ora syntax on).

Conditional templates could be a solution, but unfortunately, flyway doesn't support it out of the box, see year old request.

You can implement conditionals or "light-weight dynamic stripping of the SQL" yourself via the callbacks mechanism. This will be bit dirty because the callbacks are not treated as first class citizens by the flyway, but very much doable. The downside is that you are losing the big part of the Flyway's appeal in its simplicity and technically could be better off by using Liquibase's DSL.

What worked for us really well at the end was using the dockerized XE 11g images all the way on dev machines and CI servers. docker-maven-plugin was also of great help. On the downside, non-linux based developers had to get used to boot2docker, but running tests against the real DB did pay off big time. Unless you use plain Hibernate without any DB logic.