すべてのエディタでSQLの自動補完をするためにSQL Language Server(sqls)を作った #SQL

sqlsとは

sqlsとは、いま私が開発中のSQL用Language Serverです。SQLをエディタで編集するときの支援機能を実装したサーバとなっており、主な特徴は以下です。

Language ServerなのでLSクライアントが存在するエディタであればどんなエディタでも利用可能
SQL編集支援機能
- 自動補完(テーブル名、カラム名など)
- 定義参照
- SQL実行
複数のRDSMSに対応
- MySQL
- PostgreSQL
- SQLite3

Language Serverとは

Language Server(あるいはLanguage Server Protocol)とは、プログラム言語の開発支援機能をエディタに提供するサーバ、およびその通信内容を規定したプロトコルです。ただしサーバといってもほとんどの場合ローカル内にホスティングしてローカルのエディタと通信をします。

ここでは主題ではないので詳しくは以下の資料などをご覧ください。

ここで知っておいてほしいのはLanguage Serverとして実装されているので、LSPで話すクライアントさえあればどのエディタであってもその支援機能を利用することができるということです。

私が知る限り、複数エディタにおいて複数RDBMSのインテリセンスを提供するサーバはこのsqlsと後述のsql-language-serverの他にはないのではないでしょうか。

開発後記

およそ半年ほどsqlsというOSSの開発をしてきましたが、現状のsqlsは私が当初思い描いていた初期フェーズになんとか到達できました。
SQLファイルを開けば裏側でDB情報をキャッシュし、ある程度適切な候補がサジェストされますし、記述した識別子にカーソルを合わせればテーブルやカラムの情報を参照することもできます。更に記述したSQLを実行することができます(ただしVim限定。理由はあとで後述します)
少なくとも現状でsqlsを使わないよりも、使ったほうが便利な編集ができるというレベルにはなっています。

一見するとある特定言語の自動補完や検証(Lint)や整形(Format)は一般のプログラマが手を出す領域ではないように見えます。少なくとも私はこういうものはすごいプラグラマがある日突然ポンッと作るものだと思っていました。
確かに構文解析に関して全くの無知でできるというものでもないですが、実際やってみるとそういった知識でスマートにこなす部分よりも泥臭い部分のほうが多かったりします。それは他のパーサーやSQL補完を見ていても同様に見えました。

なのでこの機会にこういったものを作る人間がどんな試行錯誤をしたりしているのかというのを残しておくのも面白いかと思い。この開発後記を書いています。自分の開発環境を自分で作るのも楽しそうだなと思ってくれる人がいれば幸いです。

なぜ私はsqlsを作ったか

私は基本的に仕事でもプライベートでもだいたいターミナルを使って開発しています。エディタはVimです。そんな私がSQLを書くときは(仕事では主にMySQLを利用するので)mycliでDBに接続してそのコンソール上でSQLを書いています。mycliを使っているのはデフォルトのクライアントと違って自動補完や;の打ち忘れ支援などの機能があるからです。この自動補完はかなり優れていて簡単なSQLを書くなら不足はありません。

しかし2行以上にわたるSQLを書くとなると、mycliでは不便を感じる部分が出てきます。SQLクライアントはシェル上で動作する1ラインエディタなので。上下移動をするのがとても難しいのです。すごく長い横移動を頑張ってする羽目になります。
そこで便利機能\eを使います。知らない方のために説明しておくと\eはデフォルト設定したエディタを起動するためのメタキーです。これを実行すると編集途中のSQLを一時ファイルに出力し、そのファイルをエディタ(私の場合はVim)で開きます。更にそのファイルを保存すると、ファイル内容を実行してくれます。便利!!!

なので私のSQL編集は以下のような流れになるわけです。なお編集するSQLがすでにあるなら3からスタートするイメージです。

mycliでDB接続する
SQLを書く
SQLが複数行になったりしたり上下左右移動がめんどくさいと思ったら\eでVimを開く
編集して保存する
SQLが実行される
2~5を繰り返す

しかしこの作業にはいくつかのボトルネックがありました。

VimでSQL自動補完はできない
Vimで編集した内容が正しいか確認するためには一旦保存して実行する必要がある。このとき開いていたファイルに対するVimのカーソル位置などは失われる。

