Question

Is Flyway suitable for implementation in an application that will support multiple databases?

We don t know what our customers are using - could be either MySQL, Postgres or Oracle. Can we still use Flyway to migrate the database for new versions of the application?

Answer 1

if your question is: does Flyway provide a DDL abstraction layer across the databases it supports, the answer is no.

This was a conscious design decision, to make sure the full power of the underlying database is available and not just the smallest common denominator supported by the migration tool.

For your use case, you could either provide different migration scripts for the different databases. They should be very similar though.

If you do not wish to potentially duplicate the migration scripts and can live with the smallest common denominator approach, have a look at LiquiBase which might be a better fit for your usecase (if you can live with the XML)

Answer 2

You could use jOOQ s parsing connection, which wraps your target JDBC connection and is capable of translating your input DDL to any target dialect (if it s not too fancy and vendor specific). Flyway wouldn t be aware of this translating JDBC proxy, and wouldn t have to be. The online version of the SQL translator can be seen here. For example, if your input SQL is a MySQL specific:

create table t (i int primary key auto_increment);

The output could be:

-- Oracle
create table T (
  I number(10) generated by default as identity(start with 1) not null,
  primary key (I)
);

-- SQL Server
create table T (
  I int identity(1, 1) not null,
  primary key (I)
)

-- PostgreSQL
create table T (
  I int generated by default as identity not null,
  primary key (I)
)

-- PostgreSQL 9.4
create table T (
  I serial4 not null,
  primary key (I)
)

Disclaimer: I work for the company behind jOOQ.

友情链接