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?
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.
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.
