Rails v. Liquibase: Creating Tables in Liquibase (Part 6)


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
Rails post

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
database.

$ mvn liquibase:update
...
[INFO] --- liquibase-plugin:1.9.5.0: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?

the changeSet element defines a group of database
changes to apply to the database. Each changeset is assigned a
unique 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 createTable element.

What’s going on in the createTable element?

@tableName
Self-explanatory, this just configures the name of the table.
column/@name
Here we’re just configuring the name of the column and the type
of the column.
column/@type
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)”.
column/@autoIncrement
When the database support auto-increment (for ids and other
values), you can pass in autoIncrement = true to create a table with
this feature.
column/constraints
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
a 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
rollback with "mvn liquibase:rollback -Dliquibase.rollbackCount=1".
The "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 sql
element in your changeSet.

  
    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
-Dliquibase.rollbackCount=1”.

In the next post, I’m going to draw some comparisons between Rails
and Liquibase in the area of table creation.