Using SQLFluff – SQLServerCentral

I thought I didn’t care about linting, and lately, I haven’t written a lot of SQL, but for the SQL I do write, I have SQLFluff to help me format it. A friend of mine is big into SQLFluff and finally talked me into installing and using it. For more information about SQLFluff itself, visit here.

Why Use SQLFluff?

SQLFluff offers several advantages:

• Cross-Database Support: Handles various SQL dialects like T-SQL, PostgreSQL, Snowflake, and BigQuery.
• Customizable Rules: Tailor linting rules to match your team’s coding standards.
• Auto-Fix Capability: Automatically corrects linting issues where possible.
• CI/CD Integration: Easily integrates with CI/CD pipelines to enforce code quality.

Installing SQLFluff

SQLFluff is distributed via pip, making installation simple. Follow these steps:

Ensure you have installed Python 3.8 or later. To verify, run:

python --version

Install SQLFluff using pip:

pip install sqlfluff

To confirm SQLFluff is installed:

sqlfluff --version

After that, I wanted the path to the python and sqlfluff to be set manually in my vscode, as you will see further down in this post. To get the paths for each, you can execute the following:

which sqlfluff
which python

Note those paths for more straightforward configuration in VS Code.

Using SQLFluff with Visual Studio Code

SQLFluff can be integrated with Visual Studio Code. You need to install the extension from dorzey.

Settings json

Once you have the extension installed, add some settings to the config (settings.json) to make it work more smoothly. To get to that file, hit Windows-P or Command-P. That takes you into the command palette, and then search for settings. Choose the settings.json file.

I put this in before the last curly brace.

"sqlfluff.executablePath": "putyourpathhere",
    "sqlfluff.format.enabled": true,
    "sqlfluff.linter.run": "onSave",
    "sqlfluff.excludeRules": ["L014"],
    "sqlfluff.suppressNotifications": false,
    "sqlfluff.linter.lintEntireProject": false,
    "sqlfluff.format.arguments": ["--FIX-EVEN-UNPARSABLE"],
    "[sql]": {
        "editor.defaultFormatter": "dorzey.vscode-sqlfluff"
    },
    "python.defaultInterpreterPath": "putyourpathhere",

Mine looks like this.

.sqlfluff file

Then, you need a .sqlfluff file with your settings for how you want the linter to behave. This file can be in the top level of the structure you are working in or whatever folder your SQL file is in. In my case, I have Postgres SQL I’ve been doing, so I’m going to drop the .sqlfluff file with those settings into that structure. Here’s the postgres version:

[sqlfluff]
dialect = postgres
templater = jinja
sql_file_exts = .sql,.sql.j2,.dml,.ddl
rules = core
exclude_rules = L031, L034
max_line_length = 150
large_file_skip_byte_limit = 30000
 
[sqlfluff:indentation]
indented_joins = False
indented_ctes = False
indented_using_on = True
indented_on_contents = False
template_blocks_indent = True
tab_space_size = 4

Here’s a version for tsql:

[sqlfluff]
dialect = tsql
templater = jinja
sql_file_exts = .sql,.sql.j2,.dml,.ddl
rules = core
 
# AL07 = aliasing.forbid
# ST06 = structure.column_order
exclude_rules = AL07, ST06
 
max_line_length = 150
large_file_skip_byte_limit = 30000
 
[sqlfluff:indentation]
indented_joins = False
indented_ctes = False
indented_using_on = True
indented_on_contents = False
template_blocks_indent = True
tab_space_size = 4
indent_unit = space
 
[sqlfluff:rules:capitalisation.identifiers]
extended_capitalisation_policy = pascal
unquoted_identifiers_policy = column_aliases
 
[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = upper
 
[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = upper
 
[sqlfluff:rules:capitalisation.types]
extended_capitalisation_policy = lower

This error occurs if the .sqlfluff file is not set in the folder where your SQL file is or in a parent folder.

Fluffing your SQL in VS Code

To accept all SQLFluff recommendations, use the command palette with Win+Shift+P or Cmd+Shift+P—type in Format Document.

This will align your file with all the settings you’ve configured in your .sqlfluff file.

Best Practices for Using SQLFluff

To maximize SQLFluff’s effectiveness, follow these best practices:

1. Define Clear Standards: Collaborate with your team to establish coding conventions for SQL.
2. Customize Rules Thoughtfully: Tailor rules to your project’s style for more accurate linting.
3. Integrate Early: Introduce SQLFluff in the development phase to catch issues sooner.
4. Review Auto-Fixes Carefully: While helpful, auto-fixes may not always align with your intent.
5. Update Regularly: New features and improved rules are frequently added to SQLFluff.

The post Using SQLFluff appeared first on sqlkitty.