Database refactoring with Liquibase

Database changes are painful. Every time you have to do it, there is a mess of alter table statements and sql scripts.
What I really liked about Liquibase is its maven plugin.
Consider the scenario where each colleague has a database for testing purposes on his local pc.
Once you have pushed to the repository your Liquibase configuration files It is a matter of a maven command in order for your
colleagues to be on the same page with you.

Add inside your pom add the plugin configuration

<build>
	<resources>
		<resource>
			<directory>src/main/resources</directory>
			<filtering>true</filtering>
		</resource>
	</resources>
	<plugins>
		<plugin>
			<groupId>org.liquibase</groupId>
			<artifactId>liquibase-maven-plugin</artifactId>
			<version>3.0.5</version>
			<configuration>
				<driver>{your driver}</driver>
				<url>jdbc:mysql://127.0.0.1:3306/test</url>
				<username>test</username>
				<password>test</password>
				<changeLogFile>database/refactor/db.changelog.master.xml</changeLogFile>
			</configuration>
			<executions>
				<execution>
					<phase>process-resources</phase>
					<goals>
						<goal>update</goal>
					</goals>
				</execution>
			</executions>
		</plugin>
	</plugins>
</build>

Our changelog file would be place on the resources

src/main/resources/database/refactor/db.changelog.master.xml

We can include other changelog files inside one

<?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 db.changelog-3.1.xsd">
	<include file="db.changelog-1.0.xml" relativeToChangelogFile="true" />
	<include file="db.changelog-2.0.xml" relativeToChangelogFile="true" />
	<include file="db.changelog-3.0.xml" relativeToChangelogFile="true" />
	<include file="db.changelog-4.0.xml" relativeToChangelogFile="true" />
	<include file="db.changelog-5.0.xml" relativeToChangelogFile="true" />
</databaseChangeLog>

db.changelog-1.0.xml creates a table

<?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 db.changelog-3.1.xsd">
	<changeSet id="1" author="change1">
		<createTable tableName="supervisor">
			<column name="username" type="varchar(50)" />
			<column name="password" type="varchar(50)"/>
			<column name="securePassword" type="varchar(50)"/>
		</createTable>
	</changeSet>
</databaseChangeLog>

db.changelog-2.0.xml creates another table

<?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 db.changelog-3.1.xsd">
	<changeSet id="2" author="change2">
		<createTable tableName="employee">
			<column name="username" type="varchar(50)"/>
			<column name="password" type="varchar(50)"/>
		</createTable>
	</changeSet>
</databaseChangeLog>

db.changelog-3.0.xml makes an insert

<?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 db.changelog-3.1.xsd">
	<changeSet id="3" author="change3">
		
		<insert tableName="employee">
			<column name="username" value="emmanouil"/>
			<column name="password" value="whatever"/>
		</insert>

	</changeSet>
</databaseChangeLog>

db.changelog-4.0.xml is a showcase of insert but with a precondition

<?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 db.changelog-3.1.xsd">
<changeSet id="4" author="change4">
	<preConditions>
		<sqlCheck expectedResult="0">select count(*) from supervisor</sqlCheck>
	</preConditions>
	<insert tableName="supervisor">
		<column name="username" value="james"/>
		<column name="password" value="test"/>
	</insert>
</changeSet>
</databaseChangeLog>

db.changelog-5.0.xml adds a unique constraint

<?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 db.changelog-3.1.xsd">
	<changeSet id="5" author="change5">
		<addUniqueConstraint tableName="supervisor" columnNames="username"/>
	</changeSet>
</databaseChangeLog>

In order to run just issue

mvn resources:resources liquibase:update

Liquibase has some great features. If you do iterative development and you do a lot of refactoring it is great for the collaboration among the team members.