Quick Start

Get started with Kysely in minutes by setting up your database schema and running your first type-safe query.

Quick Start

This guide will help you set up Kysely and execute your first type-safe SQL query in minutes.

1. Define Your Database Schema

   Create TypeScript interfaces that describe your database structure. Kysely uses these to provide type-safety and autocompletion.

   src/types.ts

   import {
     ColumnType,
     Generated,
     Insertable,
     Selectable,
     Updateable,
   } from 'kysely'
   
   // Database interface - maps table names to table schemas
   export interface Database {
     person: PersonTable
     pet: PetTable
   }
   
   // Table schema for 'person' table
   export interface PersonTable {
     // Generated columns (like auto-increment IDs) are marked with Generated<T>
     // This makes them optional in inserts and updates
     id: Generated<number>
     
     first_name: string
     
     // Nullable columns use TypeScript's union with null
     last_name: string | null
     
     gender: 'man' | 'woman' | 'other'
     
     // ColumnType allows different types for select, insert, and update
     created_at: ColumnType<Date, string | undefined, never>
   }
   
   // Helper types for working with person records
   export type Person = Selectable<PersonTable>
   export type NewPerson = Insertable<PersonTable>
   export type PersonUpdate = Updateable<PersonTable>
   
   export interface PetTable {
     id: Generated<number>
     name: string
     owner_id: number
     species: 'dog' | 'cat'
   }
   
   export type Pet = Selectable<PetTable>
   export type NewPet = Insertable<PetTable>
   export type PetUpdate = Updateable<PetTable>
   

   For production applications, consider using kysely-codegen to automatically generate types from your database schema.

2. Create a Kysely Instance

   Set up a Kysely instance with your database dialect and connection details.

   PostgreSQL

   src/database.ts

   import { Database } from './types'
   import { Kysely, PostgresDialect } from 'kysely'
   import { Pool } from 'pg'
   
   const dialect = new PostgresDialect({
     pool: new Pool({
       database: 'test',
       host: 'localhost',
       user: 'admin',
       port: 5432,
       max: 10,
     })
   })
   
   // Create a single instance and export it
   export const db = new Kysely<Database>({
     dialect,
   })
   

   MySQL

   src/database.ts

   import { Database } from './types'
   import { Kysely, MysqlDialect } from 'kysely'
   import { createPool } from 'mysql2'
   
   const dialect = new MysqlDialect({
     pool: createPool({
       database: 'test',
       host: 'localhost',
       user: 'admin',
       password: '123',
       port: 3306,
       connectionLimit: 10,
     })
   })
   
   export const db = new Kysely<Database>({
     dialect,
   })
   

   Do not import from mysql2/promise! Use the default mysql2 import.

   SQLite

   src/database.ts

   import { Database } from './types'
   import { Kysely, SqliteDialect } from 'kysely'
   import SQLite from 'better-sqlite3'
   
   const dialect = new SqliteDialect({
     database: new SQLite('database.db'),
   })
   
   export const db = new Kysely<Database>({
     dialect,
   })
   

   Keep secrets safe! Never hardcode credentials. Use environment variables or a secrets manager:

   const dialect = new PostgresDialect({
     pool: new Pool({
       database: process.env.DB_NAME,
       host: process.env.DB_HOST,
       user: process.env.DB_USER,
       password: process.env.DB_PASSWORD,
       port: parseInt(process.env.DB_PORT || '5432'),
     })
   })
   
