The hardware and bandwidth for this mirror is donated by dogado GmbH, the Webhosting and Full Service-Cloud Provider. Check out our Wordpress Tutorial.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]dogado.de.

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")

These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.
Health stats visible at Monitor.