TypedSQL

TypedSQLの始め方

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

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

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

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

   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フラグを指定してPrisma Clientを生成し、SQLクエリ用のTypeScript関数と型が作成されるようにします

   警告

   sqlフラグを指定してクライアントを生成する前に、保留中の移行がすべて適用されていることを確認してください。

   prisma generate --sql

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

   prisma generate --sql --watch

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

   /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

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

-- @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が接続できる実行中のデータベースインスタンスが必要です。directUrlがPrisma構成で提供されている場合、TypedSQLはその接続に使用します。

動的カラムを使用した動的SQLクエリ

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

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

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

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

謝辞

この機能は、PgTypedとSQLxから大きな影響を受けています。さらに、SQLiteの解析はSQLxによって処理されます。