Let’s talk about whitespace

If there is one part of building a linter that is going to be controversial it’s going to be whitespace (closely followed by cApiTaLiSaTiOn 😁).

More specifically, whitespace divides into three key themes:

1. Spacing: The amount of whitespace between elements on the same line.

2. Line Breaks: The choice of where within the code it is inappropriate, appropriate or even compulsory to have a line break.

3. Indentation: Given a line break, how much whitespace should precede the first code element on that line.

SQLFluff aims to be opinionated on this theme, but also configurable (see Configuring Layout). The tool will have a default viewpoint and will aim to have views on all of the important aspects of SQL layout, but if you (or your organisation) don’t like those views then we aim to allow enough configuration that you can lint in line with your views, and still use SQLFluff. For more information on how to configure rules to your own viewpoint see Configuration.

Note

This section of the docs handles the intent and reasoning behind how layout is handled by SQLFluff. For a deeper look at how this is achieved internally see Reflow Internals.

Spacing

Of the different elements of whitespace, spacing is likely the least controversial. By default, all elements are separated by a single space character. Except for very specific circumstances (see section on Aligned elements), any additional space between elements is usually unwanted and a distraction for the reader. There are however several common cases where no whitespace is more appropriate, which fall into two cases (for more details on where to configure these see Configuring layout and spacing).

1. No whitespace but a newline is allowed. This option is configured using the touch option in the spacing_* configuration settings. The most common example of this is the spacing around commas. For example SELECT a , b would be unusual and more normally be written SELECT a, b. Inserting a newline between the a and comma would not cause issues and may even be desired, for example:

   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
   

2. No whitespace and a newline is not allowed. This option is configured using the inline option in the spacing_* configuration settings. The most common example of this is spacing within the parts of qualified identifier e.g. my_schema.my_table. If a newline were present between the . and either my_schema or my_table, then the expression would not parse and so no newlines should be allowed.

Aligned elements

A special case of spacing is where elements are set to be aligned within some limits. This is not enabled by default, but can be be configured to achieve layouts like:

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

In this example, the alias expressions are all aligned with each other. To configure this, SQLFluff needs to know what elements to align and how far to search to find elements which should be aligned with each other. The configuration to achieve this layout is:

[sqlfluff:layout:type:alias_expression]
# 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

Of these configuration values, the align_scope is potentially the least obvious. The following example illustrates the impact it has.

-- With
--    align_scope = bracketed
--    align_within = select_clause

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;

-- With
--    align_scope = bracketed
--    align_within = statement

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            -- Now the FROM alias is also aligned.

-- With
--    align_scope = file
--    align_within = select_clause

WITH foo as (
   SELECT
      a,
      b,
      c        AS first_column   -- Now the aliases here are aligned
      d + e    AS second_column  -- with the outer query.
)

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

-- With
--    align_scope = file
--    align_within = statement

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

Line Breaks

When controlling line breaks, we are trying to achieve a few different things:

1. Do we have enough line breaks that line length doesn’t become excessive. Long lines are hard to read, especially given that readers may be on varying screen sizes or have multiple windows open. This is (of course) configurable, but the default is 80 characters (in line with the dbt Labs SQL style guide.)

2. Is the positioning of blank lines (i.e. lines with nothing other than whitespace on them) appropriate. There are some circumstances where a blank line is desired (e.g. between CTEs). There are others where they are not, in particular multiple blank lines, for example at the beginning of a file.

3. Where we do have line breaks, are they positioned appropriately and consistently with regards to other elements around them. This is most common when it comes to commas, and whether they should be leading (e.g. , my_column) or trailing (e.g. my_column,). In less common cases, it may also be desirable for some elements to have both a line break before and after (e.g. a set operator such as UNION).

Indentation

Lastly, given we have multiple lines of SQL, to what extent should we indent some lines to provide visual cues to the structure of that SQL. It’s important to note that SQL is not whitespace sensitive in its interpretation and that means that any principles we apply here are entirely for the benefit of humans. Your database doesn’t care.

