TypedSQL

TypedSQLを使い始める

PrismaプロジェクトでTypedSQLを使用するには、以下の手順に従ってください

1. `@prisma/client`と`prisma`がインストールされ、バージョン`5.19.0`以降にアップデートされていることを確認してください。

   npm install @prisma/client@latest
   npm install -D prisma@latest

2. `schema.prisma`ファイルに`typedSql`プレビュー機能フラグを追加します

   generator client {
     provider = "prisma-client-js"
     previewFeatures = ["typedSql"]
   }

3. `prisma`ディレクトリ内に`sql`ディレクトリを作成します。ここにSQLクエリを記述します。

   mkdir -p prisma/sql

4. `prisma/sql`ディレクトリに新しい`.sql`ファイルを作成します。例：`getUsersWithPosts.sql`。ファイル名は有効なJS識別子である必要があり、`$`で始めることはできません。

5. 新しい`.sql`ファイルにSQLクエリを記述します。例：

   prisma/sql/getUsersWithPosts.sql

   SELECT u.id, u.name, COUNT(p.id) as "postCount"
   FROM "User" u
   LEFT JOIN "Post" p ON u.id = p."authorId"
   GROUP BY u.id, u.name

6. SQLクエリのTypeScript関数と型が作成されるように、`sql`フラグを付けてPrisma Clientを生成します

   警告

   `sql`フラグを付けてクライアントを生成する前に、保留中のマイグレーションがすべて適用されていることを確認してください。

   prisma generate --sql

   変更ごとにクライアントを再生成したくない場合は、このコマンドは既存の`--watch`フラグでも機能します

   prisma generate --sql --watch

7. これで、SQLクエリをTypeScriptコードにインポートして使用できます

   /src/index.ts

   import { PrismaClient } from '@prisma/client'
   import { getUsersWithPosts } from '@prisma/client/sql'
   
   const prisma = new PrismaClient()
   
   const usersWithPostCounts = await prisma.$queryRawTyped(getUsersWithPosts())
   console.log(usersWithPostCounts)

TypedSQLクエリへの引数の渡し方

TypedSQLクエリに引数を渡すには、パラメータ化されたクエリを使用できます。これにより、型安全性を維持しながら、柔軟で再利用可能なSQLステートメントを記述できます。その方法を以下に示します

1. SQLファイルで、渡したいパラメータのプレースホルダーを使用します。プレースホルダーの構文は、データベースエンジンによって異なります

   PostgreSQL

   PostgreSQLでは、位置プレースホルダー`$1`、`$2`などを使用します。

   prisma/sql/getUsersByAge.sql

   SELECT id, name, age
   FROM users
   WHERE age > $1 AND age < $2

   MySQL

   MySQLでは、位置プレースホルダー`?`を使用します。

   prisma/sql/getUsersByAge.sql

   SELECT id, name, age
   FROM users
   WHERE age > ? AND age < ?

   SQLite

   SQLiteでは、いくつかの異なるプレースホルダーを使用できます。位置プレースホルダー（`$1`、`$2`など）、汎用プレースホルダー（`?`）、および名前付きプレースホルダー（`:minAge`、`:maxAge`など）がすべて利用可能です。この例では、名前付きプレースホルダー`:minAge`と`:maxAge`を使用します。

   prisma/sql/getUsersByAge.sql

   SELECT id, name, age
   FROM users
   WHERE age > :minAge AND age < :maxAge
   注

   以下の「SQLファイルで引数の型を定義する」を参照してください。

2. 生成された関数をTypeScriptコードで使用する際は、追加のパラメータとして`$queryRawTyped`に引数を渡します

   /src/index.ts

   import { PrismaClient } from '@prisma/client'
   import { getUsersByAge } from '@prisma/client/sql'
   
   const prisma = new PrismaClient()
   
   const minAge = 18
   const maxAge = 30
   const users = await prisma.$queryRawTyped(getUsersByAge(minAge, maxAge))
   console.log(users)

パラメータ化されたクエリを使用することで、型安全性を確保し、SQLインジェクションの脆弱性から保護できます。TypedSQLジェネレーターは、SQLクエリに基づいてパラメータの適切なTypeScript型を作成し、クエリ結果と入力パラメータの両方に対して完全な型チェックを提供します。

TypedSQLに配列引数を渡す

