Mastering Database Queries with Kysely: A Practical Guide

Kysely is a type-safe SQL query builder for TypeScript that helps you write complex queries with full autocompletion and error checking. Unlike ORMs, Kysely gives you fine-grained control over SQL while keeping your code readable and maintainable. In this tutorial, you’ll learn how to perform common queries using Kysely’s expressive API.

Setting Up Your Database Connection

First, define your database schema using Kysely’s type inference. Import Kysely and a dialect (e.g., Postgres, SQLite). Create a typed instance by passing a Database interface that maps table names to their row types.

import { Kysely, PostgresDialect } from 'kysely'
import { Pool } from 'pg'

interface Database {
  users: { id: number; name: string; email: string }
  posts: { id: number; user_id: number; title: string }
}

const db = new Kysely<Database>({
  dialect: new PostgresDialect({ pool: new Pool() })
})

Performing SELECT Queries

Kysely’s selectFrom method starts a query. Chain .select() to pick columns, then .where() to filter. Use .execute() to run the query and get results.

const user = await db
  .selectFrom('users')
  .select(['id', 'name', 'email'])
  .where('id', '=', 1)
  .executeTakeFirst()

For pagination, add .limit() and .offset(). Kysely also supports .orderBy() and .groupBy().

Joins and Subqueries

Use .innerJoin() or .leftJoin() to combine tables. For subqueries, wrap a query with db.selectFrom() and use the result as a value in where or select.

const posts = await db
  .selectFrom('posts')
  .innerJoin('users', 'posts.user_id', 'users.id')
  .select(['posts.title', 'users.name'])
  .where('users.id', 'in', db.selectFrom('users').select('id').where('active', '=', true))
  .execute()

Inserting, Updating, and Deleting

Insert a single row with .insertInto().values(). Kysely validates all columns at compile time. Use .returning() to get back inserted fields.

const newUser = await db
  .insertInto('users')
  .values({ name: 'Alice', email: 'alice@example.com' })
  .returning('id')
  .executeTakeFirst()

Update rows with .updateTable().set().where(). Delete using .deleteFrom().where().

Transactions and Error Handling

Wrap multiple queries inside db.transaction(). If any query fails, the whole transaction rolls back. Use try/catch to handle database errors gracefully.

await db.transaction().execute(async (trx) => {
  await trx.insertInto('posts').values({ user_id: 1, title: 'New Post' }).execute()
  await trx.updateTable('users').set({ post_count: 10 }).where('id', '=', 1).execute()
})

Kysely’s type safety means you catch mismatched columns or invalid comparisons at compile time, reducing runtime errors significantly.

Conclusion

Kysely bridges the gap between raw SQL and ORM convenience. By leveraging TypeScript’s type system, you get autocompletion, inline documentation, and fewer bugs. Start with simple queries, then explore advanced features like raw expressions, unions, and custom SQL fragments. Your database code will be both readable and robust.