DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Securing Your Software Supply Chain with JFrog and Azure
Register Today

Trending

  • Structured Logging
  • Microservices With Apache Camel and Quarkus (Part 2)
  • What ChatGPT Needs Is Context
  • Comparing Cloud Hosting vs. Self Hosting

Trending

  • Structured Logging
  • Microservices With Apache Camel and Quarkus (Part 2)
  • What ChatGPT Needs Is Context
  • Comparing Cloud Hosting vs. Self Hosting
  1. DZone
  2. Data Engineering
  3. Data
  4. Source control your database with Liquibase

Source control your database with Liquibase

$$anonymous$$ user avatar by
$$anonymous$$
·
Aug. 06, 13 · Interview
Like (0)
Save
Tweet
Share
12.62K Views

Join the DZone community and get the full member experience.

Join For Free

i recently started to use liquibase on a project to keep track of the database changes in our java enterprise application. i must say that i like the way it works. it makes the deployment of my application (or a new release of it) to another environment easier and (more) fool proof. in the past i had to supply a database script to the dba which had to be executed right after or before i redeployed my ear/ war file with all issiues that comes with that procedure (script fails/ dba not available, etc.). now i won’t say there couldn’t be issues with this solution but from a developer perspective it does make life easier.

here is how it works for a straight maven project with some web services that are backed by a mysql database. since i deploy the web services in a war file on jboss i chose to have the liquibase scripts being triggered by a servletcontextlistener instance. to be able to test the database script without having to deploy the application i also embedded the maven plugin for liquibase in my pom file. this way i can run the liquibase scripts manually with maven against my local development database.
first add the necessary dependencies to the maven pom.xml:

<dependency>                                           
	<groupid>org.liquibase</groupid>                      
	<artifactid>liquibase-core</artifactid>               
	<version>2.0.5</version>                              
</dependency>                                          
<dependency>                                           
    <groupid>mysql</groupid>                           
    <artifactid>mysql-connector-java</artifactid>      
    <version>5.1.15</version>                          
    <scope>provided</scope>                            
</dependency>                                          

the mysql dependency is only necessary to be able to run the liquibase scripts with maven. the application itself uses a datasource that is managed by the container in this case jboss.
to be able to run the liquibase scripts with maven i defined the plugin like this:

<plugin>                                                                       
    <groupid>org.liquibase</groupid>                                           
    <artifactid>liquibase-maven-plugin</artifactid>                            
    <version>2.0.1</version>                                                   
    <configuration>                                                            
        <changelogfile>db.changelogs/my-changelog-master.xml</changelogfile>   
        <driver>com.mysql.jdbc.driver</driver>                                 
        <url>jdbc:mysql://localhost:3306/mydb</url>                            
        <username>admin-user</username>                                              
        <password>*****</password>                                              
    </configuration>                                                           
    <executions>                                                               
        <execution>                                                            
            <goals>                                                            
                <goal>update</goal>                                            
            </goals>                                                           
        </execution>                                                           
    </executions>                                                              
</plugin>                                                                      

like i said i execute the scripts by triggering liquibase with a servlet listener. to set this up add the following elements to the ‘web.xml’ inside the war file:

...
    <context-param>
        <param-name>liquibase.changelog</param-name>
        <param-value>db.changelogs/db.changelog-master.xml</param-value>
    </context-param>
    <context-param>
        <param-name>liquibase.datasource</param-name>
        <param-value>java:jboss/datasources/liquibaseds</param-value>
    </context-param>




    <listener>
        <listener-class>liquibase.integration.servlet.liquibaseservletlistener</listener-class>
    </listener>
...

here we see that i defined two parameters:

  • liquibase.changelog
  • this parameter refers to the classpath location of the master db.changelogs file. it is important to notice that this path matches the one used in the maven plugin if you are running against the same database. see this issue which i ran into when using different paths.

  • liquibase.datasource
  • this one refers to the jndi name of the datasource to be used with the liquibase script. since this database user needs rights to create database obejcts like tables i suggest to create a separate datasource to use with the application. this datasource should then connect with a user that only has rights to access the data and not be able to create or drop objects.

the final part is the creation of the db.changelogs files. as suggested here as a best practice i refer to a ‘master’ changelog file in the configuration. in this master changelog file i refer to the individual scripts. for example i have the following ‘master’ script in my ‘resources’ folder:

<?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">
    <include file="db.changelog-1.0.xml" relativetochangelogfile="true" />
    <include file="db.changelog-2.0.xml" relativetochangelogfile="true" />
</databasechangelog>

in the ‘db.changelog-1.0.xml’ script i create some tables with xml based changeset:

<?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="pascalalma">
        <createtable tablename="test_table">
            <column name="id" type="bigint(20)" autoincrement="true">
                <constraints primarykey="true" nullable="false"/>
            </column>
            <column name="code" type="varchar(20)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="description" type="varchar(200)"/>
            <column name="value" type="varchar(200)"/>
        </createtable>
        <adduniqueconstraint columnnames="code,description"
                             constraintname="code_uk"
                             tablename="test_table"/>
        <createtable tablename="another_test_table">
            <column name="id" type="bigint(20)" autoincrement="true">
                <constraints primarykey="true" nullable="false"/>
            </column>
            <column name="code" type="varchar(20)">
                <constraints nullable="false" unique="true"/>
            </column>
            <column name="test_code" type="bigint(20)">
            </column>
        </createtable>
        <addforeignkeyconstraint basecolumnnames="test_code"
                                 basetablename="another_test_table"
                                 constraintname="test_other_fk"
                                 referencedcolumnnames="code"
                                 referencedtablename="test_table"/>
    </changeset>
</databasechangelog>

in this example script i show how to create tables with unique constrained on a combination of columns and how to add foreign keys to them.
in ‘db.changelog-2.0.xml’ i show how to load data into the created tables by using a csv file as input:

<?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="2" author="pascalalma">
    <loaddata file="db.changelogs/mydata.csv"
              schemaname="mydb"
              quotchar="'"
              tablename="test_table"/>
    </changeset>
</databasechangelog>

in the ‘mydata.csv’ i define the rows to be uploaded into the table:

code,description,value
't01','not specified','foo'
't02','wrongly specified','bar'
't03','correct','foo-bar'

the first line of the csv file states the columns in the order the fields are defined in the other rows. this is a very convenient way to load initial data into your database tables.
when all this is in place you can run liquibase from maven. build the project first with ‘mvn clean install’ and then give a ‘mvn liquibase:update’:
screen shot 2013-06-25 at 08.37.55
now if we look at the result in the database scheme we see the following tables:
screen shot 2013-06-25 at 08.39.11
we see our tables with the data are being created. we also see two more tables: ‘databasechangelog’ and ‘databasechangeloglock’. these are used by liquibase to determine the state of the database. if i deploy the war file with a datasource against this database liquibase will see that the scripts already have run and won’t execute them again. if i remove the tables however the scripts will be executed as it was against a new database. as you can see this can simplify the deployment of new releases of your software, at least the database part.

Database Liquibase source control Apache Maven application Datasource Data (computing) WAR (file format) Best practice

Published at DZone with permission of $$anonymous$$. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • Structured Logging
  • Microservices With Apache Camel and Quarkus (Part 2)
  • What ChatGPT Needs Is Context
  • Comparing Cloud Hosting vs. Self Hosting

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: