8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

Liquibase : Automating Your SQL and PL/SQL Deployments

This article gives a quick overview of applying changes to the database using Liquibase.

There is a rewrite of this article using the SQLcl implementation of Liquibase, instead of the Liquibase client.

Related articles.

That's Not How You Use It!

When you look at examples of using Liquibase on the internet they all have a few things in common.

The major issue for me is the way code objects are managed. This may not affect you if you never have code in the database, but for a PL/SQL developer, this feels like a show-stopper. As a result, I prefer to work using scripts, which are kept in source control, and use Liquibase as the deployment and sequencing mechanism. I'm sure many Liquibase users will not like this, and will think I'm using it incorrectly. That's fine. There's more discussion about script management here.

I'm not hating on Liquibase. This is what feels right to me, but I reserve the right to change my mind!

Downloads

Download the latest version of the software from here. Unzip it into a directory of your choice.

Download the ojdbc8.jar file from here and place it in the "lib" directory of the Liquibase installation you just unzipped.

Assumptions

For the examples here, we have a file system that look like one of these, depending on the OS used. Adjust as required.

Rem Windows
c:\software\liquibase\liquibase-3.10.0            : The Liquibase software.
c:\git\my_project\changelogs                      : The location for the individual Liquibase changelogs.
c:\git\my_project\scripts                         : The location of any scripts referenced by Liquibase.
c:\git\my_project\changelogs\changelog_master.xml : The master.xml that maintains the changelog order.

# Linux
/software/liquibase/liquibase-3.10.0              : The Liquibase software.
/git/my_project/changelogs                        : The location for the individual Liquibase changelogs.
/git/my_project/scripts                           : The location of any scripts referenced by Liquibase.
/git/my_project/changelogs/changelog_master.xml   : The master.xml that maintains the changelog order.

You have Liquibase in your path. Notice I use the UNIX-style separator throughout the rest of the article. Liquibase and Windows don't really care about the separator. A path beginning with "/" is assumed to be relative to the current drive.

Rem Windows
set PATH=c:\software\liquibase\liquibase-3.10.0;%PATH%
set JAVA_HOME=c:\Program Files\Java\jre1.8.0_221

# Linux
export PATH=/software/liquibase/liquibase-3.10.0:$PATH
export JAVA_HOME=/u01/java/latest:$PATH

liquibase.properties

When calling Liquibase you will need to include a number of parameters.

liquibase --driver=oracle.jdbc.OracleDriver ^
      --classpath="/software/liquibase/liquibase-3.10.0/lib" ^
      --changeLogFile="/git/my_project/changelogs/changelog_00001.xml" ^
      --url="jdbc:oracle:thin:@localhost:1521/pdb1" ^
      --username=test ^
      --password=test ^
      {command}

This is a little clumsy, so you can replace all the parameters with a "liquibase.properties" file. If we keep that name and it is present in our current location it will be used, but we will probably need several to cope with different schemas. We create a file called "test_liquibase.properties" with the following contents.

driver: oracle.jdbc.OracleDriver
classpath: /software/liquibase/liquibase-3.10.0/lib
url: jdbc:oracle:thin:@localhost:1521/pdb1
username: test
password: test

We can now run commands using the "--defaultsFile" parameter.

liquibase --defaultsFile="/software/liquibase/test_liquibase.properties" {command}

Generating a Changelog?

If you are starting to use Liquibase against an existing project, you can generate an initial changelog that represents your day 1 state. To demonstrate this, let's create some objects in a clean schema called "TEST".

create table tab1 (
  id          number,
  description varchar2(50),
  constraint tab1_pk primary key (id)
);

create sequence tab1_seq;

insert into tab1 (id, description) values (tab1_seq.nextval, 'Description for ' || tab1_seq.currval);
commit;

create or replace function get_tab1_count return number as
  l_count  number;
begin
  select count(*)
  into   l_count
  from   tab1;

  return l_count;
end;
/

We generate an initial changelog to capture the state of our initial schema contents.

liquibase --defaultsFile="/software/liquibase/test_liquibase.properties" --changeLogFile="my_changelog.xml" generateChangeLog

The resulting changelog looks like this. Notice it doesn't contain definitions of stored code (procedures, functions, packages, triggers) and the seed data is also missing. It's also worth noting that the object definitions are XML-based, rather than DDL and DML.