私は効率化の鬼(自称)なので、ボトルネックを放置することに多大なストレスを抱えていましたが、良い解決策が思いつかずしばらくこの状況を放置をしていました。MySQL WorkbenchやRe;dashの提供するWeb画面を利用すればよいだろうという声はありましたが、常用エディタのVim以外で編集する行為はその時点でとてつもなく非効率なのです。自分が最高効率で編集をするためにチューンしたエディタとそれ以外で差があるのは当然なのです。

LSPという解決策

去年(2019年)の夏頃、私はVimにLSPを導入し、LSから提供される開発支援の快適さに酔いしれていました。そこでふと思ったわけです。SQLのLanguage Serverがあれば以前から悩んでいたあの問題は解消するのでは？

SQLのLanguage Serverを導入したらと想像してみると、入力すればSQLの補完が表示され、カーソルを合わせればのカラム情報が表示する。さらに編集したSQLを実行するそれらすべてがシームレスにつながるイメージが浮かびました。これはとてつもなく便利だぞと思いました。

早速調べてみるとsql-language-serverというLSがすでにありました。
しかし見てみるとサポートしているのはSELECT文の自動補完だけでした。かつSSHトンネル経由のDB接続などかゆいところに手を届かせるようなサポートないようでした。これはまだ成熟していないなと思ってそっとGitHubのページを閉じました。~~それからたまに状況を確認したりしましたが、更新はほぼ止まっていました。~~

追記:なんとこの記事を投稿してすぐ、sql-language-serverの作者の方からこっちも頑張るとメッセージをいただきました。現在開発を再開されて、INSERT, UPDATE, DELETE構文の補完やポートフォワーディングなどの機能追加がされました。とてつもない勢いで機能追加がされていってこれはsqls負けるのではと恐怖しています。私も頑張ります。

サーバサイドを扱うエンジニアなら、だいだいSQLを触るはずだ。利用ユーザは各言語ごとのユーザ数よりも多い筈なのに、そのインテリセンスを支援するサーバがないのは何事だと私は憤りました。そして同時に私はこのままSQL編集にストレスを抱えたまま生きてゆかねばならないのかと恐怖しました。

その恐怖もありましたし、私は以前LSP経由で自動補完候補を取得するVimプラグインを作っており、LSPの自動補完について少しの知識を持っていたこと。自分のOSS活動であるCLIコマンドづくりに若干の飽きを感じていたことなどあり、これはSQL Language Serverを自分で作るのが一番早いのでは？と考えるようになりました。まったく作り方がわからないソフトウェアを作ることに対する若干のワクワク感もありました。

既存のsql-language-serverに対する開発支援を行う手もありましたが、以下の理由がありGoでフルスクラッチすることにしました。

自分のプライマリ言語がGo
sql-language-serverの資産はSELECT補完だけだったので一から書いても追いつけそう
現状最も出来のいいLSはGoのLS(gopls)なので、実装の参考になるかつプライマリ言語なので割と読める
DBに対するリクエストを非同期に行うシーンなどあったときにgoroutineが使える心理的安全性

Language Serverはどうやって作るのか

さあSQLを解析して自動補完をやってやるぞといきたいところですが、これから作るLanguage ServerはLSPというプロトコルに則した会話をできるサーバでなくてはなりません。
まずLanguage Serverというものを作るにあたってどこから手を付ければ良いのだろうか？軽く調査したところ、なんとなくLanguage Serverは以下のような雰囲気だということはわかりました。というか未だにわかっているかと言われたらわかっていません。雰囲気で作っています。

Language ServerとそのクライアントはJSON-RPC 2.0で通信をしている
- RPCとはRemote Procedure Callの略。ようはプログラムから外部のプログラムをコールすることをいう
- コールとレスポンスをjsonでやりとりするからJSON-RPC
GoでこのJSON-RPC通信をしようと思ったらsourcegraph社が提供しているライブラリを使うのが一般的
- jsonrpc2ライブラリ
  - GoのLanguage Serverもこれを利用している
    - gopls
    - bingo

