Docs/Govern/SQL validation
OSSProEnterprise

SQL Validation (validate-sql)

Scan SQL migration files offline. This is separate from the OSS validate command, which checks migration history against the configured database.

CommandNeeds databasePurpose
validateYesHistory, checksums, and migration metadata consistency
validate-sqlNoMigration-safety rules on .sql files, plus a SAFE/REVIEW/UNSAFE verdict. It does not verify SQL syntax. OSS prints console or JSON.

Python migrations are not linted by validate-sql. The command does not run SQLFluff, and it does not claim to check SQL syntax. The open-source package runs 17 migration-safety rules. It has no performance analyser. drop-unread-object and copy-server-side are warnings. An unread statement, CALL, or procedural block is statement-not-analysed at INFO severity, and the script is REVIEW. REVIEW does not exit 1 on its own. The command exits 1 when something is UNSAFE. -- dblift:allow statement-not-analysed accepts that finding. pg-missing-lock-timeout also covers LOCK TABLE in a share mode without NOWAIT, and VACUUM FULL. validate-sql needs dblift[analysis] and has no execution mode.

What each licence adds

One command. Pro keeps the OSS baseline and adds the Pro row. Enterprise keeps both and adds the Enterprise row.

The baseline needs no licence on the open-source package (pip install "dblift[cli]") or the commercial build. A paid flag needs Pro.

LicenceWhat this edition adds on validate-sql
OSSThe baseline: 17 migration-safety rules, a SAFE/REVIEW/UNSAFE verdict, exit 1 on UNSAFE, and --files, --dialect, --format console or json, and --placeholders. Any paid flag exits 2. validate-sql on the open-source package never returns 4. No performance analyser and no MCP tool. The commercial build runs this same baseline with no licence.
ProKeeps that baseline. Adds a custom --rules-file, --rules security, --rules schema, --output, --fail-on, --base, --severity-threshold, --no-performance, the formats sarif, github-actions, gitlab, compact and html, and the performance analyser. With a Pro licence and no profile, pack or rules file, the performance analyser also runs. The MCP validate_sql tool is Pro.
EnterpriseKeeps the OSS baseline and the Pro additions. Adds named profiles (core, enterprise, strict, technical-debt), the best_practices, performance and naming packs, and individual rule IDs. An individual rule ID is Enterprise even when the rule belongs to the security or schema pack.

--rules-file cannot be combined with --profile or --rules.

Basic usage

OSS (pip install "dblift[cli]"). Without --files, the command reads the V and R scripts in the migration directories. --files takes files, not directories:

