Liquibase change sets

As told before, we’ll take a closer look at Liquibase, and create the database with this tool. The documentation at the website is very good, they have a lot of tutorials for all needs, so I’ll give you only a short introduction how to convert the showed SQL script to Liquibase, how to run the change sets (from command line and Maven), and give some personal opinions. Later with the examples I’ll include the liquibase execution into the application, so it updates the database automatically at startup. So, we have our database located somewhere, we know the URL, the username and the password. So we want to add the tables showed in the previous chapter of this series (Shaking the foundations). I use a simple H2 database with following parameters:

  • URL: jdbc:h2:D:/db/variation
  • Username: sa
  • Password:

The URL is a description for the H2 DBMS where to find my database (which is a file). Do not worry if you have no database-file located at your destination folder: H2 is so kind and will create it for you if it does not exist. The username is the default of every H2 instance, and yes, the password is really empty. For testing there’s no need for a password. Naturally if you use another database you may have a password — so don’t forget to set it.

XML

Now we need our script as a liquibase file — first as an XML. Well here it is (or just a part):

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<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-2.0.xsd">

    <changeSet author="ghajba" id="variations-initial">
        <comment>Create the initial database schema</comment>

        <createTable tableName="VEHICLE">
            <column name="ID" type="NUMERIC">
                <constraints nullable="false" primaryKey="true" primaryKeyName="PK_VEHICLE" />
            </column>
            <column name="LICENSE_PLATE" type="VARCHAR(30)">
                <constraints nullable="false" />
            </column>
            <column name="VIN" type="VARCHAR(30)">
                <constraints nullable="false" />
            </column>
            <column name="LICENSE_EXPIRES" type="DATE">
                <constraints nullable="false" />
            </column>
            <column name="CAPACITY" type="NUMERIC">
                <constraints nullable="false" />
            </column>
            <column name="CURB_WEIGHT" type="NUMERIC">
                <constraints nullable="false" />
            </column>
            <column name="GROSS_WEIGHT" type="NUMERIC">
                <constraints nullable="false" />
            </column>
            <column name="MANUFACTURER_NAME" type="VARCHAR(50)">
                <constraints nullable="false" />
            </column>
            <column name="MODEL" type="VARCHAR(255)" />
            <column name="COLOR" type="VARCHAR(255)" />
        </createTable>

        <createTable tableName="DRIVER">
            <column name="ID" type="NUMERIC">
                <constraints nullable="false" primaryKey="true" primaryKeyName="PK_DRIVER" />
            </column>
            <column name="NAME" type="VARCHAR(255)">
                <constraints nullable="false" />
            </column>
            <column name="DRIVING_LICENCE_NUMBER" type="VARCHAR(30)">
                <constraints nullable="false" />
            </column>
            <column name="DRIVING_LICENCE_EXPIRES" type="DATE">
                <constraints nullable="false" />
            </column>
            <column name="DRIVING_LICENCE_CATEGORY" type="VARCHAR(30)">
                <constraints nullable="false" />
            </column>
        </createTable>

...................................

   </changeSet>

</databaseChangeLog>

It’s a big one, you’ll find the file in the repository… As you see in most projects the initial database change is the hugest one because it’s changing most of the time during development.

Let’s save it to /src/main/resources/dbchange/r1.0/ChangeLog-initial.xml The path and the name is not really relevant, it’s my convention to this project to break up the changesets into folders of the releases. However it is not a bad idea for the future if the project grows. Another possibility is to give each file a unique name using the actual date as part of the name. If you are using a bug and issue tracker tool you could create for every database change a ticket and use the number as part of the filename and as the id of the changeset.

Update: in the mean time preparing this post and eventually writing it, the website of Liquibase was changed a bit: it got a face lift and the documentation got into shape. But nevertheless you’ll be able to find everything on it’s place.

SQL

As of Liquibase 2.0 there is a possibility to use formatted SQL files as change-log files. If you prefer SQLs or you just dislike XML you could use this version too.

I think it is a pretty neat feature so I’ll intorduce it — and the nicest ist we can use the SQLs from the previous post. This is a great relief (after I spent almost an aeon to convert the SQLs to XML).

The only thing we have to add is the standard comment of liquibase changes (first line of the file) and the changeset comment with author:id and any other attributes


--liquibase formatted sql
--changeset ghajba:changelog-initial
CREATE TABLE VEHICLE (
  ID NUMBER NOT NULL,
  LICENSE_PLATE VARCHAR(30) NOT NULL,
  VIN VARCHAR(30) NOT NULL,
  LICENSE_EXPIRES DATE NOT NULL,
  CAPACITY NUMBER NOT NULL,
  CURB_WEIGHT NUMBER NOT NULL,
  GROSS_WEIGHT NUMBER NOT NULL,
  MANUFACTURER_NAME VARCHAR(50) NOT NULL,
  MODEL VARCHAR(255), COLOR VARCHAR(255),

  CONSTRAINT PK_VEHICLE PRIMARY KEY (ID)
);

As you can see too creating changesets depends on user preferences. Most of the developers like to deal with SQL scripts however using XMLs gives you the possibility to emit DBMS-specific features and use the same script on various databases. As you can guess I prefer the XML version but in the future I’ll mix up both.

Follow up

After creating the changesets we want to run them. How to do this? Stay tuned until the next post. I promise it will not take so long.

Advertisements

One thought on “Liquibase change sets

  1. Pingback: Running Liquibase change sets — Part 1: command line | HaHaMo Group

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s