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.
| Command | Needs database | Purpose |
|---|---|---|
validate | Yes | History, checksums, and migration metadata consistency |
validate-sql | No | Migration-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.
| Licence | What this edition adds on validate-sql |
|---|---|
| OSS | The 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. |
| Pro | Keeps 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. |
| Enterprise | Keeps 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 postgresqldblift validate-sql --dialect postgresql --format jsondblift validate-sql --files migrations/*.sql --dialect postgresql
Pro. HTML, SARIF, and --output are Pro:
dblift validate-sql --dialect postgresql --format sarif --output results.sarifdblift 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.
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| Key | Default | What it does |
|---|---|---|
enabled | true | Turns 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_on | error | The severity that makes the command exit non-zero. |
severity_threshold | warning | The lowest severity that gets reported at all. Anything below is dropped. |
performance_enabled | true | Pro. 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_format | console | Default 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.
| Pack | Rules | Focus |
|---|---|---|
security | 21 | Injection surfaces, credentials in scripts, over-broad grants, unprotected PII, and destructive operations on sensitive tables. |
naming | 24 | Snake case across tables, columns, views, procedures, sequences and triggers; reserved words; prefix and suffix conventions for indexes, keys and booleans. |
performance | 27 | Missing indexes on foreign keys, cartesian joins, unbounded UPDATE/DELETE, subquery shapes, functions on indexed columns, and leading-wildcard LIKE. |
best_practices | 20 | Primary 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.
| Profile | Selects | When to use it |
|---|---|---|
core | security + three named rules | The security pack plus no_drop_without_backup, update_delete_must_have_where and require_primary_key. The smallest useful gate. |
enterprise | security, best_practices, performance | Everything that affects safety and runtime, without the naming opinions. |
strict | naming, security, best_practices, performance | All four packs. Expect naming findings on an existing codebase. |
technical-debt | naming, best_practices, performance | The quality packs without security — for a cleanup pass rather than a release gate. |
dblift validate-sql migrations/ \--profile enterprise \--fail-on warning \--format github-actionsdblift 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
| Rule | Severity | Control | Fix |
|---|---|---|---|
no_hardcoded_credentials | Error | — | Use environment variables or secure credential management. |
no_dynamic_sql_without_validation | Error | SOC2-CC7.1 · ISO27001-A.14.2.5 | Use parameterized execution and document the allowed statement shape. |
no_grant_all_privileges | Error | SOC2-CC6.3 · ISO27001-A.9.2.3 | Grant specific privileges: GRANT SELECT, INSERT ON table TO user. |
password_columns_must_be_encrypted | Error | — | Use appropriate encryption or hashing: password_hash VARCHAR(255) with bcrypt or argon2. |
no_plaintext_storage_of_secrets | Error | — | Use encryption at rest or secure secret management systems. |
require_soft_delete_for_important_data | Warning | — | Add a deleted_at column and use UPDATE … SET deleted_at = NOW() instead of DELETE. |
performance
| Rule | Severity | Control | Fix |
|---|---|---|---|
fk_must_have_index | Error | — | Foreign keys must have indexes for join performance. |
update_delete_must_have_where | Error | — | UPDATE/DELETE without WHERE affects the entire table. |
no_cartesian_join | Error | — | Use explicit JOIN ... ON: FROM table1 JOIN table2 ON table1.id = table2.id. |
avoid_not_in_with_nullable_subquery | Error | — | Use NOT EXISTS: WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table2.id = table1.id). |
no_select_star | Warning | DBLIFT-PERF-001 | List specific columns: SELECT id, name, email FROM users. |
avoid_correlated_subquery | Warning | — | Rewrite using JOIN: SELECT DISTINCT … FROM t1 JOIN t2 ON t1.id = t2.id. |
best_practices
| Rule | Severity | Control | Fix |
|---|---|---|---|
require_primary_key | Error | — | All tables should have a primary key. Log, temp, staging and archive tables are exempt. |
use_proper_date_types | Error | — | Store dates and times in real date types, not text. |
no_drop_without_backup | Warning | SOC2-CC7.2 · ISO27001-A.12.1.2 | Use an archival migration, backup proof, or an approved drop plan. |
require_audit_timestamps | Warning | — | Add created_at and updated_at. Log, temp, staging, ref and lookup tables are exempt. |
fk_must_specify_cascade | Warning | — | Add ON DELETE CASCADE, RESTRICT, SET NULL, or NO ACTION as appropriate. |
use_if_exists_for_drops | Info | — | Use DROP TABLE IF EXISTS or DROP INDEX IF EXISTS. |
naming
| Rule | Severity | Fix |
|---|---|---|
table_name_no_reserved_words | Error | Use descriptive names that do not conflict with SQL keywords. |
schema_name_no_special_chars | Error | Letters, digits and underscores only. |
table_name_snake_case | Warning | Lowercase snake_case — ^[a-z][a-z0-9_]*$. |
column_name_snake_case | Warning | Lowercase snake_case — ^[a-z][a-z0-9_]*$. |
boolean_columns_naming | Info | Start boolean columns with is_, has_, can_, should_ or will_. |
index_name_prefix | Info | Prefix 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.
# .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: warningdblift 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:
| Type | Purpose |
|---|---|
pattern | Regex match on SQL text |
naming | Identifier naming conventions |
presence | Required elements (primary keys, columns) |
relational | Relationships between objects (e.g. FK must have index) |
Severity levels: error, warning, info.
Exceptions
Skip a rule for specific cases:
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
- name: Validate SQL
run: |
dblift validate-sql migrations/ \
--dialect postgresql \
--profile enterprise \
--fail-on warning \
--format sarif \
--output results.sarifSee Commands for all CLI flags and CI/CD Integration for pipeline patterns.