The indentation therefore should be treated as a hint to the reader of the structure of the code. This explains the common practice within most languages that nested elements (for example the contents of a set of brackets in a function call) should be indented one step from the outer elements. It’s also convention that elements with the same level in a nested structure should have the same indentation, at least with regards to their local surroundings. As an example:

SELECT
   nested_within_select AS first_column,
   some_function(
      nested_within_function,
      also_nested_within_function
   ) AS indented_the_same_as_opening_bracket
FROM indented_the_same_as_select

Comment Indents

Note

The notes here about block comments are not implemented prior to 2.0.x. They should be coming in that release or soon after.

Comments are dealt with differently, depending on whether they’re block comments (/* like this */), which might optionally include newlines, or inline comments (-- like this) which are necessarily only on one line.

• Block comments cannot share a line with any code elements (so in effect they must start on their own new line), they cannot be followed by any code elements on the same line (and so in effect must be followed by a newline, if we are to avoid trailing whitespace). None of the lines within the block comment may have an indent less than the first line of the block comment (although additional indentation within a comment is allowed), and that first line should be aligned with the first code element following the block comment.

  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
  

• Inline comments can be on the same line as other code, but are subject to the same line-length restrictions. If they don’t fit on the same line (or if it just looks nicer) they can also be the only element on a line. In this latter case, they should be aligned with the first code element following the comment.

  SELECT
     -- This is fine
     this_column as what_we_align_to,
     another_column as something_short,  -- Is ok
     case
        -- This is aligned correctly with below
        when indented then take_care
        else try_harder
     end as the_general_guidance
  -- Even here we align with the line below
  FROM my_table
  

  Note

  When fixing issues with comment indentation, SQLFluff will attempt to keep comments in their original position but if line length concerns make this difficult, it will either abandon the fix, or move same line comments up and before the line they are currently on. This is in line with the assumption that comments on their own line refer to the elements of code which they come before, not after.

Hanging Indents

One approach to indenting nested elements is a layout called a hanging indent. In this layout, there is no line break before the first nested element, but subsequent elements are indented to match the line position of that first element. Two examples might be:

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

In some circumstances this layout can be quite neat (the DECLARE statement is a good example of this), however once indents are nested or indentation styles are mixed it can rapidly become confusing (as partially shown in the first example). Additionally, unless the leading element of the first line is very short, hanging indents use much larger indents than a traditional simple indent where a line break is used before the first element.

Hanging indents have been supported in SQLFluff up to the 1.x versions, however they will no longer by supported from 2.0.0 onwards. This is due to the ambiguity which they bring to fixing poorly formatted SQL. Take the following code:

SELECT   this_is,
badly_formatted, code_and,
   not_obvious,
      what_was,
intended FROM my_table

Given the lack of line break between SELECT and this_is, it would appear that the user is intending a hanging indent, however it is also plausible that they did not and they just forgot to add a line break between them. This ambiguity is unhelpful, both for SQLFluff as a tool, but also for people who write SQL that there two ways of indenting their SQL. Given SQLFluff aims to provide consistency in SQL layout and remove some of the burden of needing to make choices like this - and that it would be very unusual to keep only hanging indents and disable traditional ones - the only route left to consistency is to not allow hanging indents. Starting in 2.0.0, any hanging indents detected will be converted to traditional indents.

Implicit Indents

A close cousin of the hanging indent is the implicit indent. While it does look a little like a hanging indent, it’s much more consistent in its behaviour and is supported from SQLFluff 2.0.0 onwards.

An implicit indent is exactly like a normal indent, but doesn’t have to be actually taken to influence the indentation of lines after it - it just needs to be left un-closed before the end of the line. These are normally available in clauses which take the form of KEYWORD <expression>, like WHERE clauses or CASE expressions.

-- This WHERE clause here takes advantage of an implicit indent.
SELECT *
FROM my_table
WHERE condition_a
   AND condition_b;

-- With implicit indents disabled (which is currently the
-- default), the above formulation is not allowed, and instead
-- there should be a newline immediately after `WHERE` (which
-- is the location of the _implicit_ indent).
SELECT *
FROM my_table
WHERE
   condition_a
   AND condition_b;

