Posted inDatabase

SQLFluff on Linux: Automate SQL Linting and Code Standards in Your CI/CD Pipeline

Database tutorial - IT technology blog
Database tutorial - IT technology blog

SQL code quality is one of those things that nobody cares about until they inherit a codebase where every developer used different keyword casing, inconsistent indentation, and random quoting styles across 300 files. SQLFluff solves this — it brings the same discipline that ESLint gives JavaScript and Black gives Python, but for SQL.

Having worked with MySQL, PostgreSQL, and MongoDB across different projects, each has its own strengths and its own SQL quirks. SQLFluff handles this through dialect-aware linting, which is what makes it practical for real teams rather than a toy formatter.

Quick Start: Get SQLFluff Running in 5 Minutes

Install it with pip — no system dependencies, no configuration required to get started:

pip install sqlfluff
sqlfluff --version

Create a test file with intentionally messy SQL:

select id,name,email FROM users where status='active' and created_at > '2024-01-01'

Run the linter against it:

sqlfluff lint query.sql --dialect postgres

You’ll see output like this:

== [query.sql] FAIL
L:   1 | P:   1 | CP01 | Keywords must be consistently upper case.
L:   1 | P:  10 | LT04 | Leading comma enforcement.
L:   1 | P:  17 | LT01 | Expected single space before keyword.

Then fix it automatically:

sqlfluff fix query.sql --dialect postgres

SQLFluff rewrites the file in place. The result is clean, consistently formatted SQL — no manual editing. That’s the core workflow: lint to see what’s wrong, fix to correct it automatically.

Deep Dive: Configuration and Dialect Support

Running SQLFluff with flags every time gets old quickly. Put a .sqlfluff config file in your project root and it picks it up automatically:

[sqlfluff]
dialect = postgres
templater = jinja
max_line_length = 120

[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = upper

[sqlfluff:rules:capitalisation.identifiers]
capitalisation_policy = lower

[sqlfluff:rules:capitalisation.functions]
capitalisation_policy = upper

[sqlfluff:rules:layout.long_lines]
ignore_comment_clauses = True

Now just run sqlfluff lint . from the project root and it recursively lints all .sql files using the configured dialect.

SQLFluff supports a wide range of dialects out of the box:

  • ansi — generic SQL, good fallback
  • postgres, mysql, sqlite — the classics
  • bigquery, snowflake, tsql — cloud/enterprise
  • spark, duckdb, hive — analytics engines

Rules are organized into categories that map to different concerns:

  • Layout (LT): indentation, spacing, line length, trailing whitespace
  • Capitalisation (CP): keyword and identifier casing
  • Aliasing (AL): table and column alias requirements
  • Structure (ST): subquery structure, join conditions
  • References (RF): column references, ambiguity detection

To suppress a specific rule inline without disabling it globally:

SELECT *  -- noqa: LT09
FROM really_long_table_name_here;

To exclude rules project-wide:

[sqlfluff]
exclude_rules = LT09, RF01

Advanced Usage: CI/CD Integration

Local linting helps individual developers. CI/CD enforcement means no inconsistent SQL ever lands in main. Here’s how to set it up across the common platforms.

GitHub Actions

# .github/workflows/sql-lint.yml
name: SQL Lint

on:
  pull_request:
    paths:
      - '**.sql'

jobs:
  sqlfluff:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: '3.11'

      - name: Install SQLFluff
        run: pip install sqlfluff==3.0.7

      - name: Run SQLFluff
        run: sqlfluff lint . --dialect postgres --format github-annotation

The --format github-annotation flag is the key detail here. It outputs errors as inline PR annotations, so reviewers see lint failures directly on the diff rather than digging through CI logs.

GitLab CI

# .gitlab-ci.yml
sql-lint:
  image: python:3.11-slim
  stage: test
  script:
    - pip install sqlfluff==3.0.7
    - sqlfluff lint . --dialect postgres
  only:
    changes:
      - "**/*.sql"

Pre-commit Hooks

Pre-commit hooks catch issues before they even reach CI — faster feedback and less wasted pipeline time:

# .pre-commit-config.yaml
repos:
  - repo: https://github.com/sqlfluff/sqlfluff
    rev: 3.0.7
    hooks:
      - id: sqlfluff-lint
        args: [--dialect, postgres]
      - id: sqlfluff-fix
        args: [--dialect, postgres]
pip install pre-commit
pre-commit install
pre-commit run --all-files

After this, every git commit automatically lints and fixes SQL. Teammates don’t need to remember to run anything manually.

dbt Project Support

If your project uses dbt, SQLFluff handles Jinja templating natively:

[sqlfluff]
templater = dbt
dialect = postgres

[sqlfluff:templater:dbt]
project_dir = ./dbt_project
profiles_dir = ~/.dbt
sqlfluff lint models/ --templater dbt

Practical Tips

Start Permissive, Then Tighten

When introducing SQLFluff to a legacy project, you’ll likely get hundreds of violations on day one. Trying to fix them all before merging will kill adoption. Instead, add -- noqa: all at the top of existing files temporarily, then fix them file by file over subsequent PRs. This makes the rollout feel manageable instead of blocking everyone.

Lint Only Changed Files in CI

For large codebases, linting everything on every PR is slow. Scope it to changed files:

git diff --name-only origin/main | grep '\.sql$' | xargs sqlfluff lint --dialect postgres

Pin the Version

SQLFluff releases frequently and rule behavior can shift between minor versions. Pin it to avoid surprise CI failures:

# requirements-dev.txt
sqlfluff==3.0.7

Keep Lint and Fix Separate in CI

The lint step should fail the build. The fix step is a developer tool only — you don’t want CI auto-committing reformatted files back to your repo. Make this explicit in your Makefile:

.PHONY: lint-sql fix-sql

lint-sql:
	sqlfluff lint . --dialect postgres

fix-sql:
	sqlfluff fix . --dialect postgres

VS Code Integration

The SQLFluff VS Code extension (by dorzey) gives inline lint errors while you type. Point it at the project’s venv binary so it uses the same version as CI:

// .vscode/settings.json
{
  "sqlfluff.dialect": "postgres",
  "sqlfluff.executablePath": "${workspaceFolder}/.venv/bin/sqlfluff"
}

The combination of pre-commit hooks for local development and CI enforcement for pull requests creates a two-layer safety net. Issues get caught before commit. Anything that slips through gets caught before merge. After a few weeks, the team stops thinking about SQL style entirely — it just stays consistent on autopilot.

One thing I wish I’d set up earlier on PostgreSQL projects: enforce a strict SQLFluff config from day one. Retrofitting consistency across hundreds of SQL files is tedious work that SQLFluff makes trivially avoidable when adopted early.

Share:
Tags: