Typesafe Raw query Prisma Phần 1: Kysely Query Builder

Raw query với Prisma

Prisma là một open source ORM rất phổ biến trong hệ sinh thái NodeJS thời gian gần đây, Prisma giúp đơn giản hóa và tối ưu hóa việc làm việc với cơ sở dữ liệu trong các ứng dụng Node.js và TypeScript. Prisma hỗ trợ nhiều loại cơ sở dữ liệu phổ biến như PostgreSQL, MySQL, SQLite, MongoDB, và SQL Server.

Tuy nhiên, việc đơn giản hoá khiến cho Prisma chưa phù hợp cho những database query mang tính phức tạp, đặc biệt là những query liên quan đến các database function, subquery,…

Với prisma.schema dưới đây, blog sẽ đưa ra bài toán: hãy query số lượng user được tạo trong database mỗi ngày, từ 01/10/2024 – 01/03/2024, sử dụng Prisma để trả về kết quả.

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model users {
  id           Int      @id @default(autoincrement())
  user_name    String?
  email        String?
  created_at   DateTime @default(now()) @db.Timestamptz(3)
  update_at    DateTime @default(now()) @db.Timestamptz(3)
}

Bài toán trên sẽ giải quyết dễ dàng với SQL query như bên dưới:

SELECT COUNT(id), date(created_at) FROM "users"
GROUP BY date(created_at)
HAVING date(created_at) >= date('2024-10-01')
AND date(created_at) <= date('2024-10-03')
ORDER BY date(created_at) DESC

Với Prisma client ORM method, tuy hỗ trợ group by method (Aggregation, grouping, and summarizing (Concepts) | Prisma Documentation), nhưng Prisma không thể sử dụng hàm date() đã đề cập ở SQL Query phía trên. Chúng ta phải sử dụng prisma.$queryRaw hoặc prisma.$queryRawUnsafe và cast return type về đúng với SQL raw query trả về:

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

const startDate = '2024-10-01';
const endDate = '2024-10-03';
const result = await prisma.$queryRaw<{ count: string; date: string }[]>` 
  SELECT COUNT(id), date(created_at) FROM "users"
  GROUP BY date(created_at)
  HAVING date(created_at) >= date(${startDate})
  AND date(created_at) <= date(${endDate})
  ORDER BY date(created_at) DESC
`;

console.log(
  JSON.stringify(
    result, // unknow type nếu { count: string; date: string }[] không được set
    null,
    2
  )
);

Việc tự viết SQL query và define return type cho queryRawUnsafe/queryRaw phá vỡ chức năng và mục đích cốt lõi “typesafe database client” mà Prisma đã đề ra.

Typescript check/ESLint cho dự án khó phát hiện ra những thay đổi trong database ảnh hưởng đến những controller sử dụng raw query này (đổi bảng, đổi tên cột,…), nên sẽ không đưa ra cảnh báo sớm, dẫn đến nguy cơ tiềm tàng của crash server, sai kết quả.

Query với Kysely

Kysely là một type-safe & autocomplete TypeScript SQL Query builder được lấy cảm ứng bởi Knex. Kysely còn hỗ trợ các hệ quản trị cơ sở dữ liệu phổ biến như PostgreSQL, MySQL, và SQLite dưới các Kysely Dialect.

Với bài toán kể trên, dưới đây là SQL query được biểu diễn bởi Kysely

import { Kysely, PostgresDialect } from 'kysely';
import { DB } from 'kysely-codegen';
import { Pool } from 'pg';

const db = new Kysely<DB>({
  dialect: new PostgresDialect({
    pool: new Pool({
      connectionString: process.env.DATABASE_URL,
    }),
  }),
});

(async () => {
  const startDate = '2024-10-01';
  const endDate = '2024-10-03';
  const result = await db
    .selectFrom('users')
    .groupBy((qb) => qb.fn('date', [qb.ref('created_at')]))
    .having((qb) =>
      qb.and([
        qb.eb(
          qb.fn("date", [qb.ref("created_at")]),
          ">=",
          qb.fn("date", [qb.val(startDate)]),
        ),
        qb.eb(
          qb.fn("date", [qb.ref("created_at")]),
          "<=",
          qb.fn("date", [qb.val(endDate)]),
        ),
      ]),
    )
    .select((qb) => qb.fn<string>('date', [qb.ref('created_at')]).as('date'))
    .select((qb) => qb.fn.count('id').as('count'))
    .execute();

  console.log(
    JSON.stringify(
      result, // Result Type { date: string; count: string | number | bigint; }[]
      null,
      2,
    ),
  );
})();

Tất cả các column trong bảng users được gợi ý bởi Kysely và tuân theo Typescript, nên khả năng viết sai tên cột/tính toán sai có thể hoàn toàn tránh được.

