execute-sql

The execute-sql command executes a SQL string or file.

Uses

Use the execute-sql command to directly run SQL queries without changing and applying changelog files with changesets. An example of using the execute-sql command is to check the database data or make unplanned changes when you run Liquibase in automation.

Compared with other database query tools, the execute-sql command runs with the credentials and configuration you already have in Liquibase without any need to set up another tool. If statements run correctly in your database CLI but fail in Liquibase, running exploratory statements through execute-sql might help you find differences in permission configurations, connection setup, and SQL run in the database CLI or database-native SQL.

Note: The command does not affect the DATABASECHANGELOG table because the table is associated with changesets and tracks which changesets have been run. Liquibase recommends handling your changes with execute-sql only on rare occasions to not lose their traceability.

Syntax

To run the execute-sql command, specify the following parameters in the Liquibase properties file, environment variables, or the command prompt while running the command:

  • URL, driver [optional], and user authentication information such as username and password.
  • sql or sql-file parameter.

Tip: For more information about the options to store parameters, see Create and Configure a liquibase.properties File, Liquibase Environment Variables, and JAVA_OPTS Environment Variable. To find the format of the parameter, see Liquibase Parameters.

Run the execute-sql command with the SQL string:

liquibase execute-sql --sql="SELECT * FROM databasechangelog"

Run the execute-sql command with the SQL file:

liquibase execute-sql --sql-file=path/to/file.sql

Note: You can also specify --delimiter=/. The default value for delimiter is ;, which will work as long as you do not have stored logic.

Parameters

Global parameters

Attribute Definition Requirement

--output-file=<string>

File path to where the command output will be written: a CSV file that is the result of running a SELECT query. If not specified, output goes to STDOUT. See --output-file.

 Optional

Command parameters

Attribute Definition Requirement

--sql=<string>

The SQL string to execute

Note: Columns display in the same order they are specified in the argument. If no columns are specified (For example select * <table_name>), then the output will display columns in the same order they are in the database.

 Required

--sql-file=<string>

Specifies the file where SQL statements are stored

 Required

--url=<string>

The JDBC database connection URL. See Using JDBC URL in Liquibase.

 Required

--default-catalog-name=<string>

Name of the default catalog to use for the database connection

 Optional

--default-schema-name=<string>

Name of the default schema to use for the database connection. If defaultSchemaName is set, then objects do not have to be fully qualified. This means you can refer to just mytable instead of myschema.mytable.

Tip: In Liquibase v4.23.0+, camelCase for defaultSchemaName works successfully. If you are on an earlier version, camelCase may not work as expected.

Note: The syntax liquibase.command.defaultSchemaName is valid for v4.19.0+. For prior versions, use defaultSchemaName.

 Optional

--delimiter=<string>

The delimiter to use when executing SQL scripts

 Optional

--driver=<string>

The JDBC driver class

 Optional

--driver-properties-file=<string>

The JDBC driver properties file

 Optional

--password=<string>

Password to connect to the target database.

Tip: It is a best practice to store sensitive data in a Secrets Management tool with Liquibase Pro.

 Optional

--username=<string>

Username to connect to the target database.

Tip: It is a best practice to store sensitive data in a Secrets Management tool with Liquibase Pro.

 Optional

Global parameters

Attribute Definition Requirement

cmdArgs: { output-file: "<string>" }

File path to where the command output will be written: a CSV file that is the result of running a SELECT query. If not specified, output goes to STDOUT. See --output-file.

 Optional

Command parameters

Attribute Definition Requirement

cmdArgs: { sql: "<string>" }

The SQL string to execute

Note: Columns display in the same order they are specified in the argument. If no columns are specified (For example select * <table_name>), then the output will display columns in the same order they are in the database.

 Required

cmdArgs: { sql-file: "<string>" }

Specifies the file where SQL statements are stored

 Required

cmdArgs: { url: "<string>" }

The JDBC database connection URL. See Using JDBC URL in Liquibase.

 Required

cmdArgs: { default-catalog-name: "<string>" }

Name of the default catalog to use for the database connection

 Optional

cmdArgs: { default-schema-name: "<string>" }

Name of the default schema to use for the database connection. If defaultSchemaName is set, then objects do not have to be fully qualified. This means you can refer to just mytable instead of myschema.mytable.

Tip: In Liquibase v4.23.0+, camelCase for defaultSchemaName works successfully. If you are on an earlier version, camelCase may not work as expected.

Note: The syntax liquibase.command.defaultSchemaName is valid for v4.19.0+. For prior versions, use defaultSchemaName.

 Optional

cmdArgs: { delimiter: "<string>" }

The delimiter to use when executing SQL scripts

 Optional

cmdArgs: { driver: "<string>" }

The JDBC driver class

 Optional

cmdArgs: { driver-properties-file: "<string>" }

The JDBC driver properties file

 Optional

cmdArgs: { password: "<string>" }

Password to connect to the target database.

Tip: It is a best practice to store sensitive data in a Secrets Management tool with Liquibase Pro.

 Optional

cmdArgs: { username: "<string>" }

Username to connect to the target database.

