Skip to content

kysely

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

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

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

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">