SQLFluffを使ってみた。 - シナプス技術者ブログ

シナプスの技術部システム開発課の小園です。
SQLFluffを使ってみました。

SQLFluff とは？
関連サイト
サポートするSQL
ルール
設定
CLI（コマンドラインインタフェース）から使う
VSCodeから使う
所感

SQLFluff とは？

SQLFluff はPython3以降で動く、SQL構文のLinter（静的解析ツール）です。
SQL構文に対して lint（静的解析）や fix（静的解析内容に基づく修正）ができます。

サポートするSQL

SQLは標準規格（ANSI,ISO）があるのですが、歴史的経緯でデータベースごとに方言があります。
サポートするSQLは下記リンク先より参照してください。
Dialects Reference — SQLFluff 2.2.0 documentation

今回の検証は標準規格であるANSIで行っています。

ルール

SQLFluff には50個を超えるルールが存在しています。
ルールの一覧は下記リンク先ページより参照してください。
Rules Reference — SQLFluff 2.2.0 documentation

ページには、ルールの一覧があり、そのルール違反の例とベストプラクティスの例が記載されています。
SQLのlint時に検出されたルール名が表示されますので、そこからどのように修正すべきかを確認できます。
実際には、fixコマンドでSQLFluffにお任せで修正してしまう方が早いです。

設定

SQLFluff では、SQL方言の選択やルールの有効化、無効化の設定、ルール毎の設定を設定ファイルに記載することができます。
CLIで一部の設定を実行時に設定することもできますが、プロジェクト共通の設定として設定ファイルに記載する方法が望ましいです。
Configuration — SQLFluff 2.2.0 documentation

SQLFluff は以下のファイルを順番に検索してその設定内容を読み取り、実行時の設定内容とします。
（複数のファイルがあり、設定内容が重複した場合には、後から読み取った設定内容が有効になります。）

setup.cfg
tox.ini
pep8.ini
.sqlfluff
pyproject.toml

CLI（コマンドラインインタフェース）から使う

環境

OS AlmaLinux release 8.8
Python 3.11.2
sqlfluff, version 2.2.0

インストール

pipでSQLFluffをインストールします。
# pip install sqlfluff
- Pythonは事前にインストールしてください。

ディレクトリ、ファイル構成

ディレクトリ構成

  .
  ├── example.sql
  └── .sqlfluff

.sqlfluff の内容
```
  [sqlfluff]
  dialect = ansi
  templater = raw

  [sqlfluff:indentation]
  tab_space_size = 4

  [sqlfluff:rules:capitalisation.keywords]
  capitalisation_policy = upper

  [sqlfluff:rules:capitalisation.functions]
  capitalisation_policy = upper

  [sqlfluff:rules:capitalisation.literals]
  capitalisation_policy = upper
```
- dialect でSQLの方言としてANSIを選択しています。
  設定できる値（SQLの種類）は公式ドキュメントを確認してください。
- 他の設定としてフォーマット時のタブスペースのサイズや、予約語を大文字にする設定がされています。
- 適用しないルールがあった場合には、このファイルで適用しない旨を定義します。

example.sql の内容

    select     id,ifnull(name,'unknown') as name,age from
  user where id =1;

操作

