SQLFluff

https://docs.sqlfluff.com/en/stable/
Admin
SQLFluff is an extensible and modular SQL linter designed to help write good SQL and catch errors...
Tokens:145,941
Snippets:1,246
Trust Score:8
Update:1 week ago
Show doc for...
Tokens:
Raw
### Install and lint SQL with SQLFluff

Source: https://docs.sqlfluff.com/en/stable/guide

Quick start: install SQLFluff, create a test SQL file, and run the linter with the ANSI dialect. The output shows lint violations for spacing, indentation, and file start/end whitespace.

```bash
pip install sqlfluff
echo "  SELECT a  +  b FROM tbl;  " > test.sql
sqlfluff lint test.sql --dialect ansi

== [test.sql] FAIL
L:   1 | P:   1 | LT01 | Expected only single space before 'SELECT' keyword.
                        | Found '  '. [layout.spacing]
L:   1 | P:   1 | LT02 | First line should not be indented.
                        | [layout.indent]
L:   1 | P:   1 | LT13 | Files must not begin with newlines or whitespace.
                        | [layout.start_of_file]
L:   1 | P:  11 | LT01 | Expected only single space before binary operator '+'.
                        | Found '  '. [layout.spacing]
L:   1 | P:  14 | LT01 | Expected only single space before naked identifier.
                        | Found '  '. [layout.spacing]
L:   1 | P:  27 | LT01 | Unnecessary trailing whitespace at end of file.
                        | [layout.spacing]
L:   1 | P:  27 | LT12 | Files must end with a single trailing newline.
                        | [layout.end_of_file]
All Finished 📜 🎉!
```

--------------------------------

### Example 2.0 config

Source: https://docs.sqlfluff.com/en/stable/reference/release-notes.html

Example SQLFluff configuration file showing rule exclusions, indentation settings, and rule-specific configurations.

```ini
# Exclude some specific rules based on a mixture of codes and names
exclude_rules = RF02, RF03, RF04, ST06, ST07, AM05, AM06, convention.left_join, layout.select_targets

[sqlfluff:indentation]
# Enabling implicit indents for this project.
# See https://docs.sqlfluff.com/en/stable/perma/indent_locations.html
allow_implicit_indents = True

# Add a few specific rule configurations, referenced by the rule names
# and not by the rule codes.
[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = lower

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

[sqlfluff:rules:capitalisation.functions]
extended_capitalisation_policy = lower

# An example of setting a custom layout specification which
# is more lenient than default config.
[sqlfluff:layout:type:set_operator]
line_position = alone
```

--------------------------------

### Example .sqlfluffignore file

Source: https://docs.sqlfluff.com/en/stable/configuration/ignoring.html

Place a `.sqlfluffignore` file in the root of your project to control which files are linted. Comments start with a hash. Patterns include ignoring a directory (`/temp/`), a specific file (`testing.sql`), and all `.tsql` files (`*.tsql`). Ignore files can also be placed in subdirectories and apply within that subdirectory.

```ini
# Comments start with a hash.

# Ignore anything in the "temp" path
/temp/

# Ignore anything called "testing.sql"
testing.sql

# Ignore any ".tsql" files
*.tsql
```

--------------------------------

### Install SQLFluff with pip

Source: https://docs.sqlfluff.com/en/stable

Install SQLFluff using pip. Run this command in your terminal to install the linter.

```bash
pip install sqlfluff
```

--------------------------------

### Install SQLFluff with testutils extra

Source: https://docs.sqlfluff.com/en/stable/development/plugins.html

Command to install SQLFluff with the testutils extra, which is required for using the testing library for plugin development.

```bash
pip install sqlfluff[testutils]
```

--------------------------------

### Example .sqlfluff config for capitalisation.keywords

Source: https://docs.sqlfluff.com/en/stable/reference/internals/config.html

Shows a .sqlfluff configuration file setting the capitalisation_policy for the capitalisation.keywords rule.

```ini
[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = lower

```

--------------------------------

### Specify dbt adapter version in pre-commit additional_dependencies

Source: https://docs.sqlfluff.com/en/stable/usage/pre-commit.html

