Liquibase

How to integrate Liquibase with Oracle? How to configure Oracle to log / track all DDL statements (users must provide comments for the DDL)? How to configure Oracle so that users must specify a comment for each DDL?

--driver=oracle.jdbc.OracleDriver
--classpath=\path\to\classes:jdbcdriver.jar
--changeLogFile=com/example/db.changelog.xml
--url="jdbc:oracle:thin:@localhost:1521:oracle"
--password=tiger


What is the basic steps to get started?

2. Create new changelog file in XML, YAML, JSON or SQLformat
3. Add changeset to changelog file
4. Run liquibase update
5. Commit changelog file to source control
6. Repeat

How to Create a Changelog File?

The database changelog file is where all database changes are listed. It is XML based, so start with an empty XML 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-2.0.xsd">
</databaseChangeLog>


How to Add a ChangeSet?

Each change set is uniquely identified by an “id” attribute and an “author” attribute. These two tags, along with the name and package of the changelog file uniquely identify the change. If only an “id” needed to be specified, it would be too easy to accidentally duplicate them, especially when dealing with multiple developers and code branches. Including an “author” attribute minimizes the chances of duplications.

Think of each change set as an atomic change that you want to apply to your database. It’s usually best to include just one change in your change set, but more are allowed and can make sense if you are inserting multiple rows that should be added as a single transaction. Liquibase will attempt to run each change set as a single transaction, but many databases will silently commit and resume transactions for certain commands (create table, drop table, etc.)

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


How to Run the ChangeSet?

There are many ways to execute your change log including via command line, Ant, Maven, Spring, a servlet listener, and a CDI Environment.

Here is an example for mysql via jdbc:

liquibase --driver=com.mysql.jdbc.Driver \
--classpath=/path/to/classes \
--changeLogFile=com/example/db.changelog.xml \
--url="jdbc:mysql://localhost/example" \
migrate


There are many more databases supported by liquibase. For a list of them and which jdbc driver, url, classpath etc. they need, please visit the databases section.

How to generate the changeset SQL script without executing the changeset?

java -jar liquibase.jar updateSQL > /tmp/script.sql


How to run Liquibase using defaults from ./liquibase.properties?

java -jar liquibase.jar update


How to run the standard migrator?

java -jar liquibase.jar  update


How to run migrator pulling changelogs from a .WAR file?

java -jar liquibase.jar --classpath=website.war update


How to run migrator pulling changelogs from an .EAR file?

java -jar liquibase.jar --classpath=application.ear


How to list locks on the database change log?

java -jar liquibase.jar listLocks


How to export data from a target database?

java -jar liquibase.jar --changeLogFile="./data/filename" --diffTypes="data" generateChangeLog


This will export the data from the targeted database and put it in a folder “data” in a file name specified

How to update passing changelog parameters?

liquibase.bat update -Dengine=myisam


What is the purpose of the liquibase.properties file?

Liquibase can read paramaters for its execution from standard Java Property files.

What does a sample liquibase.properties look like?

driver: oracle.jdbc.OracleDriver
classpath: jdbcdriver.jar
url: jdbc:oracle:thin:@localhost:1521:oracle
password: tiger


Note that paths are relative to the current directory.

Why does each change set need to be uniquely identified by an "id" attribute and an "author" attribute?

Each change set is uniquely identified by an “id” attribute and an “author” attribute. These two tags, along with the name and package of the changelog file uniquely identify the change. If only an “id” needed to be specified, it would be too easy to accidentally duplicate them, especially when dealing with multiple developers and code branches. Including an “author” attribute minimizes the chances of duplications.

Should we include multiple statements in a change set?

Think of each change set as an atomic change that you want to apply to your database. It’s usually best to include just one change in your change set, but more are allowed and can make sense if you are inserting multiple rows that should be added as a single transaction. Liquibase will attempt to run each change set as a single transaction, but many databases will silently commit and resume transactions for certain commands (create table, drop table, etc.)

How to keep your changelog file clean and unwieldy?

As projects grow, the number of changeSets in a changeLog can grow unwieldy. To help ease this problem, and to make management of changes easier, databaseChangeLogs can be included to create a tree of change-logs. The include tag allows you to break up your change-logs into more manageable pieces. To easily include multiple files, use the includeAll tag.

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">
<include file="com/example/news/news.changelog.xml"/>
<include file="com/example/directory/directory.changelog.xml"/>
</databaseChangeLog>


We can use directory structure, organize it by release, and then include those directories or files into the main changelog file.

In the example above, the root change log includes first the changes in com/example/news/news.changelog.xml then the changes in com/example/directory/directory.changelog.xml.

Included change-logs are run in the order they are found so care does need to be taken to make sure that the included changelogs are either completely independent, or that any required changelogs are run first.

Any preconditions defined at the changelog level in sub changelog files will be evaluated before any changesets are ran.

The reason to use the <include> tag rather than using XML’s built-in include functionality is that with the built-in functionality the parser sees just one big XML document. We uniquely identify each change with the id, the author, and the file name so you only have to ensure that the id/author combinations are unique within each file, not across all change logs.

Currently there is no checking for looping changelogs or double inclusion of changelogs.

If you include a changelog twice, you shouldn’t run into problems because the second time around, Liquibase will know that the changeSets have been run and won’t run them again (unless there is a runAlways tag). Do not rely on this functionality remaining constant.

If you create a changeLog loop (root.changelog.xml includes news.changelog.xml which includes root.changelog.xml) you will get an infinite loop. Checks for loops is a feature on our list of enhancements, but is currently not implemented

How to generate the changelog file from an existing database?

When starting to use Liquibase on an existing database, it is often useful, particularly for testing, to have a way to generate the change log to create the current database schema. Liquibase allows you to do this with the “generateChangeLog” command_line command.

Note that this command currently has some limitations. It does not export Stored procedures, functions, packages, and Triggers

liquibase
--driver=oracle.jdbc.OracleDriver
--classpath=\path\to\classes:jdbcdriver.jar
--changeLogFile=com/example/db.changelog.xml
--url="jdbc:oracle:thin:@localhost:1521:XE"
generateChangeLog