Best way for “database specific” sql scripts with

Question

I started to use Flyway in my current project for database migrations and I like it very much. I currently use Oracle in PROD- and Derby in TEST-Environment.

Pretty soon, I did run in the problem of database specific sql commands, e.g.

• ALTER TABLE T1 MODIFY F1 VARCHAR(256); on Oracle vs
• ALTER TABLE T1 ALTER F1 SET DATA TYPE VARCHAR(256); on Derby.

I can't see a way to write a "vendor neutral alter table modify column datatype" sql.

What's the best way to deal with this problem using Flyway?

Answer 1

The differences in SQL between Oracle and some of these desktop databases is minor. Is it possible for a developer to insert custom code to do some light-weight dynamic stripping of the SQL at runtime based on the environment (e.g. remove tablespace designation)?

I prefer this approach to relying on each developer to manually keep two sets of SQL in sync.

Answer 2

You can use the flyway.locations property.

In test in would look like this:

flyway.locations=sql/common,sql/derby

and in prod:

flyway.locations=sql/common,sql/oracle

You could then have the common statements (V1__Create_table.sql) in common and different copies of the DB-specific statements (V2__Alter_table.sql) in the db-specific locations.

An even better solution, in my opinion, is to have the same DB in prod and test. Yes, you do lose a bit of performance, but on the other hand you also eliminate another difference (and potential source of errors) between the environments.