Creating a table with Liquibase is straightforward. Unlike Rails, there’s no choice that has to be made about whether your database migration (or “change set” in Liquibase) is connected to a model object. It isn’t. Liquibase isn’t concerned with your application, as it only does one thing: it manages database changes. When compared to Rails it also makes fewer assumptions (if any) about table names and relationships. In this post, I’m going to introduce some very basic change sets to create tables. One change set will use the Liquibase
createTable element and the other will demonstrate how to do the same thing with raw SQL.
Compare the Liquibase change sets with the Rails migrations from yesterday’s post covering the same topic from a Rails Migrations perspective.
Creating a New Table with Liquibase
Note: If you want to follow along with this post and experiment
with Liquibase you will find instructions for setting up a new
Liquibase project in this post.
If you created the project from this post, you have an
empty Maven project with an empty Liquibase changelog and you’ve
already run “mvn liquibase:update” to create the necessary support
tables in your database. Since a datbase without tables isn’t very
useful, let’s see what it takes to create the tables from yesterday’s
In liquibase, you create a new table by editing your changelog.
In this project, your changelog lives in src/main/db in a file named
db-changelog.xml. Let’s create the politician table by putting the
following XML in your db-changelog.xml.
Creating the Politician Table
To execute this changeset, execute “mvn liquibase:update” from the
root directory of your project. You should see the following output
as Liquibase connects to your database, reads the DATABASECHANGELOG
table and figures out which updates need to be executed against your
$ mvn liquibase:update ... [INFO] --- liquibase-plugin:220.127.116.11:update (default-cli) @ liquibase-test --- [INFO] ------------------------------------------------------------------------ [INFO] Parsing Liquibase Properties File [INFO] File: src/main/db/liquibase.properties [INFO] ------------------------------------------------------------------------ [INFO] Executing on Database: jdbc:mysql://localhost/liquitest_development Nov 26, 2010 8:51:57 AM liquibase.database.template.JdbcTemplate comment INFO: Lock Database Nov 26, 2010 8:51:57 AM liquibase.lock.LockHandler acquireLock INFO: Successfully acquired change log lock Nov 26, 2010 8:51:57 AM liquibase.database.AbstractDatabase getRanChangeSetList INFO: Reading from `DATABASECHANGELOG` Nov 26, 2010 8:51:57 AM liquibase.database.template.JdbcTemplate comment INFO: Changeset src/main/db/db-changelog.xml::create_politician::tobrien\ ::(MD5Sum: a1416efaa95f2f91538ce37aae56b45) Nov 26, 2010 8:51:57 AM liquibase.database.template.JdbcTemplate comment INFO: Creating the Politician Table Nov 26, 2010 8:51:58 AM liquibase.database.template.JdbcTemplate comment INFO: Release Database Lock Nov 26, 2010 8:51:58 AM liquibase.lock.LockHandler releaseLock INFO: Successfully released change log lock Nov 26, 2010 8:51:58 AM liquibase.database.template.JdbcTemplate comment INFO: Release Database Lock Nov 26, 2010 8:51:58 AM liquibase.lock.LockHandler releaseLock INFO: Successfully released change log lock
If you look at your database, you’ll see that there is a new table
“politicians” with the fields: if, first_name, last_name, party,
created_at, and updated at.
What is in a changeset?
changeSet element defines a group of database
changes to apply to the database. Each changeset is assigned a
id attribute and an
author attribute. In addition to
these attributes, it is often a good idea to write a simple
comment in the
comment element. After the identifying
information, you’ll see the element that tells Liquibase to create
a new table – the
What’s going on in the createTable element?
- Self-explanatory, this just configures the name of the table.
- Here we’re just configuring the name of the column and the type
of the column.
- When you are specifying database column type,
Liquibase does offer a series of abstracted types which are
translated to specific database implementations: BOOLEAN, CURRENCY,
UUID, CLOB, BLOB, DATE, DATETIME, TIME, BIGINT. In this example,
we’re just specifying types “int” and “varchar(255)”.
- When the database support auto-increment (for ids and other
values), you can pass in autoIncrement = true to create a table with
- The constraints element gives you the opportunity to put
constraints on columns. Is the column a primary key, is there a
unique constraint on a column, is there a foreign key? In this
example, we use constraints to make id a non-nullable PK.
You will also notice that the changeset specified
rollback element. The
rollback element is
important if you ever need to rollback change to the database. If you
want to rollback the change we just made to politician, you can
"mvn liquibase:rollback -Dliquibase.rollbackCount=1".
"liquibase.rollbackCount" property tells Liquibase to rollback a
single database change.
Creating a table with Raw SQL
If you need to create database tables that make use of specific
database features, you will likely want to use raw SQL to create these
tables. If you are creating tables with partitions in MySQL, or if
you are passing table specific query cache configuration, the only
reliable way to do this in a tool like Liquibase is to use the
element in your
Creating the Election Table CREATE TABLE election ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, election_date DATE, name VARCHAR(128) ) TYPE=innodb; DROP TABLE election;
To execute this migration run “mvn liquibase:update” and to
rollback after running it run “mvn liquibase:rollback
In the next post, I’m going to draw some comparisons between Rails
and Liquibase in the area of table creation.