というわけで参考にgoplsとbingoを軽く見てみたのですが、どちらもそれなりに機能の多いLanguage Serverなので中身を見てもよくわかりませんでした。特にgoplsは格納されているリポジトリが他のGo Toolsをまとめたモノリシックなリポジトリになっているため、gopls以外にGo Toolsのコンテキストの理解が必要でした。
しかし実はこれら以外にもGoで実装されていて、かつシンプルな中身のLanguage Serverがあったのです。mattnさんが作成されたefm-langserverがそれです。
efm-langserverはLinterコマンドの出力するエラーメッセージをパースして、LSPのDiagnostics(要するにエラー位置とその箇所で発生したエラーメッセージ)に変換してクライアントに提供するという特殊なLanguage Serverでした。そのため逆に内部に構文解析ロジックはあまりなく、Language Serverとしての基本機能が読みやすくなっていました。

具体的には以下のような機能(LSPに規定されたメソッド)です。

メソッド	説明
initialize	初期化。クライアントがサーバ側に起動オプションを渡したり、Language Serverがどのような機能を持っているかクライアントに教えたりする。
textDocument/didOpen	エディタが開くファイルパスとその内容を通知する。
textDocument/didChange	上記のファイル変更版。
textDocument/didSave	上記のファイル保存版。
textDocument/didClose	上記のファイル閉じる版。

これらを移植しつつbingoのテストコードを参考にユニットテストを書くことで、Language Serverとしての基本機能の実装方法と動作内容を体で覚えていきました。これが完了すると、エディタを起動したときにLanguage Serverが起動する。ファイルを開いたときにそのファイル内容をサーバがキャッシュするというLanugage Serverの土台ができました。ここから更に読み取ったファイル内容を解析して機能を提供するロジックを追加すれば私のLanguage Serverの完成というわけです。

SQLの補完を表示するには構文解析

さて前述の通り準備は整ったので、SQLの補完を出すぞといきたいわけなのですが、またわからないことがありました。そもそも補完候補をどうやって算出すればよいのかがわからないのです。
とりあえず調査の足がかりとしてゼロベースで補完を実現する方法を考え、そこからアタリをつけて調査をしようと思い、補完は以下のような仕組みになっていると推測しました。

補完とはコード全体の文脈から現在のカーソル位置において適切な入力の候補を出す機能である
- つまり以下のような機能が必要となる
  - コードを解析する機能
  - 上記を用いてカーソル位置がどのような位置なのか判定する機能
  - 位置に応じた補完候補を取得する機能

まず取り掛かるべきはこのコードを解析する機能。俗に言う構文解析機(パーサー)であるという結論に至りました。私は早速Goで実装されているパーサーを調べました。Goはライブラリが充実していることでも知られています。OSSの海に飛び込めばSQLのパーサーはゴロゴロ出てくるはずです。golang sql parseなどのワードをGoogleに打ち込んだ結果は以下の通りでした。

パーサー	説明
sqlparser	Go package for parsing MySQL SQL queries.
xsqlparser	sql parser for golang. This repo is ported of sqlparser-rs in Go.
parser	A MySQL Compatible SQL Parser
vitess-sqlparser	Simply SQL and DDL parser for Go (powered by vitess and TiDB ) this library inspired by https://github.com/xwb1989/sqlparser

MySQL互換のパーサーが多い印象ですね。どのSQLパーサーを利用するのが最も適切なのか3日ほど悩みましたが、おそらく1ヶ月悩んでも結論はでないと途中で気が付き、ザラッと中身を見た感じシンプルな作りのxwb1989/sqlparserをチョイスしました。

xwb1989/sqlparserで適当なSELECT文をパースするとAST(Abstract Syntax Tree)が構造体として返却されてきました。ASTとは聞いたことがありますが何なのかは全くわかりません。怖いですね。
ASTが何なのかはわかりませんが、ひとまず中身を解析してみるとSELECT文を読み取ったらSELECT文を表現した構造体に情報を格納しているということはわかりました。構造体を更にたどってみるとこのSELECT文どのテーブルを参照しているのか、JOINしているテーブルは何かなんてこともわかります。これなら補完候補が出せそうです。これで コードを解析する機能 は解消したかに思えました。