TypedSQLは、PostgreSQLの引数として配列を渡すことをサポートしています。PostgreSQLの`ANY`演算子を配列パラメータとともに使用します。

prisma/sql/getUsersByIds.sql

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

/src/index.ts

import { PrismaClient } from '@prisma/client'
import { getUsersByIds } from '@prisma/client/sql'

const prisma = new PrismaClient()

const userIds = [1, 2, 3]
const users = await prisma.$queryRawTyped(getUsersByIds(userIds))
console.log(users)

TypedSQLは、配列パラメータの適切なTypeScript型を生成し、入力とクエリ結果の両方で型安全性を確保します。

注

配列引数を渡す際は、データベースが単一のクエリでサポートするプレースホルダーの最大数に注意してください。非常に大きな配列の場合、クエリを複数の小さなクエリに分割する必要がある場合があります。

SQLファイルでの引数型の定義

TypedSQLでの引数の型付けは、SQLファイル内の特定のコメントによって行われます。これらのコメントの形式は次のとおりです

-- @param {Type} $N:alias optional description

ここで、Typeは有効なデータベース型であり、Nはクエリ内の引数の位置、aliasはTypeScript型で使用される引数のオプションのエイリアスです。

例として、エイリアスnameと「ユーザーの名前」という説明を持つ単一の文字列引数を型付けする必要がある場合、SQLファイルに次のコメントを追加します

-- @param {String} $1:name The name of the user

パラメータがnull許容であることを示すには、エイリアスの後に疑問符を追加します

-- @param {String} $1:name? The name of the user (optional)

現在受け入れられている型は、Int、BigInt、Float、Boolean、String、DateTime、Json、Bytes、null、およびDecimalです。

上記の例を考慮すると、SQLファイルは次のようになります

-- @param {Int} $1:minAge
-- @param {Int} $2:maxAge
SELECT id, name, age
FROM users
WHERE age > $1 AND age < $2

引数型の定義形式は、データベースエンジンに関係なく同じです。

注

配列引数に対しては、手動での引数型定義はサポートされていません。これらの引数については、TypedSQLが提供する型推論に依存する必要があります。

例

さまざまなシナリオでのTypedSQLの使用に関する実践的な例については、Prisma Examplesリポジトリを参照してください。このリポジトリには、TypedSQLの実装を含む、ベストプラクティスと一般的なユースケースを示すすぐに実行できるPrismaの例プロジェクトが多数含まれています。

TypedSQLの制限事項

サポートされているデータベース

TypedSQLは、MySQLおよびPostgreSQLの最新バージョンを特別な設定なしでサポートします。MySQL 8.0より古いバージョンおよびすべてのSQLiteバージョンでは、SQLファイルで手動で引数型を記述する必要があります。入力の型は、PostgreSQLのすべてのサポートされているバージョンおよびMySQL 8.0以降で推論されます。

TypedSQLはSQLデータベース向けに特別に設計されているため、MongoDBでは動作しません。

アクティブなデータベース接続が必要

TypedSQLが正しく機能するには、アクティブなデータベース接続が必要です。これは、`sql`フラグを使用してクライアントを生成する際に、Prismaが接続できる実行中のデータベースインスタンスが必要であることを意味します。Prisma設定で`directUrl`が提供されている場合、TypedSQLはその接続を使用します。

動的カラムを持つ動的SQLクエリ

TypedSQLは、動的にカラムが追加されるSQLクエリの構築をネイティブにサポートしていません。実行時にカラムが決定されるクエリを作成する必要がある場合は、`$queryRaw`および`$executeRaw`メソッドを使用する必要があります。これらのメソッドは、動的なカラム選択を含む生のSQLの実行を可能にします。

動的なカラム選択を使用するクエリの例

const columns = 'name, email, age'; // Columns determined at runtime
const result = await prisma.$queryRawUnsafe(
  `SELECT ${columns} FROM Users WHERE active = true`
);

この例では、選択されるカラムが動的に定義され、SQLクエリに含まれています。このアプローチは柔軟性を提供しますが、セキュリティ、特にSQLインジェクションの脆弱性を回避することに細心の注意を払う必要があります。さらに、生のSQLクエリを使用することは、TypedSQLの型安全性とDXを放棄することを意味します。

謝辞

この機能はPgTypedおよびSQLxから強くインスパイアされました。さらに、SQLiteのパースはSQLxによって処理されます。