Wrapper to the 'SQL' Linter and Formatter 'sqlfluff'

An R interface to the 'Python' 'sqlfluff' 'SQL' linter and formatter via the 'reticulate' package. Enables linting, fixing, and parsing of 'SQL' queries with support for multiple dialects. Includes special handling for 'glue' 'SQL' syntax with curly-brace placeholders.

sqlfluffr

sqlfluffr is an R wrapper around SQLFluff, the popular SQL linter and formatter. Lint, fix, and parse SQL directly from R with support for multiple dialects and glue::glue_sql() syntax.

Installation

Install from CRAN:

install.packages("sqlfluffr")

Or from GitHub:

remotes::install_github("brendensm/sqlfluffr")

Then run the one-time Python setup:

library(sqlfluffr)
sqlf_install()

This creates a dedicated virtual environment and installs Python and sqlfluff via reticulate. You only need to run sqlf_install() once.

Basic usage

Lint

Check SQL for style violations:

sqlf_lint(sql = "SELECT  a,b from t where x=1\n")
#> # sqlf_lint_results: 4 violations
#>   line_no line_pos code description
#>   <int>   <int>    <chr> <chr>
#> 1 1       8        LT01  Expected single whitespace...
#> 2 1       10       LT04  Keywords must be consistently...
#> ...

Fix

Auto-fix style issues:

sqlf_fix(sql = "SELECT  a,b from t where x=1\n")
#> SELECT
#>     a,
#>     b
#> FROM t
#> WHERE x = 1

Parse

Get the syntax tree:

sqlf_parse(sql = "SELECT 1\n")

Lint or fix a file

All functions accept a file argument:

sqlf_lint(file = "query.sql")
sqlf_fix(file = "query.sql", overwrite = TRUE)  # overwrites the file

Dialects

Specify a SQL dialect with the dialect argument:

sqlf_lint(sql = "SELECT TOP 10 * FROM t\n", dialect = "tsql")

See all available dialects:

sqlf_dialects()

Project configuration

Instead of passing dialect and other options on every call, write a .sqlfluff config file for the project:

sqlf_config(dialect = "postgres", max_line_length = 120)

All subsequent sqlf_lint(), sqlf_fix(), and sqlf_parse() calls in that directory will use those settings automatically.

You can replace an existing config file with the argument overwrite, or edit the config file manually:

sqlf_config_edit()

glue_sql() support

SQL containing {var} placeholders from glue::glue_sql() would normally cause parsing errors. Pass glue = TRUE to handle them:

sql <- "SELECT {`col`} FROM {`tbl`} WHERE id = {id}\n"

sqlf_lint(sql = sql, glue = TRUE)
sqlf_fix(sql = sql, glue = TRUE)

To enable glue support project-wide, include it in the config:

sqlf_config(dialect = "postgres", glue = TRUE)

Exploring rules

List all available linting rules:

sqlf_rules()

Apply or exclude specific rules:

sqlf_lint(sql = "SELECT a FROM t\n", rules = c("LT01", "LT02"))
sqlf_lint(sql = "SELECT a FROM t\n", exclude_rules = "AM01")