34

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?

Peti
  • 1,670
  • 1
  • 20
  • 25

2 Answers2

42

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.

Axel Fontaine
  • 34,542
  • 16
  • 106
  • 137
  • Thanks Axel for the fast reply! – Peti Nov 29 '12 at 09:09
  • Probably worth putting somewhere in [the flyway FAQ](http://flywaydb.org/documentation/faq.html)? – Peti Nov 29 '12 at 09:10
  • 1
    Axel's solution from above works fine. I found one tiny issue to be aware of: I used flyway.locations=db/migration/common,db/migration/oracle and flyway.locations=db/migration/common,db/migration/derby and this messes up with the deprecated "baseDir" to one large merged "db/migration". – Peti Nov 29 '12 at 10:40
  • You are correct. This is unfortunate, but will be resolved by itself with 3.0 once all the deprecated stuff will be removed. – Axel Fontaine Nov 29 '12 at 11:41
  • The fix is now checked in. – Axel Fontaine Dec 19 '12 at 08:44
  • If you are using Spring you can put flyway.locations in the configuration file used by Spring (PropertySource) and set it in the Flyway bean using the environment object. – Manuel Palacio May 26 '15 at 08:32
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.

dropofahat
  • 283
  • 1
  • 2
  • 12