Modifying generated SQL
Although Liquibase supports most standard SQL statements with its Change Types, there are times when you need the generated SQL to be different. The following examples show how to change data types or add additional vendor-specific clauses such as ENGINE INNODB
to CREATE TABLE
statements. Since 1.9
Note: Currently, the modifySql
attribute is only available in XML, YAML, and JSON formatted changelogs.

<changeSet id="1" author="nvoxland">
<createTable tableName="person">
<column name="id" type="bigint"/>
<column name="firstname" type="varchar(255)"/>
<column name="lastname" type="varchar(255)"/>
</createTable>
<modifySql>
<replace replace="bigint" with="long"/>
</modifySql>
<modifySql dbms="mysql">
<append value="engine innodb"/>
</modifySql>
</changeSet>

databaseChangeLog:
- changeSet:
id: 1
author: nvoxland
changes:
- createTable:
tableName: person
columns:
- column:
name: id
type: bigint
autoIncrement: true
constraints:
primaryKey: true
nullable: false
- column:
name: firstname
type: varchar(50)
- column:
name: lastname
type: varchar(50)
constraints:
nullable: false
- column:
name: state
type: char(2)
- modifySql:
replace:
replace: bigint
with: long
- modifySql:
dbms: mysql
append:
value: engine innodb

{
"databaseChangeLog": [
{
"changeSet": {
"id": "1",
"author": "nvoxland",
"changes": [
{
"createTable": {
"tableName": "person",
"columns": [
{
"column": {
"name": "id",
"type": "bigint",
"autoIncrement": true,
"constraints": {
"primaryKey": true,
"nullable": false
},
}
},
{
"column": {
"name": "firstname",
"type": "varchar(50)"
}
},
{
"column": {
"name": "lastname",
"type": "varchar(50)",
"constraints": {
"nullable": false
},
}
},
{
"column": {
"name": "state",
"type": "char(2)"
}
},
{
"modifySql": {
"replace": {
"replace": "bigint",
"with":long
}
}
},
{
"modifySql": {
"dbms": "mysql",
"append": {
"value": "engine innodb"
}
}
}
]
}
}
]
}
},
]
}
Available attributes
Attribute | Description |
---|---|
dbms |
The type of a database for which that changeset will be used. When the migration step is running, it checks the database type against this
attribute. To see all database type names, refer to the supported databases page. You can also do the following:
|
context | Specifies the list of Contexts in which to run the SQL modification. If you do not specify any context, every changeset will be applied. Since 2.0 |
applyToRollback | Defines whether the SQL modification should be applied to rollback statements. The default value is false . Since 2.0 |
Available subtags
Subtag | Description | Attribute |
---|---|---|
prepend | Adds SQL to the beginning of the statement. | value – the text to add to the beginning of the statement. |
append | Adds SQL to the end of the statement. | value – the text to add to the end of the statement. |
replace | Replaces all instances of the text specified. |
|
regExpReplace | Replaces all instances of the regular expression specified. |
|