Introduction
https://vercel.com/docs/storage/vercel-postgres
Upon the announcement of Vercel Postgres, I personally thought:
- There will likely be more scenarios to use Postgres with Typescript
- Started to become interested in the query builder called Kysely that Vercel provides as a wrapper
- I've used Prisma before, but it can be difficult when dealing with complex queries
-
vercel/@postgres
is a wrapper forpg
. I want to question the premise of using a query builder or ORM in the first place
With these thoughts in mind, I decided to re-examine the ways to use SQL with Typescript in this article.
Components
Database Driver
Manually generates TCP packets and sends them to the database. It handles connecting to the database and managing the connection pool. At this level, you write raw SQL strings and pass them to the database, and receive responses from the database.
Examples:
Query Builder
Allows you to write SQL queries in Typescript.
Examples:
ORM
As the name suggests, it maps RDB records to application objects.
Examples:
Overall Flow
- First, we will look at the opinions of those who use ORM vs query builders, and query builders vs drivers.
- Thoughts
- Practice
🙋♂️ Opinions of those who use ORM
Everyone is using it
There seems to be a lot of information out there. Is it due to ActiveRecord's contribution?
Easily switch databases
For example, if you are using an ORM that supports both Postgres and MySQL, you can "relatively" easily switch between the two (regardless of frequency).
ORM can convert object-oriented query methods to SQL
- Applications are written in an object-oriented manner, so SQL can be written in that context
- Focusing on describing the interface allows you to concentrate on business logic
- It's impossible to manage database schema and objects (types) in a DRY manner
Mastering SQL strictly is difficult
- Has robust transaction support
- Guards against SQL injection vulnerabilities
Supports raw queries
This allows developers to also use raw queries as a means of writing queries.
Want to leverage the power of TypeScript
- Autocomplete
- Type checking
🙋♂️ Opinions of those who use query builders
Writing raw SQL is painful for dynamic queries
pg
:
client.query(
'INSERT INTO users(name, email) VALUES($1, $2) RETURNING *',
[name, email],
(err, res) => { /** .. */ }
)
Kysely:
kysely
.selectFrom("users")
.where("name", "=", name)
.where("email", "=", email)
There are occasional queries that cannot be realized with ORM
There are queries that even the giant ActiveRecord cannot handle, and in the end, you have to write raw SQL.
Writing complex ORM descriptions can be inefficient (performance)
Kysely:
SELECT
post.*, user.name AS user_name, user.email AS user_email
FROM
post
LEFT JOIN
user ON user.id = post.user_id
WHERE
post.category_id = ?;
Sequelize:
SELECT
"category"."id", "category"."name", "user"."id" AS "user.id",
"user"."name" AS "user.name", "user"."email" AS "user.email",
"user->post"."category_id" AS "user.post.category_id",
"user->post"."user_id" AS "user.post.user_id",
"user->post"."unit" AS "user.post.unit"
FROM
"user" AS "user"
LEFT OUTER JOIN (
"post" AS "user->post"
INNER JOIN "user" AS "user" on "user"."id" = "user->post"."user_id"
) ON "category"."id" = "user->post"."category_id"
WHERE
"category"."id" = ?
Supports raw queries
This allows developers to also use raw queries when needed.
Want to utilize TypeScript's capabilities
- Autocompletion
- Type checking
🙋♂️ Opinions of those who use database drivers directly
Even if you learn a specific ORM or query builder, you cannot apply that knowledge across languages or libraries
For example, the descriptions are subtly different between Sequelize and Prisma, both Node.js libraries.
Sequelize:
const users = await user.findAll({ where: { name: "Kevin" } });
const user = users[0];
const id = user.dataValues.id;
Prisma:
const users = await db.user.findAll({ where: { name: "Kevin" } });
const user = users[0];
const id = user.id;
On the other hand, there are only a few dialects of SQL. By learning how to generate queries using raw SQL, you can easily switch between different languages.
Thoughts
What I felt
- I don't want to learn ORM one by one
- I want to be proficient in SQL
- I want TypeScript to autocomplete
- ORM doesn't reach the itchy spots
What I felt uneasy about
- Transaction and SQL injection prevention when writing raw SQL
- Wondering about the duplication of descriptions in the schema and type (object)
What I thought might not be a problem
- Query builder specifications seem simple, so it's easy to learn
Preliminary conclusion
- I want to try using a query builder
- I still want to define the schema with Prisma
💪 Practical use of TypeScript (Kysely x Prisma)
For now, I'll define the schema with Prisma and write queries with Kysely!
There seems to be something like this, but I'll write it myself this time.
valtyr/prisma-kysely: 🪄 Generate Kysely types directly from your Prisma schema!
schema.prisma
:
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
}
model User {
id Int @id @default(dbgenerated("uuid()"))
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
email String @unique
name String?
posts Post[]
}
model Post {
id Int @id @default(dbgenerated("gen_random_uuid()"))
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
title String
body String?
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
userId Int
}
One thing to note here is the @default
.
In Prisma, it's typically defined like this, but since this is a JS-based implementation, it will not be reflected in the actual database when inserting with Kysely.
@default(gen_random_uuid())
So, we'll use the SQL-based UUID like this:
- @default(uuid())
+ @default(dbgenerated("uuid()"))
Initialization of Kysely.
Here, we pass User
and Post
from @prisma/client
to Kysely.
import { Pool } from "pg"
import { Kysely, PostgresDialect } from "kysely"
import { User, Post } from "@prisma/client"
interface Database {
User: User
Post: Post
}
const db = new Kysely<Database>({
dialect: new PostgresDialect({
pool: new Pool({
connectionString: process.env.DATABASE_URL,
}),
}),
})
export { db as kysely }
Trying it out:
const posts = kysely
.selectFrom("Post")
.where("userId", "=", 1)
.execute()
Summary
For now, I've tried leaving the schema and objects to Prisma and composing queries with Kysely. I'd like to try this for a while. If you have any additional information or advice, please let me know!
Top comments (0)