🎃

Prismaが発行するSQLを観察してみる（参照系）

tekihei2317

2023/02/20に公開

動機

主に参照系で、PrismaがどのようなSQLを発行しているのかが気になったため、調べてみることにしました。ドキュメントをざっくり見た感じでは、発行されるSQLについて記述されているページがなかったため、実際にPrisma Clientを動かして確認してみました。

バージョン

prisma 4.10.1
MySQL 8.0.31

準備

今回は、次のブログアプリ風のスキーマを使って試してみます。

schema.prisma

model User {
  id      Int          @id @default(autoincrement())
  email   String       @unique
  name    String?
  posts   Post[]
  profile UserProfile?
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  author    User    @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
}

model UserProfile {
  userId      Int     @id
  displayName String
  bio         String?
  user        User    @relation(fields: [userId], references: [id])
}

発行されるSQLを確認するためには、Prisma Clientにlogオプションを渡します。

export const prisma = new PrismaClient({
  log: ["query"],
});

一つのテーブルに対しての操作は、以下のようにPrisma Clientの記述と発行されるSQLがほぼ同じなので、主にリレーションに関する操作を中心に見ていきます。

await prisma.post.findMany({
  select: {
    id: true,
    title: true,
    content: true,
  },
  where: {
    authorId: 1,
    published: true,
  },
  orderBy: { createdAt: 'desc' },
  limit: 10,
})

-- 発行されるSQL
SELECT
  `simple_blog`.`Post`.`id`,
  `simple_blog`.`Post`.`title`,
  `simple_blog`.`Post`.`content`
FROM
  `simple_blog`.`Post`
WHERE (
  `simple_blog`.`Post`.`authorId` = ? AND
  `simple_blog`.`Post`.`published` = ?
)
ORDER BY `simple_blog`.`Post`.`createdAt` DESC
LIMIT ? OFFSET ?

Prismaができること

Prismaでリレーションに関してできる操作は、主に以下の3つです。

リレーションを取得する
リレーションで検索する
リレーションで並べ替える

リレーションに関する操作以外では、集約系のAPI（aggregateとgroupBy）もありますが、今回は試していません。

リレーションを取得する

リレーションを取得するには、includeまたはselectを使います。

// (User & { posts: Post[] })[]
const usersWithPosts1 = await prisma.user.findMany({
  include: { posts: true },
  take: 10,
})

// { posts: Post[] }[]
const usersWithPosts2 = await prisma.user.findMany({
  select: { posts: true },
  take: 10,
})

発行されるSQLは以下です。includeとselectのどちらとも、Userを取得した後に、そのIDを使ってPostを取得していることが分かります。

-- include
SELECT `simple_blog`.`User`.`id`, `simple_blog`.`User`.`email`, `simple_blog`.`User`.`name`
FROM `simple_blog`.`User`
WHERE 1=1
ORDER BY `simple_blog`.`User`.`id` ASC
LIMIT ? OFFSET ?

SELECT `simple_blog`.`Post`.`id`, `simple_blog`.`Post`.`title`, `simple_blog`.`Post`.`content`, `simple_blog`.`Post`.`published`, `simple_blog`.`Post`.`authorId`, `simple_blog`.`Post`.`createdAt`
FROM `simple_blog`.`Post`
WHERE `simple_blog`.`Post`.`authorId` IN (?,?,?,?,?,?,?,?,?,?)

-- select
SELECT `simple_blog`.`User`.`id`
FROM `simple_blog`.`User`
WHERE 1=1 ORDER BY `simple_blog`.`User`.`id` ASC
LIMIT ? OFFSET ?

SELECT `simple_blog`.`Post`.`id`, `simple_blog`.`Post`.`title`, `simple_blog`.`Post`.`content`, `simple_blog`.`Post`.`published`, `simple_blog`.`Post`.`authorId`, `simple_blog`.`Post`.`createdAt`
FROM `simple_blog`.`Post`
WHERE `simple_blog`.`Post`.`authorId` IN (?,?,?,?,?,?,?,?,?,?)

