always-override-stored-logic-schema parameter

The always-override-stored-logic-schema global parameter is a Boolean value that you can use with the createProcedure Change Type.

When you generate SQL for a procedure without specifying a schema name, always-override-stored-logic-schema decides whether to explicitly include the default schema name with your procedure in the SQL.

The default value of always-override-stored-logic-schema is false.

schemaName attribute

You can use the createProcedure Change Type to define a stored SQL procedure in your changelog or a file you specify. createProcedure has an optional attribute called schemaName that determines which schema the procedure is created in.

When you create a procedure in Liquibase, the outputs of the updateSQL command and rollbackSQL <tag> command change based on what you specify for schemaName and always-override-stored-logic-schema:

  • If you do specify schemaName, Liquibase explicitly prefixes your schema name to your procedure name in the CREATE PROCEDURE statement of the SQL. Liquibase does this even if the schema name was not specified in the actual SQL, and no matter what you set always-override-stored-logic-schema to:

    CREATE <your_schema>.<your_procedure> AS…
  • If you don't specify schemaName and you set always-override-stored-logic-schema to true, Liquibase prefixes the value of the default-schema-name parameter to your procedure name:

    CREATE <default_schema>.<your_procedure> AS…
  • If you don't specify schemaName and you set always-override-stored-logic-schema to false, Liquibase does not explicitly modify the SQL to include the schema name. However, Liquibase still creates your procedure in schema specified by default-schema-name:

    CREATE <your_procedure> AS…

Note: You can set a value for default-schema-name or use the default one, which depends on your database. For example, the default value on an H2 database is PUBLIC.

Uses

If you run the SQL generated with updateSQL or rollbackSQL on another database or client, the new system may have a different setting of default-schema-name than the old one. In this case, you can set always-override-stored-logic-schema to true to ensure that objects are consistently created in the same schema names across multiple databases.

The always-override-stored-logic-schema parameter also affects the SQL Liquibase uses with the update command and rollback <tag> command. However, when you use these two commands, Liquibase always creates objects in the same schemas, even if the default schemas are different across the two systems, and regardless of the value of always-override-stored-logic-schema.

Note: Some databases store SQL in an alias instead of a procedure, such as H2. In these cases, Liquibase may not explicitly add the schema prefix to the alias in the updateSQL output. However, Liquibase still creates the alias in the appropriate schema.

Setting the always-override-stored-logic-schema parameter

You can set always-override-stored-logic-schema in four ways:

  • As a JVM system property
  • In your liquibase.properties file
  • As a global parameter in the CLI
  • As an environment variable (Liquibase Pro)

Java system property

You can set always-override-stored-logic-schema as a Java system property by using the JAVA_OPTS Environment Variable in your command line. The syntax on Mac/Linux is as follows:

JAVA_OPTS=-Dliquibase.alwaysOverrideStoredLogicSchema=<true|false> && liquibase update-sql --changelog-file=dbchangelog.xml

The syntax on Windows requires the set command:

set JAVA_OPTS=-Dliquibase.alwaysOverrideStoredLogicSchema=<true|false> && liquibase update-sql --changelog-file=dbchangelog.xml

liquibase.properties parameter

In Liquibase 4.1+, you can set always-override-stored-logic-schema by adding the following to your liquibase.properties file:

liquibase.alwaysOverrideStoredLogicSchema: <true|false>

CLI global parameter

You can use always-override-stored-logic-schema as a global parameter in your command line with a single Liquibase command, such as updateSQL:

liquibase --always-override-stored-logic-schema=<true|false> update-sql --changelog-file=dbchangelog.xml

Environment variable (Liquibase Pro)

If you use Liquibase Pro, you can set always-override-stored-logic-schema as an environment variable. The syntax on Mac/Linux is as follows:

LIQUIBASE_ALWAYS_OVERRIDE_STORED_LOGIC_SCHEMA=<true|false>

The syntax on Windows requires the set command:

set LIQUIBASE_ALWAYS_OVERRIDE_STORED_LOGIC_SCHEMA=<true|false>

Note: The commands shown above only apply to the current shell. If you need to pass an environment variable to a child process without affecting the parent process, you can use the export command on Mac/Linux or the setx command on Windows.