How to update old database schema to new one in Java with liquibase

Question

Me and my team have worked on a project separately from other team. That team worked for 2 years on this project and now they are leaving, leaving us the entire project and in the past 2 months we had to learn about the project and add some features (like multi-currency, new onboarding process etc) and this features implies new columns in the database, new tables and so on. And we need to update the old production database to new one, without breaking anything (we can't just delete it and start fresh with the new scripts). We want to create some generic script in Java Spring for this and other migrations in the future, something like "take this table and add this column if it doesn't exists, but if it exists and it is empty/null, add this data in it (for example, phone_number that every user needs to have and if it is not present, add something autoincrement: 000000001, 00000002 etc., and after that we need to ask users to verify their phone and override that 0000001, 0000002)". And so with that, we can have the old schema look exactly like the new one and we want to create an interface or something to make more generic those migrations. How do you think is the best to resolve this?

Thank you!

htshame · Accepted Answer · 2020-04-18T19:47:55.020

Since you know what changes your "old schema" needs, and you're willing to implement them - Liquibase looks like a handy solution.

You should write the necessary changeSets to update your schema.

Liquibase offers a lot of changes, enough to script basic migration stuff.

To add a new table, you may use createTable change:

<changeSet id="foo" author="bar">
    <preConditions onFail="MARK_RAN">
        <not>
            <tableExists tableName="your_table"/>
        </not>
    </preConditions>
    <createTable tableName="your_table">
        <!-- your columns here -->
    </createTable>
</changeSet>

For adding column you may use addColumn change:

<changeSet id="foo2" author="bar">
    <preConditions onFail="MARK_RAN">
        <not>
            <columnExists tableName="your_table" columnName="your_column"/>
        </not>
    </preConditions>
    <addColumn tableName="your_table">
        <column name="your_column" type="integer">
            <constraints nullable="false"/>
        </column>
    </addColumn>
</changeSet>

For data update you may use update change:

<changeSet id="foo3" author="bar">
    <preConditions onFail="MARK_RAN">
        <columnExists tableName="your_table" columnName="your_column"/>
    </preConditions>
    <update tableName="your_table">
        <column name="your_column" value="123"/>
        <where>your_column IS NULL</where>
    </update>
</changeSet>

For basic SQL requests you may use sql change:

<changeSet id="foo4" author="bar">
    <preConditions onFail="MARK_RAN">
        <sqlCheck expectedResult="1">
            <!-- something like -->
            SELECT * from your_table where your_column = 100;
        </sqlCheck>
    </preConditions>
    <sql>DELETE FROM your_table WHERE your_column=100</sql>
</changeSet>

And so on and so forth. You bundle up all the changeSets into databaseChangeLog, add changeLogs to your application and you're ready to go with the "old schema" update!

This is exactly what I asked. I know what changes needs to be done to make old schema look like the new one, but didn't know that Liquibase is so good at this. I will surely use it Thank you, @htshame! — Mihai Marius, Apr 16 '20 at 14:41

How to update old database schema to new one in Java with liquibase

1 Answers1