<?xml version="1.1" 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-3.9.xsd">

    <changeSet author="tim (generated)" id="1571293572660-1">
        <createTable tableName="TAB1">
            <column name="ID" type="NUMBER">
                <constraints primaryKey="true" primaryKeyName="TAB1_PK"/>
            </column>
            <column name="DESCRIPTION" type="VARCHAR2(50 BYTE)"/>
        </createTable>
    </changeSet>
    <changeSet author="tim (generated)" id="1571293572660-2">
        <createSequence maxValue="9999999999999999999999999999" sequenceName="TAB1_SEQ" startValue="21"/>
    </changeSet>
</databaseChangeLog>

For this reason I don't believe generating an initial changelog makes sense for all but the simplest projects. Instead, have a fixed point in time and start using Liquibase from there onward.

Create a master.xml

The master.xml, also known as a controlfile or master index, is an ordered list of changelogs. When we start using Liquibase we can just create an empty one manually with the following contents.

<?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.9.xsd">

</databaseChangeLog>

Every changelog that happens from now on has to be referenced in order from this master.xml.

We can create one that references every changelog in a specific directory, but this requires the changelogs to be named in a way that the application order is preserved, which could prove problematic. It feels safer to avoid this approach unless you are extremely disciplined.

<?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.9.xsd">
    <includeAll path="/git/my_project/changelogs"/>
</databaseChangeLog>

Applying Changes

We have a script called "tab1_seq.sql" in our "/git/my_project/scripts" directory with the following contents.

-- tab1_seq.sql
create sequence tab1_seq;

The comment line at the start of each SQL file is the file name. This isn't necessary, but it makes things a clearer when looking at the changelog.

We have a script called "tab1.sql" in our "/git/my_project/scripts" directory with the following contents.

-- tab1.sql
create table tab1 (
  id          number,
  description varchar2(50),
  constraint tab1_pk primary key (id)
);

insert into tab1 (id, description) values (tab1_seq.nextval, 'Description for ' || tab1_seq.currval);
commit;

We have a script called "get_tab1_count.sql" in our "/git/my_project/scripts" directory with the following contents.

-- get_tab1_count.sql
create or replace function get_tab1_count returns number as
  l_count  number;
begin
  select count(*)
  into   l_count
  from   tab1;

  return l_count;
end;
/

If we want to apply these as a change to the database, so we create a changelog for them. Let's call this "changelog_00001_setup_tab1.xml" and place it in the "/git/my_project/changelogs" directory.

<?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.9.xsd">

    <changeSet author="tim" id="01_tab1_seq">
      <sqlFile dbms="oracle"
               endDelimiter=";"
               path="../scripts/tab1_seq.sql"
               relativeToChangelogFile="true"
               splitStatements="true"
               stripComments="false"/>
    </changeSet>
    <changeSet author="tim" id="02_tab1">
      <sqlFile dbms="oracle"
               endDelimiter=";"
               path="../scripts/tab1.sql"
               relativeToChangelogFile="true"
               splitStatements="true"
               stripComments="false"/>
    </changeSet>
    <changeSet author="tim" id="03_get_tab1_count" runOnChange="true">
      <sqlFile dbms="oracle"
               endDelimiter=";"
               path="../scripts/get_tab1_count.sql"
               relativeToChangelogFile="true"
               splitStatements="false"
               stripComments="false"/>
    </changeSet>
</databaseChangeLog>

There are some things to consider here.

We add a reference to this new changelog into the master.xml, so it now looks like this.

<?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.9.xsd">

  <include file="./changelog_00001_setup_tab1.xml" relativeToChangelogFile="true"/> 
</databaseChangeLog>

We can then apply the change using the following command. This is run on Windows, hence the "^" for the line continuation. For Linux replace this by "\".

liquibase --defaultsFile="/software/liquibase/test_liquibase.properties" ^
          --changelog-file="/git/my_project/changelogs/changelog_master.xml" ^
          update

This will create the objects associated with our change, and create some Liquibase objects to track the changes that have been applied.

column object_name format a30

select object_name, object_type
from   user_objects
order by 1, 2;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
DATABASECHANGELOG              TABLE
DATABASECHANGELOGLOCK          TABLE
GET_TAB1_COUNT                 FUNCTION
PK_DATABASECHANGELOGLOCK       INDEX
TAB1                           TABLE
TAB1_PK                        INDEX
TAB1_SEQ                       SEQUENCE

7 rows selected.

SQL>

If we describe the DATABASECHANGELOG table we can see the information that's captured.