補足: includeとselectの違いと使い分け

includeとselectの違いは以下の2点です。

includeはリレーションにしか使えないが、selectはフィールドとリレーションの両方に使える
includeを使った場合は、親のテーブルの全てのカラムが暗黙的に取得される

注意点として、includeとselectは同じ階層に書けないことがあります。なぜ両方を書けないかというと、includeとselectは同じような役割のため、どちらに書かれたデータを取得すればいいのか判断できないからだと考えられます。

// 以下はコンパイルエラーになる
await prisma.post.findMany({
  // post.titleとタグを取得するのか、post.*とタグを取得するのか判断できない
  select: { title: true, tags: true },
  include: { tags: true },
  take: 10,
})

両方を使いたくなった場合は、includeでできることはselectでできるので、selectを使えば良いです。

参考: Relation queries (Concepts)

取得するリレーションに条件をつける

取得するリレーションに条件をつけることもできます。その場合は、リレーションを取得するSQLに条件が付けられます。

const userWithPosts3 = await prisma.user.findMany({
  select: {
    posts: {
      select: { id: true, title: true },
      where: { published: true },
    },
  },
  take: 10,
});

SELECT `simple_blog`.`User`.`id`
FROM `simple_blog`.`User`
WHERE 1=1 ORDER BY
`simple_blog`.`User`.`id` ASC
LIMIT ? OFFSET ?

SELECT `simple_blog`.`Post`.`id`, `simple_blog`.`Post`.`title`, `simple_blog`.`Post`.`authorId` 
FROM `simple_blog`.`Post`
WHERE
  (`simple_blog`.`Post`.`published` = ? AND
  `simple_blog`.`Post`.`authorId` IN (?,?,?,?,?,?,?,?,?,?))

リレーションの件数を取得する

includeやselectで、リレーションの件数を取得することもできます。

const userWithPostCount = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    _count: true,
  },
  take: 10,
});

この場合は、件数を取得するクエリがJOINされていました。

SELECT
  `simple_blog`.`User`.`id`,
  `simple_blog`.`User`.`name`,
  `aggr_selection_0_Post`.`_aggr_count_posts`
FROM
  `simple_blog`.`User`
  LEFT JOIN (
    SELECT `simple_blog`.`Post`.`authorId`, COUNT(*) AS `_aggr_count_posts`
    FROM `simple_blog`.`Post`
    WHERE 1=1
    GROUP BY `simple_blog`.`Post`.`authorId`
  ) AS `aggr_selection_0_Post`
    ON (`simple_blog`.`User`.`id` = `aggr_selection_0_Post`.`authorId`)
WHERE 1=1
ORDER BY `simple_blog`.`User`.`id`
ASC LIMIT ? OFFSET ?

リレーションで検索する

リレーションのフィールドの値で検索する

取得するモデルを、リレーションのフィールドの値で絞り込むことができます。例えば、投稿をユーザーのメールアドレスで絞り込むためには、次のように書きます。

const result = await prisma.post.findMany({
  select: { id: true, title: true },
  where: {
    published: false,
    author: {
      email: "test1000@example.com",
    },
  },
});

次のSQLが発行されています。条件に該当するユーザーのIDをサブクエリで取得してから、IN句で絞り込んでいることが分かります。

SELECT
  `simple_blog`.`Post`.`id`, `simple_blog`.`Post`.`title`
FROM `simple_blog`.`Post`
WHERE (
  `simple_blog`.`Post`.`published` = ? AND
  (`simple_blog`.`Post`.`id`) IN (
    SELECT `t0`.`id`
    FROM
      `simple_blog`.`Post` AS `t0`
      INNER JOIN `simple_blog`.`User` AS `j0` ON (`j0`.`id`) = (`t0`.`authorId`)
    WHERE (`j0`.`email` = ? AND `t0`.`id` IS NOT NULL)
  )
)

