DBLift SQL Validation Documentation

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

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	Policy rules and performance checks on `.sql` files. It does not verify SQL syntax.

Python migrations are not linted by validate-sql. The command does not run SQLFluff, and it does not claim to check SQL syntax. Selected DBLift rules and the performance checks still run.

What each licence unlocks

The command is Pro. Built-in packs and named profiles are Enterprise.

Licence	What it can run
Pro	The command, a custom `--rules-file`, `--rules security`, and the built-in performance analyser.
Enterprise	All four packs (`security`, `naming`, `performance`, `best_practices`) and the named profiles (`core`, `enterprise`, `strict`, `technical-debt`).

--profile and any --rules value other than security require Enterprise. --rules-file cannot be combined with --profile or --rules. Without a profile, a pack list, or a rules file, Pro still runs the performance analyser (cartesian joins, unbounded UPDATE/DELETE, SELECT *).

Basic usage

dblift validate-sql migrations/ --dialect postgresql
dblift validate-sql migrations/ --dialect postgresql --format sarif > results.sarif
dblift validate-sql migrations/ --dialect postgresql --fail-on warning

--fail-on 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`	Runs the performance analyser in addition to the pattern rules.
`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-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

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_`.

[!WARNING] 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_.*quot;
    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:

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

Machine-readable formats for CI: sarif, github-actions, gitlab, json, compact, html.

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.sarif

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

DBLift is information technology / developer tools software. Contact: contact@dblift.com.

SQL Validation (validate-sql)