When addressing both indentation and line-length, implicit indents allow a slightly more compact layout, without significant drawbacks in legibility. They also enable a style much closer to some established style guides.

They are however not recommended by many of the major style guides at time of writing (including the dbt Labs SQL style guide and the Mozilla SQL style guide), and so are disabled by default. To enable them, set the allow_implicit_indents flag in sqluff.indentation to True.

Templated Indents

SQLFluff supports templated elements in code, such as those offered by jinja2 (or dbt which relies on it). For simple cases, templated elements are handled as you would expect by introducing additional indents into the layout.

SELECT
   a,
   {% for n in ['b', 'c', 'd'] %}
      -- This section is indented relative to 'a' because
      -- it is inside a jinja for loop.
      {{ n }},
   {% endfor %}
   e
FROM my_table

This functionality can be turned off if you wish using the template_blocks_indent option in your Configuration.

It’s important to note here, that SQLFluff lints the code after it has been rendered, and so only has access to code which is still present after that process.

SELECT
   a,
   {% if False %}
   -- This section of the code cannot be linted because
   -- it is never rendered due to the `if False` condition.
   my    + poorly
      +   spaced - and/indented AS    section_of_code
   {% endif %}
   e
FROM my_table

More complex templated cases are usually characterised by templated tags cutting across the parse tree. This more formally is where the opening and closing tags of a templated section exist at different levels in the parsed structure. Starting in version 2.x, these will be treated differently (Prior to version 2.x, situations like this were sometimes handled inconsistently or incorrectly).

Indentation should act as a visual cue to the structure of the written SQL, and as such, the most important thing is that template tags belonging to the same block structure use the same indentation. In the example below, this is the opening and closing elements of the second if statement. If treated as a simple case, these tags would have different indents, because they are at different levels of the parse tree and so clearly there is a conflict to be resolved.

The view SQLFluff takes on how to resolve this conflict is to pull all of the tags in this section down to the indent of the least indented (in the example below that would be the closing endif tag). This is similar to the treatment of C Preprocessor Directives, which are treated somewhat as being outside the structure of the rest of the file. In these cases, the content is also not further indented as in the simple case because it makes it harder to line up elements within the affected section and outside (in the example below the SELECT and FROM are a good illustration).

SELECT
   a,
   {% if True %}
      -- This is a simple case. The opening and closing tag are
      -- both at the same level within the SELECT clause.
      simple_case AS example,
   {% endif %}
   b,
{% if True %}
   -- This is a complex case. The opening tag is within the SELECT
   -- clause, but the closing tag is outside the statement
   -- entirely.
   complex_case AS example
FROM table_option_one
{% else %}
   complex_case_two AS example
FROM table_option_two
{% endif %}

Configuring Layout

Configuration for layout is spread across three places:

1. Indent behavior for particular dialect elements is controlled by the parser. This is because in the background SQLFluff inserts Indent and Dedent tokens into the parse tree where those things are expected. For more detail see Configuring indent locations.

2. Configuration for the spacing and line position of particular types of element (such as commas or operators) is set in the layout section of the config file. For more detail see Configuring layout and spacing.

3. Some elements of layout are still controlled by rules directly. These are usually very specific cases, see Rules Reference for more details.

Configuring indent locations

One of the key areas for this is the indentation of the JOIN expression, which we’ll use as an example.

Semantically, a JOIN expression is part of the FROM expression and therefore would be expected to be indented. However according to many of the most common SQL style guides (including the dbt Labs SQL style guide and the Mozilla SQL style guide) the JOIN keyword is expected to at the same indent as the FROM keyword. By default, SQLFluff sides with the current consensus, which is to not indent the JOIN keyword, however this is one element which is configurable.

By setting values in the sqlfluff:indentation section of your config file you can control how this is parsed.

For example, the default indentation would be as follows:

SELECT
   a,
   b
FROM my_table
JOIN another_table
   ON
      condition1
      AND condition2

By setting your config file to:

[sqlfluff:indentation]
indented_joins = True

Then the expected indentation will be:

SELECT
   a,
   b
FROM my_table
   JOIN another_table
      ON
         condition1
         AND condition2