サブクエリでJOINしている理由が気になったのですが、外部キーが複数カラムからなる場合^[1]にINが使えなくなることを考慮しているのかなと思いました。

-- このクエリも同等だが、外部キーが複合の場合は使えない（INのサブクエリは1カラムしか選択できないため）
SELECT
  `simple_blog`.`Post`.`id`, `simple_blog`.`Post`.`title`
FROM `simple_blog`.`Post`
WHERE (
  `simple_blog`.`Post`.`published` = ? AND
  (`simple_blog`.`Post`.`authorId`) IN (
    SELECT `t0`.`id`
    FROM
      `simple_blog`.`User` as `t0`
    WHERE (`t0`.`email` = ? AND `t0`.`id` IS NOT NULL)
  )
)

リレーションの存在性で検索する

また、リレーションの存在性で検索することもできます。リレーションがto-many側の場合はsome・none・everyを使い、to-one側の場合は{ is: null }または、{ isNot: null }を使います。

const userWithSomePosts = await prisma.user.findMany({
  select: { id: true, name: true },
  where: {
    posts: { some: {} },
  },
  take: 10,
});

SELECT
  `simple_blog`.`User`.`id`,
  `simple_blog`.`User`.`name`
FROM `simple_blog`.`User`
WHERE
  (`simple_blog`.`User`.`id`) IN (
    SELECT `t0`.`id`
    FROM
      `simple_blog`.`User` AS `t0`
      INNER JOIN `simple_blog`.`Post` AS `j0` ON (`j0`.`authorId`) = (`t0`.`id`)
      WHERE (1=1 AND `t0`.`id` IS NOT NULL)
  )
ORDER BY `simple_blog`.`User`.`id` ASC LIMIT ? OFFSET ?

const userWithProfile = await prisma.user.findMany({
  select: { id: true, name: true },
  where: {
    profile: { isNot: null },
  },
  take: 10,
});

SELECT
  `simple_blog`.`User`.`id`,
  `simple_blog`.`User`.`name`
FROM `simple_blog`.`User`
WHERE (
  NOT (`simple_blog`.`User`.`id`) NOT IN (
    SELECT `simple_blog`.`UserProfile`.`userId`
    FROM `simple_blog`.`UserProfile`
    WHERE `simple_blog`.`UserProfile`.`userId` IS NOT NULL
  )
)
ORDER BY `simple_blog`.`User`.`id` ASC LIMIT ? OFFSET ?

現時点では、リレーションの個数で検索することはできないようです（参考）。

リレーションで並べ替える

リレーションのフィールドで並べ替える

リレーションのフィールドで並べ替えることができます。

const sortedPosts = await prisma.post.findMany({
  select: { id: true, title: true },
  orderBy: {
    author: { name: "desc" },
  },
  take: 5,
});

このときはJOINをしていました。

SELECT
  `simple_blog`.`Post`.`id`,
  `simple_blog`.`Post`.`title`
FROM
  `simple_blog`.`Post`
  LEFT JOIN `simple_blog`.`User` AS `orderby_1_User`
    ON (`simple_blog`.`Post`.`authorId` = `orderby_1_User`.`id`)
WHERE 1=1
ORDER BY `orderby_1_User`.`name` DESC LIMIT ? OFFSET ?

リレーションの個数で並べ替える

リレーションの個数で並べ替えることもできます。

const activeUsers = await prisma.user.findMany({
  select: { id: true, name: true },
  orderBy: {
    posts: {
      _count: "desc",
    },
  },
  take: 10,
});

リレーションの件数を計算したサブクエリをJOINしているようです。

SELECT
  `simple_blog`.`User`.`id`,
  `simple_blog`.`User`.`name`
