Clever Geek Handbook

Database structure versioning with Liquibase

Most of the applications I've come across store data in a SQL database. If you have a corporate application, then most likely there are several stands: a development stand, pre-prod and prod. And a team of developers is working on the application.





Such applications face the problem of synchronizing the database schema between the circuits and the developers themselves. It is necessary to somehow transfer the changes that you made to everyone else and at the same time not get conflicts.





These problems are solved by the Liquibase migration management system. It is a kind of version control system for your database.





Liquibase is a database independent library for tracking, managing and applying changes to a database schema.





Changes for the database are recorded in a format understandable by Liquibase, and it, in turn, executes queries to the database. Thus, independence from a specific database is realized. Liquibase supports 10 database types including DB2, Apache Derby, MySQL, PostgreSQL, Oracle, Microsoft® SQL Server, Sybase, and HSQL. A list of all supported databases can be found on the website .





There are other migration management systems: Doctrine 2 migrations, Rails AR migrations, DBDeploy, etc. But some of them are platform-dependent, some do not have such a wide functionality.





Also, a serious drawback of many systems is the impossibility of applying some changes without losing data, for example, renaming a column will occur as two operations: drop + add, which will lead to data loss.





How Liquibase Works

Liquibase is a cross-platform Java application, which means you can download a JAR file and use it on Windows, Mac or Linux.





As an example, we will consider working with a spring-boot application and a PostgresSQL database. But you should be aware that liquibase can also be used separately as a .jar file. Like this:





java -jar liquibase.jar --driver=com.mysql.jdbc.Driver--classpath=lib/mysql-connector-java-5.1.21-bin.jar --changeLogFile=/path/to/changelog.yaml --url="jdbc:mysql://localhost/application" --username=dbuser --password=secret update

Changelog

Changes to the database structure are written to files called changelog. Supported formats: XML, YAML, JSON or SQL.





Change files can be arbitrarily included in each other for better control. More on this below.





XML , .





ChangeSet

ChangeSet – , Git. ChangeSet . ChangeSet.





changeSet id



, author



filename



, .





Liquibase :





  • databasechangelog



    – . changeSet.





  • databasechangelock



    – , Liquibase.





Liquibase , . , , Liquibase .





Liquibase databasechangelock



, boolean locked



. Liquibase , true



, false



.





, Liquibase , false



. :





locked-database.jpg
locked-database.jpg

, databasechangelock



locked



false



.





databasechangelock.jpg
databasechangelock.jpg

Liquibase changelog, , .





changeSet databasechangelog



MD5 changeSet. XML.





Liquibase , . changeSet, , .





error-md5.jpg
error-md5.jpg

changeset





spring-boot , Liquibase.





GitHub:

https://github.com/Example-uPagge/liqubase





spring-boot

Liquibase, maven:





<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

application.yml



:





spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/liquibase_example
    username: postgres
    driver-class-name: org.postgresql.Driver
    password: password

Hibernate, .





changelog. spring-boot Liquibase resources/db/changelog/db.changelog-master.yml



. XML .





resources/db/changelog/db.changelog-master.xml



. application.yml



:





spring:
  # .. .. .. .. ..
  liquibase:
    change-log: classpath:db/changelog/db.changelog-master.xml

:





<databaseChangeLog
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

    //   changeSets

</databaseChangeLog>

, changeSet , .





Person.





<changeSet id="create-table-person" author="uPagge">
    <createTable tableName="person">
        <column name="id" type="int" autoIncrement="true">
            <constraints nullable="false" primaryKey="true"/>
        </column>
        <column name="name" type="varchar(64)"/>
        <column name="telegram_id" type="int">
            <constraints unique="true"/>
        </column>
    </createTable>
</changeSet>

createTable



tableName



, . , .





. Liquibase, .





id. , constraints



:





  • primaryKey="true"



    – .





  • nullable="false"



    – NULL.





    primaryKey nullable . H2 , - .





spring-boot 3 , person



.





changeSet. :