Use this example to pin a specific dbt adapter version in the additional_dependencies list. This is equivalent to running pip install <dbt-adapter> sqlfluff-templater-dbt.

```yaml
additional_dependencies : ['dbt-bigquery==1.0.0', 'sqlfluff-templater-dbt']
```

--------------------------------

### Install SQLFluff with Rust extra

Source: https://docs.sqlfluff.com/en/stable/guide/install.html

Install SQLFluff with the optional Rust-backed parser and lexer using the `rs` extra. On supported CPython 3.10+ platforms, a prebuilt ABI3 wheel is installed; otherwise, pip builds `sqlfluffrs` from source, requiring a Rust toolchain and native build toolchain.

```bash
pip install sqlfluff[rs]
```

--------------------------------

### Build docs locally with pnpm

Source: https://docs.sqlfluff.com/en/stable/development/documentation.html

Commands to install dependencies, build, and preview the VitePress documentation site.

```bash
cd docsv
pnpm install
pnpm run docs:build
pnpm run docs:preview
```

--------------------------------

### Check Git version

Source: https://docs.sqlfluff.com/en/stable/development/git.html

Run in a terminal to verify Git is installed. If a version number is printed, Git is ready; otherwise, install Git for your OS.

```bash
git --version
```

--------------------------------

### set_value usage examples

Source: https://docs.sqlfluff.com/en/stable/reference/internals/config.html

Two usage examples for set_value. The first sets the dialect to postgres and retrieves it. The second sets the tab_space_size in the indentation section and retrieves it.

```python
>>> cfg = FluffConfig(overrides={"dialect": "ansi"})
>>> cfg.set_value(["dialect"], "postgres")
>>> cfg.get("dialect")
'postgres'
```

```python
>>> cfg = FluffConfig(overrides={"dialect": "ansi"})
>>> cfg.set_value(["indentation", "tab_space_size"], 2)
>>> cfg.get("tab_space_size", section="indentation")
2
```

--------------------------------

### SQL alignment example with aliases

Source: https://docs.sqlfluff.com/en/stable/configuration/layout.html

Demonstrates alignment of column aliases in a SQL query. Shows how aliases are aligned for readability.

```sql
WITH foo as (
   SELECT
      a,
      b,
      c        AS first_column,
      d + e    AS second_column
)

SELECT
   a           AS first_column,
   (a + b) / 2 AS third_column
FROM foo       AS bar
```

--------------------------------

### SQL example with aligned alias expressions

Source: https://docs.sqlfluff.com/en/stable/configuration/layout.html

Demonstrates aligned aliases, configured via the alias_expression layout type.

```sql
SELECT
   a           AS first_column,
   b           AS second_column,
   (a + b) / 2 AS third_column
FROM foo AS bar
```

--------------------------------

### Best practice: consistent capitalisation of unquoted identifiers

Source: https://docs.sqlfluff.com/en/stable/reference/rules/capitalisation.html

Shows acceptable examples: all lower-case, all upper-case, and a consistent version of the complex example.

```sql
select
    a,
    b
from foo;

-- ...also good...

select
    A,
    B
from foo;

--- ...or for comparison with our more complex example, this too:

select
    col_1 + col_2 as col_3,
    "COL_4" as col_5
from foo as bar
```

--------------------------------

### Common placeholder styles

Source: https://docs.sqlfluff.com/en/stable/configuration/templating/placeholder.html

List of supported placeholder styles with example SQL queries.

```sql
-- colon
WHERE bla = :my_name

-- colon_nospaces
-- (use with caution as more prone to false positives)
WHERE bla = table:my_name

-- colon_optional_quotes
SELECT :"column" FROM :table WHERE bla = :'my_name'

-- numeric_colon
WHERE bla = :2

-- pyformat
WHERE bla = %(my_name)s

-- dollar
WHERE bla = $my_name or WHERE bla = ${my_name}

-- dollar_surround (DbUp compatible variable)
WHERE bla = $my_name$

-- question_mark
WHERE bla = ?

-- numeric_dollar
WHERE bla = $3 or WHERE bla = ${3}

-- percent
WHERE bla = %s

-- ampersand
WHERE bla = &s or WHERE bla = &{s} or USE DATABASE MARK_{ENV}
```