3. Write Your First Queries

   Now you can start writing type-safe queries! Here are common operations:

   Select Query

   import { db } from './database'
   
   // Find a person by ID
   export async function findPersonById(id: number) {
     return await db
       .selectFrom('person')
       .where('id', '=', id)
       .selectAll()
       .executeTakeFirst()
   }
   
   // Find people with criteria
   export async function findPeople(criteria: Partial<Person>) {
     let query = db.selectFrom('person')
   
     if (criteria.id) {
       // Kysely is immutable - always re-assign!
       query = query.where('id', '=', criteria.id)
     }
   
     if (criteria.first_name) {
       query = query.where('first_name', '=', criteria.first_name)
     }
   
     if (criteria.last_name !== undefined) {
       query = query.where(
         'last_name',
         criteria.last_name === null ? 'is' : '=',
         criteria.last_name
       )
     }
   
     return await query.selectAll().execute()
   }
   

   import { db } from './database'
   import { NewPerson } from './types'
   
   export async function createPerson(person: NewPerson) {
     return await db
       .insertInto('person')
       .values(person)
       .returningAll()
       .executeTakeFirstOrThrow()
   }
   
   // Usage:
   const newPerson = await createPerson({
     first_name: 'John',
     last_name: 'Doe',
     gender: 'man',
     created_at: new Date().toISOString(),
   })
   

   import { db } from './database'
   import { PersonUpdate } from './types'
   
   export async function updatePerson(
     id: number,
     updateWith: PersonUpdate
   ) {
     await db
       .updateTable('person')
       .set(updateWith)
       .where('id', '=', id)
       .execute()
   }
   
   // Usage:
   await updatePerson(1, {
     first_name: 'Jane',
   })
   

   import { db } from './database'
   
   export async function deletePerson(id: number) {
     return await db
       .deleteFrom('person')
       .where('id', '=', id)
       .returningAll()
       .executeTakeFirst()
   }
   

   import { db } from './database'
   
   // Get persons with their pets
   export async function getPersonWithPets(personId: number) {
     return await db
       .selectFrom('person')
       .innerJoin('pet', 'pet.owner_id', 'person.id')
       .select([
         'person.id',
         'person.first_name',
         'person.last_name',
         'pet.name as pet_name',
         'pet.species',
       ])
       .where('person.id', '=', personId)
       .execute()
   }
   

   Kysely is immutable! Query builder methods return a new query builder instance. Always re-assign the result:

   // ✅ Correct
   let query = db.selectFrom('person')
   query = query.where('id', '=', 1)
   
   // ❌ Wrong - this does nothing!
   let query = db.selectFrom('person')
   query.where('id', '=', 1)
   
4. Execute and Handle Results

   Kysely provides several execution methods depending on your needs:

   // executeTakeFirst() - returns first row or undefined
   const person = await db
     .selectFrom('person')
     .selectAll()
     .executeTakeFirst()
   // Type: Person | undefined
   
   // executeTakeFirstOrThrow() - throws if no rows found
   const person = await db
     .selectFrom('person')
     .selectAll()
     .executeTakeFirstOrThrow()
   // Type: Person
   
   // execute() - returns all rows as array
   const people = await db
     .selectFrom('person')
     .selectAll()
     .execute()
   // Type: Person[]
   

Complete Example

Here’s a complete working example using the user repository pattern from the Kysely source:

src/user.repository.ts

import { Kysely, Generated, Insertable, Selectable } from 'kysely'

// Define the schema
export interface UserTable {
  user_id: Generated<string>
  first_name: string | null
  last_name: string | null
  email: string | null
  created_at: Generated<Date>
}

export type UserRow = Selectable<UserTable>
export type InsertableUserRow = Insertable<UserTable>

export interface Database {
  user: UserTable
}

// Repository functions
export async function insertUser(
  db: Kysely<Database>,
  user: InsertableUserRow,
): Promise<UserRow> {
  const insertedUser = await db
    .insertInto('user')
    .values(user)
    .returningAll()
    .executeTakeFirstOrThrow()

  return insertedUser
}

export async function findUserById(
  db: Kysely<Database>,
  id: string,
): Promise<UserRow | undefined> {
  const user = await db
    .selectFrom('user')
    .where('user_id', '=', id)
    .selectAll('user')
    .executeTakeFirst()

  return user
}

export async function setUserEmail(
  db: Kysely<Database>,
  id: string,
  email: string,
): Promise<void> {
  await db
    .updateTable('user')
    .where('user_id', '=', id)
    .set({ email })
    .execute()
}

Next Steps

Migrations

Learn how to manage database schema changes

Advanced Queries

Explore joins, subqueries, CTEs, and more

API Reference

Browse the complete API documentation

Pro tip: Hover over any Kysely method in your IDE to see inline API documentation with examples!