PrismaのTypedSQLでMySQLに配列を渡す方法

はじめに

プレビューとなったPrismaのTypedSQLは便利な機能ですが、MySQLで配列を直接引数として渡せないという制限があります。(2024/10月現在)
PostgreSQLではANY演算子を使って実現できますが、MySQLでは異なるアプローチが必要となります。

この記事では、Json型を使ってMySQLでTypedSQLに配列を渡す方法を紹介します！

問題点

シンプルなUserテーブルを例に考えます。IDの配列を受け取り、該当するユーザーを取得したいとします。

prisma/sql/findUsersByIds.sql

SELECT id, email FROM User WHERE id IN (?)

しかしprisma generate --sqlを実行すると、生成されるTypeScriptの関数の引数の型は、配列でなく数値型になってしまいます。(この場合はnumber | bigint)

export const findUsersByIds: (_0: number | bigint) => $runtime.TypedSql<a.Parameters, a.Result>
// ...(省略)

ちなみにPostgreSQLではANY演算子を利用することで、配列型で受け取れるようになります。

PostgreSQLの場合

SELECT id, email FROM User WHERE id = ANY($1)

解決策

TypedSQLのJson型を利用する方法でMySQLでも配列を扱えるようになります。具体的には、以下の手順で実現します。

1. SQLでJson型の引数を受け取る
2. JSONをキーバリュー形式に変換し、JSON_TABLE関数でテーブルとして扱う
3. 生成されたテーブルからIDを抽出し、IN句で使用する
4. TypeScriptで数値配列をJSON形式に変換する

最終的には以下のような実装になります。

SQLの実装

prisma/sql/findUsersByIds.sql

-- @param {Json} $1:userIds
WITH Input AS (
  SELECT userId 
  FROM JSON_TABLE(
    JSON_KEYS(?),
    '$[*]' COLUMNS( userId INT PATH '$')
  ) AS jt
)
SELECT id, email FROM User
WHERE 
  id IN (SELECT userId FROM Input)
;

TypeScriptの実装

src/withPrismaClient.ts

import { prisma } from "@/extensions/prisma/client"
import { InputJsonObject } from "@/extensions/prisma/generated/runtime/library"
import { findUsersByIds } from "@/extensions/prisma/generated/sql"

const withFindUsersByIds = async (userIds: number[]): Promise<findUsersByIds.Result[]> => {
  const jsonObject: InputJsonObject = Object.fromEntries(userIds.map(id => [id, id]))
  return prisma.$queryRawTyped(findUsersByIds(jsonObject))
} 

解説

SQLでJSON型の引数を受け取る

-- @param {Json} $1:userIds

TypedSQLではSQLに渡す引数の型を指定できます。String型でも同様の機能が実現できますが、今回はより型を厳格に指定できMySQLのサポート関数が豊富なJson型を利用します。

JSONをキーバリュー形式に変換し、JSON_TABLE関数でテーブルとして扱う

TypedSQLのJson型はInputJsonObjectとして定義されています。

export declare type InputJsonObject = {
    readonly [Key in string]?: InputJsonValue | null;
};
export declare type InputJsonValue = string | number | boolean | InputJsonObject | InputJsonArray | {
    toJSON(): unknown;
};

この型でユーザーIDの配列を表現するため、以下のようにオブジェクトのkeyにユーザーIDを設定するようにします。(オブジェクトのvalueはなんでも良いため"xx"としています。)

{"1": xx, "5": xx, ..., "userId": xx}

MySQLのJSON_KEYS関数とJSON_TABEL関数を利用してオブジェクトのkeyをカラムとするテーブルを作成します。

SELECT userId 
  FROM JSON_TABLE(
    JSON_KEYS('{"1": xx, "5": xx }'),     -- ["1", "5"]
    '$[*]' COLUMNS( userId INT PATH '$')
  ) AS jt;

結果：

userId
1
5

生成されたテーブルからIDを抽出し、IN句で使用する

JSON_KEYS関数に引数で指定したJson型を指定し、JSON_TABLE関数で抽出したユーザーIDをIN句で指定します。

-- @param {Json} $1:userIds
WITH Input AS (
  SELECT userId 
  FROM JSON_TABLE(
    JSON_KEYS(?),　-- Json型の引数を渡す
    '$[*]' COLUMNS( userId INT PATH '$')
  ) AS jt
)
SELECT id, email FROM User
WHERE 
  id IN (SELECT userId FROM Input) -- IN句に指定する
;

これでSQLの実装は完了です。次にTypeScriptの実装をしていきます。

TypeScriptで数値配列をJSON形式に変換する

TypeScriptではユーザーIDがKeyとなるオブジェクトを生成し、自動生成したfindUsersByIds関数に渡します。

const withFindUsersByIds = async (userIds: number[]): Promise<findUsersByIds.Result[]> => {
　// ユーザーIDがKeyとなるオブジェクトを生成する
  const jsonObject: InputJsonObject = Object.fromEntries(userIds.map(id => [id, id]))
  return prisma.$queryRawTyped(findUsersByIds(jsonObject))
} 

まとめ

MySQLでPrismaのTypedSQLに配列を渡す方法を紹介しました！
Json型とJSON_TABLE関数を活用することで、PostgreSQLのANY演算子のような機能を実現できます。

この方法を利用すれば、より柔軟なクエリを記述することができると思います。
参考にしていただければ幸いです！