--------------------------------

### process_raw_file_for_config usage example

Source: https://docs.sqlfluff.com/en/stable/reference/internals/config.html

Example usage of process_raw_file_for_config. It creates a FluffConfig with an ANSI dialect override, processes a raw SQL string containing an inline config to set the dialect to postgres, and then retrieves the dialect value.

```python
>>> cfg = FluffConfig(overrides={"dialect": "ansi"})
>>> cfg.process_raw_file_for_config(
...     "-- sqlfluff:dialect:postgres",
...     "test.sql"
... )
>>> cfg.get("dialect")
'postgres'
```

--------------------------------

### pyproject.toml entry point for SQLFluff plugin

Source: https://docs.sqlfluff.com/en/stable/development/custom-rules.html

Defines the SQLFluff entry point in pyproject.toml so that SQLFluff can discover the plugin when installed as a Python package.

```ini
[project.entry-points.sqlfluff]

```

--------------------------------

### Run prebuild and dev server

Source: https://docs.sqlfluff.com/en/stable/development/documentation.html

Generate auto-generated reference pages first, then start the hot-reloading dev server to avoid 404s on /reference/ links.

```bash
pnpm run docs:prebuild   # generates rules, CLI, dialect, and API pages
pnpm run docs:dev        # then start the hot-reloading server
```

--------------------------------

### Entry point registration in setup.py

Source: https://docs.sqlfluff.com/en/stable/development/custom-rules.html

Registers the plugin rule with SQLFluff via the 'sqlfluff.rules' entry point. The name 'sqlfluff_example' should be changed to reflect your plugin's purpose. This information is registered on install, so re-install is needed after changes.

```python
entry_points={
    "sqlfluff.rules": [
        "sqlfluff_example = sqlfluff_plugin_example.rules.rules"
    ]
}
```

--------------------------------

### Block comment indentation example

Source: https://docs.sqlfluff.com/en/stable/configuration/layout.html

Demonstrates how block comments should be indented: starting on a new line, aligned with the following code, and with internal lines having at least the same indent as the first line.

```sql
SELECT
   /* This is a block comment starting on a new line
   which contains a newline (continuing with at least
   the same indent.
      - potentially containing greater indents
      - having no other code following it in the same line
      - and aligned with the line of code following it */
   this_column as what_we_align_the_column_to
FROM my_table
```

--------------------------------

### Starter .sqlfluff configuration

Source: https://docs.sqlfluff.com/en/stable/configuration

A minimal .sqlfluff configuration file recommended for new projects, keeping the config concise and documenting only decisions that differ from defaults.

```ini
[sqlfluff]
```

--------------------------------

### Configure alias alignment with spacing_before, align_within, and align_scope

Source: https://docs.sqlfluff.com/en/stable/configuration/layout.html

Layout configuration for aligning alias expressions in SELECT clauses. spacing_before = align requests non-default spacing before aliases, align_within = select_clause limits alignment to the next outer select clause, and align_scope = bracketed stops the outward search at brackets. This is the setup illustrated by the SQL example that follows.

```ini
# We want non-default spacing _before_ the alias expressions.
spacing_before = align
# We want to align them within the next outer select clause.
# This means for example that alias expressions within the FROM
# or JOIN clause would _not_ be aligned with them.
align_within = select_clause
# The point at which to stop searching outward for siblings, which
# in this example would likely be the boundary of a CTE. Stopping
# when we hit brackets is usually a good rule of thumb for this
# configuration.
align_scope = bracketed
```

--------------------------------

### Example .sqlfluff configuration file

Source: https://docs.sqlfluff.com/en/stable/configuration

Shows a typical .sqlfluff configuration file (also valid for setup.cfg, tox.ini, pep8.ini) with sections for general settings, indentation, templater, and Jinja-specific options. The section names use a colon delimiter, e.g., [sqlfluff:jinjacontext].

```ini
[sqlfluff]
templater = jinja
sql_file_exts = .sql,.sql.j2,.dml,.ddl

[sqlfluff:indentation]
indented_joins = False
indented_using_on = True
template_blocks_indent = False

[sqlfluff:templater]
unwrap_wrapped_queries = True

[sqlfluff:templater:jinja]
apply_dbt_builtins = True
```

