Liquibase - with Postgres

I wanted a quick guide on how to get Liquibase working on a Macbook Pro.

I didn’t want to faff about with Java, and Maven.

Just get the liquibase command line working with Postgres.

Postgres

Postgres was already installed, but use homebrew to install:

brew install postgresql

Use the pg_ctl command to see if postgres is running:

pg_ctl -D /usr/local/var/postgres start

pg_ctl -D /usr/local/var/postgres status
pg_ctl: server is running (PID: 1231)
/usr/local/Cellar/postgresql/11.2/bin/postgres "-D" "/usr/local/var/postgres"

I didn’t have a database created for my local user (milesd).

I used the createdb command

psql -U milesd -h localhost
psql: FATAL:  database "milesd" does not exist

$ createdb milesd

psql -U milesd -h localhost
psql (11.4, server 11.2)
Type "help" for help.

milesd=#
Liquibase

Install:

brew install liquibase

I created a ~/liquibase/properties directory, with the following:

./liquibase.properties
./lib
./lib/postgresql-42.2.6.jar
./changelog.xml

postgresql-42.2.6.jar was downloaded from https://jdbc.postgresql.org/download.html

liquibase.properties

driver: org.postgresql.Driver
classpath: ./lib/postgresql-42.2.6.jar
url: jdbc:postgresql://localhost:5432/milesd
username: milesd
password:
changeLogFile: ./changelog.xml

changelog.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.1.xsd">

    <changeSet id="1" author="milesd">
        <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>

Run liquibase with properties file, and update:

liquibase --defaultsFile=liquibase.properties update
Starting Liquibase at Thu, 08 Aug 2019 21:06:05 BST (version 3.7.0 built at 2019-07-16 02:26:39)
Liquibase: Update has been successful.

Confirm that the department database has been created:

$ psql --u milesd
psql (11.4, server 11.2)
Type "help" for help.

milesd=# \c milesd
psql (11.4, server 11.2)
You are now connected to database "milesd" as user "milesd".
milesd=# \d department;

# \d department;
                    Table "public.department"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 id     | integer               |           | not null |
 name   | character varying(50) |           | not null |
 active | boolean               |           |          | true
Indexes:
    "department_pkey" PRIMARY KEY, btree (id)

Add a second change-set to liquibase.properties (below the existing changeSet):

<changeSet author="liquibase-docs" id="addUniqueConstraint-example">
       <addUniqueConstraint catalogName="department"
            columnNames="name,active"
            constraintName="const_name"
            deferrable="true"
            disabled="true"
            initiallyDeferred="true"
            schemaName="public"
            tableName="department"/>
       </changeSet>

</databaseChangeLog>

the “databaseChangeLog” closing XML tag is for reference

Run liquibase update a second time:

liquibase --defaultsFile=liquibase.properties update
Starting Liquibase at Thu, 08 Aug 2019 21:17:23 BST (version 3.7.0 built at 2019-07-16 02:26:39)
Liquibase: Update has been successful.

Checking the department table:

# \d department;
                    Table "public.department"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 id     | integer               |           | not null |
 name   | character varying(50) |           | not null |
 active | boolean               |           |          | true
Indexes:
    "department_pkey" PRIMARY KEY, btree (id)
    "const_name" UNIQUE CONSTRAINT, btree (name, active) DEFERRABLE INITIALLY DEFERRED

The second constraint, const_name has been created.

Reference https://www.liquibase.org/documentation/changes/add_unique_constraint.html