Beliebte Suchanfragen

Cloud Native



Agile Methoden



Testing your Liquibase Migrations in Continuous Integration

23.7.2015 | 5 minutes of reading time

We are using Liquibase now for almost one year in one of our current projects and it really worked out very well so far. We are basically using the “best practice” approach described in one of our previous blog posts here .

But … and isn’t there always a but? There is this one sentence in our project that -when being said during the daily standup or just written to the chat – is freezing your blood ice cold in pure horror. Ok, this might be slightly exaggerated :-).

The sentence is: We need a Liquibase restart!

This basically means there is no chance for us to make the required changes to the database using proper Liquibase changesets. But typically this ends up in taking a backup of the database, dropping the entire database, then setting it up again from scratch using Liquibase and then performing a data import again. Definitely not the nicest thing to do, right?

At some point of time this could become a real problem if your application is for example running for a larger customer base where those kind of things would impose a lot of work and a rather big risk. But let’s maybe start with the question how it comes to these situations where there is no moving back nor forth with Liquibase.

Incompatible Liquibase Changes

Ok, just a few nice examples and not all of them can be detected using testing in a CI-environment, but anyway. When starting the project we have assigned all database objects to a certain database user. But as we are part of a bigger project that user was used elsewhere and potentially even dropped there while our database objects still belong to that user. Well, bad luck that could probably not be prevented by testing, but only by thinking :-).

But there are other changes that are a problem and those are mainly making changes to already existing SQL-files which is not allowed in Liquibase and will result in an error during the next update. Another thing is missing rollback statements as we have a project agreement to deliver rollback statements for all our Liquibase changesets.

If those things are detected too late, which means a new Liquibase release is already in production it can be very hard to fix those still. This could lead up to the described process of recreating the database from scratch including a data export and import.

Best Practices Reloaded: Sometimes projects are working with Liquibase allowing incompatible changes until a delivery is really done into production. For our project we found this approach rather error-prone and time consuming as on all test systems and all developer machines the database needs to be recreated often which also typically means loosing all testdata.

Therefore we have the agreement to make all changes compatible changes directly. If we see for example a column was added that was not needed we create another changeset to drop that column again. In the end this is less work for us than informing everyone in the project that the next Liquibase update might fail. Of course there can be changes that are too complicated or even not possible. Then a small Liquibase Restart needs to be done that only affects our testing and development environments but not our productive installations. But this is really always only our last resort.

Continuous Integration to the Rescue

Being human (oh what a click bait, but the series is really cool :-)), well, means making mistakes. And as usual the earlier we notice those mistakes the better it is.

Therefore we have created two CI-jobs on our Jenkins that are dealing with our Liquibase installations.

Semantic & Syntactical Checks

The first one is a Perl-script – and I really still like Perl while my colleagues are hating me a little bit for writing that Script in Perl 😉 – that is performing a few simple semantic and syntactical checks on the Liquibase files. Those are:

  • Simply checking that the first line in all SQL-files referenced from our Changeset-files is starting with “–liquibase formatted sql”.
  • In the line after that one we are defining the name of the author and changeset. The script checks that the agreed naming conventions are followed here.
  • Finally it checks that at least one “–rollback” section is found. Of course this cannot check if this is correct already, but this can already help missing the rollback accidentally completely.

This test is running with a frequent schedule and is this way checking our Liquibase files already shortly after a new check-in.

Liquibase Rollback Check

The second CI-job is running on a database instance we have installed on the Jenkins-server for this purpose. Again we are checking out the most recent version of our Liquibase scripts. Then we are dropping the database entirely and re-creating it again. Creating the database and the user is happening outside Liquibase in our project.

After that we are performing a Liquibase install. Then we are doing a rollback for the 10 latest changesets and then we are performing a Liquibase update again.

This is how this looks in the Jenkins-job. In this case it is tailored to a Postgres database, but of course it would also work similar with any other database.

1cd "/data/jenkins/jobs/Codecentric Liquibase Rollback Test/workspace/sample/"
2psql -q -U postgres -d postgres -c "DROP DATABASE codecentric"
3psql -U postgres -c "DROP ROLE codecentric"
4psql -U postgres -c "CREATE ROLE codecentric LOGIN INHERIT REPLICATION PASSWORD 'topsecret'"
5psql -q -U postgres -d postgres -c "CREATE DATABASE codecentric OWNER codecentric"
6java -jar ./liquibase-core-3.3.0.jar --driver=org.postgresql.Driver --classpath=postgresql-9.2-1002-jdbc4.jar --changeLogFile=./src/main/resources/liquibase/db.changelog.xml --url="jdbc:postgresql://localhost:5432/codecentric" --username=codecentric --password=topsecret update
7java -jar ./liquibase-core-3.3.0.jar --driver=org.postgresql.Driver --classpath=postgresql-9.2-1002-jdbc4.jar --changeLogFile=./src/main/resources/liquibase/db.changelog.xml --url="jdbc:postgresql://localhost:5432/codecentric" --username=codecentric --password=topsecret rollbackCount 10
8java -jar ./liquibase-core-3.3.0.jar --driver=org.postgresql.Driver --classpath=postgresql-9.2-1002-jdbc4.jar --changeLogFile=./src/main/resources/liquibase/db.changelog.xml --url="jdbc:postgresql://localhost:5432/codecentric" --username=codecentric --password=topsecret update

Together with the previous check we can be at least rather sure that some rollback statements are existing and that those are working if a rollback and update works properly.

Possible enhancements

Of course we are not at the end of possible tests here. For example we cannot detect this way if really all relevant rollback statements have been written. But it is a rather good compromise for us between effort and benefits.

For a complete rollback test you would need to probably install the latest release version and check all database elements to compare those with the results after some rollback. Would be maybe a nice project to do in some spare time :-), but the tests we implemented already help us a lot to prevent the one sentence we all fear. Liquibase restart no more!

share post




Gemeinsam bessere Projekte umsetzen.

Wir helfen deinem Unternehmen.

Du stehst vor einer großen IT-Herausforderung? Wir sorgen für eine maßgeschneiderte Unterstützung. Informiere dich jetzt.

Hilf uns, noch besser zu werden.

Wir sind immer auf der Suche nach neuen Talenten. Auch für dich ist die passende Stelle dabei.