Change Happens

Use open source tools to handle database change management.

May/June 2017

Oracle Database offers a powerful foundation on which to build and manage applications. And because applications are constantly changing, the underlying databases supporting those applications must also change.

How do you manage these changing databases? The obvious answer is or should be, “Just check the SQL scripts into my version control system.” That works pretty well, right up until you need to go back and alter one of the existing objects. Changing your database structure is not as simple as changing application code.

This article explores how you can use a popular open source tool to help you manage database changes.

In the past, I’ve handled database changes by running a set of scripts at release time. I prefer to have each script handle a single task, such as creating a table, when possible.

For each release, I’ve maintained two directories of scripts:

1. A set of master/create scripts for a fresh install of the product at that version.
2. A set of “alter…” scripts to modify the database from the previous release to this release.

Each script set had its own master script, and each script contained validation PL/SQL to make sure the script ran only when it was supposed to and logged errors when something went wrong.

I have made this process work, but it has been a bit of a struggle. Every change needed to be performed twice, once in each directory. And this approach did not address several critical issues, such as

• What if I need to roll something back?
• Did everything run?
• Can I run certain scripts only under specific conditions, such as testing?

There are several open source tools to help manage these and other critical database update issues. And there’s one open source application I’ve used off and on that handles these database change issues and quite a bit more: Liquibase.

Liquibase is a change management tool. It is open source, was written in Java, and works with several different databases.

Because different databases implement variations on American National Standards Institute(ANSI) SQL, Liquibase uses its own object definitions, in multiple formats such as XML, JSON, and YAML. And instead of SQL scripts, Liquibase uses change set files. Most of the change set examples on the Liquibase site use XML, but I think XML is a bit too pointy. These days the most commonly used notation is JSON, and that’s what I use most often. You can also create change sets with SQL, but Liquibase will not auto-generate rollbacks. I’ll explain more on this later.

Much as with my master run script approach, Liquibase uses a master change log file that executes the individual change set files in the order they are intended to be run.

For example, the following master log file will run the changelog-1.0 and then the changelog-2.0 JSON-formatted change sets:

{
 "databasechangelog": [
   {
     "include": {
       "file": "changelog/db.changelog-1.0.json"
     }
   },
   {
     "include": {
       "file": "changelog/db.changelog-2.0.json"
     }
   }
  ]
}

The first change set—db.changelog-1.0.json, in Listing 1 —creates a table called lb_groups with three columns: id, name, and description.

Code Listing 1: DB.changelog-1.0.json change set

{"databaseChangeLog": [{
  "preConditions": [{
    "runningAs": {"username": "lb_demo"}
                }]
        },{
            "changeSet": {
               "id": "Two-Table-1",
               "author": "BlaineCarter",
               "comment": "Add table lb_groups",
               "tagDatabase": {"tag":"myCoolTag1"},
               "context": "context1",
               "labels": "label1",
               "changes": [
                  {
                    "createTable": {
                       "tableName": "lb_groups",
                       "columns": [
                           {
                             "column": {
                                "name": "id",
                                "type": "int",
                                "autoIncrement": true,
                                "constraints": {
                                  "primaryKey": true,
                                  "nullable": false
                                }
                              }
                            },
                            {
                              "column": {
                                 "name": "name",
                                 "type": "varchar(50)",
                                 "constraints": {
                                   "unique": true,
                                   "uniqueConstraintName": "uk_lb_groups_name"
                                 }
                              }
                            },
                            {
                              "column": {
                                 "name": "description",
                                 "type": "varchar(200)"
                                 }
                              }
                          ]
                       }
                    }
                ]
            }
        }
    ]
}

The second and slightly more complex change set—db.changelog-2.0.json, in Listing 2 —creates the lb_people table and includes a foreign key on lb_groups.id.

Code Listing 2: DB.changelog-2.0.json change set

{"databasechangelog": [{
  "preConditions": [
    {"runningAs": {"username": "lb_demo"}}
  ]
        },{
            "changeSet": {
                "id": "Two-Table-2",
                "author": "BlaineCarter",
                "comment": "Add table lb_people",
                "tagDatabase": {"tag":"myCoolTag2"},
                "context": "context2",
                "labels": "label2",
                "changes": [
                    {
                        "createTable": {
                            "tableName": "lb_people",
                            "columns": [
                                {
                                    "column": {
                                        "name": "id",
                                        "type": "int",
                                        "autoIncrement": true,
                                        "constraints": {
                                            "primaryKey": true,
                                            "nullable": false
                                        },
                                    }
                                },
                                {
                                    "column": {
                                        "name": "firstname",
                                        "type": "varchar(50)"
                                    }
                                },
                                {
                                    "column": {
                                        "name": "lastname",
                                        "type": "varchar(50)",
                                        "constraints": {
                                            "nullable": false
                                        },
                                    }
                                },
                                {
                                    "column": {
                                        "name": "group_id",
                                        "type": "int",
                                        "constraints": {
                                            "foreignKeyName": "groupFK",
                                            "references": "lb_groups(id)"
                                        },
                                    }
                                }
                            ]
                        }
                    }
                ]
            }
        }
    ]
}

