always-override-stored-logic-schema

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 update-sql command and rollback-sql 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 update-sql or rollback-sql 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 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 update-sql 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:

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

Liquibase properties file parameter

In Liquibase 4.1+, add the following to Liquibase properties file:

liquibase.alwaysOverrideStoredLogicSchema: <true|false>

CLI global parameter

In your command line, use a global parameter with a single Liquibase command:

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

Java system property

In your command line, use the JAVA_OPTS Environment Variable to set a JVM system property:

Mac/Linux syntax:

JAVA_OPTS=-Dliquibase.alwaysOverrideStoredLogicSchema=<true|false>

Windows syntax:

set JAVA_OPTS=-Dliquibase.alwaysOverrideStoredLogicSchema=<true|false>

Note: To use a Liquibase command alongside JAVA_OPTS, add && liquibase <command> to the end of your input.

Environment variable (Liquibase Pro)

In Liquibase Pro, set an environment variable:

Mac/Linux syntax:

LIQUIBASE_ALWAYS_OVERRIDE_STORED_LOGIC_SCHEMA=<true|false>

Windows syntax:

set LIQUIBASE_ALWAYS_OVERRIDE_STORED_LOGIC_SCHEMA=<true|false>

Note: These environment variable commands 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.