Changing Data Types with Liquibase

The other day I was changing some data types in our schema script and thought of sharing it as it might becomes trickier for some us. I use Liquibase for managing our database schema in groovy format, which is much easier as compared to XML, JSON and YAML formats and Liquibase just recently announced to adopt groovy format as well for writing schema scripts. So let's get started, let's consider this table schema in Liquibase script:

1:  changeSet(id: '1451109624', author: 'wakasmalik') {  
2:      createTable(tableName: 'LiquibaseExample) {  
3:        column(name: 'ID, type: 'NUMBER(19, 0)') {  
4:          constraints(nullable: false)  
5:        }  
6:        column(name: 'ABC', type: 'VARCHAR2(255 CHAR)')  
7:        column(name: 'DEF', type: 'VARCHAR2(255 CHAR)')  
8:        column(name: 'DATE_SAVED', type: 'TIMESTAMP')  
9:        column(name: 'DATE_CREATED', type: 'TIMESTAMP') {  
10:          constraints(nullable: false)  
11:        }  
12:        column(name: 'CREATED_BY', type: 'NUMBER(19, 0)') {  
13:          constraints(nullable: false)  
14:        }  
15:        column(name: 'MODIFIED_BY', type: 'NUMBER(19, 0)') {  
16:          constraints(nullable: false)  
17:        }  
18:      }  
19:    }  
20:    changeSet(id: '14511096241', author: 'wakasmalik') {  
21:      addPrimaryKey(columnNames: 'ID', constraintName: 'primaryKey', tableName: 'LiquibaseExample')  
22:    }  

Every change in Liquibase is added into a changeset. The id of the changeset has to be unique across all the changeset and most importantly it can be run only once on a database. Now let's say we want to change Data Types for these two columns:

• Column ABC from String to Numeric without any NULL constraint.
• Column Created_By from Numeric to String along with maintaining the NULL constraint.

Here is the changeset that will performs the above operations:

1:  changeSet(id: '1451273089', author: 'wakasmalik') {  
2:      modifyDataType(tableName: "LiquibaseExample", columnName: "ABC", newDataType: "NUMBER(19, 0)")  
3:      modifyDataType(tableName: "LiquibaseExample", columnName: "CREATED_BY", newDataType: "VARCHAR2(255 CHAR)")  
4:    }  

The above changeset will work fine as long as we don't have data in those two columns or we are fine losing data during conversion process and also the NULL constraint cannot be set on a empty column. These are typical scenarios for development machines and would rarely exists for a testing or production environments. So let's consider not modifying the Data Types instead drop the columns, add the columns, set column default values and then add NULL Constraint in the end.

1:  changeSet(id: '1451365535', author: 'wakasmalik') {  
2:      dropColumn(columnName: 'ABC', tableName: 'LiquibaseExample')  
3:      dropColumn(columnName: 'CREATED_BY', tableName: 'LiquibaseExample')  
4:      addColumn(tableName: 'LiquibaseExample') {  
5:        column(name: 'ABC', type: 'NUMBER(19, 0)')  
6:        column(name: 'CREATED_BY', type: 'VARCHAR2(255 CHAR)')  
7:      }  
8:      sql("UPDATE LiquibaseExample SET ABC = 11111")  
9:      sql("UPDATE LiquibaseExample SET CREATED_BY = '11111'")  
10:     addNotNullConstraint(columnName: 'CREATED_BY', tableName: 'LiquibaseExample')  
11:    }  

You can also use the "defaultValue" attribute to set the default values for a column instead of writing the sql query.
For a complete list of Liquibase supported attributes and examples in different formats, you can visit the Liquibase Documentation Page.

Leave a comment below if you like, dislike or need any help.