The Liquibase documentation recommends that you perform one change per change set. You could do everything in a single file if you wanted to, but the more you pack into a single change set, the more difficult it will be to troubleshoot problems later.

As you can see in Listings 1 and 2, most of the change set properties are self-explanatory, but there are a couple of extra properties.

The change sets require an “id” and an “author.” These values are combined with the name of the change set file and package to uniquely identify a change set. The id is a string type and can be any value. The author value should refer to the person who authored the change set. Although an author value is required, it is not validated against anything, so you could use “Unknown” as the author value if you would rather not track authors.

The preConditions array enables you to identify checks that must pass for the changes to run. In these change sets, I’m making sure I’m logged in as lb_demo in the preConditions array. (There are many other preconditions you can use.) If any of the preConditions at the change log level fail, the current execution of the change log will fail and none of the change sets will run. You can also include preConditions inside the changeSet code, and these will apply to that specific change only.

The “comment,” “tagDatabase,” “context,” and “labels” properties are used by Liquibase to track changes and for some more-advanced control options I won’t cover in this article. There are links to the documentation for these properties and more at the end of the article.

To create your own master log and change set files in Liquibase and exercise Liquibase database change management features, first set up your environment:

1. In your database, create a schema named lb_demo. If you would rather use another schema, replace lb_demo with your schema in the examples. Make sure the schema you use is safe to experiment in.
2. Download and extract Liquibase. I extracted it to /opt/liquibase/.
3. Check your Java version with this command:

   java -version

Install Java or upgrade Java as needed. (Liquibase 2.x requires Java 1.5+; Liquibase 3.x requires Java 1.6+.)

1. Locate—or download and locate—the Oracle JDBC driver. Mine is located at /usr/lib/oracle/12.1/client64/lib/ojdbc7.jar.
2. Create a directory to work in.
3. Inside that directory, create a child directory called changelog.
4. Take the ChangeLog master example above, and save it in the changelog directory to a file named db.changelog-master.json.
5. Take the first change set example—Listing 1 —and save it in the changelog directory to a file named db.changelog-1.0.json.
6. Take the second change set example—Listing 2 — and save it in the changelog directory to a file named db.changelog-2.0.json.
7. (Optional) Define an environment variable with any additional configurations your database connection may need. Skip this step if your database connection doesn’t require anything extra.

I use Oracle Database Exadata Express Cloud Service, which includes additional security enhancements, so I use the following configuration information for my connection:

export JAVA_OPTS="-Doracle.net.tns_admin=/pathToMyCreds/
EE_Credentials
-Doracle.net.ssl_server_dn_match=true
-Doracle.net.ssl_version=1.2
-Djavax.net.ssl.trustStore=/pathToMyCreds/EE_Credentials/
truststore.jks
-Djavax.net.ssl.trustStorePassword=SuperSecurePassword
-Djavax.net.ssl.keyStore=/pathToMyCreds/EE_Credentials/keystore.jks
-Djavax.net.ssl.keyStorePassword=SuperSecurePassword"

Warning: There are no “Are you sure?” prompts or separate commits when you run Liquibase. Commands that alter the database just run. Use a schema that is safe to experiment with.

Liquibase is a Java application, so you can run it from the command line as follows:

java $JAVA_OPTS -jar /opt/liquibase/liquibase.jar
--driver=oracle.jdbc.OracleDriver
--classpath="/usr/lib/oracle/12.1/client64/lib/ojdbc7.jar"
--url=jdbc:oracle:thin:lb_demo/dd@dbaccess
--changeLogFile=changelog/db.changelog-master.json
updateSQL >> output.sql

Replace the values for -jar, --classpath, and --url with the values your system requires.

Here are explanations of the parameters:

