Typesafe Raw query Prisma Phần 2: Built-in TypedSQL

Phần 1: https://ant.ncc.asia/typesafe-raw-query-prisma-phan-1-kysely-query-builder

Ở bài trước, chúng ta đã nói về giới hạn của Prisma khi query raw SQL và cách kết hợp giữa Prisma và Kysely query builder để khắc phục nó.

Nhà phát triển của Prisma cũng đã nhận thấy từ lâu điểm yếu này, từ phiên bản 5.19.0. Prisma đã có thể typesafe query mà không phải sử dụng query builer: tính năng thử nghiệm TypedSQL.

TypeSQL sẽ cho phép chúng ta viết SQL query sẵn trong thư mục prisma/sql, sau đó sẽ được prisma client generate typed function từ những query được định nghĩa trong đó.

Thiết lập TypedSQL

1. Đảm bảo chắc chắn rằng Prisma @prisma/client và prisma là phiên bản >= 5.19.0

2. Thêm vào prisma.schema preview feature: typedSql flag

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

3. Thêm thư mục sql trong thư mục prisma

4. Tạo file .sql trong thư mục prisma/sql, như ví dụ của Phần 1, chúng ta sẽ tạo

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à lưu nó dưới tên getDailyCreatedUser.sql, lưu ý rằng, tên file không được bắt đầu bằng $, và tuân theo định nghĩa tên biến của Javascript

5. Generate typescript từ query ở trên vào prisma/client bằng câu lệnh:

$ prisma generate --sql

Lưu ý: Đảm bảo tất cả schema change và migration đã được apply vào database trước khi generate TypedSQL

6. Sử dụng function getDailyCreatedUser bằng cách import nó từ @prisma/client/sql, query raw kết hợp với $queryRawTyped của Prisma

import { getDailyCreatedUser } from "@prisma/client/sql"

const dailyUsers = await prisma.$queryRawTyped(getDailyCreatedUser())
console.log(dailyUsers) // getDailyCreatedUser.Result[]

Truyền biến số vào TypedSQL query.

Để truyền dateStart và dateEnd vào raw SQL query ở trên, chúng ta sẽ thay đổi getDailyCreatedUser.sql:

SELECT COUNT(id), date(created_at) FROM "users"
GROUP BY date(created_at)
HAVING date(created_at) >= DATE($1)
AND date(created_at) <= DATE($2)
ORDER BY date(created_at) DESC

Sau khi thực thi prisma generate --sql, Typescript sẽ báo lỗi getDailyCreatedUser cần có 2 biến số kiểu Date. Chúng ta phải tạo 2 biến Date và truyền vào getDailyCreatedUser:

const startDate = new Date("2024-10-01")
const endDate = new Date("2024-10-03")
const dailyUsers = await prisma.$queryRawTyped(
    // getDailyCreatedUser(timestamptz: Date, timestamptz: Date):
    // TypedSql<getDailyCreatedUser.Parameters, getDailyCreatedUser.Result>
    getDailyCreatedUser(startDate, endDate)
)
console.log(dailyUsers) // getDailyCreatedUser.Result[]

Bằng các sử dụng SQL query đã được biến số hoá, Prisma sẽ generate đúng type với biến số và đảm bảo ứng dụng chúng ta phát triển không bị SQL injection.

Lưu ý: tuỳ theo db dialect, kí hiệu biến số định nghĩa ($1, $2) là khác nhau

Truyền array vào TypedSQL query

Để truyền một array gồm các giá trị vào TypedSQL query, với PostgreSQL chúng ta sẽ sử dụng từ khoá ANY: ANY($1)

SELECT COUNT(id), date(created_at) FROM "users"
GROUP BY date(created_at)
HAVING date(created_at) = ANY($1)
ORDER BY date(created_at) DESC

const dates = [
  new Date("2024-10-01"),
  new Date("2024-10-02"),
  new Date("2024-10-03"),
];
const dailyUsers = await prisma.$queryRawTyped(
    // getDailyCreatedUser(_date: Date[]):
    // TypedSql<getDailyCreatedUser.Parameters, getDailyCreatedUser.Result>
    getDailyCreatedUser(dates),
);
console.log(dailyUsers); // getDailyCreatedUser.Result[]

TypedSQL sẽ tự động generate type cho imported sql function, đảm bảo typesafe giữa đối số truyền vào và kết quả do $queryRawTyped trả về.

Định nghĩa parameter type cho TypedSQL

Prisma cho phép chúng ta có thể định nghĩa type của biến số truyền vào bằng cách comment vào phía trước sqlQuery:

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

• Type: Prisma database type, định nghĩa như prisma.schema với các kiểu sau: Int, BigInt, Float, Boolean, String, DateTime, Json, Bytes, và Decimal.
• $N là thứ tự biến số cần định nghĩa: $1, $2, $3,…
• alias: là tên có thể hiểu được
• optional description: developer có thể giải thích mục đích của biến số

Dựa vào ví dụ ở trên, chúng ta có thể thay đổi startDate và endDate theo kiểu String và để hàm date() của Database tự chuyển, tránh việc phải new Date object từ Javascript:

-- @param {String} $1:startDate date start
-- @param {String} $2:endDate date end
SELECT COUNT(id), date(created_at) FROM "users"
GROUP BY date(created_at)
HAVING date(created_at) >= DATE($1)
AND date(created_at) <= DATE($2)
ORDER BY date(created_at) DESC

const startDate = "2024-10-01";
const endDate = "2024-10-03";
const dailyUsers = await prisma.$queryRawTyped(
  getDailyCreatedUser(startDate, endDate),
);
console.log(dailyUsers);

Lưu ý: tự định nghĩa cho biến đầu vào không được hỗ trợ với kiểu dữ liệu là array, chúng ta phải tuân theo type mà TypedSQL đã cung cấp

Điểm hạn chế của Prisma TypedSQL

1. TypedSQL không hỗ trợ MongoDB, và hỗ trợ các phiên bản mới của MySQL, PostgreSQL

2. Khi sử dụng prisma generate --sql, cần phải có database url/direct url ở .env của project, TypedSQL mới được generate vào Prisma Client, so với prisma-kysely, đây có thể là điểm bất lợi của TypedSQL

3. Dynamic column: TypedSQL không hỗ trợ dynamic colum như query dưới đây:

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

4. Phải viết từng SQL Query cho từng function, từng query để Prisma có thể generate TypedSQL: Không linh động khi viết raw query (so với phương thức Kysely)

Tổng kết

Khi có TypeSQL, chúng ta cần phải thay đổi bước CI, thay vì prisma generate, chúng ta sẽ phải thay thế bằng prisma generate --sql để có thể generate cả Prisma Client type lẫn Prisma Client TypedSQL type

Việc ứng dụng TypedSQL vào Prisma là một bước tiến lớn khi nó loại bỏ đi điểm hạn chế nhất khi rawQuery của Prisma so với những phiên bản trước đây, tuy nhiên, nó cũng đi với những bất cập kể trên, lựa chọn ra giải pháp phù hợp nhất phụ thuộc vào mỗi Developer

References: Writing Type-safe SQL with TypedSQL and Prisma Client | Prisma Documentation