In most web application development environments I have been in, you are always dealing with constant change whether it is the application itself, software tools or the underlying database structures. One area that has gotten annoying to me is underlying database changes. In almost every development effort, there are several code streams being developed at the same time. Sometimes it is by accident and sometimes it is by design. With different code streams comes different databases and if you are like most developers, you are constantly switching code streams. The problem arises when the database you have in your development environment does not match the code stream you are working in. The easiest (and most time-consuming) fix for this problem is dropping all tables in the database and recreating the version you need. Of course this is very irritating, especially if your database is huge. It wastes so much time dropping and rebuilding databases instead of focusing on development problems. You could have a different version of the database for each code stream you have, but you also run the risk of taking up too much space with replicas of the same database.
A co-worker told me about a product called Liquibase. So I did some research on it and it looks like it could be a very handy tool in my development environments. Liquibase is essentially an SVN-type change repository for your database scripts. You can just create your sql scripts depending on what version you have and when the time comes, run the scripts and poof your database is ready to work with.
It takes the guesswork out of whether or not you have all the correct changes to your databases to run the version of software you need to make development changes to.
To get started you can download Liquibase and install it. Next, run the command here against any of your existing databases. I am using a MYSQL database as an example.

java -jar /usr/local/liquibase/liquibase.jar --driver=com.mysql.jdbc.Driver --classpath=mysql.jar --changeLogFile= changelog-base.xml --url="jdbc:mysql://localhost/testdb --username=root --password=password generateChangeLog

This command generates a list of all the current tables in your database. This way you can create your “base” DB quickly the next time.
If you drop all your tables in your current database and run the next ”migrate” command, you will see all your database tables are re-created.

java -jar /usr/local/liquibase/liquibase.jar --driver=com.mysql.jdbc.Driver --classpath=mysql.jar --changeLogFile= changelog-base.xml --url="jdbc:mysql://localhost:3306/testdb --username=root --password=password migrate

So now you have a quick and easy way to create your base DB. Running a change set against your new database uses the same command but with a different file name.

java -jar /usr/local/liquibase/liquibase.jar --driver=com.mysql.jdbc.Driver --classpath=mysql.jar --changeLogFile= changelog-development.xml --url="jdbc:mysql://localhost:3306/testdb --username=root--password=password migrate

As you can see, all of the database change files are XML-based, but there are many other formats you can use.
Here is an example of what a change file looks like in XML:

<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.0.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
    <preConditions>
        <runningAs username="root"/>
    </preConditions>
    <changeSet id="base" author="jfreeman">
        <createTable tableName="requirement">
            <column name="id" type="int" autoIncrement="true"/>
            <column name="title" type="varchar(100)"/>
            <column name="created_date" type="datetime"/>
            <column name="status" type="varchar(10)"/>
            <column name="description" type="varchar(500)"/>
        </createTable>
    </changeSet>
</databaseChangeLog>

Now if you have updates/changes to your base database, you can put those in a separate change file and run the “migrate” command.
Liquibase is simple to use and very simple to get started with. In my next blog, I will talk about how you setup Liquibase in a modern development environment. I will show how to set it up using Maven so that you don’t have to keep writing these long commands to get things done. Pretty soon you will switching back and forth with your databases as fast as you switch back and forth with your development branches!