• $JAVA_OPTS is optional. You can leave it out if you don’t need it.
• -jar points to the liquibase.jar file.
• --driver identifies the type of database driver you’re using.
• --classpath points to your JDBC driver.
• --url is a standard JDBC connection string. (Replace the url value with your connection string.)
• --changeLogFile points to the master change log file you created. Note that I’m running the command from the working directory above changelog. Refer to the Liquibase Best Practices page for the recommended directory structure.
• updateSQL is the command you’re passing to Liquibase. updateSQL instructs Liquibase to generate and display the SQL that would run to make the changes, but it does not actually run the SQL in the database. The generated SQL will be returned to the command line after you run the command.
• Because just dumping the SQL to the screen can be a little messy, I prefer to pipe the output to a file, using >> output.sql.

Assuming that everything is set up correctly when you run this command, it will create a file called output.sql that you can inspect and/or run in your database manually.

Most of the Liquibase commands include an option that ends with SQL. For example, the update command will update your database and the updateSQL command will generate the same SQL and return it but not run it. If your DBA prefers to inspect any SQL before it runs in the database, use the …SQL commands and pipe the output to a file.

Listing 3 contains the output.sql file created by your Liquibase run.

Code Listing 3: output.sql

-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: changelog/db.changelog-master.json
-- Ran at: 1/4/17 4:04 PM
-- Against: LB_DEMO@jdbc:oracle:thin:lb_demo/dd@dbaccess
-- Liquibase version: 3.5.1
-- *********************************************************************

SET DEFINE OFF;

-- Create Database Lock Table
CREATE TABLE LB_DEMO.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL,
LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY
VARCHAR2(255), CONSTRAINT PK_DATABASECHANGELOGLOCK
PRIMARY KEY (ID));

-- Initialize Database Lock Table
DELETE FROM LB_DEMO.DATABASECHANGELOGLOCK;
INSERT INTO LB_DEMO.DATABASECHANGELOGLOCK (ID, LOCKED)
VALUES (1, 0);

-- Lock Database
UPDATE LB_DEMO.DATABASECHANGELOGLOCK SET LOCKED = 1,
LOCKEDBY = ‘myPc (192.168.0.111)’, LOCKGRANTED =
to_timestamp(‘2017-01-04 16:04:08.757’, ‘YYYY-MM-DD
HH24:MI:SS.FF’) WHERE ID = 1 AND LOCKED = 0;

-- Create Database Change Log Table
CREATE TABLE LB_DEMO.DATABASECHANGELOG (ID VARCHAR2(255)
NOT NULL, AUTHOR VARCHAR2(255) NOT NULL, FILENAME VARCHAR2(255)
NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED
INTEGER NOT NULL, EXECTYPE VARCHAR2(10) NOT NULL, 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));

-- Changeset changelog/db.changelog-1.0.json::Two-Table-1::BlaineCarter
-- Add table lb_groups
CREATE TABLE LB_DEMO.lb_groups (id INTEGER GENERATED BY DEFAULT
AS IDENTITY NOT NULL, name VARCHAR2(50), description VARCHAR2(200),
CONSTRAINT PK_LB_GROUPS PRIMARY KEY (id), CONSTRAINT
uk_lb_groups_name UNIQUE (name));

INSERT INTO LB_DEMO.DATABASECHANGELOG (ID, AUTHOR, FILENAME,
DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS,
EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID)
VALUES (‘Two-Table-1’, ‘BlaineCarter’,
‘changelog/db.changelog-1.0.json’, SYSTIMESTAMP, 1,
‘7:a3aa285c230661c094a6e34ae7639b74’, ‘createTable
tableName=lb_groups’, ‘Add table lb_groups’, ‘EXECUTED’,
NULL, NULL, ‘3.5.1’, ‘3571049605’);

-- Changeset changelog/db.changelog-2.0.json::Two-Table-2::BlaineCarter
-- Add table lb_people
CREATE TABLE LB_DEMO.lb_people (id INTEGER GENERATED BY DEFAULT
AS IDENTITY NOT NULL, firstname VARCHAR2(50), lastname VARCHAR2(50)
NOT NULL, group_id INTEGER, CONSTRAINT PK_LB_PEOPLE PRIMARY KEY (id),
CONSTRAINT groupFK FOREIGN KEY (group_id)
REFERENCES LB_DEMO.lb_groups(id));

INSERT INTO LB_DEMO.DATABASECHANGELOG (ID, AUTHOR, FILENAME,
DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS,
EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID)
VALUES (‘Two-Table-2’, ‘BlaineCarter’,
‘changelog/db.changelog-2.0.json’, SYSTIMESTAMP, 2,
‘7:86ab24036ce9b179375b13899471ddbd’,
‘createTable tableName=lb_people’, ‘Add table lb_people’,
‘EXECUTED’, NULL, NULL, ‘3.5.1’, ‘3571049605’);

-- Release Database Lock
UPDATE LB_DEMO.DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL,
LOCKGRANTED = NULL WHERE ID = 1;

Liquibase uses a couple of internal tables to track when changes are actively being made and what changes have already been made. The first few lines of the output.sql file set up and populate these tables. Liquibase creates these tables only the first time it runs; subsequent runs will use the existing tables to track changes.

Here are explanations of the output.sql elements:

1. Because this is the first time you’re running Liquibase, your script will create and initialize the Liquibase table databasechangeloglock. This is used to make sure only one person is running Liquibase at a time.
2. The first Liquibase run also creates the databasechangelog table. This is the table Liquibase uses to track information about the change sets that have been run.
3. Based on information in the db.changelog-1.0.json file, output.sql creates the lb_groups table.
4. The output.sql file adds a row to databasechangelog with data about the execution of the change set.
5. Based on information in the db.changelog-2.0.json file, output.sql creates the lb_people table.
6. The output.sql file adds a row to databasechangelog with data about the execution of the change set.
7. The output.sql file updates databasechangeloglock to release the lock.

At this point, you or your DBA could run the output.sql file against your database to make the changes. Instead, let’s let Liquibase make the changes.

Before Liquibase makes the changes, let’s make it a little easier to run the tool. If you add the Liquibase directory to your path, you will be able to run the included shell script or .bat file that reads a properties file that stores most of the parameters described earlier (and others if needed).

Save the following property information in the working directory above changelog as liquibase.properties:

#Liquibase.properties
driver: oracle.jdbc.OracleDriver
classpath: /usr/lib/oracle/12.1/client64/lib/ojdbc7.jar
url: jdbc:oracle:thin:lb_demo/dd@dbaccess
changeLogFile: changelog/db.changelog-master.json

Replace the values for classpath: and url: with the values your system requires.

If you’ve already set $JAVA_OPTS, you don’t need to include it here; Liquibase will include it automatically.

Now run Liquibase and have it make the changes to the database as follows, remembering that there are no “Are you sure?” prompts:

liquibase update

Connect to your database with your favorite SQL tool, and inspect the changes. You should have four new tables: databasechangeloglock, databasechangelog, lb_groups, and lb_people.

Now run the following SQL query:

select * from  databasechangelog;

and let’s take a look at the data tracked by Liquibase. In this example, ID, AUTHOR, COMMENTS, TAG, CONTEXTS, and LABELS all are set in the change sets.

The rest of the columns are populated by Liquibase. Most of them are self-explanatory, except for the following:

• MD5SUM is the checksum Liquibase uses to determine if the change set file has been altered.
• LIQUIBASE is the version of Liquibase used when the change set was run.
• DEPLOYMENT_ID is an internally generated id you can ignore.

Tags can be set in the change set. Note that the last change set to run has a myCoolTag2 tag. Typically you wouldn’t set the tag in the change set but would instead set it from the command line, like this:

liquibase tag newCoolTagCL

And when you rerun the SQL query

select * from  databasechangelog;

you can see that the tag on the last-run change set has changed to newCoolTagCL. When you set the tag with a command, it will overwrite the existing tag on the last-run change set. This is useful if you don’t know what the application release number will be when you are creating change sets. I like to set up my automated build process to autogenerate the current release number and then use the tag command to tag the last-run change set.

One of the potential questions I mentioned with my pre-Liquibase script library strategy was what if I want to roll back a change. For most changes, Liquibase can automatically generate the rollback.

There are three different command modes you can use to roll back changes:

Number of change sets. In the above example, you executed two change sets. This rollback command will roll back changes in the reverse order of execution. Running it for one count would drop the lb_people table. If you ran it a second time, it would drop the lb_groups table.

Run the command

liquibase rollbackCount 1

and look at your database to see that the lb_groups table has been dropped. In SQL, run the query

select * from  databasechangelog;

Note that the row for changelog-2.0 has been deleted. Liquibase tracks only change sets that have been applied to the database and will delete those that are rolled back.

To a date/time. This command will roll back all change sets that were applied after the given date and time. The date/time format used is “yyyy-MM-dd HH:mm:ss.”

Let’s reapply changelog-2.0 with the command

liquibase update

In SQL run the query

select dateexecuted from databasechangelog order by orderexecuted;

Look at the output, choose a date/time between the two “dateexecuted” values, and run the following using that value:

liquibase rollbackToDate "2017-01-05 18:37:00"

Look at your database to see that the lb_groups table has been dropped. In SQL run the query

select * from  databasechangelog;

Note that the row for changelog-2.0 has been deleted.

To a tag. This command will find the newest change set with a matching tag (tags are not unique) and roll back all the change sets that were run after that. If the tag doesn’t exist, Liquibase will throw an error.

Let’s reapply changelog-2.0 with the command

liquibase update 

And let’s roll back to a specific tag:

liquibase rollback myCoolTag1

Look at your database to see that the lb_groups table has been dropped. In SQL, run the query

select * from  databasechangelog; 

Note that the row for changelog-2.0 has been deleted.

Changes that cannot be automatically rolled back. Changes such as DROP TABLE and “data changes” (INSERT, UPDATE, DELETE) cannot be rolled back automatically.

I mentioned earlier that you can create change sets by using SQL instead of the other formats. The main drawback to this method is that these changes cannot be automatically rolled back. If you attempt to roll back a SQL change set and run into something that cannot be automatically rolled back, Liquibase will throw an error.

Defining a rollback. Liquibase includes a method for defining your own rollback commands for times when it cannot autogenerate them. You can also use these if you have a special case and you want more control over the rollback.

For the times when there is no automatic rollback, you need to add a rollback tag. Otherwise, Liquibase will throw an error when it hits a change that can’t be rolled back.

Let’s add a change set that inserts some data.

Create a file named db.changelog-3.0.json in the changelog directory, and add the code in Listing 4 to the file.

Code Listing 4: DB.changelog-3.0.json change set

{"databaseChangeLog": [{
            "preConditions": [{"runningAs": {"username": "lb_demo"}} ]
        },{
            "changeSet": {
                "id": "Two-Table-2",
                "author": "BlaineCarter",
                "changes": [
                  {
                    "insert": {
                      "columns": [
                        {
                          "column": {
                            "name": "firstname",
                            "value": "Blaine"
                          }
                        },
                        {
                          "column": {
                            "name": "Lastname",
                            "value": "Carter"
                          }
                        }],
                      "dbms": "oracle",
                      "schemaName": "lb_demo",
                      "tableName": "lb_people"
                    }
                  }]
              }
        }
    ]
}

Now add the change set to the db.changelog-master.json file:

,
    {
      "include": {
        "file": "changelog/db.changelog-3.0.json"
      }
    }

Run the changes with the command

liquibase update

Use a SQL query to check that the data was inserted into the lb_people table. Now attempt a rollback:

liquibase rollbackCount 2

You should get an error message:

Unexpected error running Liquibase: No inverse to
liquibase.change.core.InsertDataChange created

To fix this problem, modify changelog/db.changelog-3.0.json to include a rollback tag:

...
                      "schemaName": "lb_demo",
                      "tableName": "lb_people"
                    }
                  },
                  {"rollback": "delete from lb_people"}
                ]
              }
...

Rerun the rollback:

liquibase rollbackCount 2

Look at your database to see that the lb_groups table has been dropped. Then, in SQL, run the query

select * from databasechangelog;

Note that the rows for changelog-2.0 and changelog-3.0 have been deleted.

If you’re sure that nothing needs to be done for a rollback but you still want the ability to roll back multiple change sets, you can include an empty rollback:

                  {"rollback": ""}

At this point, you should be able to create and roll back changes in your database by using Liquibase.

Now let’s compare the database update approaches:

This article just scratches the surface of what’s possible with Liquibase, but with this information, you should be able to get started. Even if you never go beyond what’s covered here, Liquibase will save you a significant amount of effort and automatically generate some very useful data.

Liquibase helps you get your database changes under control and integrated into your build process, leading to smoother upgrades and fewer fires to put out. It also includes many features not covered in this article, and the Liquibase documentation is comprehensive and easy to navigate. The documentation includes advanced features such as

• Using contexts and labels for fine-grained control over which change sets are run under specific conditions
• Autotagging your change sets from your continuous integration process
• Branching and merging change sets
• Working with advanced preconditions
• Generating change logs from your existing database (to reverse-engineer your database)
• Finding the difference between two schemas
• Using and/or creating extensions to Liquibase
• Loading data from a .csv file

And if, by any chance, you come across a requirement that Liquibase cannot satisfy, well, it is an open source project. So you can submit a request for an enhancement or, even better, come up with an enhancement and submit a pull request on GitHub.