kysely

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

About

§Section titled 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

§Section titled 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

§Section titled 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()
  
  // newNumQuotes.count is a string like "188"

• 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)

§Section titled 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">