Consult https://github.com/bigpuritz/javaforge-blog/tree/master/liquibase-sample for the sample project sources.
Quote:
Liquibase is an open source (Apache 2.0 Licensed), database-independent library for tracking, managing and applying database changes.
It is built on a simple premise: All database changes are stored in a human readable yet trackable form and checked into source control.
This post is a simple tutorial demonstrating how to use Liquibase in a real world project. We’ll assume that our sample project lives through multiple phases, each of which adds diverse changes to the database.
Let’s prepare our sample project to use Liquibase within Maven build first. We need to define the liquibase-maven-plugin within the <plugins>…</plugins> block and point it to the liquibase.properties file, containing all properties required by the Liquibase at runtime. Both are demonstrated below.
pom.xml
<build> <plugins> ... <plugin> <groupId>org.liquibase</groupId> <artifactId>liquibase-maven-plugin</artifactId> <version>2.0.5</version> <configuration> <promptOnNonLocalDatabase>false</promptOnNonLocalDatabase> <propertyFile>db/liquibase.properties</propertyFile> </configuration> </plugin> ... </plugins> </build>
liquibase.properties
promptOnNonLocalDatabase=false changeLogFile=db/ddl/db.changelog-master.xml driver=org.h2.Driver url=jdbc:h2:liquibase-sample username=sa password=sa
Since we are using H2 database for demonstation purpose, we have to add it as a dependency to the pom.xml too:
<dependencies> ... <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <version>1.3.168</version> <scope>test</scope> </dependency> ... </dependencies>
As you may note, liquibase.properties file references the db.changelog-master.xml file. This file is an entry point for Liquibase and contains all our database changes.
In our sample project it has include definitions, referencing to the changes affecting the major versions only (1.x, 2.x etc):
<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"> <preConditions> <dbms type="h2" /> </preConditions> <include file="1.x/db.changelog-1.x.xml" relativeToChangelogFile="true" /> <!-- <include file="2.x/db.changelog-2.x.xml" relativeToChangelogFile="true" /> <include file="3.x/db.changelog-3.x.xml" relativeToChangelogFile="true" /> .... --> </databaseChangeLog>
The folder structure we will create for our change management through this tutorial is demonstrated in the screenshot below:
Now let’s start with the database management for the version 1.x.
Step 1.
Assume we have started with the completely new project and our first database change will have the version 1.0.
In the first version we’ll apply following changes to the database:
change set 1.0_2
change set 1.0_3
change set 1.0_4
First we have to add an appropriate include to the db.changelog-1.x.xml file:
<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"> <include file="1.0/db.changelog-1.0.xml" relativeToChangelogFile="true" /> </databaseChangeLog>
And to write the db.changelog-1.0.xml itself:
<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.0_1" author="mk"> <createTable tableName="t_user"> <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> <changeSet id="1.0_2" author="mk"> <renameColumn tableName="t_user" oldColumnName="name" newColumnName="username" /> <createTable tableName="t_role"> <column name="name" type="varchar2(50)"> <constraints primaryKey="true" nullable="false" /> </column> <column name="description" type="varchar2(250)" /> </createTable> <addColumn tableName="t_user"> <column name="role" type="varchar2(50)"> <constraints nullable="false" /> </column> </addColumn> <addForeignKeyConstraint constraintName="fk_user_role" baseTableName="t_user" baseColumnNames="role" referencedTableName="t_role" referencedColumnNames="name" /> </changeSet> <changeSet id="1.0_3" author="xyz"> <createIndex tableName="t_user" indexName="indx_t_user_0003"> <column name="username" /> </createIndex> <createView viewName="v_user"> <![CDATA[ select u.id, u.username, r.name from t_user u join t_role r on u.role = r.name ]]> </createView> </changeSet> <changeSet id="1.0_4" author="foobar"> <tagDatabase tag="1.0" /> </changeSet> </databaseChangeLog>
Now we are ready to apply our changes against the database. To do this, just execute the following maven command:
mvn liquibase:update
After execution you’ll see, that following database objects were created by the Liquibase:
As you may note, Liquibase additionaly created two special tables DATABASECHANGELOG and DATABASECHANGELOGLOCK containing metadata required by the Liquibase at runtime.
Below is a contents of the DATABASECHANGELOG table after executing our first database changelog:
Note! If you don’t want to apply the changes directly to the database, but to generate a sql sciprt instead, run the following maven command:
mvn liquibase:updateSQLThis will generate a migration sql script migrate.sql into the target/liquibase folder. For our first database changelog this script looks like this:
-- ********************************************************************* -- Update Database Script -- ********************************************************************* -- Change Log: db/ddl/db.changelog-master.xml -- Ran at: 06/11/12 09:23 -- Against: SA@jdbc:h2:liquibase-sample -- Liquibase version: 2.0.5 -- ********************************************************************* -- Create Database Lock Table CREATE TABLE DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID)); INSERT INTO DATABASECHANGELOGLOCK (ID, LOCKED) VALUES (1, FALSE); -- Lock Database -- Create Database Change Log Table CREATE TABLE DATABASECHANGELOG (ID VARCHAR(63) NOT NULL, AUTHOR VARCHAR(63) NOT NULL, FILENAME VARCHAR(200) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INT NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONSTRAINT PK_DATABASECHANGELOG PRIMARY KEY (ID, AUTHOR, FILENAME)); -- Changeset db/ddl/1.x/1.0/db.changelog-1.0.xml::1.0_1::mk::(Checksum: 3:f5d02804c70da7f8eda3e5eda3c987b3) CREATE TABLE t_user (id INT NOT NULL, name VARCHAR(50) NOT NULL, active BOOLEAN DEFAULT TRUE, CONSTRAINT PK_T_USER PRIMARY KEY (id)); INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mk', '', NOW(), 'Create Table', 'EXECUTED', 'db/ddl/1.x/1.0/db.changelog-1.0.xml', '1.0_1', '2.0.5', '3:f5d02804c70da7f8eda3e5eda3c987b3', 1); -- Changeset db/ddl/1.x/1.0/db.changelog-1.0.xml::1.0_2::mk::(Checksum: 3:55092b96f60bb8379f251b553c7ddc1e) ALTER TABLE t_user ALTER COLUMN name RENAME TO username; CREATE TABLE t_role (name varchar2(50) NOT NULL, description varchar2(250), CONSTRAINT PK_T_ROLE PRIMARY KEY (name)); ALTER TABLE t_user ADD role varchar2(50) NOT NULL; ALTER TABLE t_user ADD CONSTRAINT fk_user_role FOREIGN KEY (role) REFERENCES t_role (name); INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('mk', '', NOW(), 'Rename Column, Create Table, Add Column, Add Foreign Key Constraint', 'EXECUTED', 'db/ddl/1.x/1.0/db.changelog-1.0.xml', '1.0_2', '2.0.5', '3:55092b96f60bb8379f251b553c7ddc1e', 2); -- Changeset db/ddl/1.x/1.0/db.changelog-1.0.xml::1.0_3::xyz::(Checksum: 3:b97854eff530d4d96b3fe096b308e239) CREATE INDEX indx_t_user_0003 ON t_user(username); CREATE VIEW v_user AS select u.id, u.username, r.name from t_user u join t_role r on u.role = r.name; INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED) VALUES ('xyz', '', NOW(), 'Create Index, Create View', 'EXECUTED', 'db/ddl/1.x/1.0/db.changelog-1.0.xml', '1.0_3', '2.0.5', '3:b97854eff530d4d96b3fe096b308e239', 3); -- Changeset db/ddl/1.x/1.0/db.changelog-1.0.xml::1.0_4::foobar::(Checksum: 3:2436467c358c5141a858c2705374d989) UPDATE DATABASECHANGELOG SET TAG = '1.0' WHERE DATEEXECUTED = (SELECT MAX(DATEEXECUTED) FROM DATABASECHANGELOG); INSERT INTO DATABASECHANGELOG (AUTHOR, COMMENTS, DATEEXECUTED, DESCRIPTION, EXECTYPE, FILENAME, ID, LIQUIBASE, MD5SUM, ORDEREXECUTED, TAG) VALUES ('foobar', '', NOW(), 'Tag Database', 'EXECUTED', 'db/ddl/1.x/1.0/db.changelog-1.0.xml', '1.0_4', '2.0.5', '3:2436467c358c5141a858c2705374d989', 4, '1.0');
And one more remark:
To rollback the applyed changes, simply run:
mvn liquibase:rollback
Step 2.
Now assume we are done with the version 1.0 and starting to develop a new version 1.1. This contains some database changes too, so we have to create a new changelog file and reference it within db.changelog-1.x.xml:
<include file="1.1/db.changelog-1.1.xml" relativeToChangelogFile="true" />
Version 1.1 specifies only one changeset 1.1_1 containing following changes:
<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.1_1" author="mk"> <createTable tableName="t_address"> <column name="id" type="int" autoIncrement="true"> <constraints primaryKey="true" nullable="false" /> </column> <column name="street" type="varchar(50)"> <constraints nullable="true" /> </column> <column name="house_no" type="varchar(10)"> <constraints nullable="true" /> </column> <column name="zipcode" type="varchar(50)"> <constraints nullable="true" /> </column> <column name="city" type="varchar(50)"> <constraints nullable="true" /> </column> </createTable> </changeSet> <changeSet id="1.1_2" author="foobar"> <tagDatabase tag="1.1" /> </changeSet> </databaseChangeLog>
Once again to apply the changes, just run mvn liquibase:update. This time Liquibase recognizes, that the changes from db.changelog-1.0.xml were already applyed to the databse (remember the DATABASECHANGELOG table!) and applies only outstanding changes from db.changelog-1.1.xml.
Step 3.
Analog to the step 2 it specifies new changes for the next development version 1.2:
<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.2_1" author="xyz"> <createIndex tableName="t_address" indexName="indx_t_address_001"> <column name="street" /> </createIndex> <createIndex tableName="t_address" indexName="indx_t_address_002" unique="true"> <column name="street" /> <column name="house_no" /> <column name="city" /> </createIndex> <createIndex tableName="t_address" indexName="indx_t_address_003"> <column name="city" /> </createIndex> </changeSet> <changeSet id="1.2_2" author="foobar"> <tagDatabase tag="1.2" /> </changeSet> </databaseChangeLog>
To execute it, run again mvn liquibase:update (or mvn liquibase:updateSQL).
Further procedure is straightforward and similar to the previous steps.
Read the Liquibase Manual or consult some training videos for further details and advanced features.