If you are a back-end developer, you are often faced with having to migrate your database schema with each new release. The framework called Liquibase can make it easier for you when you need to upgrade your database schema. In this article, I’ll explain how Liquibase can be used in a Java project, with Spring/Hibernate, to version the database schema. How does Liquibase work? Changelog Liquibase works with files (the list of all the changes, in order, that need to execute to update the database). Changelog There are 4 supported formats for these Changelogs: SQL, XML, YAML, and JSON. Changeset A represents a single change to your database. Changeset Each changeset is identified by “id” and “author” attributes, and by the directory and file name of the changelog file - which makes it possible to uniquely identify it and to be applied only once. When the changelog is executed, the changesets defined in it will be executed one by one, in the order of definition. Thus, if there is an error on a changeset, all the precedents will have already been applied. Liquibase will end its execution on an error. So, you will only have to correct the incorrect changeset in error, then relaunch Liquibase and continue your migration. (I will explain later how Liquibase finds from which changeset it should resume). Change types Each changeset contains one or more that describe a type of operation to apply to the database. Change Types Liquibase supports both raw SQL and Change Types (that generate SQL for supported databases). But, if you want to execute the same changelog in different database vendors, it’s better to use Change Types. Change Types also allow automatic rollback if there is an error. Generally, there should only be one Change Type per changeset to avoid failed auto-commit statements that can leave the database in an unexpected state. Rollback Liquibase allows you to undo changes you have made to your database, either automatically (generated from a Change Type) or via custom rollback SQL. You need to include a clause, in each changeset, whenever a change doesn’t support out of box rollback (e.g. , , , and other destructive modifications). <rollback> <sql> <insert> <update> For example, the tag of Liquibase is a destructive modification, as it is not possible to by retrieving deleted data. In these cases, the tag cannot be automatic. You must therefore manage the rollback yourself in order to return to the previous version of your database. dropTable add a table rollback Tracking tables If your database does not already contain tracking tables, Liquibase will create 2 tables in your database when it executes: and . DATABASECHANGELOG DATABASECHANGELOGLOCK 1. DATABASECHANGELOG When Liquibase runs, it queries the table for the changesets that are marked as executed, and then executes all changesets that have not yet been executed. DATABASECHANGELOG After each execution of a changeset, Liquibase logs it in the table. A row corresponds to a changeset, identified by a unique combination of the “id,” “author,” and “filename” columns. DATABASECHANGELOG a. filename The column can be: filename an absolute path a relative path depending on how the changelog was passed to Liquibase the value of the attribute of the changelog logicalFilePath the value of the attribute of the changeset logicalFilePath b. md5sum There is another important column: . As its name suggests, it checks that the content of a changeset has not been modified since its first execution. md5sum You MUST NEVER modify an applied !!! changeset 2. DATABASECHANGELOGLOCK To prevent conflicts from different Liquibase instances, there is another table: . DATABASECHANGELOGLOCK Sometimes,the lock is not released, if a Liquibase instance doesn’t exit cleanly.  After you are sure that all Liquibase instances are stopped, you can clear out the current lock by executing the following SQL command: DATABASECHANGELOGLOCK = UPDATE SET LOCKED 0 Set up Gradle dependency Let's start with the Gradle dependency that we need to add into our : build.gradle : , name: , version: compile group 'org.liquibase' 'liquibase-core' '3.9.0' Changelogs tree Now that you know the basics of Liquibase, I'm going to explain how to organize your . changelogs/changesets If you want, you can use only one changelog, but after several versions, your file will become unreadable. It’s better practice to split your changesets into several files. The best practice is to have 1 file per feature + 1 folder per version + 1 master changelog which aggregates all changelogs with an or tag. include includeAll First, create the master file in the folder . changelog src/main/resources To include the sub-changelogs, there are 2 ways: using or tags. include includeAll 1. include You must specify one by one, which are run in the order they are found. changelogs So, if you use this option, you need to pay attention to the order of the declaration, and not create a loop. changelog If you create a loop ( includes which includes ) you will get an infinite loop. changelog root.changelog.xml sub.changelog.xml root.changelog.xml You can also create an intermediate master for all versions, to avoid having to list all the in the root master. changelog changelogs master -> 0.0.0/master, 1.0.0/master, 1.0.1/master 0.0.0/master -> 0.0.0/file1, 0.0.0/file2… 1.0.0/master -> 1.0.0/… ... 2. includeAll This is similar to the tag, but instead of passing a specific file to include, you specify a directory which includes all *.xml files, and all *.sql files, as individual changes. Include changelog changelog All files that are found are run in alphabetical order. So you must use a naming convention for files such that they run in the correct order. 3. relativeToChangelogFile This attribute of and calculates the path of the included file (for the table) relative to the file containing the included file rather than to the classpath. include includeAll DATABASECHANGELOG changelog 4. logicalFilePath This attribute of and overrides the file name and path when creating the unique identifier of changesets. changelog changeset It is required when moving or renaming . changelogs Also, it can be useful when many Liquibase instances use the same database, but the classpath of the files they use are not the same. changelog For example, you can split your changelogs in different modules of your project to test your repositories/services independently. But you can also have final tests when all your modules/changelogs are packaged. If the classpath of your changelogs in the packaging are not the same as in the modules, Liquibase doesn’t recognize the changelogs as the same. The best solution is to have different databases for the different tests, but sometimes, the company you work for will refuse to make another available because of cost. Without logicalFilePath With logicalFilePath Advanced You can find some interesting advanced features , like , , and . here changelog parameters column tag preconditions tag 1. Preconditions If you want to apply a only under certain conditions, you need to use the tag. There are many kinds of preconditions. You can find them . changelog/changeset precondition here If the is not valid, you can either halt the update, skip a changeset, mark a changeset as run, or show a warning. precondition 2. Liquibase Extension If you don’t want to use the SQL, XML, YAML, and JSON formats for your , you can use the to create it in whatever format you like. changelog Liquibase extension Integration Liquibase easily integrates into your Java application, if you use Spring or Spring Boot. Spring If you want to execute the changes on the Spring application startup, use the following Spring Bean: {
    SpringLiquibase liquibase = SpringLiquibase();
    liquibase.setchangelog( );
    liquibase.setDataSource(dataSource()); liquibase;
} @Bean SpringLiquibase public liquibase () new "classpath:db/changelog/db.changelog-master.xml" return Note: must already exist in the folder . db/changelog/db.changelog-master.xml src/main/resources Spring Boot 2 If you are using Spring Boot, there is no need to define a Bean for Liquibase. All you need to do is change the value of in the file of Spring, and the Liquibase migrations will run automatically on the application startup: spring.liquibase.change-log src/main/resources/application.properties = spring.liquibase.change-log classpath:/db/changelog/db.changelog-master.xml Note: The default value is . You can find all the properties of Spring Boot for Liquibase, . classpath:/db/changelog/db.changelog-master.yaml here Test Unit test For the unit tests, you don’t need to use Liquibase, as you don’t test the interaction with the database. If you’re using Spring Boot, disable Liquibase & Spring Data Source, in this way: = = spring.liquibase.enabled false spring.autoconfigure.exclude org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration, \
org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration, \
org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration, \
org.springframework.boot.autoconfigure.data.jpa.JpaRepositoriesAutoConfiguration Integration test There are many ways to store your test data. You can: Create a changelog for the test (stored in ), and point it in the Spring for testing, to store the data at Liquibase initialization. But at each modification of the database, you will have to modify the test . src/test/resources configuration changelogs Use Spring annotations & SQL scripts to store the data at Spring initialization. But as in the first solution, you need to modify the SQL scripts at each time the data model is modified. Define all operations in each test, using the repositories (Liquibase loads the database schema at Spring initialization) (SpringExtension.class) { AnimalRepository animalRepository; OwnerRepository ownerRepository; {
        animalRepository.deleteAll();
        ownerRepository.deleteAll();
    } { Owner owner = ownerRepository.save( Owner( )); Animal animal = animalRepository.save( Animal( , owner)); Animal result = animalRepository.findByName( ); assertEquals(animal, result);
    }
} @ExtendWith public class AnimalRepositoryTest @Autowired private @Autowired private @BeforeEach public void setUp () @Test Exception public void findByName_should_return_the_right_animal () throws // Given final new "Céline" final new "Peluche" // When final "Peluche" // Then This solution is more verbose, but it’s easier to read (and does not need to find all the test data in SQL/ files). It’s also easier to maintain, because if the data model changes, you’ll see that in your IDE or during the compilation phase of the tests. changelog Tools Gradle Plugin Instead of writing the changelog file manually, use the Liquibase Gradle plugin to generate one. Then you have just to split it. 1. Plugin Configuration Add the Gradle plugin into the : build.gradle id version 'org.liquibase.gradle' '2.0.2' 2. Generate a changelog from 1 existing database Add the plugin configuration, for the task, into the : generateChangeLog build.gradle liquibase {
    activities {
        main {
            changeLogFile changeLog
            url username password }
    }
} 'jdbc:postgresql://localhost:5432/bonita' '%USER_NAME%' '%PASSWORD%' Then, execute the task: ./gradlew generateChangeLog This can be useful during the development, if you want to let Hibernate generate the database schema, and then generate the changelog from the generated database. 3. Generate a changelog from the differences between 2 existing databases Add the plugin configuration, for the task, into the : diff build.gradle liquibase {
    activities {
        main {
            changeLogFile changeLog
            url username password referenceUrl referenceUsername referencePassword }
    }
} 'jdbc:postgresql://localhost:5432/bonita' '%USER_NAME%' '%PASSWORD%' 'jdbc:postgresql://localhost:5432/bonita_7.5' '%USER_NAME%' '%PASSWORD%' Then, execute the task: ./gradlew diff So you can compare the schema of a production database with the schema of a freshly installed blank database, if there is a problem during the migration of the production database. 4. Community commands There are other Gradle tasks for this plugin that you can find . here Liquibase Hibernate Plugin If the application uses Hibernate, you can use this Gradle plugin to generate the changelog from your entities during the development. 1. Plugin Configuration Add the into the : Gradle plugin build.gradle : , name: , version: compile group 'org.liquibase.ext' 'liquibase-hibernate5' '3.10.0' 2. Generate a changelog from the differences between a database and persistence entities Add the plugin configuration, for the task, into the : diffChangeLog build.gradle liquibase {
    activities {
        main {
            changeLogFile changeLog
            url username password referenceUrl }
    }
} 'jdbc:postgresql://localhost:5432/bonita' '%USER_NAME%' '%PASSWORD%' 'hibernate:spring:com.example?dialect=org.hibernate.dialect.MySQL5Dialect&hibernate.physical_naming_strategy=org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy&hibernate.implicit_naming_strategy=org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy' Then, execute the task: ./gradlew diffChangeLog Conclusion In this tutorial, we’ve seen  several very useful features of Liquibase which enable you to evolve your database schema easily in a Java application. Versioning your database with Liquibase means you can: Refactor your code easily while having simple production releases (no need to manually execute a SQL statement suite or develop your own migration tool, as the updating of the database is done when launching the new version of your application) Easily test the state of the database (locally or in a continuous integration) Improve teamwork, because the changes are visible and easily applied by everyone The implementation of all these examples can be found in my . github project Links https://www.baeldung.com/liquibase-refactor-schema-of-java-app https://blog.soat.fr/2015/10/liquibase-et-le-versioning-de-base-de-donnees https://mvnrepository.com/artifact/org.liquibase/liquibase-core https://www.liquibase.org/

Read My Stories

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

Database Schema Versioning and Migrations Made Simpler For High Speed CI/CD

Too Long; Didn't Read

Company Mentioned

Souchet Céline

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

Database Schema Versioning and Migrations Made Simpler For High Speed CI/CD

Too Long; Didn't Read

Company Mentioned

Souchet Céline

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES