Using Liquibase with H2 Databases on Windows

The purpose of this document is to guide you through the process of creating a new Liquibase project with H2 on a Windows machine.

In this tutorial, you will generate an example project and follow the instructions to apply and learn concepts associated with creating new Liquibase projects with an H2 database.

Prerequisites

Tutorial

To create a Liquibase project with an H2 database on your Windows machine:

  1. Create a new project folder and name it LiquibaseH2.
  2. Place the jdbc H2 jar driver file in the LiquibaseH2 project directory.
  3. In your LiquibaseH2 folder, Right-click then select New > Text Document to create an empty text file.
  4. Rename the text file to myChangeLog.xml.

    Note: changelog files contain a sequence of changesets, each of which make small changes to the structure of your database. Instead of creating an empty changelog file in step 2, you can also use an existing database to generate a changelog. For more information see the changelog topic.

Step One: Update your changelog

In this tutorial, you will need to modify your changelog by adding a template.

To add this changelog template:

  1. Open the myChangeLog.xml file.
  2. Update the changelog file with the following code snippet, then save your file:
                  <?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>
            


Step Two: Create a liquibase.properties file

Next we will create a liquibase.properties file. To create the file:

  1. In your LiquibaseH2 folder, Right-click and select New > Text Document to create a new text file.
  2. Rename the text file to liquibase.properties.
  3. Edit theliquibase.properties file to add the following properties:
                    changeLogFile:  myChangeLog.xml  
    url:  jdbc:h2:file:./h2tutorial  
    username:  admin  
    password:  password  
    classpath:  h2-1.4.200.jar
            

Note: In this liquibase.properties file example, the driver jar file is h2-1.4.200.jar under the classpath: property. Please rename it to match the one that you have previously downloaded accordingly.

Step Three: Add your changesets

Now we will manually add a changeset to the changelog file. Remember, when adding changesets, they are uniquely identified by author and id attributes. Liquibase attempts to execute each changeset in a transaction that is committed at the end.

To add additional changesets:

  1. In the myChangeLog.xml file line 9 to 20 add a new department create table changeset as follows:
                
                    <?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"  author="bob">  
        <createTable  tableName="department">  
            <column  name="id"  type="int">  
                <constraints  primaryKey="true"  nullable="false"/>  
            </column>  
            <column  name="name"  type="varchar(50)">  
                <constraints  nullable="false"/>  
            </column>  
            <column  name="active"  type="boolean"  
              defaultValueBoolean="true"/>  
        </createTable>  
   </changeSet>  
</databaseChangeLog>
            

Note: This create table changeset is XML format. The corresponding SQL statement should look like the following:

                
                    CREATE  TABLE  "department"  
  ("id"  number(*,0),  
   "name"  VARCHAR2(50  BYTE),  
   "active"  NUMBER(1,0)  DEFAULT  1  
  );
            
  1. Open the command prompt. Navigate to the Liquibase H2 directory.
  2. Run the following command: liquibase update.

From a database UI Tool, for example: DBeaver check your database changes.

You should see a new “department” table added to the database.

Example Output

                
                    SELECT  *  FROM  public.department;
            
ID NAME ACTIVE
NULL NULL NULL

You should also see two more tables:

  • DATABASECHANGELOG tracking table – This table keeps a record of all the changesets that were deployed. This way, next time when you deploy again, the changesets in the changelog will be compared with the DATABASECHANGELOG tracking table and only the new changesets that were not found in the DATABASECHANGELOG will be deployed. You will notice that a new row was created in that table with the changeset information we have just deployed.
ID AUTHOR FILENAME DATEEXECUTED ORDEREXECUTED EXECTYPE MDSUM
1 bob dbchangelog.xml date&time 1 EXECUTED checksumvalue
  • DATABASECHANGELOGLOCK – This table is used internally by Liquibase to manage access to the changelog table during deployment.