DBA Hub

📋Steps in this guide1/8

Liquibase : Automating Your SQL and PL/SQL Deployments

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

oracle miscconfigurationintermediate
by OracleDba
14 views
1

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. - They are typically examples used to track table changes and not much else. - Like my examples, they are based on small simple schemas. This always makes sense, but issues arise with some methods when things grow. - They don't include code objects (procedure, functions, packages, triggers, types etc.). - If they do include code objects, they assume each version of the code is in a new file. This means you're going to lose the conventional commit history of a file you would normally expect for code. Instead you have to manually diff between separate files. - They assume people need to rollback changes to previous versions of the database through this mechanism. I think creating a rollback script for each schema change makes sense, but I think it's a bad idea to include it in this mechanism. In my opinion all changes should move forward. So a "rollback" is really a new change applied to the database that reverts the changes. This is especially true as the code related functionality of the Pro edition doesn't support rollback anyway. - Following on from the previous point, the database code related functionality of the Pro edition doesn't support rollback, so if I have to manually handle rollback, I might as well do the lot. 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!
2

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.
3

Assumptions

For the examples here, we have a file system that look like one of these, depending on the OS used. Adjust as required. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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.

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
4

liquibase.properties

When calling Liquibase you will need to include a number of parameters. 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. We can now run commands using the "--defaultsFile" parameter.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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}

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

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

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". We generate an initial changelog to capture the state of our initial schema contents. 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. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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;
/

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

<?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>
6

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. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?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>

<?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>
7

Applying Changes

We have a script called "tab1_seq.sql" in our "/git/my_project/scripts" directory with the following contents. 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. We have a script called "get_tab1_count.sql" in our "/git/my_project/scripts" directory with the following contents. 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. There are some things to consider here. - The entries in the changelog need to be in the correct order. The creation of the seed data requires the sequence, so the sequence creation must come first in the changelog. The function depends on the table, so the table creation has to happen before the function change. - We need to make sure "splitStatements" is set to false for database code (triggers, procedures, functions, packages, types etc.) or Liquibase will try to split on ";" and errors will be produced when those changes are applied. - We are using relative paths (relativeToChangelogFile="true"). Explicit paths can be used if required. - The ID should be unique for all changesets applied to the schema, so you will often see people using a GUID. We add a reference to this new changelog into the master.xml, so it now looks like this. 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 "\". This will create the objects associated with our change, and create some Liquibase objects to track the changes that have been applied. If we describe the table we can see the information that's captured. We display just the and from the table. We can see the three changesets from our changelog. 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. We have a script called "tab2.sql" in our "/git/my_project/scripts" directory with the following contents. We create a new changelog for this called "changelog_00002_setup_tab2.xml". We add a reference to this new changelog into the master.xml, so it now looks like this. We can then apply the change the same way we did the first time. Liquibase knows it applied the first changelog already, so it applies only the second. We can see the new objects present in the database. We query the table, and we can see the two changesets from our second changelog have been applied.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
-- tab1_seq.sql
create sequence tab1_seq;

-- 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;

-- 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;
/

<?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>

<?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>

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

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>

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>

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>

-- tab2_seq.sql
create sequence tab2_seq;

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

<?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>

<?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>

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

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>

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>
8

Considerations

- As mentioned previously, I don't think you should generate a changelog from an existing schema. I think you should only track changes from the day you start using Liquibase onward. - All change scripts should have an equivalent "undo" script, but I don't like the concept of rollback as described in Liquibase changesets. Instead I feel a better solution is "fix forward". If a change has a problem and needs to be backed out, the "undo" script should be included as a new change in a changelog, and applied via the master.xml, so any form of rollback/undo is still a move forward. This includes reverting code object versions. - Everything should be in version control, which is why I've included "git" in the paths. The changelogs and the code don't have to be in the same repository though, depending on your requirements. - Management of the changelog is important, because it is the serialisation of changes to the database. How you manage the changelog is an interesting process. - This requires some discipline. So should your manual process, but I suspect many people are more lax than you can be when using an automation tool like this. - I'm purposely keeping this scripts-based. I know a lot of people will not like this and will prefer to use the XML tag approach to creating changelogs. It just doesn't work for me at this point. It also doesn't support a whole bunch of object types I care about. - Deciding how to approach different schema and privileged accounts takes some thought. In some cases you will need to coordinate changes to multiple schemas, including some via privileged accounts. - I don't like the thought of relying on file names to control the apply order, but I know some other products encourage that. This is a personal choice, and the example changelogs here are named with an order in mind, even though we are not using it. - Every change needs to be in a separate changeset, or this can affect the way failures are handled. That may feel odd to people who like to combine multiple changes into a single build scripts. This is one concession I'm prepared to make. - There is more functionality in the Pro edition, but it still feels like it suffers from the same issues to me. The added metadata is fine, but the source control issue is still present, and it doesn't include any meaningful rollback functionality. For more information see: - Liquibase - Liquibase Best Practices - Liquibase : All Articles - Liquibase : Deploying Oracle Application Express (APEX) Applications - SQLcl : Automating Your SQL and PL/SQL Deployments using the SQLcl implementation of Liquibase Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!