Tip: It is a best practice to store sensitive data in a Secrets Management tool with Liquibase Pro.

 Optional

Global parameters

Attribute Definition Requirement

liquibase.command.outputFile: <string>

liquibase.command.<cmdName>.outputFile: <string>

File path to where the command output will be written: a CSV file that is the result of running a SELECT query. If not specified, output goes to STDOUT. See --output-file.

 Optional

Command parameters

Attribute Definition Requirement

liquibase.command.sql: <string>

liquibase.command.<cmdName>.sql: <string>

The SQL string to execute

Note: Columns display in the same order they are specified in the argument. If no columns are specified (For example select * <table_name>), then the output will display columns in the same order they are in the database.

 Required

liquibase.command.sqlFile: <string>

liquibase.command.<cmdName>.sqlFile: <string>

Specifies the file where SQL statements are stored

 Required

liquibase.command.url: <string>

liquibase.command.<cmdName>.url: <string>

The JDBC database connection URL. See Using JDBC URL in Liquibase.

 Required

liquibase.command.defaultCatalogName: <string>

liquibase.command.<cmdName>.defaultCatalogName: <string>

Name of the default catalog to use for the database connection

 Optional

liquibase.command.defaultSchemaName: <string>

liquibase.command.<cmdName>.defaultSchemaName: <string>

Name of the default schema to use for the database connection. If defaultSchemaName is set, then objects do not have to be fully qualified. This means you can refer to just mytable instead of myschema.mytable.

Tip: In Liquibase v4.23.0+, camelCase for defaultSchemaName works successfully. If you are on an earlier version, camelCase may not work as expected.

Note: The syntax liquibase.command.defaultSchemaName is valid for v4.19.0+. For prior versions, use defaultSchemaName.

 Optional

liquibase.command.delimiter: <string>

liquibase.command.<cmdName>.delimiter: <string>

The delimiter to use when executing SQL scripts

 Optional

liquibase.command.driver: <string>

liquibase.command.<cmdName>.driver: <string>

The JDBC driver class

 Optional

liquibase.command.driverPropertiesFile: <string>

liquibase.command.<cmdName>.driverPropertiesFile: <string>

The JDBC driver properties file

 Optional

liquibase.command.password: <string>

liquibase.command.<cmdName>.password: <string>

Password to connect to the target database.

Tip: It is a best practice to store sensitive data in a Secrets Management tool with Liquibase Pro.

 Optional

liquibase.command.username: <string>

liquibase.command.<cmdName>.username: <string>

Username to connect to the target database.

Tip: It is a best practice to store sensitive data in a Secrets Management tool with Liquibase Pro.

 Optional

Global parameters

Attribute Definition Requirement

JAVA_OPTS=-Dliquibase.command.outputFile=<string>

JAVA_OPTS=-Dliquibase.command.<cmdName>.outputFile=<string>

File path to where the command output will be written: a CSV file that is the result of running a SELECT query. If not specified, output goes to STDOUT. See --output-file.

 Optional

Command parameters

Attribute Definition Requirement

JAVA_OPTS=-Dliquibase.command.sql=<string>

JAVA_OPTS=-Dliquibase.command.<cmdName>.sql=<string>

The SQL string to execute

Note: Columns display in the same order they are specified in the argument. If no columns are specified (For example select * <table_name>), then the output will display columns in the same order they are in the database.

 Required

JAVA_OPTS=-Dliquibase.command.sqlFile=<string>

JAVA_OPTS=-Dliquibase.command.<cmdName>.sqlFile=<string>

Specifies the file where SQL statements are stored

 Required

JAVA_OPTS=-Dliquibase.command.url=<string>

JAVA_OPTS=-Dliquibase.command.<cmdName>.url=<string>

The JDBC database connection URL. See Using JDBC URL in Liquibase.

 Required

JAVA_OPTS=-Dliquibase.command.defaultCatalogName=<string>

JAVA_OPTS=-Dliquibase.command.<cmdName>.defaultCatalogName=<string>

Name of the default catalog to use for the database connection

 Optional

JAVA_OPTS=-Dliquibase.command.defaultSchemaName=<string>

JAVA_OPTS=-Dliquibase.command.<cmdName>.defaultSchemaName=<string>

Name of the default schema to use for the database connection. If defaultSchemaName is set, then objects do not have to be fully qualified. This means you can refer to just mytable instead of myschema.mytable.

Tip: In Liquibase v4.23.0+, camelCase for defaultSchemaName works successfully. If you are on an earlier version, camelCase may not work as expected.

Note: The syntax liquibase.command.defaultSchemaName is valid for v4.19.0+. For prior versions, use defaultSchemaName.

 Optional

JAVA_OPTS=-Dliquibase.command.delimiter=<string>

JAVA_OPTS=-Dliquibase.command.<cmdName>.delimiter=<string>

The delimiter to use when executing SQL scripts

 Optional

JAVA_OPTS=-Dliquibase.command.driver=<string>

JAVA_OPTS=-Dliquibase.command.<cmdName>.driver=<string>

The JDBC driver class

 Optional