しかし次に進むとすぐに問題が出ました。 カーソル位置がどのような位置なのか判定する機能 です。ここでいきなりつまづきました。なぜかというとxwb1989/sqlparserが解析したトークン(パーサーが解析した要素の最小単位と考えてください)がソースファイルのどの位置(何行の何文字目)に対応するのかを示す情報を持っていなかったのです。これではカーソル位置が構文のどこにあたるのかを解析できません。致命的です。
というわけで再びパーサーを選び直しとなりました。解析後のASTにソースの位置情報を含むものという縛りが追加されたので、選択肢は一つしかありませんでした。xsqlparserです。xsqlparseのパース方法を調べてパース部分を置き換えしました。無事にASTに位置情報が渡されてきたので、あとはカーソルの座標からどの構文にいるのかは、まあ判定ロジックでいろいろゴニョゴニョやればどうにかなります。これでついに補完ができるぞとワクワクしました。

non-validation parserとはどういうことか

雑に補完候補を返すロジックができたのでユニットテストを書くことにしました。SELECT | FROM cityを渡せばcityテーブルのカラム一覧が返却されるはずです。実行した結果はパースした時点でのエラーでした。そのほかいろいろ試してみましたが、私がパースしてほしいほとんどのタイミングで、パースエラーが出力されました。

ほどなくして原因がわかりました。SQLで補完が必要なタイミングだと、大体クエリがSQLの構文としては正しくない状態になっているからです。例えばSELECT | FROM cityはでcityテーブルのカラム一覧がほしいですが、この時点ではSELECTの出力列であるselect exprが存在していないのです。これは構文として間違っています。
では他の言語はなぜ補完ができるのかと考えたのですが、大きくSQLと違うことに気が付きました。なぜなら他の言語は以下のようになっています。

ほとんど上から下に解析される
構文はいくつかのブロックに分かれているのでブロックごとに解析できる
- エラーの読み飛ばしができる
- 補完候補はだいたい他のブロックで定義されている内容

つまりSQLは一つの構文の中で補完候補の情報と補完したい位置があり、前後が逆になるパターン(SELECTとか)があるということに気が付きました。そしてこの中途半端なSQLをパースできるパーサーはGoのライブラリとして存在しませんでした。
しかし、世の中にはSQLの自動補完の実績がいくつかあるわけで。私の愛用するmycliもその一つでした。そこでmycliのソースを読んでSQLのパース方法を調査することにしました。mycliの開発セットアップスクリプトを見るとsqlparseというライブラリを使っていることがわかりました。あとで知ったことなのですがsqlparseはRedashの自動補完にも使われているようです。このsqlsparseのREADMEにはこう書いてありました。

A non-validating SQL parser module for Python

non-validating？？？なんだそれは。Googleで検索してもよくわかりません。幸い私はPythonを多少読めたので、sqlparseのソースを読んでパースの方式を調べることにしました。
sqlparseの処理内容について本筋ではないので省略しますが、sqlparseには厳密な構造化をしないという特徴がありました。一般的なパーサーは想像ですが大体こんなことをしています。

SQLの先頭から順番に読み取りその過程でなんの構文(Statement)なのか判定
- Data Definition StatementsなのかData Manipulation Statementsなのか
トークンを順番に読み取りしてASTの要素に情報を詰め込んでいく
- 例えばSELECTなら以下のようなもの
  - 取得するカラムはなにか
  - テーブル定義はどれか
  - JOINはあるのかINNERかOUTERか

ざっくりいえば厳密かつ明示的なんですね。この構文ならこうなっているはずだからそのルールでsqlparseは逆でSQLの構文をほとんど意識しないように作られています。sqlparseのパースロジックはだいたいこのグルーピング処理に書かれているんですが、このグルーピングの粒度が細かいのです。SELECTとかINSERTとかそういった構文レベル差異を意識しないレベルです。

更にイメージとしてはグルーピング処理が数十個あってそれを順番にかけていく方式になっていました。このグルーピング処理は途中でグルーピングができてもできなくてもスルーするようになっていて、グルーピングできるところまでするという形になっています。これによって厳密ではないけども頑張れば解析できる程度の構造化ができるわけです。

non-validation parserを作るか使うか

というわけでSQLの自動補完にはnon-vaildation parserが必要であることがわかりました。この時点で私には２つの大きな選択肢がありました。

non-validationなSQLパーサーを自作して、Goで開発を継続する。
Pythonでサーバを作り直して、sqlparseを使う

