typescript-sql-interpretor.png
Feb 26, 2021

TypeScriptで世界一型安全な型レベルSQL Interpreterを作っている話

こんにちは。DevOps芸人と化して久しいAndyです。

2020年の秋にTypeScript 4.1へTemplate Literal Typesが導入され、そのインパクトに俄かに一部の界隈がザワついたのは記憶に新しいかと思います。

今回は型プログラミングの可能性を大いに押し広げたTemplate Literal Typesを用いてSQL文を型レベルで解析し、その実行結果を型情報として導出するためのsqlptureというライブラリを作ったので紹介します。

Embedded content: https://github.com/andoshin11/sqlpture

SQLの実行/検証対象はPostgreSQL v13です。

tl;dr

  • SQL文を型レベルで解析・評価して返り値型を取得できるmini interpreterを作ったよ
  • 型レベルのSQL validatorも作ってるよ
  • 実際にNode.js(TS)からPostgresに安全にSQLを発行するexample repositoryのご紹介

get query result type from SQL

目次

  1. Template Literal Typesのおさらい
  2. 型レベルSQLインタプリタ
    1. 先行事例
    2. 作るもの
    3. SQL Parser
    4. SQL Evaluator
    5. SQL Validator
    6. 正しいクエリのサジェスチョン
  3. 実例紹介
    1. DBの立ち上げ
    2. API Serverの起動・変更
    3. Databaseの型はどこから？
  4. 最後に

Template Literal Typesのおさらい

本題に入る前に、改めてTemplate Literal Typesについておさらいしておきましょう。(すでにご存知の方はこちらまでskip!)

TypeScriptにはversion 4.0以前より文字列全般を扱う string typesの他に string literal typesというliteral型が存在していました。

type T1 = string
type T2 = ‘hello, world’

const val1: T1 = ‘random string’  // `string types`にはどのような文字列もassignが可能
const val2: T2 = ‘hello, world’ // `string literal types`には特定の文字列しかassignすることができない

Template Literal Typesはこれを拡張したものとなり、シンタックスとしてはJavaScriptのtemplate literal stringと同じような記法で記述する型となります。

type World = 'world'
const _World: World = 'world'

type Hello = 'hello'
const _Hello: Hello = 'hello'

type Greeting = `${Hello}, ${World}` // 複数のstring literal typesから新たなliteral typesを構築
const _Greeting: Greeting = 'hello, world'

また、上記のシンプルなliteral型合成に加えて Uppercase<T>, Lowercase<T>, Capitalize<T>, Uncapitalize<T>という強力な組み込み型ヘルパーも導入されたことでより柔軟で汎用性の高い型定義が可能となりました。

type EventHandler<T extends string> = `on${Capitalize<T>}`
type T1 = EventHandler<'change'>
const funcName1: T1 = 'onChange'

template literal syntaxを用いて動的な型合成・型整形が行えるようになったことの他に、literalに対するinference(部分推論)が大幅に強化された点も重要なポイントです。

// `onSomething`というシグネチャを満たすliteral型からon以降の部分のみを取得
type ExtractEventName<T> = T extends `on${infer EventName}` ? Uncapitalize<EventName> : never
type T1 = 'onClick'
const eventName1: ExtractEventName<T1> = 'click'

// 動的パラメータを持つURIからパラメータ変数のみを抽出
type ParamFromTemplate<T extends string> = T extends `:${infer P}` ? P : never;
type ExtractParamNames<T extends string> = T extends `${infer T}/${infer U}` ? ParamFromTemplate<T> | ExtractParamNames<U> : ParamFromTemplate<T>
type T2 = '/api/v1/users/:userId/posts/:postId'
type T3 = ExtractParamNames<T2>
const paramNames: T3[] = ['userId', 'postId']

上記の例だけでもその可能性の一端を感じていただけたのではないかと思います。

実際の活用例としてはawsome-template-literal-typesというレポジトリに事例がまとまっているので興味のある方はそちらをご参考ください。

Embedded content: https://github.com/ghoullier/awesome-template-literal-types

また他の静的解析言語における型レベル文字列インタプリタの事例として、@lotz先生がHaskellの実装を紹介してくれました！こちらも要チェック！

Embedded content: https://zenn.dev/lotz/articles/85577e9d9059cd9e1245

型レベルSQLインタプリタ

本題です。

型安全にDB通信を行うしくみとしては、各プログラミング言語で実装されたORMを介する方式が一般的でしょう。