SQLFluffで、lint をしてみます。

  # sqlfluff lint example.sql
  == [example.sql] FAIL
  L:   1 | P:   1 | LT01 | Expected only single space before 'select' keyword.
                         | Found '  '. [layout.spacing]
  L:   1 | P:   1 | LT02 | First line should not be indented.
                         | [layout.indent]
  L:   1 | P:   1 | LT13 | Files must not begin with newlines or whitespace.
                         | [layout.start_of_file]
  L:   1 | P:   3 | CP01 | Keywords must be upper case.
                         | [capitalisation.keywords]
  L:   1 | P:   3 | LT09 | Select targets should be on a new line unless there is
                         | only one select target.
                         | [layout.select_targets]
  L:   1 | P:   3 | ST06 | Select wildcards then simple targets before calculations
                         | and aggregates. [structure.column_order]
  L:   1 | P:   9 | LT01 | Expected only single space before naked identifier.
                         | Found '     '. [layout.spacing]
  L:   1 | P:  17 | CV02 | Use 'COALESCE' instead of 'IFNULL'.
                         | [convention.coalesce]
  L:   1 | P:  17 | LT01 | Expected single whitespace between comma ',' and
                         | function name identifier. [layout.spacing]
  L:   1 | P:  29 | LT01 | Expected single whitespace between comma ',' and quoted
                         | literal. [layout.spacing]
  L:   1 | P:  40 | CP01 | Keywords must be upper case.
                         | [capitalisation.keywords]
  L:   1 | P:  43 | RF04 | Keywords should not be used as identifiers.
                         | [references.keywords]
  L:   1 | P:  48 | LT01 | Expected single whitespace between comma ',' and naked
                         | identifier. [layout.spacing]
  L:   1 | P:  52 | CP01 | Keywords must be upper case.
                         | [capitalisation.keywords]
  L:   1 | P:  56 | LT01 | Unnecessary trailing whitespace.
                         | [layout.spacing]
  L:   2 | P:   1 | LT02 | Expected indent of 4 spaces.
                         | [layout.indent]
  L:   2 | P:   5 | LT02 | Expected line break and no indent before 'where'.
                         | [layout.indent]
  L:   2 | P:   6 | CP01 | Keywords must be upper case.
                         | [capitalisation.keywords]
  L:   2 | P:  16 | LT01 | Expected single whitespace between raw comparison
                         | operator '=' and numeric literal.
                         | [layout.spacing]
  All Finished 📜 🎉!

様々なルールに引っ掛かっています。
Lが行、Pが横文字位置、その後ろのLT01やCP01のようなものがルールの別名になります。
ルールのページから別名の箇所を参照することで、引っ掛かった理由や修正方法が判ります。
SQLFluffで、fix をしてみます。
```
  # sqlfluff fix example.sql
```

fix後のSQL

  # cat example.sql
  SELECT
      id,
      age,
      COALESCE(name, 'unknown') AS name
  FROM
      user
  WHERE id = 1;

同名ファイルにfix後のSQLが保存されます。
同名ファイルになるのは、ツール等での自動変換を想定しているためと思われます。
（オプションで出力ファイルにサフィックスを追加して、別名で保存することができます。）

VSCodeから使う

環境

OS Windows 10 バージョン 22H2
Python 3.11.4
sqlfluff, version 2.2.0
Visual Studio Code バージョン: 1.80.0
Visual Studio Code の拡張機能 sqlfluff v2.4.1

インストール

windowsのコマンドラインでsqlfluffが実行できるようにします。
様々なインストール方法がありますが、今回は、ChocolateyでPythonをインストールしました。
```
  # choco install python
  # pip install sqlfluff
```
インストール後にコマンドラインで、SQLFluffが実行できることを確認してください。
```
  # sqlfluff --version
  sqlfluff, version 2.2.0
```
VSCodeを起動してMarketplaceで拡張機能sqlfluffを検索してインストールします。

ディレクトリ、ファイル構成

ディレクトリ構成

  .
  ├── example.sql
  ├── .sqlfluff
  └── .vscode
      └── settings.json

settings.json の内容

  {
      "sqlfluff.config": "${workspaceFolder}/.sqlfluff"
  }

.sqlfluff, example.sql の内容はCLIと同じです。

操作

VSCodeでSQLの書かれたファイル（拡張子が .sql のファイル）を開いた画面で、右クリックからドキュメントのフォーマットを選択します。
SQLが整形されます。（fixが実行されます。）
VSCodeの拡張機能のインストール状況によっては、拡張子 .sql に対する関連付けがインストールされた拡張機能になっている場合があります。
その場合には、関連付けをSQL（.sql）へ変更してください。
- 関連付けが拡張機能SQLiteになっている例
  - SQLiteの箇所をクリックして、SQL（.sql） を選択します。

所感

SQLファイルをプロジェクトで統一されたフォーマットにしてくれるのは意味があると思います。
- 公式ドキュメントには、git の pre-commit で処理する方法が案内されていました。
SQL構文の静的解析なので、SQLアンチパターンのようなものを提案するものではありません。又、SQLの方言をコンバートするものでもありません。

SQLFluff とは？

関連サイト

サポートするSQL

ルール

設定

CLI（コマンドラインインタフェース）から使う

環境

インストール

ディレクトリ、ファイル構成

操作

VSCodeから使う

環境

インストール

ディレクトリ、ファイル構成

操作

所感