Slickでユーザー定義関数を使ってPlainSQLを避ける

初めに

Scalaを使っての開発を行なっている場合
Playの公式ライブラリであるSlickはDB用ライブラリとして採用される機会が多いと思います。
ドキュメントも日本語で翻訳され一次情報を拾いやすい状態ではありますが
DATE_FORMATやDATE_ADDなどの関数を
Slickを使って記述する際に苦戦したため記事として残しておきます。

やりたいこと

下記のような処理をSlickでPlainSQLを使わずに書きたい

create table user (
  id VARCHAR(255) NOT NULL,
  date Timestamp NOT NULL
)

select id, DATE_FORMAT(u.date, "%Y-%M-%D")
from user u

User-Defined Futures

If your database system supports a scalar function that is not available as a method in Slick you can define it as a SimpleFunction.
https://scala-slick.org/doc/3.3.3/userdefined.html

データベースではサポートされているが
Slickの関数として定義されていないメソッドに対しては
ユーザー定義関数を使って記述することが可能です。

SimpleFunctionを使うパターン

create table user (
  id VARCHAR(255) NOT NULL,
  day DATE NOT NULL
)

select id, DAY_OF_WEEK(u.day)
from user u

引数が単一のシンプルな関数の場合はSimpleFunction関数を用いて定義します。

A SimpleFunction gets translated to a plain function call or JDBC/ODBC scalar function {fn ...} call in SQL.
https://scala-slick.org/doc/3.3.3/api/index.html#slick.lifted.SimpleFunction

val dayOfWeek = SimpleFunction.unary[Date, Int]("day_of_week")

上記の処理のポイントは2つです。

1. SimpleFunctionの型変換を明記
   1. ここではday_of_weekメソッドが日付から週の番号への変換を行うため、Date => Int。
2. データベースシステム上で使用可能な関数名を引数として受け渡す。渡された関数はSQLとして展開されます。

定義した関数は以下のように
Slickのメソッドとして呼び出しを行うことが可能です。

val query = users.map(user => (user.id, dayOfWeek(user.day)))

上記のクエリは以下のようなSQLとして生成されます。

select id, DAY_OF_WEEK(u.day)
from user u

SimpleExpressionを使うパターン

For even more flexibility (e.g. function-like expressions with unusual syntax), you can use SimpleExpression.
https://scala-slick.org/doc/3.3.3/api/index.html#slick.lifted.SimpleExpression

より柔軟なユースケースを実現したい場合はSimpleExpressionを使うことが推奨されています。
(このパターンのドキュメントがほぼ無く苦戦したため、この記事を書くモチベーションに繋がっています)

create table user (
  id VARCHAR(255) NOT NULL,
  date Timestamp NOT NULL
)

select id, DATE_FORMAT(DATE_ADD(u.date, interval 9 hour), "%Y-%M-%D")
from user u

上記の例の場合

1. DATE_FORMAT関数は引数として(Timestamp, String)を要求
2. DATE_ADD関数は(Timestamp, Int)を要求

このような、関数が引数を複数受け取る場合など複雑なケースの場合、SimpleExpressionが便利です。
具体的な定義方法は以下のようになります。

def dateAddByHour(date: Rep[Timestamp], interval: Int): Rep[Timestamp] = {
  val expression = SimpleExpression.binary[Timestamp, Int, Timestamp] { (date, interval, queryBuilder) =>
    queryBuilder.toString
    queryBuilder.sqlBuilder += " date_add("
    queryBuilder.expr(date)
    queryBuilder.sqlBuilder += ", interval "
    queryBuilder.expr(interval)
    queryBuilder.sqlBuilder += " hour)"
  }
  expression.apply(date, interval)
}

def timestampToLocalDate(date: Rep[Timestamp]): Rep[LocalDate] = {
  val format = "%Y-%M-%D"
  val expression = SimpleExpression.binary[Timestamp, String, Timestamp] { (date, format, queryBuilder) =>
    queryBuilder.toString
    queryBuilder.sqlBuilder += " date_format("
    queryBuilder.expr(date)
    queryBuilder.sqlBuilder += ", "
    queryBuilder.expr(format)
    queryBuilder.sqlBuilder += ")"
  }
  expression.apply(date, format)
}

queryBuilderを使用することで受け取った引数を柔軟にクエリとして合成することが可能です。
上記で定義した関数は呼び出し側では以下のように使用することが出来ます。

val jstTimezoneOffset = -9
val query = users.map(user => (user.id, timestampToLocalDate(dateAddByHour(user.date, jstTimezoneOffset))))

このようにslick側で標準で定義されていない関数を
使用するユーザー側で定義することで
SQLを直書きすることなく実現することが出来ています。

まとめ

この記事ではユーザー定義関数を使って
Slick上でサポートされていない関数を使う方法についてまとめました。
PlainSQLを書くことによるデメリットは
カラムの変更をコンパイル時に検知できないなど様々ありますが
少し工夫を行うことにより回避することが可能です。
さらにより良い方法などありましたら、是非コメント頂けると助かります！

参考