Goからsqlparseを使うという選択肢もあるといえばありますが筋があまりいいとは思えませんでした。結論から言うと私はGoで開発を継続するという選択をしました。

短期的に見れば、Pythonで作ったほうが早いのは間違いありませんでした。なぜならすでにパース結果を扱うロジックはmycli上に存在しており、mycliをLanguage Serverとして再構築する作業を行えば、mycliと同レベルの補完機能実現できる保証があります。
しかしそれは逆に言えばmycli以上のクオリティで補完をできるかどうか保証はないということでもありました。
実はmycliは1ラインエディタ上で実行される補完ということもあり、サブクエリなどの複雑なクエリを解析していなかったのです。また補完もあらゆるケースで望む補完候補が出てくるというわけでもありませんでした。実際私もmycliを利用しているときはその補完のクオリティで満足していました。しかし今作ろうとしているのはエディタの補完です。複雑なクエリであってもそれなりの補完がでなくてはなりません。

また考えるにあたってmycliの補完候補取得ロジックやテーブル取得のためのユーティリティを読んでいたのですが、パーサーの返す構造体の情報不足などにより泥臭い処理を書いている部分があるように思えました。mycliの補完精度がある程度でとどまっているのはこのような事情により、拡張が困難になっていることが一因になっている気配がしました。
これを改善するためにはsqlparseの修正が必要になりますが、修正は自動補完のために必要な機能をsqlparseの汎用性にあわせ、うまい具合に提案する必要があります。これは後々大きなボトルネックになると考えました。

あとこれは個人的なポリシーの話なのですが、私はせっかく何か作るなら何かしらの学びの要素。つまりどう作ったらいいかわからない要素を入れたいと思っています。パーサーというものはこれまで手を出していない、かつ困難な領域であると感じました。
あとこういうツールは、環境依存による導入の障害を排除するためにバイナリ配布できるべきであると考えているのもありました。

いろいろ理屈はありますが、最終的に自分が気持ちよくコードを書くならどうするべきかを一番大事にしました。
こうしてGoでSQLパーサーをフルスクラッチすることを決めましたが、結果的にそこそこの精度で補完が動いているので、そんなに悪くない判断だったんじゃないかなと思います。

構文解析を学ぶ

パーサーを書くと決めたものの私は文系出身のなんちゃってエンジニアなので構文解析の知識などは皆無です。なのでまずパーサーの作り方を知るためにインタプリタを作ることにしました。なんと今は『Go言語でつくるインタプリタ』という超優良本があります。なので一通り写経して、なんとなくパーサーの作り方を知っている状態になりました。読んだ感想などは過去記事に書いてあります。

しかし大きな誤算がありました。私のやろうとしている構文解析はnon-validatingな構文解析であり、monkey(『Go言語でつくるインタプリタ』で作るインタプリタ言語)のParserを少し手直しするとか、基本のアルゴリズムを流用して作るようなものではなかったのです。
そのため結局のところmonkeyの細かいロジックの作り方(特に再帰的に解析するコードの書き方は参考にしました)を参考にしつつ。sqlparseの持つ構造やアルゴリズム(という程のものはないのですが)をGoに移植するように書いていきました。ただGoはPythonに比べるとオブジェクトに対する姿勢が大きく違うため(クラスとか継承とかないので)結局ほぼ別物になりました。
実は最もsqlparseで参考にしている部分はテストケースで、テストケースを移植して、ケースと同様の結果が得られるようにパース処理を逆算することが多くありました。プログラムは結局入力を変換して出力するフィルタなので、入出力が明確であれば割となんとかなります。なおパーサー書くときにユニットテストがないと変更したときに死ねるので、書いたほうがいいです。

sqlsのパーサーのテスト

sqlsの初期構想からおよそ4ヶ月くらい経過したころ。sqlsでそれっぽく動く自動補完が実現されました。ほとんどパーサーの勉強と作成だった気がします。パーサーを書いている間は機能的には何も動くものがなくて、パースできる構文が増えるだけの毎日でしたが、不思議と楽しい時間でした。ですが解析した結果を用いて、SELECT分で指定したテーブルからカラムを取得して補完候補を出したときは、自分の作ったパーサーに価値があったという実感があり、ものすごく興奮したことを覚えています。

最初のPull Request