そんなORMの裏側に目を向けてみると、黒魔術といって差し支えないようなquery builderが動いているケースがほとんどで、生のSQLチューニングに慣れた人間からすると「ｳｯ」なることも正直少なくありません。とはいえActive Record以降の最近のORMはどれも優秀なので、特に不便せず安全なDB通信が行えてかつ型情報も得られるとすれば一アプリケーション実装者としては使わない手はないかと思います。(本記事もその優位性を否定するつもりは全くありません。)

しかしそのようなORMが重宝されるのはある意味「SQLの仕様そのものの複雑性」やそれを取り巻く開発環境の貧弱に起因しているのかな、とも思うわけです。極端に言うなれば、プログラム言語自体に生SQLのインタプリタを積めばORMはいらなくね？という主張ですね。

「もっと安全にSQLを書きたいなー」

「ランタイムに与えるSQLの記述結果がそのまま型情報として展開できたら便利だなー」

そういった積年の思いに対してTemplate Literal Typesの登場はまさに僥倖でした。

先行事例

型レベルでSQLを扱う実装としてはすでにts-sqlというライブラリが知られています。こちらは簡易的なSQL parserを備えた型レベルdatabase実装となっており、実際のDB通信には利用できないものの型レベルのSQL TokenizerやAST(抽象構文木)の実装など参考になる部分が非常に多いです。

今回はこちらのParser実装を踏襲・拡張していくアプローチで取組んで行こうと思います。

(※ ちなみにTypeScriptやHaskell以外の言語も調べてみましたが、一般的なプログラミング言語で型レベルのSQLリテラルインタプリタを実装している言語やライブラリは見当たりませんでした)

作るもの

SQL mini interpreterの実装にあたって必要なもの・欲しいものはザックリこんな感じ

  • SQL Parser: リテラルとして与えられたSQL文 → AST(抽象構文木)に変換するための解析器
  • SQL Evaluator: Databaseのschemaに対してパースしたSQL Treeを適用するための評価器。 SELECTやJOINの挙動などもこいつがシミュレート。
  • SQL Validator: 任意のschema(テーブル構造)に対する命令分(SELECT, INSERT, UPDATE, etc...)およびパラメータの整合性検査器
  • (AST Printer): パースされたASTからもとのSQL文に復元するための出力器

SQL Parser

前述のts-sqlのParser実装を参考にさせていただきました。

入力となるSQL文に対して愚直にConditional TypesとTemplate Literal Type Inferenceを用いてAST Nodeを作成していく作業になります。 例として SELECT文の解析順序を紹介するとこんな具合に。

  1. Statement(SELECT, INSERT, UPDATE, etc...)の判定
  2. select_listと FROM以降の部分の分離
  3. select_listのtokenizeとfield指定部分、column alias、column source等の解析
  4. FROM以降のclauseをtokenizeし、末尾から先頭に向かって順番に解析( OFFSET → LIMIT → WHERE → JOIN → FROM)
  5. JOIN clauseや FROM clause中のTable SourceおよびAliasの解析

実際に下記のQueryをASTに変換した様子です。

const Q1 = 'SELECT customer.first_name, customer.last_name, rental.rental_date rented FROM rental INNER JOIN customer ON rental.customer_id = customer.customer_id;'
type T1 = typeof Q1
type R1 = Parse<T1>

// R1の型は下記のようになる
type T1AST = {
  fields: [
    FieldSpecifier</* Source */ MemberExpression<"customer", "first_name">, /* Alias */ Identifier<"first_name">>,
    FieldSpecifier</* Source */ MemberExpression<"customer", "last_name">, /* Alias */ Identifier<"last_name">>,
    FieldSpecifier</* Source */ MemberExpression<"rental", "rental_date">, /* Alias */ Identifier<"rented">>
  ],
  from: TableSpecifier</* Source */ Identifier<"rental">, /* Alias */ Identifier<"rental">>,
  where: Expression.BooleanLiteral<true>,
  limit: NumericLiteral<-1>,
  offset: NumericLiteral<0>,
  joins: [
    InnerJoinSpecifier<
      /* From */ TableSpecifier<Identifier<"customer">, Identifier<"customer">>,
      /* Where */ Expression.BinaryExpression<
        /* Left */ MemberExpression<"rental", "customer_id">,
        /* Operator */ "=",
        /* Right */ MemberExpression<"customer", "customer_id">>>
  ]
}

Eq<R1, T1AST> // true

