modifySql

The modifySql Change Type allows you to change data types or add additional vendor-specific clauses such as ENGINE INNODB to CREATE TABLE statements.

Uses

Although Liquibase supports most standard SQL statements with its Change Types, there are times when you need the generated SQL to be different. This Change Type allows you to change data types or add additional vendor-specific clauses such as ENGINE INNODB to CREATE TABLE statements.

Note: The modifySql Change Type is only available in XML, YAML, and JSON changelogs.

Running the modifySql Change Type

To run this Change Type, follow these steps:

  1. Add the Change Type to your changeset, as shown in the examples on this page.

  2. Specify any required attributes. Use the table on this page to see which ones your database requires.

  3. Deploy your changeset by running the update command:liquibase update

Now, you should see a new table.

Available attributes

Attribute

Description

dbms

The type of database for which the changeset will be used. When the migration step runs, 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 a context, every changeset will be applied.

applyToRollback

Defines whether the SQL modification should be applied to rollback statements. The default value is false.

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.

Database support

Database

Notes

Auto Rollback

DB2/LUW

Supported

Yes

DB2/z

Supported

Yes

Derby

Supported

Yes

Firebird

Supported

Yes

Google BigQuery

Supported

Yes

H2

Supported

Yes

HyperSQL

Supported

Yes

INGRES

Supported

Yes

Informix

Supported

Yes

MariaDB

Supported

Yes

MySQL

Supported

Yes

Oracle

Supported

Yes

PostgreSQL

Supported

Yes

Snowflake

Supported

Yes

SQL Server

Supported

Yes

SQLite

Supported

Yes

Sybase

Supported

Yes

Sybase Anywhere

Supported

Yes

modifySql examples

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