Substituting Properties in Changelogs

Liquibase allows a dynamic substitution of properties in your changelog. The tokens to replace in your changelog are described using the ${property-name} syntax.

Uses

By substituting values for replacement tokens in the format of ${property-name}, you can use the same changesets to reflect small environmental changes.

For example, your tablespace name in Oracle may differ from environment to environment, but you want to only write one create table changeset that can be used in all of your environments.

Using property substitution in your changelog

You can set property values in Liquibase in several ways. Liquibase assigns these values in the following order:

As an attribute passed to your Liquibase runner. See the Ant, Maven, or Servlet Listener documentation for more information on how to pass them.
As a JVM system property. See JAVA_OPTS Environment Variable for more information.
As an environment variable. See Liquibase Environment Variables for more information.
As a CLI attribute, if executed from the command line. Use the syntax liquibase <command.name> -D<property.name>=<property.value>. On Windows, surround the property name in quotation marks if it contains a dot. For example: -D"property.name"=value.
In the parameters block (property element of the changelog file itself).

Once a property has been set, it cannot be changed. Also, only the first definition is used, others are skipped.

Note: If the content of ${property-name} does not match a property, it is left as-is and it is not removed. The supported format includes alphanumeric characters, +, -, . , and _.

Note: If you do not want to replace an otherwise matching ${property-name} expression, add : to the beginning of the expression. An expression of ${: property-name} will always be converted to ${property-name} regardless of whether property-name is defined.

`sql` and `sqlFile`

You can use property substitution with the sql and sqlFile Change Types. Note that Liquibase obtains the checksum of a sql changeset after substituting any properties you specify. However, it obtains the checksum of a sqlFile changeset before substituting the properties in the external SQL file.

This affects the behavior of attributes like runOnChange. For example, suppose you set an environment variable ENV_EXAMPLE=value and you use property substitution for ENV_EXAMPLE in two changesets: sql and sqlFile. If you deploy your changes with the update command, ENV_EXAMPLE is substituted for the value you set. Suppose each changeset is also labeled with runOnChange=true, so if you run the update command, the changesets rerun only if their contents have changed since the last deployment. If you then set ENV_EXAMPLE=new_value and run update, only the sql changeset runs a second time.

Nested properties

Name	Description	Required for	Supports	Multiple allowed?
`<property>`	Defined before changesets in the changelog	Substituting portions of a changeset during runtime	All supported changeset tokens	No. Property names are unique and can only be set one.

Available attributes

Attribute	Description
`name`	The name of the parameter. Required if `file` is not set
`value`	The value of the property. Required if `file` is not set
`file`	The name of the file from which the properties should be loaded. It will create a property for all properties in the file. The content of the file must follow the Java properties file format.
`relativeToChangelogFile`	The `relativeToChangelogFile` attribute is used in conjunction with the `file` attribute to allow Liquibase to find the referenced file without having to configure search-path. The default for `relativeToChangelogFile` remains `FALSE` for backwards compatibility.
`context`	Contexts in which the property is valid. Expected as a comma-separated list.
`dbms`	Specifies which database type(s) a changeset is to be used for. See valid database type names on `dbms`. Separate multiple databases with commas. Specify that a changeset is not applicable to a particular database type by prefixing with `!`. The keywords `all` and `none` are also available.
`global`	Boolean. The attribute defines whether the property is global or limited to the actual DATABASECHANGELOG. Default: `true`.

--liquibase formatted sql

--changeset example:1
CREATE TABLE ${schema.name}.person
( id int primary key,
  first_name varchar(50) NOT NULL,
  last_name varchar(50) NOT NULL
)

Using the example changelog and running the liquibase update-sql command with the configured environment variable schema.name=dev_schema, you will receive the following SQL output: CREATE TABLE dev_schema.person.

Note: Dynamic property substitution via changelog is only supported with formatted SQL. It is not supported for raw SQL.

databaseChangeLog:
  -  property:
        dbms:  oracle,postgresql
        name:  clob.type
        value:  clob
  -  property:
        dbms:  mysql,mariadb
        name:  clob.type
        value:  longtext
  -  property:
        name:  table.name
        value:  tableA
  -  changeSet:
          id:  1
          author:  joe
          dbms: mariadb
          changes:
          -  createTable:
               tableName:  ${table.name}
               columns:
                 -  column:
                      name:  id
                      type:  int
                 -  column:
                      name:  Column1
                      type:  ${clob.type}
                 -  column:
                      name:  Column2
                      type:  int

{
  "databaseChangeLog": [
    {
      "property": {
        "_name": "clob.type",
        "_value": "clob",
        "_dbms": "oracle,postgresql"
      }
    },
    {
      "property": {
        "_name": "clob.type",
        "_value": "longtext",
        "_dbms": "mysql"
      }
    },
    {
      "property": {
        "_name": "table.name",
        "_value": "tableA"
      }
    },
    {
      "changeSet": {
        "id": "1",
        "author": "joe",
        "dbms": "mariadb",
        "changes": [
          {
            "createTable": {
              "tableName": "${table.name}",
              "columns": [
                {
                  "column": {
                    "name": "id",
                    "type": "int"
                  }
                },
                {
                  "column": {
                    "name": "Column1",
                    "type": "${clob.type}"
                  }
                },
                {
                  "column": {
                    "name": "Column2",
                    "type": "int"
                  }
                }
              ]
            }
          }
        ]
      }
    }
  ]
}

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro
        http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">
						
    <property  name="clob.type"  value="clob"  dbms="oracle,postgresql"/>
    <property  name="clob.type"  value="longtext"  dbms="mysql"/>
    <property  name="table.name"  value="tableA"/>

    <changeSet  id="1"  author="joe" dbms="mariadb">
        <createTable tableName="${table.name}">
            <column  name="id"  type="int"/>
            <column  name="Column1"  type="${clob.type}"/>
            <column  name="Column2"  type="int"/>
        </createTable>
    </changeSet>

</databaseChangeLog>