There is a similar indented_using_on config (defaulted to True) which can be set to False to prevent the USING or ON clause from being indented, in which case the original SQL would become:

SELECT
   a,
   b
FROM my_table
JOIN another_table
ON
   condition1
   AND condition2

It’s worth noting at this point, that for some users, the additional line break after ON is unexpected, and this is a good example of an implicit indent. By setting your config to:

[sqlfluff:indentation]
indented_using_on = False
allow_implicit_indents = True

Then the expected indentation will be:

SELECT
   a,
   b
FROM my_table
JOIN another_table
ON condition1
   AND condition2

There is also a similar indented_on_contents config (defaulted to True) which can be set to False to align any AND subsections of an ON block with each other. If set to False (assuming implicit indents are still enabled) the original SQL would become:

SELECT
   a,
   b
FROM my_table
JOIN another_table
   ON condition1
   AND condition2

These can also be combined, so if indented_using_on config is set to False, indented_on_contents is also set to False, and allow_implicit_indents is set tot True then the SQL would become:

SELECT
   a,
   b
FROM my_table
JOIN another_table
ON condition1
AND condition2

There is also a similar indented_ctes config (defaulted to False) which can be set to True to enforce CTEs to be indented within the WITH clause:

WITH
   some_cte AS (
      SELECT 1 FROM table1
   ),

   some_other_cte AS (
      SELECT 1 FROM table1
   )

SELECT 1 FROM some_cte

There is also a similar indented_then config (defaulted to True) which can be set to False to allow THEN without an indent after WHEN:

SELECT
   a,
   CASE
      WHEN b >= 42 THEN
         1
      ELSE 0
   END AS c
FROM some_table

By default, SQLFluff aims to follow the most common approach to indentation. However, if you have other versions of indentation which are supported by published style guides, then please submit an issue on GitHub to have that variation supported by SQLFluff.

Configuring layout and spacing

The [sqlfluff:layout] section of the config controls the treatment of spacing and line breaks across all rules. The syntax of this section is very expressive; however in normal use, only very small alterations should be necessary from the Default Configuration.

The syntax of the section headings here select by type, which corresponds to the type defined in the dialect. For example the following section applies to elements of the type comma, i.e. ,.

[sqlfluff:layout:type:comma]
spacing_before = touch
line_position = trailing

Within these configurable sections there are a few key elements which are available:

• Spacing Elements: spacing_before, spacing_after and spacing_within. For each of these options, there are a few possible settings:

  • The default spacing for all elements is single unless otherwise specified. In this state, elements will be spaced with a single space character unless there is a line break between them.

  • The value of touch allows line breaks, but if no line break is present, then no space should be present. A great example of this is the spacing before commas (as shown in the config above), where line breaks may be allowed, but if not they should touch the element before.

  • Both of the above can be qualified with the :inline modifier - which prevents newlines within the segment. This is best illustrated by the spacing found in a qualified identifier like my_schema.my_table which uses touch:inline or other clauses where we want to force some elements to be on the same line.

• Line Position: set using the line_position option. By default this is unset, which implies no particular line position requirements. The available options are:

  • trailing and leading, which are most common in the placement of commas. Both of these settings also allow the option of a comma on its own on a line, or in the middle of a line, but if there is a line break on either side then they make sure it’s on the correct side. By default we assume trailing commas, but if you (or your organisation) have settled on leading commas then you should add the following section to your config:

    [sqlfluff:layout:type:comma]
    line_position = leading
    

  • alone, which means if there is a line break on either side, then there must be a line break on both sides (i.e. that it should be the only thing on that line.

  • All of the above options can be qualified with the :strict modifier - which prevents the inline case. For example:

    -- Setting line_position to just `alone`
    -- within [sqlfluff:layout:type:set_operator]
    -- would not allow:
    SELECT a
    UNION SELECT b;
    -- ...or...
    SELECT a UNION
    SELECT b;
    -- but *would* allow both of the following:
    SELECT a UNION SELECT b;
    SELECT a
    UNION
    SELECT b;
    
    -- However the default is set to `alone:strict`
    -- then the *only* acceptable configuration is:
    SELECT a
    UNION
    SELECT b;