How to disable drop table and column when using mvn liquibase:diff?

Question

I'm setting up a release process to upgrade our production environment as easily as possible.

To achieve that concerning database, I've chosen liquibase. Each time an environment is upgrade :

The code is deliver on servers,
mvn liquibase:diff is run to generate a changeLog with previous version
mvn spring-boot:run is run to start the application and execute liquibase with the new changeLog, and so, adapt the database from code entity freshly delivered.

The problem is that if a field in a java @Entity is, for example, moved to an other entity, liquibase will remove the column without transfering data to the new field location.

So my question is, can we configure liquibase to avoid column and table drop ? To be able to copy datas to their new locations, and after that, effectively remove the column (or table) for good ?

We're using spring-boot 2.1.2 and the liquibase maven plugin 3.4.1. Our database is on MySQL 5.7.27.

I've try to find how to export all database creation/alteration changeSet in one changeLog.xml and all drop changeSet in an other changeLog.xml. Like that we can execute between these two changeLog, a third changeLog dedicated to copy data to thier new location. Bu didn't find solution in liquibase documentation : https://www.liquibase.org/documentation/diff.html

To illustrate my example :

v1:

@Entity
public class Person {
     private long id;
     private String name;
     private String phoneNumber;
}

Became

v2: (creation of entity Phone to replace field phoneNumber of entity Person)

@Entity
public class Person {
     private long id;
     private String name;
     private Phone phone;
}

@Entity
public class Phone {
    private long id;
    private String phoneNumber;
    private String brand;
}

So when v2 is delivered on server and mvn liquibase:diff is run, the result changeLog will drop column phoneNumber and create column phone in table person. Without transfering data from table person, filed phoneNumber to the new table phone in field phoneNumber.

I want to execute changeLog (manually wrote) that copy Person.phoneNumber to new entity Phone.phoneNumber.

Is that possible ? Or there is any trick to do that properly ? Or maybe I'm using liquibase in bad way to achieve that ?

Thanks a lot !

htshame htshame · Accepted Answer · 2019-10-30T13:12:37

Maybe I'm missing something, but as far as I understand, diff is not the all-powerful-tool which will absolve you of any responsibility.

In some cases you have to write changeSets yourself and I think this is one of them.

Since diff is indeed a very powerful command, it has it's shortcomings.

Check out this article: The Problem With Database Diffs

Theoretically, a diff tool could also check for new, updated, and missing data between database, but in practice this cannot work for two reasons:

Performance. As your data set grows, the amount of information to compare grows until it is unmanageable.

Changing Data. During development, test data is often added to the development database that shouldn’t be copied into other databases. Also, new data may be added to testing and production databases that should not be deleted just because it doesn’t exist in the development database.

In your case, I'd do something like this:

Create a new table phone
Add column person.phone_id
Copy data from person.phone_number to phone.phone_number
Copy data from phone.id to person.phone_id
Create foreign key constraint between person and phone
Drop person.phone_number

The code could be like this:

<createTable tableName="phone">
    <column name="id" autoIncrement="true" type="bigserial">
        <constraints primaryKey="true" primaryKeyName="pk_phone"/>
    </column>
    <column name="phone_number" type="varchar(30)"/>
    <column name="brand" type="varchar(255)"/>
</createTable>
<addColumn tableName="person">
    <column name="phone_id" type="bigserial"/>
</addColumn>
<update tableName="phone">
    <column name="phone_number" valueComputed="(select p.phone_nubmer from person p)"/>
</update>
<comment>Assuming that person.phone_number is unique</comment>
<update tableName="person">
    <column name="phone_id" valueComputed="(select p.id from person p where p.phone_number = phone_number)"/>
</update>
<addForeignKeyConstraint baseTableName="person" baseColumnNames="phone_id"
    constraintName="person_phone_id_phone_id_fk"
    referencedTableName="phone" referencedColumnNames="id"/>
<dropColumn tableName="person" columnName="phone_number"/>

How to disable drop table and column when using mvn liquibase:diff?

To illustrate my example :

1 Answers