--------------------------------

### Empty foo/__init__.py

Source: https://docs.sqlfluff.com/en/stable/configuration/templating/jinja.html

Empty __init__.py for the foo subpackage.

```python
# empty file
```

--------------------------------

### LT13: layout.start_of_file - Best practice

Source: https://docs.sqlfluff.com/en/stable/reference/rules/layout.html

Best practice for the layout.start_of_file rule (L050). Shows files starting with code or a comment, including a block comment and an inline comment.

```sql
^SELECT
    a
FROM foo

-- Including an initial block comment.

^/*
This is a description of my SQL code.
*/
SELECT
    a
FROM
    foo

-- Including an initial inline comment.

^--This is a description of my SQL code.
SELECT
    a
FROM
    foo
```

--------------------------------

### Create test.sql with initial SQL

Source: https://docs.sqlfluff.com/en/stable/guide/basic-usage.html

Initial SQL file with formatting issues to be linted. Contains a SELECT statement with missing spaces around the '+' operator and inconsistent capitalization.

```sql
SELECT a+b  AS foo,
c AS bar from my_table
```

--------------------------------

### LT04: layout.commas - Example

Source: https://docs.sqlfluff.com/en/stable/reference/rules/layout.html

Example SQL query showing the layout.commas rule. The rule controls comma placement in select targets and other lists.

```sql
SELECT
    SUM(
        1 + SUM(
            2 + 3
        )
    ) AS col
FROM test_table;
```

--------------------------------

### In-file configuration directives example

Source: https://docs.sqlfluff.com/en/stable/configuration

SQL file using comment-based configuration directives to set indented joins, tab space size, and keyword capitalisation for the file.

```sql
-- Set Indented Joins
-- sqlfluff:indentation:indented_joins:True

-- Set a smaller indent for this file
-- sqlfluff:indentation:tab_space_size:2

-- Set keywords to be capitalised
-- sqlfluff:rules:capitalisation.keywords:capitalisation_policy:upper

SELECT *
FROM a
JOIN b USING(c)
```

--------------------------------

### Example diff-quality output

Source: https://docs.sqlfluff.com/en/stable/usage/diff-quality.html

Example output from diff-quality showing a quality report with a violation line number. Note that diff-quality only lists line numbers, not character positions.

```bash
-------------
Diff Quality
Quality Report: sqlfluff
Diff: origin/master...HEAD, staged and unstaged changes
-------------
sql/audience_size_queries/constraints/_postcondition_check_gdpr_compliance.sql (0.0%):
sql/audience_size_queries/constraints/_postcondition_check_gdpr_compliance.sql:5: Unquoted Identifiers must be consistently upper case.
-------------
Total:   1 line
Violations: 1 line
% Quality: 0%
-------------
```

--------------------------------

### Hanging indent examples

Source: https://docs.sqlfluff.com/en/stable/configuration/layout.html

Illustrates hanging indents where subsequent lines align with the first nested element. The DECLARE statement is a neat example, but nested or mixed styles can become confusing.

```sql
-- A select statement with two hanging indents:
SELECT no_line_break_before_me,
       indented_to_match_the_first,
       1 + (a
            + b) AS another_more_complex_example
FROM my_table;

-- This TSQL example is also in essence a hanging indent:
DECLARE @prv_qtr_1st_dt DATETIME,
        @last_qtr INT,
        @last_qtr_first_mn INT,
        @last_qtr_yr INT;
```

--------------------------------

### process_inline_config usage example

Source: https://docs.sqlfluff.com/en/stable/reference/internals/config.html

Example usage of process_inline_config. It creates a FluffConfig with an ANSI dialect override, processes an inline config line to set the dialect to postgres, and then retrieves the dialect value.

```python
>>> cfg = FluffConfig(overrides={"dialect": "ansi"})
>>> cfg.process_inline_config(
...     "-- sqlfluff:dialect:postgres",
...     "test.sql"
... )
>>> cfg.get("dialect")
'postgres'
```

