kysely

Created: 2023-12-18 12:06:44 -0800 Modified: 2023-12-22 11:50:07 -0800

About

Like knex, this is a query-builder. Unlike knex, it is a type-safe library, which helps greatly if you’re using TypeScript.

Basic

• Documentation (reference)
  • At the upper right of that page is a link to “API docs”. Those are helpful for figuring out anything to do with syntax.
  • They have some examples that are great if you’re getting started.
• Migrations and regenerating types
  • I did this for Abbott, so I’ll just link to the README I wrote for that.
    • Note that I started by using kysely-migration-cli, but I ended up switching away because it’s a CJS package, and I needed ES imports, so I just copy/pasted the code into Abbott and modified it to fit my needs. It’s not a very complex CLI if you just have to create one yourself (you just make a db, a migrator, and call whichever functions you need).

Quick examples

• Executing arbitrary SQL: await sql`...`.execute(db);
• Fetching the count (reference):

  const numQuotes = await db
    .selectFrom("quotes")
    .select(db.fn.countAll().as("count"))
    .executeTakeFirstOrThrow()
  
  // numQuotes.count is a string like "188", so run Number.parseInt on it

• Adding or removing columns in a migration:

  export async function up(db: Kysely<unknown>): Promise<void> {
    await db.schema
      .alterTable("user_correlation")
      .addColumn("youtube_id", "text", (col) => col.unique())
      .execute()
  }
  
  export async function down(db: Kysely<unknown>): Promise<void> {
    await db.schema
      .alterTable("user_correlation")
      .dropColumn("youtube_id")
      .execute()
  }

Wrapper classes (reference)

Kysely has wrapper classes for Selectable, Insertable, and Updateable. These can be used instead of directly specifying the parameters to the database. For example:

db.d.ts

// […]
export interface Commands {
  can_be_deleted: Generated<boolean>
  created_at: Generated<Timestamp>
  id: string
  is_privileged: Generated<boolean>
  updated_at: Generated<Timestamp>
}
// […]

// kysely-wrappers.ts
import type { Insertable } from "kysely"

import type { Commands } from "./db"

export type NewCommand = Insertable<Commands>

// Database layer
import type { NewCommand } from "./types/kysely-wrappers"

// Then, something like this:
  async addCommand(newCommand: NewCommand) {
    await db.insertInto("commands").values(newCommand).execute()
  }

// A caller of this would look like this:
addCommand({id: "some uuid"})

The benefits of doing this are:

• You don’t have to type out the types of a generic object like this:

  async addCommand({
      id,
      isPrivileged,
      canBeDeleted,
      name,
      textResponse,
    }: {
      id: string
      isPrivileged?: boolean | undefined
      canBeDeleted?: boolean | undefined
      name: string
      textResponse?: string | undefined
    })

• When you update the database (e.g. to remove or add properties), callers can be marked as broken rather than just the database calls in “addCommand” itself.

The downside to this:

• Technically, there may be properties that you don’t want to expose with something like Insertable, e.g. the “created_at” property may be something that you want the database to fill in, but now callers could technically specify it.

When using Selectable, you can use the built-in Pick in TypeScript (reference) to narrow down the types:

export type Quote = Selectable<Quotes>
export type GetQuote = Pick<Quote, "id" | "quote" | "author" | "quoted_at">