The linter will analyze the code statically whereas the debugger does it dynamically. This means that the debugger will analyze the code at runtime while the linter will focus on the project source files the rest of the time. That is, when the code is not executed.
For example, the linter will detect :
- variables that do not exist
- unused variables
- double declarations of variables, functions, etc...
- bad organization of the code
- the non-respect of the good practices of writing code
- syntax errors
Fluff is an extensible and modular linter designed to help you write good SQL and catch errors and bad SQL before it hits your database.
How to use install it ?
If you are using a virtual environment using Tailer SDK, you may prefer launch your source command before :
source /Users/FirstNameLastName/.virtualenvs/[tailer/jarvis]env/bin/activate
After checking your python and your pip version (three or not), you can lauch the install :
pip install sqlfluff
How to use it ?
Using Tailer SDK, here the command line :
sqlfluff fix --dialect bigquery --exclude-rules L034 file.sql
- argv[0] : sqlfluff is the word to call the command
- argv[1] : fix starts the process of searching and replacing indentation errors in the SQL
- argv[2] : -- dialect bigquery allows us to select the SQL dialect wanted ! You have ANSI, bigquery, hive, mySQL, postgreSQL, SnowFlake, SQLite and more.
- argv[3] --exclude-rules L034 allows us to exclude certains rules like this one switching the order of rows (here the list of rules)
- argv[4] your file to lint with the path !
For a deeper understanding, take a look at the very good documentation from the developers here
Example :
As you can see, it's possible to just take a look to your indentation ! You can also skip some rules with rule option from the command line.
The result :
On the left, the sql file before linting and on the right after it !
Why is this a good practice?
It will try to force the developer to correct as many potential errors as possible before compiling and/or executing and to encourage him to write better code, beautiful code.
In the age of collaborative work and high speed internet, we no longer want to rewrite the same applications over and over again. We want the code produced to work, of course, but it must be clean, readable, maintainable, repeatable, and improvable. We want to be able to capitalize on the knowledge of the developer and on the code produced.
How to use SQLFlyff with VSCode
The developers around the module have created an extension on VSCode :
Here the steps to install the add-on :
- install sqlfluff extension on VSCode
- use the install as seen on the first part of the article on your virtual environment or directly in your bin/bash files.
pip install sqlfluff
- get the path of where the packages are installed
which sqlfluff
- Copy the path on the extension settings of VSCode in the executable path
At this step now, you should be able to use sqlfluff module on SQL files. You need to open a script SQL, use command + A to select the text and use option + shift + F to refactor the code with the sqlfluff add-on !
However, you can't choose the rules used or the dialect. You need to had a .sqlfluff in your directory. For example, I added the file before the directory of git projects :
Applied to BigQuery and some Rules used in FashionData, here the the file content :
[sqlfluff]
verbose = 0
nocolor = False
dialect = bigquery
templater = jinja
rules = None
exclude_rules = L027,L031,L034
recurse = 0
output_line_length = 80
runaway_limit = 10
ignore_templated_areas = True
encoding = autodetect
# Comma separated list of file extensions to lint.
# NB: This config will only apply in the root folder.
sql_file_exts = .sql,.sql.j2,.dml,.ddl
[sqlfluff:indentation]
indented_joins = False
indented_using_on = True
template_blocks_indent = True
[sqlfluff:templater]
unwrap_wrapped_queries = True
[sqlfluff:templater:jinja]
apply_dbt_builtins = True
[sqlfluff:templater:jinja:macros]
# Macros provided as builtins for dbt projects
dbt_ref = {% macro ref(model_ref) %}{{model_ref}}{% endmacro %}
dbt_source = {% macro source(source_name, table) %}{{source_name}}_{{table}}{% endmacro %}
dbt_config = {% macro config() %}{% for k in kwargs %}{% endfor %}{% endmacro %}
dbt_var = {% macro var(variable, default='') %}item{% endmacro %}
dbt_is_incremental = {% macro is_incremental() %}True{% endmacro %}
# Some rules can be configured directly from the config common to other rules.
[sqlfluff:rules]
tab_space_size = 4
max_line_length = 80
indent_unit = space
comma_style = trailing
allow_scalar = True
single_table_references = consistent
unquoted_identifiers_policy = all
# Some rules have their own specific config.
[sqlfluff:rules:L007] # Keywords
operator_new_lines = after
[sqlfluff:rules:L010] # Keywords
capitalisation_policy = consistent
[sqlfluff:rules:L011] # Aliasing
aliasing = explicit
[sqlfluff:rules:L012] # Aliasing
aliasing = explicit
[sqlfluff:rules:L014] # Unquoted identifiers
extended_capitalisation_policy = consistent
[sqlfluff:rules:L016]
ignore_comment_lines = False
[sqlfluff:rules:L026]
force_enable = False
[sqlfluff:rules:L028]
force_enable = False
[sqlfluff:rules:L029] # Keyword identifiers
unquoted_identifiers_policy = aliases
[sqlfluff:rules:L030] # Function names
capitalisation_policy = consistent
[sqlfluff:rules:L038]
select_clause_trailing_comma = forbid
[sqlfluff:rules:L040] # Null & Boolean Literals
capitalisation_policy = consistent
[sqlfluff:rules:L042]
# By default, allow subqueries in from clauses, but not join clauses.
forbid_subquery_in = join
[sqlfluff:rules:L047] # Consistent syntax to count all rows
prefer_count_1 = False
prefer_count_0 = False
We excluded some rules :
exclude_rules = L027,L031,L034
These rules are less compatible with the way we work with DDL and configuration deployment. Linked the list of rules.
L027 : References should be qualified if select has more than one referenced table/view.
L031 : Avoid table aliases in from clauses and join conditions.
L034 : Use wildcards then simple targets before calculations and aggregates in select statements.
After all the steps, restart VSCode and try to apply the SQLFluff module to one of your SQL files with command + A to select the text and use option + shift + F to refactor the code.
Have some fun with this useful Linter !