後述のEvaluatorやValidatorがconsumeしやすいように正規化されたNode Treeとなっていることが分かりますね。同様のParserをSELECT以外の命令文についても実装していきます。

現在までにサポートしているNodeの種類はこちらから確認できます。

src/AST.ts

SQL Evaluator

上述のようにAST化したSQL文を評価して、実際のDBが返す実行結果を型レベルでシミュレーションする仕組みとなります。

具体的には、

  • SELECT対象のフィールド型をDB Schemaの任意のTableから取得する
  • フィールド型のKey名をColumn Aliasに置き換える
  • 複数TableがJOINされた後のSchema型をシミュレートする
  • etc...

といった処理が該当します。ロジックが各所に散らばってはいますが、気になる方はこちらをご確認ください。

src/Evaluator.ts

実際の例を見てみましょう。下記のようなcustomer, address, cityという3つのテーブルがあるとします。

dvdrental=# \d customer;
                                             Table "public.customer"
   Column    |            Type             | Collation | Nullable |                    Default
-------------+-----------------------------+-----------+----------+-----------------------------------------------
 customer_id | integer                     |           | not null | nextval('customer_customer_id_seq'::regclass)
 store_id    | smallint                    |           | not null |
 first_name  | character varying(45)       |           | not null |
 last_name   | character varying(45)       |           | not null |
 email       | character varying(50)       |           |          |
 address_id  | smallint                    |           | not null |
 activebool  | boolean                     |           | not null | true
 create_date | date                        |           | not null | 'now'::text::date
 last_update | timestamp without time zone |           |          | now()
 active      | integer                     |           |          |

dvdrental=# \d address;
                                             Table "public.address"
   Column    |            Type             | Collation | Nullable |                   Default
-------------+-----------------------------+-----------+----------+---------------------------------------------
 address_id  | integer                     |           | not null | nextval('address_address_id_seq'::regclass)
 address     | character varying(50)       |           | not null |
 address2    | character varying(50)       |           |          |
 district    | character varying(20)       |           | not null |
 city_id     | smallint                    |           | not null |
 postal_code | character varying(10)       |           |          |
 phone       | character varying(20)       |           | not null |
 last_update | timestamp without time zone |           | not null | now()

dvdrental=# \d city;
                                           Table "public.city"
   Column    |            Type             | Collation | Nullable |                Default
-------------+-----------------------------+-----------+----------+---------------------------------------
 city_id     | integer                     |           | not null | nextval('city_city_id_seq'::regclass)
 city        | character varying(50)       |           | not null |
 country_id  | smallint                    |           | not null |
 last_update | timestamp without time zone |           | not null | now()

下記のようにクエリを実行して返り値型を検証してみましょう！

import { Query } from 'sqlpture'
import { DB } from './types'

type EQ<A, B> = A extends B ? B extends A ? true : false : false

type R1 = Query<"SELECT * FROM customer;", DB>
type T1 = EQ<R1, {
  customer_id: number;
  store_id: number;
  first_name: string;
  last_name: string;
  email: string | null;
  address_id: number;
  activebool: boolean;
  create_date: Date;
  last_update: Date | null;
  active: number | null; 
}[]> // true

type R2 = Query<"SELECT first_name AS name, email FROM customer;", DB>
type T2 = EQ<R2, { name: string; email: string | null; }[]> // true

type R3 = Query<"SELECT c.customer_id id, c.first_name, c.last_name, c.email, country.country, city.city, a.district, a.address, a.address2 FROM customer AS c INNER JOIN address AS a ON c.address_id = a.address_id INNER JOIN city ON a.city_id = city.city_id INNER JOIN country ON city.country_id = country.country_id WHERE customer_id = 1", DB>
type T3 = EQ<R3, {
  id: number;
  first_name: string;
  last_name: string;
  email: string | null;
  country: string;
  city: string;
  district: string;
  address:  string;
  address2: string | null;
}[]> // true

type R4 = Query<"INSERT INTO customer (store_id, first_name, last_name, email, address_id) VALUES (1, 'John', 'Smith', NULL, 5) RETURNING *;", DB>
type T4 = EQ<R4, {
  customer_id: number;
  store_id: number;
  first_name: string;
  last_name: string;
  email: string | null;
  address_id: number;
  activebool: boolean;
  create_date: Date;
  last_update: Date | null;
  active: number | null; 
}[]> // true

Tableを3つ跨ぐようなJOIN Clauseでも(Alias含めて)問題なくシミュレートできているのが分かりますね。

