kysely

What is kysely?

Kysely is a type-safe SQL query builder for TypeScript. It allows developers to write SQL queries in a type-safe manner, ensuring that the queries are correct at compile time. Kysely supports various SQL databases and provides a fluent API for building complex queries.

What are kysely's main functionalities?

Type-safe Query Building

Kysely allows you to build SQL queries in a type-safe manner. The above code demonstrates a simple SELECT query that retrieves the 'id' and 'first_name' columns from the 'person' table where the 'id' is equal to 1.

const result = await db.selectFrom('person').select(['id', 'first_name']).where('id', '=', 1).execute();

Insert Queries

Kysely supports type-safe insert queries. The above code demonstrates how to insert a new row into the 'person' table with the specified values.

const result = await db.insertInto('person').values({ first_name: 'John', last_name: 'Doe' }).execute();

Update Queries

Kysely allows you to perform type-safe update queries. The above code demonstrates how to update the 'first_name' column of the 'person' table where the 'id' is equal to 1.

const result = await db.updateTable('person').set({ first_name: 'Jane' }).where('id', '=', 1).execute();

Delete Queries

Kysely supports type-safe delete queries. The above code demonstrates how to delete a row from the 'person' table where the 'id' is equal to 1.

const result = await db.deleteFrom('person').where('id', '=', 1).execute();

Joins

Kysely allows you to perform type-safe join operations. The above code demonstrates an inner join between the 'person' and 'address' tables, selecting the 'id' from the 'person' table and the 'city' from the 'address' table.

const result = await db.selectFrom('person').innerJoin('address', 'person.id', 'address.person_id').select(['person.id', 'address.city']).execute();

Other packages similar to kysely

knex

Knex.js is a SQL query builder for Node.js that supports various SQL databases. It provides a fluent API for building queries but lacks the type-safety features of Kysely. Knex is widely used and has a large community.

sequelize

Sequelize is a promise-based Node.js ORM for various SQL databases. It provides a higher-level abstraction over SQL queries and includes features like model definitions, associations, and migrations. Unlike Kysely, Sequelize is more focused on being an ORM rather than just a query builder.

typeorm

TypeORM is an ORM for TypeScript and JavaScript that supports various SQL databases. It provides a high-level API for defining entities, relationships, and performing database operations. TypeORM offers type-safety but is more feature-rich compared to Kysely, as it includes migrations, caching, and more.

README

Kysely

A type-safe and autocompletion-friendly typescript SQL query builder for node.js. Heavily inspired by knex but not a clone.

Kysely's typings only allow you to use tables that are available in the database and refer to columns of the tables that are joined to the query. The result type always only contains the selected columns with correct types and aliases. This allows tools like vscode autocompletion to make your life so much easier.

As you can see in the gif above, through the pure magic of modern typescript, Kysely is even able to parse the alias given to pet.name and add the pet_name column to the result row type. Kysely is able to infer colum names and types from selected subqueries, joined subqueries, with statements and pretty much anything you can think of. Typescript is always there for you to tell what kind of query you can build and offer completions.

Of course there are cases where things cannot be typed at compile time, and Kysely offers escape hatches for these situations. With typescript you can always cast something to any if the types fail you. with Kysely you can also explicitly tell it to ignore the typings, but the default is always type-safety!

All you need to do is define an interface for each table in the database and pass those interfaces to the Kysely constructor:

import { Kysely } from 'kysely'

interface Person {
  id: number
  first_name: string
  last_name: string
  gender: 'male' | 'female' | 'other'
}

interface Pet {
  id: number
  name: string
  owner_id: number
  species: 'dog' | 'cat'
}

interface Movie {
  id: string
  stars: number
}

// Keys are table names.
interface Database {
  person: Person
  pet: Pet
  movie: Movie
}

// You'd create one of these when you start your app.
const db = new Kysely<Database>({
  dialect: 'postgres',
  host: 'localhost',
  database: 'kysely_test',
})

async function demo() {
  const person = await db
    .selectFrom('person')
    .innerJoin('pet', 'pet.owner_id', 'person.id')
    .select(['first_name', 'pet.name as pet_name'])
    .where('person.id', '=', 1)
    .executeTakeFirst()

  if (person) {
    person.pet_name
  }
}

Work in progress

Kysely currently only works on postgres. You can install it using

npm install kysely pg

Many features are still missing and the documentation is very limited. Kysely is being actively developed and things will improve fast. I wouldn't recommend using it for anything serious yet, but you can install it and play around with it.

Why not just contribute to knex

Kysely is very similar to knex, but it also attempts to fix things that I personally find not-so-good in knex. Bringing the type system and the changes to knex would mean very significant breaking changes That's not possible at this point of the project. Knex was also originally written for javascript and the typescript typings were added afterwards. That always leads to compromises in the types. Designing a library for typescript from the ground up produces much better and simpler types.