Database Migration with Liquibase using different DBMS

5
votes

Our customer is using Oracle and we want to use MySQL for development. Therefore our MySQL Scheme has to be in sync with Oracle. I tried to use Liquibase, but I get problems at the time of applying a changeset because of the db specific sql and different column types like NUMBER <-> BIGINT or VARCHAR <-> VARCHAR2

Am I using these tools wrong? How to solve this type of problem?

2
databasedatabase-migrationliquibase

2 Answers

8
votes

Liquibase will attempt to convert standard types like "varchar", "int", "boolean", "datetime" to the correct datatype for the database. If you define a column as type="VARCHAR(100)", when you run against oracle it will generate SQL with VARCHAR2(100).

The mapping of standard types to database-specific types is not as well documented as it should be, unfortunately.

Alternately, if you can stick to SQL-standard data types, they are generally fairly cross-database.

When you need to force a particular type, you can use a changelog parameter like the example in http://www.liquibase.org/documentation/changelog_parameters.html

<column name="notes" type="${clob.type}"/>

and define clob.type per database:

<property name="clob.type" value="clob" dbms="oracle"/>
<property name="clob.type" value="longtext" dbms="mysql"/>
6
votes

The best approach is to use changelog parameters and the dbms tag.

At the top of your changeset you can include:

<property name="autoIncrement" value="true" dbms="mysql"/>
<property name="autoIncrement" value="false" dbms="oracle"/>
<property name="chartype" value="VARCHAR" dbms="mysql"/>
<property name="chartype" value="VARCHAR2" dbms="oracle"/>

Then you can have changeSets like this: Copy code

<changeSet id="1" author="a">
    <createTable name="x">
        <column name="id" datatype="int" autoincrement="${autoIncrement}"/>
        <column name="name" datatype="${chartype}(255)" />
        ....
</changeSet>
<changeSet id="2" author="a" dbms="oracle">
    <createSequence name="seq_x"/>
</changeSet>

EDIT source: http://forum.liquibase.org/topic/auto-increment-vs-sequences-using-liquibase-with-oracle-and-mysql