FROM
  `simple_blog`.`User`
  LEFT JOIN (
    SELECT `simple_blog`.`Post`.`authorId`, COUNT(*) AS `orderby_aggregator`
    FROM `simple_blog`.`Post`
    WHERE 1=1
    GROUP BY `simple_blog`.`Post`.`authorId`
  ) AS `orderby_1_Post`
    ON (`simple_blog`.`User`.`id` = `orderby_1_Post`.`authorId`)
WHERE 1=1
ORDER BY COALESCE(`orderby_1_Post`.`orderby_aggregator`, ?) DESC
LIMIT ? OFFSET ?

まとめ

観察した結果、以下の3点がいえそうです。

selectやincludeでリレーションを取得するときは、リレーションごとにSQLが発行される
- リレーションの個数を取得する場合は例外で、countを取得するクエリがJOINされる
リレーションで検索するときは、サブクエリでリレーションを検索してからINが使われる
リレーションでソートするときはJOINが使われる

また、SQLの組み立ては、取得・検索・ソートで独立して行われていそうです。例えば、リレーションでの検索とリレーションでの並べ替えを同時に行ってみると、検索用のサブクエリとソート用のJOINがあることが分かります。

リレーションでの検索と並べ替えを同時に行う

await prisma.post.findMany({
  where: {
    author: { name: { contains: "tom" } },
  },
  orderBy: {
    author: { name: "desc" },
  },
});

SELECT
  `simple_blog`.`Post`.`id`,
  `simple_blog`.`Post`.`title`,
  `simple_blog`.`Post`.`content`,
  `simple_blog`.`Post`.`published`,
  `simple_blog`.`Post`.`authorId`,
  `simple_blog`.`Post`.`createdAt`
FROM
   `simple_blog`.`Post`
   LEFT JOIN `simple_blog`.`User` AS `orderby_1_User`
    ON (`simple_blog`.`Post`.`authorId` = `orderby_1_User`.`id`)
WHERE
  (`simple_blog`.`Post`.`id`) IN (
    SELECT `t0`.`id`
    FROM
      `simple_blog`.`Post` AS `t0`
      INNER JOIN `simple_blog`.`User` AS `j0` ON (`j0`.`id`) = (`t0`.`authorId`)
    WHERE (`j0`.`name` LIKE ? AND `t0`.`id` IS NOT NULL)
  )
ORDER BY `orderby_1_User`.`name` DESC

パフォーマンス的にはあまり問題なさそうですが、JOINを使うならばサブクエリを使わなくてもできるので、若干冗長なSQLが発行されてしまいます。

型安全なクエリについての感想

実際に使っていて、検索で指定した条件が、取得結果の型に反映されないことが気になることがありました。例えば、UserProfile（to-one側）が存在しないという条件を書いたとしても、取得結果はUserProfile | nullになります。^[2]

他には個人的な印象ですが、集計のクエリが単純なケースでなければ難しいのかなという気もしています。なぜかというと、PrismaがFROM句でJOINすることが少ないからです。

これらのケースは、pg-typedやsqlc（Go）などの、SQLから型を生成するアプローチが向いていそうです。^[3]

参考

脚注

複合外部キーというのでしょうか。 ↩︎
INNER JOINすれば解決できるものの、今度はuser.profileのようにアクセスできないことが気になりそうです。その場合は、結局アプリケーション側でなんとかすることになります。 ↩︎
Node.js x MySQLではどちらも使えないので羨ましい… ↩︎

GitHubで編集を提案

株式会社GIBJapanPublication

動機

バージョン

準備

Prismaができること

リレーションを取得する

リレーションを取得する

取得するリレーションに条件をつける

リレーションの件数を取得する

リレーションで検索する

リレーションのフィールドの値で検索する

リレーションの存在性で検索する

リレーションで並べ替える

リレーションのフィールドで並べ替える

リレーションの個数で並べ替える

まとめ

型安全なクエリについての感想

参考

Discussion