# Layout & Whitespace Configuration

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 sqlfluff.utils.reflow: Whitespace Reflow API.

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

## Templating and alignment coordinate space

When using templating (e.g. Jinja), alignment is for human readability and stable diffs. SQLFluff aligns based on the source (visible) positions whenever templated (non-literal) segments are involved in the alignment scope. This prevents excessive padding caused by the rendered output being longer than the source template. If all segments are literal (non-templated), alignment uses the regular templated working positions.

Example (Jinja templating, align alias expressions within a select clause):

    select
        {{ "longtemplated" }} as test_key,
        b                     as b_col

The alignment above is computed against the source text so that both lines line up visually in the editor, regardless of the rendered length of `{{ "longtemplated" }}`.

## Advanced: coordinate space override

You can optionally force the coordinate space either:

1. via the alignment constraint suffix (available for spacing_before and spacing_after), or

2. via the alignment_coordinate_space key in layout config for the target type.

    [sqlfluff:layout:type:alias_expression]
    spacing_before = align:alias_expression:select_clause:bracketed:source

Alternatively, the equivalent can be configured more declaratively:

    [sqlfluff:layout:type:alias_expression]
    spacing_before = align
    align_within = select_clause
    align_scope = bracketed
    alignment_coordinate_space = source

Supported values are `source` and `templated`. In most cases, `source` is the recommended choice for readability.

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

- Keyword Line Position: set using the `keyword_line_position` option.

    By default for most clauses this is unset, which implies no particular keyword line position requirements. The available options are:

    - `leading` and `alone`, which are most common in the placement of keywords. Both of these settings also allow the option of a keyword to end on a line. By default we assume leading `WHERE` keywords, but if you (or your organisation) have settled on alone `WHERE` keywords then you should add the following section to your config:

          [sqlfluff:layout:type:where_clause]
          keyword_line_position = alone

    - `trailing`, which means there should be a line break after the keyword. This is fairly uncommon but may apply to the `ON` keyword after a join. If you (or your organisation) have settled on trailing `ON` keywords then you should add the following section to your config:

          [sqlfluff:layout:type:join_on_condition]
          keyword_line_position = trailing

    - The keyword positioning is valid across a number of different clauses. For example, to apply the `leading` directive to the `PARTITION BY` clause you would add the following configuration:

          [sqlfluff:layout:type:partitionby_clause]
          keyword_line_position = leading

    - If you (or your organisation) would prefer to unset the Default Configuration of some options, you may clear it by setting the configuration to `none`.

          [sqlfluff:layout:type:where_clause]
          keyword_line_position = none

          [sqlfluff:layout:type:orderby_clause]
          keyword_line_position = none

          [sqlfluff:layout:type:groupby_clause]
          keyword_line_position = none

          [sqlfluff:layout:type:having_clause]
          keyword_line_position = none

- Exclusions: The `keyword_line_position_exclusions` option allows you to

    exclude specific types of segments from the `keyword_line_position` rule. This is useful when certain segments, such as window specifications or aggregate functions, should not follow the same keyword line position rules as other segments in the same clause.

    For example, to exclude window specifications from the `ORDER BY` clause’s keyword line position rule, you can configure it as follows:

        [sqlfluff:layout:type:orderby_clause]
        keyword_line_position = leading
        keyword_line_position_exclusions = window_specification

    This configuration ensures that the ORDER BY clause follows the leading rule, except for window specifications, which are allowed to remain inline.

    You can also specify multiple exclusions by separating them with commas:

        [sqlfluff:layout:type:orderby_clause]
        keyword_line_position = leading
        keyword_line_position_exclusions = window_specification, aggregate_order_by

    In this case, both window specifications and aggregate functions with ORDER BY clauses are excluded from the leading rule.

    Example Usage:

    With the above configuration, the following SQL would pass:

        SELECT
        a,
        b,
        ROW_NUMBER() OVER (PARTITION BY c ORDER BY d) AS e,
        STRING_AGG(a ORDER BY b, c)
        FROM f
        JOIN g
        ON g.h = f.h

    However, the following SQL would fail because the outer ORDER BY clause does not follow the leading rule:

        SELECT
        a,
        b,
        ROW_NUMBER() OVER (PARTITION BY c ORDER BY d) AS e,
        STRING_AGG(a ORDER BY b, c)
        FROM f
        JOIN g
        ON g.h = f.h ORDER BY a

    The corrected version would be:

        SELECT
        a,
        b,
        ROW_NUMBER() OVER (PARTITION BY c ORDER BY d) AS e,
        STRING_AGG(a ORDER BY b, c)
        FROM f
        JOIN g
        ON g.h = f.h
        ORDER BY a