--------------------------------

### Create .sqlfluff configuration file

Source: https://docs.sqlfluff.com/en/stable/guide/custom-usage.html

Place this .sqlfluff file in the same directory as the SQL file to set the dialect to ansi, tab space size to 2, and keyword capitalization policy to lower. Other settings remain at their defaults.

```ini
[sqlfluff]
dialect = ansi

[sqlfluff:indentation]
tab_space_size = 2

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

```

--------------------------------

### Python format string templating example

Source: https://docs.sqlfluff.com/en/stable/guide/why.html

Shows how SQL can be templated using Python's format string syntax. The placeholder values are replaced at runtime, and SQLFluff needs dummy parameters to lint such code.

```python
"SELECT {foo} FROM {tbl}".format(foo="bar", tbl="mytable")
```

```sql
SELECT bar FROM mytable
```

--------------------------------

### AL09 Interdependence example: RF06 and CP02 fixes

Source: https://docs.sqlfluff.com/en/stable/reference/rules/aliasing.html

Shows how AL09 works with RF06 (references.quoting) and CP02 (capitalisation.identifiers) to handle self aliases with mixed quoting and casing. The example shows the original query, then after RF06, after CP02, and the final result.

```sql
-- Original Query. AL09 will not trigger because casing and
-- quoting are different. RF06 will however fix the unnecessary
-- quoting of "COL".
SELECT "COL" AS col FROM table;
-- After RF06, the query will look like this, at which point
-- CP02 will see the inconsistent capitalisation. Depending
-- on the configuration it will change one of the identifiers.
-- Let's assume the default configuration of "consistent".
SELECT COL AS col FROM table;
-- After CP02, the alias and the reference will be the same
-- and at this point AL09 can take over and remove the alias.
SELECT COL AS COL FROM table;
-- ..resulting in:
SELECT COL FROM table;
```

--------------------------------

### Configure param_style and sample values

Source: https://docs.sqlfluff.com/en/stable/configuration/templating/placeholder.html

Configuration to set the parameter style to `colon` and provide a sample value for `my_name`.

```ini
[sqlfluff:templater:placeholder]
param_style = colon
my_name = 'john'
```

--------------------------------

### segment_predicates.get_type

Source: https://docs.sqlfluff.com/en/stable/reference/internals/functional.html

Returns a function that gets the segment type.

```APIDOC
## segment_predicates.get_type

### Description
Returns a function that gets segment type.

### Method
get_type

### Signature
get_type() → Callable[[BaseSegment], str]

### Returns
- **Callable[[BaseSegment], str]** - A function that returns the type of a segment.
```

--------------------------------

### get_simple_config

Source: https://docs.sqlfluff.com/en/stable/reference/api/simple.html

Gets a config object from simple API arguments.

```APIDOC
## get_simple_config

### Description
Get a config object from simple API arguments.

### Method
Python function

### Signature
```python
get_simple_config(
    dialect=None,
    rules=None,
    exclude_rules=None,
    config_path=None
) → FluffConfig
```

### Parameters
- **dialect** (str | None) - Optional - No description provided.
- **rules** (list[str] | None) - Optional - No description provided.
- **exclude_rules** (list[str] | None) - Optional - No description provided.
- **config_path** (str | None) - Optional - No description provided.

### Returns
- **FluffConfig** - See return type above.
```

--------------------------------

### get

Source: https://docs.sqlfluff.com/en/stable/reference/internals/config.html

Retrieves a specific config value by name and section path.

```APIDOC
## `get`

### Description
Get a particular value from the config. If no default is provided, a `KeyError` will be raised if no value was found.

### Method
`get`

### Signature
```python
get(
    val,
    section="core",
    default=None
) → Any
```

### Parameters
- **val** (`str`) - Required - The name of the config value to get.
- **section** (`str | collections.abc.Iterable[str]`) - Optional - The "path" to the config value. For values in the main `[sqlfluff]` section of the config, which are stored in the `core` section of the config this can be omitted.
- **default** (`Any`) - Optional - The value to return if the config value was not found. If no default is provided, then a `KeyError` will be raised if no value was found.

### Returns
`Any` — See return type.
```

