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 for pg. 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:

• pg - npm
• mysql - npm
• sqlite3 - npm

Query Builder

Allows you to write SQL queries in Typescript.

Examples:

• knex - npm
• kysely - npm

ORM

As the name suggests, it maps RDB records to application objects.

Examples:

• sequelize-typescript - npm
• prisma - npm
• bookshelf - npm

Overall Flow

1. First, we will look at the opinions of those who use ORM vs query builders, and query builders vs drivers.
2. Thoughts
3. 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!