sqlsは作成途中のソフトウェアなので特におおっぴらに宣伝はしていませんでしたが、vim-jpのslackにLSPについて話すチャンネル(#lsp)があるので、sqls開発に進展があると#lspに都度報告していました。上記の補完が動いたときも、まっさきにvim-jpに投稿しました。

それを見たmattnさんから「PostgreSQL対応のPR投げてもいいですか？」と聞いていただけたので、是非にと答えました。しかしパーサーとLanguage Serverの補完機能のつなぎ込みは影響が大きく、開発ブランチで作っておりmasterマージをしていませんでした。

なのでmasterマージした後でという意味でもうちょっと待ってくださいと言ったのですが、その数日後には開発中のブランチにPostgreSQLの対応のPRが送られてきて、その速度感に焦りました。mattnさんを知らない人のために説明しておくとGoとVimにすごく貢献されている方です。その後さらにmattnさんからSQLite3対応や開いているSQLの実行のPRやらが矢継ぎ早に飛んで来ました。

自分がそのうちやろうと思っていたことが次々に実装されているのを見て嬉しく思うのと同時に、自分が一番このソフトウェアのことをわかっているはずなのに、悔しいなという気持ちもちょっとあったりしました。ですが自分以外にもこのソフトウェアを欲しがっていて機能追加に協力してくれる人はいるんだなと、とても嬉しく思いました。

対応RDBMS

RDBMSの対応をどこまで広げるかは議論の余地があるところではありますが、一旦自分としてはMySQLとPostgreSQLとSQLite3で一旦とどめておいて、対応するプラットフォームを増やすとその分個々の奥行きは失われてしまいます。現状この3つくらいが、自分が今後考えている機能拡張と対応を維持できるラインだと考えています。

プラグイン作成

sqlsの開発を進めていく中で、いくつかLSPという枠組みの中だけでは実現できないと思われる部分ができてきました。それがSQLの実行です。LSPは現在も仕様の策定が進められており、実はまだまだリッチな機能を提供するために必要な要素やら、それ仕様といえるのか？というくらいフワフワなものもあります。
SQLの実行にexecuteCommandを利用しているのですが、引数が自由なので極論なんでもできます。
しかし、逆に自由であると各Language Serverごとの個別実装をするとあくまでLSPに準拠する形で実装がされているLSクライアントが解釈できる形にならないとか。なのでsqlsは今のところLSPに準拠したLSクライアントだけではその機能をすべて発揮することができません。
それに対応する一時措置としてsqls.vimというVimプラグインを作っています。これを利用することでクエリ実行をsqlsにやらせることができます。選択した範囲のクエリ実行もできます。
sqlsの独自定義したオプションやらレスポンスを解釈できるので、DBの接続やデータベースの切り替えもできます。ですが逆にこのプラグインがなければそれらの機能を使うことができません。これはよろしくないです。

また私は使わないのですが、一応VSCodeでsqlsを利用するための拡張機能であるvscode-sqlsも用意しています。LSPはもともとVSCodeで利用するために作られてる側面もあるので、VSCodeからLanguage Serverを利用すると、ああ仕様そのためにあったんだとか、そう使うのかなどわかったりして面白いです。ですがコチラについては最低限の実装しかしていないので、sqls.vimに比べて機能は少ないです。

これからのLSPの拡張によりsqlsの作りなども変化する可能性があるので今後も要チェックです。

最後に

sqlsはこれまでの作ってきた私のOSSの中で最も難しく、最もエキサイティングなプロジェクトです。そしてプロジェクトが私にもたらす恩恵も大きなものになってきました。私が私のために作ったので当然といえば当然なのですが。

これからどのようにsqlsを進化させていくのか楽しみです。もし興味があればあなたもsqlsを使ってみてください。あわよくばISSUEの追加もお願いします。例えばこのSQL文のここにフォーカス併せたときにこういうサジェストがほしいのに出ないですっていうだけでも十分助かります。PRもお待ちしておりますが、ここまで機能追加優先で突っ走ってきたので読みづらいかもです。特にパース結果を読み取る部分はひどい。

というわけで自分の開発環境を自分で整えてみるのも楽しいよというお話でした。今回は技術的に細かく踏み込んだ話はしませんでしたが、機会があればそういうお話もできればと思います。

それでは皆様良き編集を。