dblift validate-sql --dialect postgresql
dblift validate-sql --dialect postgresql --format json
dblift validate-sql --files migrations/*.sql --dialect postgresql

Pro. HTML, SARIF, and --output are Pro:

dblift validate-sql --dialect postgresql --format sarif --output results.sarif
dblift validate-sql --dialect postgresql --fail-on warning

--fail-on is Pro. It controls the minimum severity that returns a non-zero exit code: never, error, warning, or info (default: error).

Configuring via dblift.yaml

Everything --profile, --rules and --fail-on set on the command line has a validation: section equivalent, so a project can commit its rule selection instead of repeating flags in every pipeline. Environments can narrow it further — a stricter profile in prod than in staging.

yaml
validation: enabled: true rule_profile: enterprise fail_on: error severity_threshold: warning exclude_patterns: - "migrations/vendor/**" environments: prod: validation: rule_profile: strict fail_on: warning
KeyDefaultWhat it does
enabledtrueTurns SQL validation off entirely for this project or environment.
rule_profile—One of core, enterprise, strict or technical-debt.
rules[]Explicit pack or rule names, instead of — or alongside — a profile.
rules_file—Path to your own rule pack, in the same YAML shape as the built-in packs.
fail_onerrorThe severity that makes the command exit non-zero.
severity_thresholdwarningThe lowest severity that gets reported at all. Anything below is dropped.
performance_enabledtruePro. Runs the performance analyser in addition to the pattern rules. OSS has no performance analyser.
exclude_patterns[]File globs skipped entirely — vendor SQL, generated migrations.
output_formatconsoleDefault output format when the command is run without --format.

Built-in rule packs

DBLift ships four rule packs. Each pack is a YAML collection of named rules.

PackRulesFocus
security21Injection surfaces, credentials in scripts, over-broad grants, unprotected PII, and destructive operations on sensitive tables.
naming24Snake case across tables, columns, views, procedures, sequences and triggers; reserved words; prefix and suffix conventions for indexes, keys and booleans.
performance27Missing indexes on foreign keys, cartesian joins, unbounded UPDATE/DELETE, subquery shapes, functions on indexed columns, and leading-wildcard LIKE.
best_practices20Primary keys, audit timestamps, cascade behaviour on foreign keys, type choices, and safe migration shape — IF EXISTS on drops, defaults on added columns.

Reference individual rules by name with --rules, for example fk_must_have_index, no_select_star, or require_primary_key.

Profiles

A profile is a named selection over the packs. Setting one is usually all the configuration a project needs.

ProfileSelectsWhen to use it
coresecurity + three named rulesThe security pack plus no_drop_without_backup, update_delete_must_have_where and require_primary_key. The smallest useful gate.
enterprisesecurity, best_practices, performanceEverything that affects safety and runtime, without the naming opinions.
strictnaming, security, best_practices, performanceAll four packs. Expect naming findings on an existing codebase.
technical-debtnaming, best_practices, performanceThe quality packs without security — for a cleanup pass rather than a release gate.
dblift validate-sql migrations/ \
--profile enterprise \
--fail-on warning \
--format github-actions
 
dblift validate-sql migrations/ \
--profile core \
--rules no_public_schema_access,require_primary_key

Use --rules to add a full pack name or individual rule names on top of a profile:

dblift validate-sql migrations/ --profile enterprise --rules naming

Profiles select rules only. They do not change output format or --fail-on.

Representative rules

Six from each pack. Every rule reports a severity and, where it can, the fix. A rule that maps to a control names it, so a finding can be traced to the requirement it serves.

security

RuleSeverityControlFix
no_hardcoded_credentialsError—Use environment variables or secure credential management.
no_dynamic_sql_without_validationErrorSOC2-CC7.1 · ISO27001-A.14.2.5Use parameterized execution and document the allowed statement shape.
no_grant_all_privilegesErrorSOC2-CC6.3 · ISO27001-A.9.2.3Grant specific privileges: GRANT SELECT, INSERT ON table TO user.
password_columns_must_be_encryptedError—Use appropriate encryption or hashing: password_hash VARCHAR(255) with bcrypt or argon2.
no_plaintext_storage_of_secretsError—Use encryption at rest or secure secret management systems.
require_soft_delete_for_important_dataWarning—Add a deleted_at column and use UPDATE … SET deleted_at = NOW() instead of DELETE.

performance

RuleSeverityControlFix
fk_must_have_indexError—Foreign keys must have indexes for join performance.
update_delete_must_have_whereError—UPDATE/DELETE without WHERE affects the entire table.
no_cartesian_joinError—Use explicit JOIN ... ON: FROM table1 JOIN table2 ON table1.id = table2.id.
avoid_not_in_with_nullable_subqueryError—Use NOT EXISTS: WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table2.id = table1.id).
no_select_starWarningDBLIFT-PERF-001List specific columns: SELECT id, name, email FROM users.
avoid_correlated_subqueryWarning—Rewrite using JOIN: SELECT DISTINCT … FROM t1 JOIN t2 ON t1.id = t2.id.

best_practices

RuleSeverityControlFix
require_primary_keyError—All tables should have a primary key. Log, temp, staging and archive tables are exempt.
use_proper_date_typesError—Store dates and times in real date types, not text.
no_drop_without_backupWarningSOC2-CC7.2 · ISO27001-A.12.1.2Use an archival migration, backup proof, or an approved drop plan.
require_audit_timestampsWarning—Add created_at and updated_at. Log, temp, staging, ref and lookup tables are exempt.
fk_must_specify_cascadeWarning—Add ON DELETE CASCADE, RESTRICT, SET NULL, or NO ACTION as appropriate.
use_if_exists_for_dropsInfo—Use DROP TABLE IF EXISTS or DROP INDEX IF EXISTS.

naming

RuleSeverityFix
table_name_no_reserved_wordsErrorUse descriptive names that do not conflict with SQL keywords.
schema_name_no_special_charsErrorLetters, digits and underscores only.
table_name_snake_caseWarningLowercase snake_case — ^[a-z][a-z0-9_]*$.
column_name_snake_caseWarningLowercase snake_case — ^[a-z][a-z0-9_]*$.
boolean_columns_namingInfoStart boolean columns with is_, has_, can_, should_ or will_.
index_name_prefixInfoPrefix index names with idx_, ix_, pk_, uk_ or fk_.

Overrides expire

The rules that carry a control mapping also carry an override policy. Suppressing one requires an owner, a reason, a ticket and an expiry date, and the expiry is capped — 30 days for the security rules, 14 for no_drop_without_backup. An expired override stops suppressing.

Custom rules with --rules-file

Pass a YAML file with a top-level rules: list when you need a fully custom ruleset. Do not combine --rules-file with --profile or --rules.

yaml
# .dblift_rules.yaml rules: - name: no_drop_database type: pattern prohibit: "DROP DATABASE" message: "DROP DATABASE is not allowed in migrations" severity: error - name: require_company_prefix type: naming target: table pattern: "^company_.*$" message: "Tables must start with company_" severity: warning
dblift validate-sql migrations/ \
--dialect postgresql \
--rules-file .dblift_rules.yaml \
--fail-on warning

Copy rule definitions from the shipped packs, or start from a pack file and trim rules. Rule types:

TypePurpose
patternRegex match on SQL text
namingIdentifier naming conventions
presenceRequired elements (primary keys, columns)
relationalRelationships between objects (e.g. FK must have index)

Severity levels: error, warning, info.

Exceptions

Skip a rule for specific cases:

yaml
rules: - name: require_primary_key type: presence target: table must_have_primary_key: true message: "Tables should have a primary key" severity: warning exceptions: - table_matches: ".*_log.*" - table_matches: ".*_temp.*"

Enterprise rules can include rationale, remediation, and control_mapping metadata for audit evidence in HTML and JSON reports.

Output formats

OSS prints console or json. HTML, SARIF, and --output are Pro, as are github-actions, gitlab, and compact. json is available on both.

dblift validate-sql migrations/ \
--profile enterprise \
--fail-on warning \
--format html \
--output sql-validation-evidence.html

CI example

yaml
- name: Validate SQL run: | dblift validate-sql migrations/ \ --dialect postgresql \ --profile enterprise \ --fail-on warning \ --format sarif \ --output results.sarif

See Commands for all CLI flags and CI/CD Integration for pipeline patterns.

See it in actionSQL review and schema diff on dblift.com
On this page