SQL> desc databasechangelog
Name          Null?    Type
------------- -------- -------------
ID            NOT NULL VARCHAR2(255)
AUTHOR        NOT NULL VARCHAR2(255)
FILENAME      NOT NULL VARCHAR2(255)
DATEEXECUTED  NOT NULL TIMESTAMP(6)
ORDEREXECUTED NOT NULL NUMBER(38)
EXECTYPE      NOT NULL VARCHAR2(10)
MD5SUM                 VARCHAR2(35)
DESCRIPTION            VARCHAR2(255)
COMMENTS               VARCHAR2(255)
TAG                    VARCHAR2(255)
LIQUIBASE              VARCHAR2(20)
CONTEXTS               VARCHAR2(255)
LABELS                 VARCHAR2(255)
DEPLOYMENT_ID          VARCHAR2(10)
SQL>

We display just the ID and FILENAME from the table. We can see the three changesets from our changelog.

column id format a17
column filename format a60

select id, filename from databasechangelog;

ID                FILENAME
----------------- ------------------------------------------------------------
01_tab1_seq       /git/my_project/changelogs/changelog_00001_setup_tab1.xml
02_tab1           /git/my_project/changelogs/changelog_00001_setup_tab1.xml
03_get_tab1_count /git/my_project/changelogs/changelog_00001_setup_tab1.xml

SQL>

We then get a second set of changes.

We have a script called "tab2_seq.sql" in our "/git/my_project/scripts" directory with the following contents.

-- tab2_seq.sql
create sequence tab2_seq;

We have a script called "tab2.sql" in our "/git/my_project/scripts" directory with the following contents.

-- tab2.sql
create table tab2 (
  id          number,
  description varchar2(50),
  constraint tab2_pk primary key (id)
);

We create a new changelog for this called "changelog_00002_setup_tab2.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.9.xsd">

    <changeSet author="tim" id="01_tab2_seq">
      <sqlFile dbms="oracle"
               endDelimiter=";"
               path="../scripts/tab2_seq.sql"
               relativeToChangelogFile="true"
               splitStatements="true"
               stripComments="false"/>
    </changeSet>
    <changeSet author="tim" id="02_tab2">
      <sqlFile dbms="oracle"
               endDelimiter=";"
               path="../scripts/tab2.sql"
               relativeToChangelogFile="true"
               splitStatements="true"
               stripComments="false"/>
    </changeSet>
</databaseChangeLog>

We add a reference to this new changelog into the master.xml, so it now looks like this.

<?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.9.xsd">

  <include file="./changelog_00001_setup_tab1.xml" relativeToChangelogFile="true"/> 
  <include file="./changelog_00002_setup_tab2.xml" relativeToChangelogFile="true"/> 
</databaseChangeLog>

We can then apply the change the same way we did the first time.

liquibase --defaultsFile="/software/liquibase/test-liquibase.properties" ^
          --changelog-file="/git/my_project/changelogs/changelog_master.xml" ^
          update

Liquibase knows it applied the first changelog already, so it applies only the second. We can see the new objects present in the database.

column object_name format a30

select object_name, object_type
from   user_objects
order by 1, 2;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
DATABASECHANGELOG              TABLE
DATABASECHANGELOGLOCK          TABLE
GET_TAB1_COUNT                 FUNCTION
PK_DATABASECHANGELOGLOCK       INDEX
TAB1                           TABLE
TAB1_PK                        INDEX
TAB1_SEQ                       SEQUENCE
TAB2                           TABLE
TAB2_PK                        INDEX
TAB2_SEQ                       SEQUENCE

10 rows selected.

SQL>

We query the DATABASECHANGELOG table, and we can see the two changesets from our second changelog have been applied.

column id format a17
column filename format a60

select id, filename from databasechangelog;

ID                FILENAME
----------------- ------------------------------------------------------------
01_tab1_seq       /git/my_project/changelogs/changelog_00001_setup_tab1.xml
02_tab1           /git/my_project/changelogs/changelog_00001_setup_tab1.xml
03_get_tab1_count /git/my_project/changelogs/changelog_00001_setup_tab1.xml
01_tab2_seq       /git/my_project/changelogs/changelog_00002_setup_tab2.xml
02_tab2           /git/my_project/changelogs/changelog_00002_setup_tab2.xml

SQL>

Considerations

For more information see:

Hope this helps. Regards Tim...

Back to the Top.