--------------------------------

### Linter.get_rulepack

Source: https://docs.sqlfluff.com/en/stable/reference/api/core.html

Gets hold of a set of rules. This method is part of the Linter class.

```APIDOC
## get_rulepack

### Description
Get hold of a set of rules.

### Method
get_rulepack

### Parameters
- **self** - Required - The instance.
- **config** (FluffConfig | None) - Optional - The configuration.

### Returns
- **RulePack** - The rule pack.
```

--------------------------------

### Switch between feature branches

Source: https://docs.sqlfluff.com/en/stable/development/git.html

Shows how to create and switch between multiple feature branches. Includes commands to start a new branch, push it, and return to a previous branch.

```bash
# Start feature1
git checkout main && git pull
git checkout -b feature1
# ... make commits ...
git push --set-upstream origin feature1

# Switch to a new branch while waiting for feedback on feature1
git checkout main && git pull
git checkout -b feature2
# ... make commits ...
git push --set-upstream origin feature2

# Come back to feature1
git checkout feature1
```

--------------------------------

### SQL example with touch spacing around commas

Source: https://docs.sqlfluff.com/en/stable/configuration/layout.html

Shows how commas can touch the preceding element while allowing newlines, using the touch option in spacing configuration.

```sql
SELECT
   col_a
   , col_b
   -- Newline present before column
   , col_c
   -- When inline, comma should still touch element before.
   , GREATEST(col_d, col_e) as col_f
FROM tbl_a
```

--------------------------------

### SQLAlchemy-style query with placeholder

Source: https://docs.sqlfluff.com/en/stable/configuration/templating/placeholder.html

Example query using SQLAlchemy's colon-style placeholder `:myid`.

```sql
SELECT * FROM table WHERE id = :myid
```

--------------------------------

### Quick Start: Lint, Fix, and Check Dialects

Source: https://docs.sqlfluff.com/en/stable/reference/cli

Shows basic SQLFluff CLI usage: lint a file, fix violations automatically, and list available dialects.

```bash
# Lint SQL files
sqlfluff lint path/to/file.sql

# Fix violations automatically
sqlfluff fix path/to/file.sql

# Check available dialects
sqlfluff dialects
```

--------------------------------

### Define dbt_utils.group_by in Python

Source: https://docs.sqlfluff.com/en/stable/configuration/templating/jinja.html

Python module defining the group_by function used in the SQL example.

```python
def group_by(n):
    return "GROUP BY 1,2"
```

--------------------------------

### SQLFluff config for Jinja templater with all options

Source: https://docs.sqlfluff.com/en/stable/configuration/templating/jinja.html

Example .sqlfluff configuration enabling the Jinja templater and setting all relevant options: apply_dbt_builtins, load_macros_from_path, loader_search_path, library_path, exclude_macros_from_path, and context/macros sections.

```ini
[sqlfluff]
templater = jinja

[sqlfluff:templater:jinja]
apply_dbt_builtins = True
load_macros_from_path = my_macros
loader_search_path = included_templates
library_path = sqlfluff_libs
exclude_macros_from_path = my_macros_exclude

[sqlfluff:templater:jinja:context]
my_list = ['a', 'b', 'c']
MY_LIST = ("d", "e", "f")
my_where_dict = {"field_1": 1, "field_2": 2}

[sqlfluff:templater:jinja:macros]
a_macro_def = {% macro my_macro(n) %}{{ n }} + {{ n * 2 }}{% endmacro %}
```

--------------------------------

### Enable placeholder templater in config

Source: https://docs.sqlfluff.com/en/stable/configuration/templating/placeholder.html

Configuration to enable the placeholder templater.

```ini
[sqlfluff]
templater = placeholder
```

--------------------------------

### SQL CTE indentation with indented_ctes = True

Source: https://docs.sqlfluff.com/en/stable/configuration/layout.html

Example of CTEs indented within the WITH clause when indented_ctes is set to True.

```sql
WITH
   some_cte AS (
      SELECT 1 FROM table1
   ),

   some_other_cte AS (
      SELECT 1 FROM table1
   )

SELECT 1 FROM some_cte
```
Tokens:
Raw