Liquibase: a database version management tool

Time:2020-10-31

Liquibase: a database version management tool

In the process of R & D, database changes, table structure repair and data modification are often involved. In order to ensure that the environment can be changed correctly, we may need to maintain a database upgrade document to save these records. The environment that needs to be upgraded is upgraded according to the document.

This manual maintenance has several disadvantages:

  1. There is no guarantee that every environment will perform as required
  2. There may not be a relative rollback statement for problems encountered
  3. Unable to automate

In order to solve these problems, we have carried out some research, and the main research objects are liquibase and flyway. We hope to achieve the following goals through the database version management tool:

  1. Database upgrade
  2. Database rollback
  3. Version mark

During the investigation, we found that flyway database rollback function is a value-added function, and the implementation logic is to “intelligently” degrade through our upgrade script, which is not in line with our current usage scenarios. For the introduction of flyway, please refer to my earlier introduction: https://segmentfault.com/a/11…

Liquibase

Liquibase helps teams track, versione, and deploy database schema and logic changes

install

Check JRE

$ java -version
java version "1.8.0_231"
Java(TM) SE Runtime Environment (build 1.8.0_231-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.231-b11, mixed mode)

If you don’t have Java installed, please install it yourself

Install liquibase

Download liquibase version#- bin.tar.gz Documents,
Unzip the package and add the directory to the environment variable

$ export PATH="/opt/liquibase-3.8.2:$PATH"

This command restarts the command line and will not take effect. If you want to ensure that it is always available, you need to set this leading to.bashrcperhaps.zshrcin

Verify the installation by running the help command

$ liquibase --help
17:12:10.389 [main] DEBUG liquibase.resource.ClassLoaderResourceAccessor - Opening jar:file:/opt/liquibase-3.8.2/liquibase.jar!/liquibase.build.properties as liquibase.build.properties
Starting liquibase at Wednesday, 04 December 2019 17:12:10 CST (version 3.8.2 ා 26 build at Tue Nov 26 04:53:39 UTC 2019)


Usage: java -jar liquibase.jar [options] [command]

Standard Commands:
...

configuration file

The following is a configuration file for MySQL

$ cat liquibase.properties
driver: com.mysql.cj.jdbc.Driver
classpath: ./mysql-connector-java-8.0.18.jar
url: jdbc:mysql://127.0.0.1/test
username: root
password: 123456
changeLogFile: myChangeLog.xml

Database upgrade

establishmyChangeLog.xmlFile. This file is used to record the upgrade, update information, and initialization contents

$ cat myChangeLog.xml
<?xml version="1.0" encoding="UTF-8"?>

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

</databaseChangeLog>

Liquibase supports the creation of databases by SQL description

$ cat myChangeLog.xml
<?xml version="1.0" encoding="UTF-8"?>

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

    <changeSet id="1.0" author="bohan">
        <sql>
        CREATE TABLE `deparment` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        `name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
        PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
        </sql>
    </changeSet>
</databaseChangeLog>
$ liquibase update
Liquibase Community 3.8.2 by Datical
Liquibase: Update has been successful.

Through implementationliquibase updateThe upgraded database is as follows. The database has been created for us. At the same time, liquibase has generated two tables to manage the database upgrade records

$ mysql -h 127.0.0.1 -uroot -p123456 test -e "show tables;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------------+
| Tables_in_test        |
+-----------------------+
| DATABASECHANGELOG     |
| DATABASECHANGELOGLOCK |
| deparment             |
+-----------------------+

Continue with upgrade

$ cat myChangeLog.xml
<?xml version="1.0" encoding="UTF-8"?>

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

    <changeSet id="1.0" author="bohan">
        <sql>
        CREATE TABLE `deparment` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        `name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
        PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
        </sql>
    </changeSet>
    <changeSet id="1.1" author="bohan">
        <sql>
        insert into deparment values(1, "test");
        </sql>
    </changeSet>
</databaseChangeLog>
$ liquibase update
Liquibase Community 3.8.2 by Datical
Liquibase: Update has been successful.
$ mysql -h 127.0.0.1 -uroot -p test -e "select * from deparment;"
Enter password:
+----+------+
| id | name |
+----+------+
|  1 | test |
+----+------+

The data is added as expected

Through SQL file

Database changes can also be referenced in the form of SQL files to avoidmyChangeLog.xmlThe file is too large

<changeSet id="1.1" author="bohan">
    <sqlFile path="./update_deparment_name.sql"></sqlFile>
</changeSet>

Database rollback

liquibase --help
Usage: java -jar liquibase.jar [options] [command]

Standard Commands:
 rollbackCount <value>          Rolls back the last <value> change sets
                                applied to the database

Let’s do itrollbackCountRoll back

$ liquibase rollbackCount 1
Liquibase Community 3.8.2 by Datical
Rolling Back Changeset:myChangeLog.xml::1.0::bohan
Unexpected error running Liquibase: No inverse to liquibase.change.core.RawSQLChange created
For more information, please use the --logLevel flag

Prompt that SQL is not rolled back, modify ourmyChangeLog.xml