JAVA_OPTS=-Dliquibase.command.driverPropertiesFile=<string>

JAVA_OPTS=-Dliquibase.command.<cmdName>.driverPropertiesFile=<string>

The JDBC driver properties file

 Optional

JAVA_OPTS=-Dliquibase.command.password=<string>

JAVA_OPTS=-Dliquibase.command.<cmdName>.password=<string>

Password to connect to the target database.

Tip: It is a best practice to store sensitive data in a Secrets Management tool with Liquibase Pro.

 Optional

JAVA_OPTS=-Dliquibase.command.username=<string>

JAVA_OPTS=-Dliquibase.command.<cmdName>.username=<string>

Username to connect to the target database.

Tip: It is a best practice to store sensitive data in a Secrets Management tool with Liquibase Pro.

 Optional

Global parameters

Attribute Definition Requirement

LIQUIBASE_COMMAND_OUTPUT_FILE=<string>

LIQUIBASE_COMMAND_<CMDNAME>_OUTPUT_FILE=<string>

File path to where the command output will be written: a CSV file that is the result of running a SELECT query. If not specified, output goes to STDOUT. See --output-file.

 Optional

Command parameters

Attribute Definition Requirement

LIQUIBASE_COMMAND_SQL=<string>

LIQUIBASE_COMMAND_<CMDNAME>_SQL=<string>

The SQL string to execute

Note: Columns display in the same order they are specified in the argument. If no columns are specified (For example select * <table_name>), then the output will display columns in the same order they are in the database.

 Required

LIQUIBASE_COMMAND_SQL_FILE=<string>

LIQUIBASE_COMMAND_<CMDNAME>_SQL_FILE=<string>

Specifies the file where SQL statements are stored

 Required

LIQUIBASE_COMMAND_URL=<string>

LIQUIBASE_COMMAND_<CMDNAME>_URL=<string>

The JDBC database connection URL. See Using JDBC URL in Liquibase.

 Required

LIQUIBASE_COMMAND_DEFAULT_CATALOG_NAME=<string>

LIQUIBASE_COMMAND_<CMDNAME>_DEFAULT_CATALOG_NAME=<string>

Name of the default catalog to use for the database connection

 Optional

LIQUIBASE_COMMAND_DEFAULT_SCHEMA_NAME=<string>

LIQUIBASE_COMMAND_<CMDNAME>_DEFAULT_SCHEMA_NAME=<string>

Name of the default schema to use for the database connection. If defaultSchemaName is set, then objects do not have to be fully qualified. This means you can refer to just mytable instead of myschema.mytable.

Tip: In Liquibase v4.23.0+, camelCase for defaultSchemaName works successfully. If you are on an earlier version, camelCase may not work as expected.

Note: The syntax liquibase.command.defaultSchemaName is valid for v4.19.0+. For prior versions, use defaultSchemaName.

 Optional

LIQUIBASE_COMMAND_DELIMITER=<string>

LIQUIBASE_COMMAND_<CMDNAME>_DELIMITER=<string>

The delimiter to use when executing SQL scripts

 Optional

LIQUIBASE_COMMAND_DRIVER=<string>

LIQUIBASE_COMMAND_<CMDNAME>_DRIVER=<string>

The JDBC driver class

 Optional

LIQUIBASE_COMMAND_DRIVER_PROPERTIES_FILE=<string>

LIQUIBASE_COMMAND_<CMDNAME>_DRIVER_PROPERTIES_FILE=<string>

The JDBC driver properties file

 Optional

LIQUIBASE_COMMAND_PASSWORD=<string>

LIQUIBASE_COMMAND_<CMDNAME>_PASSWORD=<string>

Password to connect to the target database.

Tip: It is a best practice to store sensitive data in a Secrets Management tool with Liquibase Pro.

 Optional

LIQUIBASE_COMMAND_USERNAME=<string>

LIQUIBASE_COMMAND_<CMDNAME>_USERNAME=<string>

Username to connect to the target database.

Tip: It is a best practice to store sensitive data in a Secrets Management tool with Liquibase Pro.

 Optional

Note: The username and password attributes are not required for connections and systems which use alternate means of authentication. Also, you can specify database credentials as part of the url attribute.

Liquibase Version: 4.9.1
Output of select * from databasechangelog:
AUTHOR | COMMENTS | CONTEXTS | DATEEXECUTED | DEPLOYMENT_ID | DESCRIPTION | EXECTYPE | FILENAME | ID | LABELS | LIQUIBASE | MD5SUM | ORDEREXECUTED | TAG |
liquibase |  | null | 2022-05-13 12:02:12.125792 | 2464932010 | empty | EXECUTED | liquibase-internal | 1652464932094 | null | 4.9.1 | 8:d41d8cd98f00b204e9800998ecf8427e | 1 | version1 |
other.dev | example comment | example-context | 2022-05-13 12:17:30.818119 | 2465850648 | sql | EXECUTED | example-changelog.sql | 3 | example-label | 4.9.1 | 8:98b5f38170a3749834d15d3361fc6d9b | 4 | version1 |
Liquibase command 'execute-sql' was executed successfully.

Related links