<changeSet id="create-table-person" author="uPagge">
  <createTable tableName="person">
    <column name="id" type="int" autoIncrement="true">
      <constraints nullable="false" primaryKey="true"/>
    </column>
    <column name="name" type="varchar(64)"/>
    <column name="telegram_id" type="int">
      <constraints unique="true"/>
    </column>
    <column name="address" type="varchar(300)"/>
  </createTable>
</changeSet>

.





changeSet , databasechangelog



, changeSet. git .





:





  • changeSet . []





  • Liquibase.





  • changeSet databasechangelog



    . , changeSet - . .





changeSet :





<changeSet id="create-table-person" author="uPagge">
    <createTable tableName="person">
        <column name="id" type="int" autoIncrement="true">
            <constraints nullable="false" primaryKey="true"/>
        </column>
        <column name="name" type="varchar(64)"/>
        <column name="telegram_id" type="int">
            <constraints unique="true"/>
        </column>
    </createTable>
</changeSet>

<changeSet id="add-new-column-address" author="uPagge">
    <addColumn tableName="person">
        <column name="address" type="varchar(300)"/>
    </addColumn>
</changeSet>

. , .





add-new-column-success.jpg
add-new-column-success.jpg

. Book



Person



. changeSet:





<changeSet id="create-table-book" author="uPagge">
    <createTable tableName="book">
        <column name="id" type="int" autoIncrement="true">
            <constraints nullable="false" primaryKey="true"/>
        </column>
        <column name="name" type="varchar(64)"/>
        <column name="author_id" type="int">
            <constraints foreignKeyName="book_author_id_person_id" references="person(id)"/>
        </column>
    </createTable>
</changeSet>

author_id



id



Person



.





foreignKeyName



. : _ + _ + __ + ___.





:





<constraints foreignKeyName="book_author_id_person_id" references="person(id)" deleteCascade="true"/>

, , .





, :





<changeSet id="create-table-book" author="uPagge">
    <createTable tableName="book">
        <column name="id" type="int" autoIncrement="true">
            <constraints nullable="false" primaryKey="true"/>
        </column>
        <column name="name" type="varchar(64)"/>
        <column name="author_id" type="int"/>
    </createTable>

    <addForeignKeyConstraint baseTableName="book" baseColumnNames="author_id"
                             constraintName="book_author_id_person_id"
                             referencedTableName="person" 
                             referencedColumnNames="id" 
                             onUpdate="CASCADE"/>
</changeSet>

, XML, SQL:





<changeSet id="create-view-book-author" author="uPagge">
    <createView viewName="author_and_book">
        SELECT p.id as person_id, 
               p.name as person_name, 
               b.id as book_id, 
               b.name as book_name
        FROM person p
                 LEFT JOIN book b on p.id = b.author_id
    </createView>
</changeSet>

Liquibase 2017. Liquibase , .





changeSet, SQL. , .





ChangeSet

changeSet changelog : . , , changeSet.





ChangeLog . changelog.





:





  • db/changelog



    . , v.1.0.0



    .





  • -. cumulative.xml



    .





  • , changelog cumulative.xml



    .





  • db.changelog-master.xml



    cumulative.xml



    .





, changeSet. changeLogs changeLogs .





, , id



changeSet.





, :





  • changelog, cumulative.xml



    , , . : 2020-03-08-create-tables.xml







  • id



    changeSet. id="2020-03-08-create-table-person"



    .





Liquibase . Liquibase.





  • - , .





  • , .





XML

«» XML, DSL: groovy, yaml, json.





, :





  • IDE









remark

, . remark



.





<changeSet id="2021-02-22-create-person" author="uPagge">
    <createTable tableName="person" remarks=" ">
        <column name="id" type="int" autoIncrement="true" remarks="">
            <constraints nullable="false" primaryKey="true"/>
        </column>
        <column name="name" type="varchar(64)" remarks=" ">
            <constraints nullable="false"/>
        </column>
        <column name="telegram" type="int" remarks="  ">
            <constraints unique="true"/>
        </column>
    </createTable>
</changeSet>

: Liquibase.





Liquibase, . , Liquibase.





, .







More articles:


All Articles