SQLの仕様があまりに膨大すぎてまだまだ対応していないケースも多いですが、一般的なWebアプリケーションの利用ケースをカバーできるよう順次実装予定です。

SQL Validator

TypeScriptといえばやはり型検査！ということでバリデータも実装していきます。

SQL文の記述は長大になりがちで、専用のIDEを利用しない限り3回に1回はtypoするといっても過言ではありません(※自社調べ)

sqlptureではせっかくASTレベルでSQLリテラルを取り扱えるようになったので、DB Schemaと突き合わせてその整合性を検査する仕組みも実装しています。 例として使用するDB Schemaはこちら。

type DB = {
  dialect: "postgres"
  schema: {
    customer: {
      customer_id: number
      first_name: string
      last_name: string
      email: string
    }
    item: {
      item_id: number
      label: string
    }
    customer_item: {
      customer_id: number
      item_id: number
      amount: number
    }
  }
}

import { ValidateQuery } from "sqlpture" 

type Q1 = "SELECT first_name AS name, email FROM customer;"
type V1 = ValidateQuery<Q1, DB> // true

いくつかの例を見ていきましょう。

不正なテーブル名やフィールド名の検知

type Q2 = "SELECT * FROM not_customers;"
type V2 = ValidateQuery<Q2, DB> // false

type Q3 = "SELECT name, tel FROM customer;"
type V3 = ValidateQuery<Q3, DB> // false

WHERE句での存在しないフィールドへのExpressionを検知

type Q4 = "SELECT email FROM customer WHERE first_names = 'John';"
type V4 = ValidateQuery<Q4, DB> // false

存在しないFieldへのINSERT文を検知

type Q5 = "INSERT INTO customers (first_name, last_name, email) VALUES('John', 'Smith', 'test@example.com');"
type V5 = ValidateQuery<Q5, DB> // false

type Q6 = "INSERT INTO customer (first_names, last_name, email) VALUES('John', 'Smith', 'test@example.com');"
type V6 = ValidateQuery<Q6, DB> // false

INSERTする値の過不足を検知

type Q7 = "INSERT INTO customer_item (customer_id, item_id, amount) VALUES (1, 10);"
type V7 = ValidateQuery<Q7, DB> // false

type Q8 = "INSERT INTO customer_item (customer_id, item_id, amount) VALUES (1, 10, 100), (2, 20, 200, 2000);"
type V8 = ValidateQuery<Q8, DB> // false

INSERTする値の型チェック

type Q9 = "INSERT INTO customer_item (customer_id, item_id, amount) VALUES (1, 10, 'NaN');"
type V9 = ValidateQuery<Q9, DB> // false: ammount should be number type

type Q10 = "INSERT INTO customer (first_name, last_name, email) VALUES('John', 'Smith', 1);"
type V10 = ValidateQuery<Q10, DB> // false: email should be string type

Validate項目としては最低限といったところですかね？

正直まだまだ精度が甘く、false positiveも多すぎて実用的とは程遠いですが、こちらもしばらくドッグフーディングをしながら改善に勤しみたいと思います。

正しいクエリのサジェスチョン

上記の ValidateQueryという型ヘルパーはあくまでtrue/falseでクエリの整合性検証結果を返してくれるもので、「実際にどの部分が間違えているのか？」という指摘は行ってくれません。

TypeScriptで任意の型との差分を表示してくれる仕組みとして真っ先に思いつくのは、ジェネリクスパラメータの extends制約を用いるものでしょう。

extends sample

これを応用すれば「DB上に存在するTableに対してしかFROM/JOINが行えない」といったように制約を適用できそうです。

invalid from clause

invalid join target

エラーメッセージの読みやすさに難はありますが、概ね正しいFROMターゲットやJOINターゲットがサジェストされているように見て取れます。

しかし残念ながらこのアプローチで検証できる内容は、あくまでDB Schema単体から「決定的である」もののみです。

例えば「FROMターゲット」というクエリの構成要素については、DB Schemaが入力として与えられた時点でターゲット候補が指定可能ですね。

ですが SELECT c.email, a.city FROM customer AS c INNER JOIN address AS a ON c.address_id = a.id WHERE c.id = 100;というAliasを含むクエリについて考えてみると、その整合性が確定するのは当然クエリの記述時となります。("c", "a"といったAliasの名称についてDB Schemaは知り得ない)。

