.. _layoutref: 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: #. **Spacing**: The amount of whitespace between elements on the same line. #. **Line Breaks**: The choice of where within the code it is inappropriate, appropriate or even compulsory to have a line break. #. **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 :ref:`layoutconfig`). 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 :ref:`config`. .. 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 :ref:`reflowinternals`. 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 :ref:`alignedelements`), 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 :ref:`layoutspacingconfig`). #. *No whitespace but a newline is allowed.* This option is configured using the :code:`touch` option in the :code:`spacing_*` configuration settings. The most common example of this is the spacing around commas. For example :code:`SELECT a , b` would be unusual and more normally be written :code:`SELECT a, b`. Inserting a newline between the :code:`a` and comma would not cause issues and may even be desired, for example: .. code-block:: 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 #. *No whitespace and a newline is not allowed.* This option is configured using the :code:`inline` option in the :code:`spacing_*` configuration settings. The most common example of this is spacing within the parts of qualified identifier e.g. :code:`my_schema.my_table`. If a newline were present between the :code:`.` and either :code:`my_schema` or :code:`my_table`, then the expression would not parse and so no newlines should be allowed. .. _alignedelements: 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: .. code-block:: sql 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: .. code-block:: ini [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 :code:`align_scope` is potentially the least obvious. The following example illustrates the impact it has. .. code-block:: sql -- 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: #. 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`_.) #. 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. #. 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. :code:`, my_column`) or *trailing* (e.g. :code:`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: .. code-block:: sql 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 (:code:`/* like this */`), which might optionally include newlines, or *inline* comments (:code:`-- 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. .. code-block:: 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 * *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. .. code-block:: sql 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*. .. _hangingindents: 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: .. code-block:: 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; In some circumstances this layout can be quite neat (the :code:`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: .. code-block:: sql SELECT this_is, badly_formatted, code_and, not_obvious, what_was, intended FROM my_table Given the lack of line break between :code:`SELECT` and :code:`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. .. _templatedindents: 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. .. code-block:: SQL+Jinja 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 :code:`template_blocks_indent` option in your :ref:`config`. 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. .. code-block:: SQL+Jinja 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 :code:`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 :code:`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 :code:`SELECT` and :code:`FROM` are a good illustration). .. code-block:: SQL+Jinja 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 %} .. _layoutconfig: Configuring Layout ------------------ Configuration for layout is spread across three places: #. Indent behavior for particular dialect elements is controlled by the parser. This is because in the background SQLFluff inserts :code:`Indent` and :code:`Dedent` tokens into the parse tree where those things are expected. For more detail see :ref:`layoutindentconfig`. #. Configuration for the spacing and line position of particular types of element (such as commas or operators) is set in the :code:`layout` section of the config file. For more detail see :ref:`layoutspacingconfig`. #. Some elements of layout are still controlled by rules directly. These are usually very specific cases, see :ref:`ruleref` for more details. .. _layoutindentconfig: Configuring indent locations ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ One of the key areas for this is the indentation of the :code:`JOIN` expression, which we'll use as an example. Semantically, a :code:`JOIN` expression is part of the :code:`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 :code:`JOIN` keyword is expected to at the same indent as the :code:`FROM` keyword. By default, *SQLFluff* sides with the current consensus, which is to *not* indent the :code:`JOIN` keyword, however this is one element which is configurable. By setting values in the :code:`sqlfluff:indentation` section of your config file you can control how this is parsed. For example, the default indentation would be as follows: .. code-block:: sql SELECT a, b FROM my_table JOIN another_table ON condition1 AND condition2 By setting your config file to: .. code-block:: cfg [sqlfluff:indentation] indented_joins = True Then the expected indentation will be: .. code-block:: sql SELECT a, b FROM my_table JOIN another_table ON condition1 AND condition2 There is a similar :code:`indented_using_on` config (defaulted to :code:`True`) which can be set to :code:`False` to prevent the :code:`USING` or :code:`ON` clause from being indented, in which case the original SQL would become: .. code-block:: sql SELECT a, b FROM my_table JOIN another_table ON condition1 AND condition2 There is also a similar :code:`indented_on_contents` config (defaulted to :code:`True`) which can be set to :code:`False` to align any :code:`AND` subsections of an :code:`ON` block with each other. If set to :code:`False` the original SQL would become: .. code-block:: sql SELECT a, b FROM my_table JOIN another_table ON condition1 AND condition2 These can also be combined, so if :code:`indented_using_on` config is set to :code:`False`, and :code:`indented_on_contents` is also set to :code:`False` then the SQL would become: .. code-block:: sql SELECT a, b FROM my_table JOIN another_table ON condition1 AND condition2 There is also a similar :code:`indented_ctes` config (defaulted to :code:`False`) which can be set to :code:`True` to enforce CTEs to be indented within the :code:`WITH` clause: .. code-block:: sql WITH some_cte AS ( SELECT 1 FROM table1 ), some_other_cte AS ( SELECT 1 FROM table1 ) SELECT 1 FROM some_cte 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*. .. _layoutspacingconfig: Configuring layout and spacing ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ The :code:`[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 :ref:`defaultconfig`. The syntax of the section headings here select by *type*, which corresponds to the :code:`type` defined in the dialect. For example the following section applies to elements of the *type* :code:`comma`, i.e. :code:`,`. .. code-block:: cfg [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**: :code:`spacing_before`, :code:`spacing_after` and :code:`spacing_within`. For each of these options, there are a few possible settings: * The default spacing for all elements is :code:`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 :code:`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. * The value of :code:`inline` is effectively the same as :code:`touch` but in addition, no line breaks are allowed. This is best illustrated by the spacing found in a qualified identifier like :code:`my_schema.my_table`. * **Line Position**: set using the :code:`line_position` option. By default this is unset, which implies no particular line position requirements. The available options are: * :code:`trailing` and :code:`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: .. code-block:: cfg [sqlfluff:layout:type:comma] line_position = leading * :code:`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 :code:`:strict` modifier - which prevents the *inline* case. For example: .. code-block:: sql -- 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; .. _`C Preprocessor Directives`: https://www.cprogramming.com/reference/preprocessor/ .. _`dbt Labs SQL style guide`: https://github.com/dbt-labs/corp/blob/main/dbt_style_guide.md .. _`Mozilla SQL style guide`: https://docs.telemetry.mozilla.org/concepts/sql_style.html#joins