Kết quả sau khi Kysely trả về cũng đã có sẵn type, chúng ta không cần phải định nghĩa như Prisma như đã đề cập ở trên.

Typesafe raw query với Prisma và Kysely

Chúng ta có thể kết hợp Kysely và Prisma bằng cách:

• Loại bỏ Database Dialect đi kèm với Kysely, biến Kysely thành Query Builder thuần tuý
• Prisma sẽ là database query engine nhận query đã build của Kysely, query và trả về kết quả theo type mà Query Builder đã trả về

1. Setup & Generate DB type Kysely

1. Thêm prisma-kysely và kysely vào project dependency

2. Bổ sung thêm kysely generator vào prisma.schema:

generator client {
  provider = "prisma-client-js"
}

generator kysely {
  provider = "prisma-kysely"
  // Optionally provide a destination directory for the generated file
  // and a filename of your choice
  output = "../"
  fileName = "types.ts"
  // Optionally generate runtime enums to a separate file
  enumFileName = "enums.ts"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model users {
  id           Int      @id @default(autoincrement())
  user_name    String?
  email        String?
  created_at   DateTime @default(now()) @db.Timestamptz(3)
  update_at    DateTime @default(now()) @db.Timestamptz(3)
}

3. Thực thi prisma generate để prisma-kisely generate 2 output file types.ts và enums.ts

4. Tạo kysely.ts với dummy Driver từ PostgreAdapter (hoặc Dialect tương tự), export Kysely db instance,
Kysely db instance sẽ đóng vai trò query builder, vai trò query vào database sẽ được Prisma phụ trách

import {
  Kysely,
  DummyDriver,
  PostgresAdapter,
  PostgresIntrospector,
  PostgresQueryCompiler,
} from "kysely";

// ./types is generated from Prisma schema
import { DB } from "./types";

export const db = new Kysely<DB>({
  dialect: {
    createAdapter: () => new PostgresAdapter(),
    createDriver: () => new DummyDriver(),
    createIntrospector: (db) => new PostgresIntrospector(db),
    createQueryCompiler: () => new PostgresQueryCompiler(),
  },
});

2. Query kysely query builder và Prisma:

1. Viết query và truyền tham số vào Kysely, kết thúc bằng `.complile()` để trả về complied Query, bao gồm raw SQL query và parameters (đã SQL Injection protected) đã truyền vào trước đó
2. Truyền compiledQuery.sql và compiledQuery.sql và compiledQuery.parameters vào prisma.$queryRawUnsafe
3. Để queryRawUnsafe trả về đúng type, truyền InferResult<typeof compiledQuery> type exported từ kysely
4. Await queryRawUnsafe

import { PrismaClient } from "@prisma/client";
import { db } from "./kysely";
import { InferResult } from "kysely";

const prisma = new PrismaClient();

const startDate = "2024-10-01";
const endDate = "2024-10-03";
const compiledQuery = db
  .selectFrom("users")
  .groupBy((qb) => qb.fn("date", [qb.ref("created_at")]))
  .having((qb) =>
    qb.and([
      qb.eb(
        qb.fn("date", [qb.ref("created_at")]),
        ">=",
        qb.fn("date", [qb.val(startDate)]),
      ),
      qb.eb(
        qb.fn("date", [qb.ref("created_at")]),
        "<=",
        qb.fn("date", [qb.val(endDate)]),
      ),
    ]),
  )
  .select((qb) => qb.fn<string>("date", [qb.ref("created_at")]).as("date"))
  .select((qb) => qb.fn.count("id").as("count"))
  .compile();

const result = await prisma.$queryRawUnsafe<
  InferResult<typeof compiledQuery>
>(compiledQuery.sql, ...compiledQuery.parameters);

console.log(
  JSON.stringify(
    result, // // Result Type { date: string; count: string | number | bigint; }[]
    null,
    2,
  ),
);

Khi prisma client generate type bằng prisma generate, types.ts và enum.ts sẽ được tự động generate vì chúng ta đã thiết lập kysely generator ở prisma.schema.

Tổng kết

Như vậy, với sức mạnh của Prisma ORM (database dialect & migation) & Kysely typesafe query builder, sự giới hạn và rủi ro khi raw Query với Prisma đã được loại bỏ, giúp cho trải nghiệm phát triển tốt hơn cho developer.

References:

• Kysely: Kysely | Kysely
• Kysely-Codegen: GitHub – RobinBlomberg/kysely-codegen: Generate Kysely type definitions from your database.
• Prisma-kysely: GitHub – valtyr/prisma-kysely: 🪄 Generate Kysely types directly from your Prisma schema!
• Prisma: Introduction (Overview) | Prisma Documentation