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.

XML example

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

YAML example

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

JSON example

{
  "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: List multiple databases separated by commas Specify that a changeset is not applicable to a particular database type by applying `!` Specify the keywords `all` and `none`
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.	replace – the text to replace. with – the text with which to replace.
regExpReplace	Replaces all instances of the regular expression specified.	replace – the regular expression specifying the text to replace. with – the text with which to replace.