この問題をクリアするためにいろいろと試行錯誤しているのですが、僕のTS力が不足しているためスマートな解決方法がまだ思いついていません。

また「任意のDB Schemaに対して発行可能なクエリパターンを網羅的に全て事前定義する」という要件の複雑度が高すぎて、100点のソリューションを用意するのは当面ムリそうです。

しばらくは上述のValidatorを用いてクエリの整合性のみをbooleanで返し、「どこが壊れているか」というデバッグは個々の開発者に委ねたいと思います。

実例紹介

ここからは実際にWeb API Server + PostgreSQL DBでの利用方法を紹介していきます。

こちらがサンプルレポジトリです。

Embedded content: https://github.com/andoshin11/sqlpture-example

DBの立ち上げ

レポジトリをPullして $ make start-dbを実行すると、Postgres Docker Containerが localhost:15432で起動します。

初期状態ではなにもデータが入っていないので、 $ make restore-dbを実行して下記のようなschemaを流し込んであげてください。(データは永続化されているため、2度目からは必要ありません)

Sample DB Schema

リストアが完了したらpostgres://postgres:mysecretpassword1234@localhost:15432/dvdrentalで疎通を確認し、上記のようなschemaが反映されていることを確認してください。

API Serverの起動・変更

$ make start-serverを実行すると3010番ポートでExpressのAPI Serverが起動します。 (nodemonを走らせているのでserver/src/**/*.tsファイルを適当に編集すれば自動でリロードするはず)

server/src/index.tsというファイルがサーバーやハンドラーの実装となっており、こちらを見ていただくと雰囲気がつかめるかと思います。

import { Client, QueryResultBase } from 'pg'
import { Query, ValidateQuery } from 'sqlpture'
import { DB } from './types/db'

type Result<T> = QueryResultBase & { rows: Query<T, DB> }
type ValidQuery<T> = true extends ValidateQuery<T, DB> ? T : never

class Repository {
   constructor(private client: Client) {}

  async query<T extends string>(queryStr: T, values?: any[]): Promise<Result<T>> {
    return this.client.query(queryStr, values) as any
  }

  async strictQuery<T extends string>(queryStr: ValidQuery<T>, values?: any[]): Promise<Result<T>> {
    return this.client.query(queryStr, values) as any
  }
}

読みやすさのために多少変更していますが、上記のようなRepository classを通してPostgresへの通信を行っています。見て分かるようにこのファサードの役割はあくまで型ヘルパーですね。

引数として渡したSQL文(JS Value)をジェネリックなString Literal Typeに推論し、sqlptureで処理した結果を返り値型として返しています。

下記のように単純なメソッド呼び出しを行うだけで型付けされた返り値型が得られるはずです。

get query result type from SQL

またValidatorも利用することで不正なクエリ(下記の例だとinvalid field access)を型エラーとして検知できていることも分かります。

invalid insert query

これからは、さらにNode.jsアプリケーションの開発が楽しくなるのではないでしょうか！

Databaseの型はどこから？

最後になりましたが、肝心のDB Schema型はどのように得れば良いでしょうか？

こちらは対象となるDBやアウトプットを利用するFWに応じて多くのソリューションがありますが、個人的にPostgreSQLのSchema型を自動生成する際はSchematsを利用しています。

Embedded content: https://github.com/SweetIQ/schemats

対応しているDBはPostgreSQLとMySQLのみですが、シンプルなコマンドでNullableやEnumも解析できてお手軽です。

sqlptureで利用するためにはこちらから type Database = { dialect: string; schema: Record<string, any> }というシグネチャを満たすDatabase型に変換してあげる必要があるので、こちらのシンプルなスクリプトと併用してタスクランナーから実行しています。

規模の小さいDBであれば型定義を手書きする運用でもよいでしょうが、やはり自動生成を行うと実装と解離するリスクを軽減できて良いですね。

最後に

今回はTypeScript 4.1で登場したTempalte Literal Typesの応用編として型レベルのSQL Interpreterを作ってみました。

話のネタになれば良いなと思って始めたプロジェクトですが、手前味噌ながら「意外と実用的なのでは？」と思ったり。

Open APIやProtobufに対する型解析でTypeScriptのシステムでWeb API境界を安全に扱う仕組みは整い始めたので、これを機にDB境界も型安全に扱える日がくると良いなと思っています。

興味のある方からのコントリビュートをお待ちしています！

Embedded content: https://github.com/andoshin11/sqlpture

Copyright © 2020 Shin Ando