tableColumnDisallow
tableColumnDisallow
is a custom policy check that checks for DATA_TYPE
columns when creating tables.
Note: Only basic CREATE statements are supported and constraint names must be provided, not auto-generated.
Learn how to create and customize the tableColumnDisallow
Liquibase Custom Policy Check using a Python script.
This example works for Oracle. You can use this check as it is or customize it further to fit your needs in your SQL database.
For a conceptual overview of this feature, see Liquibase Pro Custom Policy Checks.
Scope | Database |
---|---|
Database | Oracle |

- Install Liquibase 4.29.0+
- Configure a valid Liquibase Pro license key
- Ensure the Liquibase Checks extension is installed. In Liquibase 4.31.0+, it is already installed in the
/liquibase/internal/lib
directory, so no action is needed. If the checks JAR is not installed, downloadliquibase-checks-<version>.jar
and put it in theliquibase/lib
directory.- Maven users only: Add this dependency to your
pom.xml
file:
- Maven users only: Add this dependency to your
<dependency>
<groupId>org.liquibase.ext</groupId>
<artifactId>liquibase-checks</artifactId>
<version>2.0.0</version>
</dependency>

Before creating a custom policy check with Python, we recommend being familiar with:
- Python 3.10.14+. (See here for the official Python tutorial)
- Optional: General coding and Python best practices which will improve your check performance:
- Efficient handling of structured data objects
- Effective and targeted parsing of text, objects, and SQL
- Using regular expressions and other pattern-matching tools within Python
- Using Python virtual environments. Liquibase comes with a built-in virtual environment for Liquibase Custom Policy Checks. The built-in environment includes Liquibase Python modules and some common external Python modules—no configuration needed. However, if you want to install additional modules, or if you want your IDE to recognize the Liquibase modules, you must Create a Python Virtual Environment separately.
Tip: Downloading Python itself is not required to create custom checks in the Liquibase checks framework, but it may be useful to test checks against Python 3.10.14+.
Step-by-step
These steps describe how to create the Custom Policy Check. It does not exist by default in Liquibase Pro.
- Create a Check Settings file: Use the Checks Settings Configuration File
-
Add this code to your Checks Settings file:
CopytableColumnDisallow Quotes Python Script###
### This script checks for DATA_TYPE columns when creating tables
###
### Notes:
### 1. Only basic CREATE statements are supported
###
###
### Helpers come from Liquibase
###
import liquibase_utilities
import sys
###
### Functions
###
def find_snapshot_object(object_list, type, key, value):
"""Returns a snapshot object given a key (e.g., name) and attribute."""
for object in object_list:
if object[type][key].lower() == value.lower():
return object
return None
###
### main
###
###
### Retrieve log handler
### Ex. liquibase_logger.info(message)
###
liquibase_logger = liquibase_utilities.get_logger()
###
### Retrieve status handler
###
liquibase_status = liquibase_utilities.get_status()
###
### Retrive datatype from check definition
###
data_type = liquibase_utilities.get_arg("DATA_TYPE").lower()
###
### Retrieve all changes in changeset
###
changes = liquibase_utilities.get_changeset().getChanges()
###
### Loop through all changes
###
for change in changes:
###
### LoadData change types are not currently supported
###
if "loaddatachange" in change.getClass().getSimpleName().lower():
liquibase_logger.info("LoadData change type not supported. Statement skipped.")
continue
###
### Retrieve sql as string, remove extra whitespace, split into statements
###
raw_sql = liquibase_utilities.strip_comments(liquibase_utilities.generate_sql(change)).casefold()
raw_sql = " ".join(raw_sql.split())
raw_statements = liquibase_utilities.split_statements(raw_sql)
###
### Process each statement
###
for raw_statement in raw_statements:
sql_list = raw_statement.split()
###
### CREATE [SCHEMA.]TABLE NAME (column1 datatype1, column2 datatype2, ...)
###
try:
if sql_list[0] == "create" and sql_list[1] == "table":
###
### Remove schema and parenthesis if provided
###
table_name = sql_list[2].split(".")[-1]
start = table_name.rfind("(")
if start != -1:
table_name = table_name[0:start]
else:
raise UserWarning
except IndexError:
liquibase_logger.warning(f"Unsupported Create Table statement skipped: {raw_statement}")
continue
except UserWarning:
liquibase_logger.info(f"Non Create Index statement skipped: {raw_statement}")
continue
###
### Process column list
###
column_list = []
search_string = f"{table_name} ("
start = raw_statement.find(search_string)
if start == -1:
liquibase_logger.warning(f"Unsupported Create Table statement skipped: {raw_statement}")
continue
start += len(search_string)
end = raw_statement.rfind(")")
if end != -1:
column_list = [column_type.strip() for column_type in raw_statement[start:end].split(",")]
###
### Look for data_type in column list
###
data_type_size = len(data_type)
for name_type in column_list:
column_type = name_type.split()[:2]
if len(column_type) < 2 or column_type[0] == "constraint":
continue
column_name = column_type[0]
column_type = column_type[1]
if column_type[0:data_type_size] == data_type:
liquibase_status.fired = True
status_message = str(liquibase_utilities.get_script_message()).replace("__COLUMN_TYPE__", f"\"{data_type}\"")
status_message = status_message.replace("__COLUMN_NAME__", f"\"{column_name}\"")
liquibase_status.message = status_message
sys.exit(1)
###
### Default return code
###
False -
Initiate the customization process. In the CLI, run this command:
liquibase checks customize --check-name=CustomCheckTemplate
The CLI prompts you to finish configuring your file. A message displays:This check cannot be customized directly because one or more fields does not have a default value.
Liquibase will then create a copy of
CustomCheckTemplate
and initiate the customization workflow. -
Give your check a short name so you can easily identify what Python script it is associated with (up to 64 alpha-numeric characters only).
In this example we will name the check:tableColumnDisallow
-
Set the Severity to return a code of 0-4 when triggered. These severity codes allow you to determine if the job moves forward or stops when this check triggers.
Learn more here: Use Policy Checks in Automation: Severity and Exit Code
options:'INFO'=0
,'MINOR'=1
,'MAJOR'=2
,'CRITICAL'=3
,'BLOCKER'=4
- Set
SCRIPT_DESCRIPTION
. In this example, we will set the description to:This script checks to see if DATA_TYPE columns exist when creating a table.
- Set
SCRIPT_SCOPE
. In this example, we will set the scope tochangelog
. changelog
: for example, if your check looks for syntax patterns or attributes in your Liquibase Changelog (the changes you author in your repository). With this value, the check runs once per changeset.- Set the
SCRIPT_MESSAGE
. This message will display when the check is triggered. In this example we will use:Datatype __COLUMN_TYPE__ is discouraged for column __COLUMN_NAME__.
- Set the
SCRIPT_PATH
. This is the relative path where your script is stored in relation to the changelog specified in--changelog-file
, whether it is stored locally or in a repository.
In this example, we will set the path to:scripts/table-column-disallow.py
. - Set the
SCRIPT_ARGUMENT to DATA_TYPE=CLOB
.
This allows you to pass dynamic information into the Custom Policy Check without modifying the Python code.
When you setDATA_TYPE=CLOB
in the CLI, you can retrieve it in your code with a variable:data_type = liquibase_utilities.get_arg("DATA_TYPE")
If you customize your check later, you can specify a new value in the CLI. If you don't need dynamic arguments, leave this field blank. - Set the
REQUIRES_SNAPSHOT
. If your script scope ischangelog
, set whether the check requires a database snapshot. Specifytrue
if your check needs to inspect database objects. (If your script scope isdatabase
, Liquibase always takes a snapshot, so this prompt does not appear.)
Tip: It is a best practice for your custom checks to have only one scope, not both scopes.
Note: The larger your database, the more performance impact a snapshot causes. If you cannot run a snapshot due to memory limitations, see Memory Limits of Inspecting Large Schemas.
You have now successfully created and customized a policy check!