$ cat myChangeLog.xml
<?xml version="1.0" encoding="UTF-8"?>

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

    <changeSet id="1.0" author="bohan">
        <sql>
        CREATE TABLE `deparment` (
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        `name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
        PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
        </sql>
        <rollback>
        DROP TABLE deparment;
        </rollback>
    </changeSet>
    <changeSet id="1.1" author="bohan">
        <sql>
        insert into deparment values(1, "test");
        </sql>
        <rollback>
        DELETE FROM deparment WHERE id = 1;
        </rollback>
    </changeSet>
</databaseChangeLog>

After performing the rollback, it is found that there are no new records

liquibase rollbackCount 1
Liquibase Community 3.8.2 by Datical
Rolling Back Changeset:myChangeLog.xml::1.1::bohan
Liquibase: Rollback has been successful.
$ mysql -h 127.0.0.1 -uroot -p test -e "select * from deparment;"
Enter password:

Again, the database is deleted as expected

$ liquibase rollbackCount 1
Liquibase Community 3.8.2 by Datical
Rolling Back Changeset:myChangeLog.xml::1.0::bohan
Liquibase: Rollback has been successful.
$ mysql -h 127.0.0.1 -uroot -p test -e "show tables;"
Enter password:
+-----------------------+
| Tables_in_test        |
+-----------------------+
| DATABASECHANGELOG     |
| DATABASECHANGELOGLOCK |
+-----------------------+

Version mark

Liquibase provides a perfect tag function. After just rolling back to the last operation, we have only implemented the change with ID 1.0

mysql> select * from DATABASECHANGELOG;
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
| ID  | AUTHOR | FILENAME        | DATEEXECUTED        | ORDEREXECUTED | EXECTYPE | MD5SUM                             | DESCRIPTION | COMMENTS | TAG  | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID |
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
| 1.0 | bohan  | myChangeLog.xml | 2019-12-05 03:15:18 |             1 | EXECUTED | 8:fe52f094e795797c89459e8f22483482 | sql         |          | NULL | 3.8.2     | NULL     | NULL   | 5515718387    |
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
1 row in set (0.00 sec)

In actual development, when we upgrade the version, we usually need to execute multiple changes at the same time. If there is a problem with variables and it needs to be rolled back, it will be more troublesome to roll back according to the quantity. We need to label our changes. The following commands may be used:

liquibase --help
11:21:31.994 [main] DEBUG liquibase.resource.ClassLoaderResourceAccessor - Opening jar:file:/opt/liquibase-3.8.2/liquibase.jar!/liquibase.build.properties as liquibase.build.properties
Starting liquibase at Thursday, 05 December, 2019 11:21:31 CST (version 3.8.2 ා 26 build at Tue Nov 26 04:53:39 UTC 2019)


Usage: java -jar liquibase.jar [options] [command]

Standard Commands:
 rollback <tag>                 Rolls back the database to the the state is was
Maintenance Commands
 tag <tag string>          'Tags' the current database state for future rollback
 tagExists <tag string>    Checks whether the given tag is already existing

For the current database, we useliquibase tagLabel operation

$ liquibase tag v1.0
Liquibase Community 3.8.2 by Datical
Successfully tagged '[email protected]@jdbc:mysql://127.0.0.1/test'
Liquibase command 'tag' was executed successfully.

Check the record and find that ID is1.0Set to in the record tag ofv1.0, in line with our expectations

mysql> select * from DATABASECHANGELOG;
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
| ID  | AUTHOR | FILENAME        | DATEEXECUTED        | ORDEREXECUTED | EXECTYPE | MD5SUM                             | DESCRIPTION | COMMENTS | TAG  | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID |
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
| 1.0 | bohan  | myChangeLog.xml | 2019-12-05 03:15:18 |             1 | EXECUTED | 8:fe52f094e795797c89459e8f22483482 | sql         |          | v1.0 | 3.8.2     | NULL     | NULL   | 5515718387    |
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
1 row in set (0.00 sec)

After the update is executed, if it is necessary to roll back theliquibase rollback v1.0that will do

$ liquibase update
Liquibase Community 3.8.2 by Datical
Liquibase: Update has been successful.

mysql> select * from DATABASECHANGELOG;
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
| ID  | AUTHOR | FILENAME        | DATEEXECUTED        | ORDEREXECUTED | EXECTYPE | MD5SUM                             | DESCRIPTION | COMMENTS | TAG  | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID |
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
| 1.0 | bohan  | myChangeLog.xml | 2019-12-05 03:15:18 |             1 | EXECUTED | 8:fe52f094e795797c89459e8f22483482 | sql         |          | v1.0 | 3.8.2     | NULL     | NULL   | 5515718387    |
| 1.1 | bohan  | myChangeLog.xml | 2019-12-05 03:28:06 |             2 | EXECUTED | 8:695a5ec0b2b3ddc4a9beeeca530adebc | sql         |          | NULL | 3.8.2     | NULL     | NULL   | 5516486105    |
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
2 rows in set (0.00 sec)


$ liquibase rollback v1.0
Liquibase Community 3.8.2 by Datical
Rolling Back Changeset:myChangeLog.xml::1.1::bohan
Liquibase: Rollback has been successful.

mysql> select * from DATABASECHANGELOG;
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
| ID  | AUTHOR | FILENAME        | DATEEXECUTED        | ORDEREXECUTED | EXECTYPE | MD5SUM                             | DESCRIPTION | COMMENTS | TAG  | LIQUIBASE | CONTEXTS | LABELS | DEPLOYMENT_ID |
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
| 1.0 | bohan  | myChangeLog.xml | 2019-12-05 03:15:18 |             1 | EXECUTED | 8:fe52f094e795797c89459e8f22483482 | sql         |          | v1.0 | 3.8.2     | NULL     | NULL   | 5515718387    |
+-----+--------+-----------------+---------------------+---------------+----------+------------------------------------+-------------+----------+------+-----------+----------+--------+---------------+
1 row in set (0.00 sec)

Recommended Today

MVC and Vue

MVC and Vue This article was written on July 27, 2020 The first question is: is Vue an MVC or an MVVM framework? Wikipedia tells us: MVVM is a variant of PM, and PM is a variant of MVC. So to a certain extent, whether Vue is MVC or MVVM or not, its ideological direction […]