Setting Configuration

SQLFluff accepts configuration either through the command line or through configuration files. There is rough parity between the two approaches with the exception that templating configuration must be done via a file, because it otherwise gets slightly complicated.

For details of what’s available on the command line check out the CLI Reference.

Configuration Files

For file based configuration SQLFluff will look for the following files in order. Later files will (if found) will be used to overwrite any values read from earlier files.

  • setup.cfg

  • tox.ini

  • pep8.ini

  • .sqlfluff

  • pyproject.toml

Within these files, the first four will be read like a cfg file, and SQLFluff will look for sections which start with sqlfluff, and where subsections are delimited by a semicolon. For example the jinjacontext section will be indicated in the section started with [sqlfluff:jinjacontext].

For example, a snippet from a .sqlfluff file (as well as any of the supported cfg file types):

[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

For the pyproject.toml file, all valid sections start with tool.sqlfluff and subsections are delimited by a dot. For example the jinjacontext section will be indicated in the section started with [tool.sqlfluff.jinjacontext].

For example, a snippet from a pyproject.toml file:

[tool.sqlfluff.core]
templater = "jinja"
sql_file_exts = ".sql,.sql.j2,.dml,.ddl"

[tool.sqlfluff.indentation]
indented_joins = false
indented_using_on = true
template_blocks_indent = false

[tool.sqlfluff.templater]
unwrap_wrapped_queries = true

[tool.sqlfluff.templater.jinja]
apply_dbt_builtins = true

# For rule specific configuration, use dots between the names exactly
# as you would in .sqlfluff. In the background, SQLFluff will unpack the
# configuration paths accordingly.
[tool.sqlfluff.rules.capitalisation.keywords]
capitalisation_policy = "upper"

New Project Configuration

When setting up a new project with SQLFluff, we recommend keeping your configuration file fairly minimal. The config file should act as a form of documentation for your team i.e. a record of what decisions you’ve made which govern how your format your SQL. By having a more concise config file, and only defining config settings where they differ from the defaults - you are more clearly stating to your team what choices you’ve made.

However, there are also a few places where the default configuration is designed more for existing projects, rather than fresh projects, and so there is an opportunity to be a little stricter than you might otherwise be with an existing codebase.

Here is a simple configuration file which would be suitable for a starter project:

[sqlfluff]

# Supported dialects https://docs.sqlfluff.com/en/stable/perma/dialects.html
# Or run 'sqlfluff dialects'
dialect = snowflake

# One of [raw|jinja|python|placeholder]
templater = jinja

# Comma separated list of rules to exclude, or None
# See https://docs.sqlfluff.com/en/stable/perma/rule_disabling.html
# AM04 (ambiguous.column_count) and ST06 (structure.column_order) are
# two of the more controversial rules included to illustrate usage.
exclude_rules = ambiguous.column_count, structure.column_order

# The standard max_line_length is 80 in line with the convention of
# other tools and several style guides. Many projects however prefer
# something a little longer.
# Set to zero or negative to disable checks.
max_line_length = 120

# CPU processes to use while linting.
# The default is "single threaded" to allow easy debugging, but this
# is often undesirable at scale.
# If positive, just implies number of processes.
# If negative or zero, implies number_of_cpus - specified_number.
# e.g. -1 means use all processors but one. 0 means all cpus.
processes = -1

# If using the dbt templater, we recommend setting the project dir.
[sqlfluff:templater:dbt]
project_dir = ./

[sqlfluff:indentation]
# While implicit indents are not enabled by default. Many of the
# SQLFluff maintainers do use them in their projects.
allow_implicit_indents = True

[sqlfluff:rules:aliasing.length]
min_alias_length = 3

# The default configuration for capitalisation rules is "consistent"
# which will auto-detect the setting from the rest of the file. This
# is less desirable in a new project and you may find this (slightly
# more strict) setting more useful.
# Typically we find users rely on syntax highlighting rather than
# capitalisation to distinguish between keywords and identifiers.
# Clearly, if your organisation has already settled on uppercase
# formatting for any of these syntax elements then set them to "upper".
# See https://stackoverflow.com/questions/608196/why-should-i-capitalize-my-sql-keywords-is-there-a-good-reason
[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = lower
[sqlfluff:rules:capitalisation.identifiers]
extended_capitalisation_policy = lower
[sqlfluff:rules:capitalisation.functions]
extended_capitalisation_policy = lower
[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = lower
[sqlfluff:rules:capitalisation.types]
extended_capitalisation_policy = lower

# The default configuration for the not equal convention rule is "consistent"
# which will auto-detect the setting from the rest of the file. This
# is less desirable in a new project and you may find this (slightly
# more strict) setting more useful.
[sqlfluff:rules:convention.not_equal]
# Default to preferring the "c_style" (i.e. `!=`)
preferred_not_equal_style = c_style

Nesting

SQLFluff uses nesting in its configuration files, with files closer overriding (or patching, if you will) values from other files. That means you’ll end up with a final config which will be a patchwork of all the values from the config files loaded up to that path. The exception to this is the value for templater, which cannot be set in config files in subdirectories of the working directory. You don’t need any config files to be present to make SQLFluff work. If you do want to override any values though SQLFluff will use files in the following locations in order, with values from later steps overriding those from earlier:

  1. […and this one doesn’t really count] There’s a default config as part of the SQLFluff package. You can find this below, in the Default Configuration section.

  2. It will look in the user’s os-specific app config directory. On macOS and Unix this is ~/.config/sqlfluff, Windows is <home>\AppData\Local\sqlfluff\sqlfluff, for any of the filenames above in the main Setting Configuration section. If multiple are present, they will patch/override each other in the order above.

  3. It will look for the same files in the user’s home directory (~).

  4. [if the current working directory is a subdirectory of the user’s home directory (~)] It will look for the same files in all directories between the user’s home directory (~), and the current working directory.

  5. It will look for the same files in the current working directory.

  6. [if parsing a file in a subdirectory of the current working directory] It will look for the same files in every subdirectory between the current working dir and the file directory.

  7. It will look for the same files in the directory containing the file being linted.

This whole structure leads to efficient configuration, in particular in projects which utilise a lot of complicated templating.

In-File Configuration Directives

In addition to configuration files mentioned above, SQLFluff also supports comment based configuration switching in files. This allows specific SQL file to modify a default configuration if they have specific needs.

When used, these apply to the whole file, and are parsed from the file in an initial step before the rest of the file is properly parsed. This means they can be used for both rule configuration and also for parsing configuration.

To use these, the syntax must start as an inline sql comment beginning with sqlfluff (i.e. -- sqlfluff). The line is then interpreted as a colon-separated address of the configuration value you wish to set. A few common examples are shown below:

-- 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)

We recommend only using this configuration approach for configuration that applies to one file in isolation. For configuration changes for areas of a project or for whole projects we recommend Nesting of configuration files.

This syntax is very similar to the method for Ignoring individual lines.