Library

Kysely

Overview

Kysely is a type-safe SQL query builder designed specifically for TypeScript. It adopts an innovative approach that combines the power of raw SQL with TypeScript's type checking, specializing in complex query creation. Unlike traditional ORMs, Kysely enables direct SQL operations without sacrificing type safety for developers who prioritize SQL control. Type inference from database schemas detects access to non-existent tables or columns at compile time, significantly reducing runtime errors. The fluent API utilizing functional programming paradigms allows natural description of complex analytics queries and data warehouse operations.

Details

Kysely 2025 is rapidly gaining attention among TypeScript developers as the "ideal balance between SQL control and type safety." Integration with modern database services like Cloudflare D1 and PlanetScale, in addition to PostgreSQL, MySQL, and SQLite, is expanding adoption in edge environments and serverless architectures. Type-level query construction provides perfect IntelliSense completion and refactoring safety. Zero-dependency design minimizes bundle size, making it ideal for performance-focused applications. Advanced features like Expression Builder, Conditional Query Building, and Dynamic Query Construction enable type-safe implementation of complex business logic.

Key Features

• Complete Type Safety: SQL construction fully integrated with TypeScript type system
• Zero Dependencies: Lightweight and optimized for serverless environments
• Expression Builder: Type-safe construction of complex SQL expressions
• Dynamic Queries: Flexible query construction based on conditions
• Multi-DB Support: Complete support for major databases and cloud services
• Latest TypeScript Features: Utilization of Template Literal Types and more

Pros and Cons

Pros

• Perfect type safety and IntelliSense completion through TypeScript type system
• Lightweight with zero dependencies, advantageous in serverless environments
• Transparent query construction that directly leverages SQL knowledge
• Type-safe implementation of complex analytics queries
• Excellent integration with modern database services
• Predictable and maintainable code through functional approach

Cons

• SQL knowledge is essential, high learning curve for beginners
• Limited ORM-like automation features (migration, relationship management)
• No support for high-level abstractions like Active Record pattern
• Additional tools required for schema-first approach
• Complex type definitions may make error messages difficult to understand
• Relatively new ecosystem with limited third-party tools

Reference Pages

• Kysely Official Site
• Kysely GitHub Repository
• Kysely Documentation

Code Examples

Installation and Setup

# Install Kysely
npm install kysely

# Install database drivers
# PostgreSQL
npm install pg
npm install --save-dev @types/pg

# MySQL
npm install mysql2

# SQLite (Node.js)
npm install better-sqlite3
npm install --save-dev @types/better-sqlite3

# Cloudflare D1
npm install @cloudflare/workers-types

# TypeScript required
npm install --save-dev typescript

// database.ts - Database type definitions
export interface Database {
  person: PersonTable;
  pet: PetTable;
  movie: MovieTable;
  person_movie: PersonMovieTable;
}

export interface PersonTable {
  id: Generated<number>;
  first_name: string;
  last_name: string | null;
  email: string;
  age: number;
  gender: 'male' | 'female' | 'other';
  created_at: ColumnType<Date, string | undefined, never>;
  updated_at: ColumnType<Date, string | undefined, string | undefined>;
  preferences: JSONColumnType<{
    theme: 'light' | 'dark';
    language: string;
    notifications: boolean;
  }>;
}

export interface PetTable {
  id: Generated<number>;
  name: string;
  species: 'dog' | 'cat' | 'bird' | 'other';
  owner_id: number;
  birth_date: Date | null;
}

export interface MovieTable {
  id: Generated<number>;
  title: string;
  release_date: Date;
  rating: number;
  genre: string[];
}

export interface PersonMovieTable {
  person_id: number;
  movie_id: number;
  rating: number | null;
  watched_at: Date;
}

// db.ts - Kysely instance setup
import { Kysely, PostgresDialect } from 'kysely';
import { Pool } from 'pg';
import { Database } from './database';

const dialect = new PostgresDialect({
  pool: new Pool({
    database: 'test',
    host: 'localhost',
    user: 'admin',
    password: 'admin',
    port: 5432,
    max: 10,
  }),
});

export const db = new Kysely<Database>({
  dialect,
});

// Type helpers
export type Person = Selectable<PersonTable>;
export type NewPerson = Insertable<PersonTable>;
export type PersonUpdate = Updateable<PersonTable>;

Basic Query Operations

import { db } from './db';

// SELECT - Basic searches
async function basicQueries() {
  // Fetch all records
  const allPersons = await db
    .selectFrom('person')
    .selectAll()
    .execute();

  // Select specific columns
  const names = await db
    .selectFrom('person')
    .select(['first_name', 'last_name', 'email'])
    .where('age', '>=', 18)
    .orderBy('last_name')
    .execute();

  // Conditional queries
  const adults = await db
    .selectFrom('person')
    .selectAll()
    .where('age', '>=', 18)
    .where('email', 'like', '%@example.com')
    .limit(10)
    .execute();

  // Aggregate functions
  const stats = await db
    .selectFrom('person')
    .select([
      (eb) => eb.fn.count<number>('id').as('total_count'),
      (eb) => eb.fn.avg<number>('age').as('average_age'),
      (eb) => eb.fn.min<number>('age').as('min_age'),
      (eb) => eb.fn.max<number>('age').as('max_age'),
    ])
    .executeTakeFirstOrThrow();

  console.log(`Total: ${stats.total_count}, Average age: ${stats.average_age}`);

  return { allPersons, names, adults, stats };
}

// INSERT - Data insertion
async function insertOperations() {
  // Single record insertion
  const newPerson = await db
    .insertInto('person')
    .values({
      first_name: 'John',
      last_name: 'Doe',
      email: '[email protected]',
      age: 30,
      gender: 'male',
      preferences: {
        theme: 'dark',
        language: 'en',
        notifications: true
      }
    })
    .returning(['id', 'first_name', 'last_name'])
    .executeTakeFirstOrThrow();

  // Multiple record insertion
  const newPersons = await db
    .insertInto('person')
    .values([
      {
        first_name: 'Jane',
        last_name: 'Smith',
        email: '[email protected]',
        age: 25,
        gender: 'female'
      },
      {
        first_name: 'Bob',
        last_name: 'Johnson',
        email: '[email protected]',
        age: 35,
        gender: 'male'
      }
    ])
    .returning('id')
    .execute();

  // ON CONFLICT (PostgreSQL)
  const upsertedPerson = await db
    .insertInto('person')
    .values({
      email: '[email protected]',
      first_name: 'Updated',
      last_name: 'Name',
      age: 28,
      gender: 'other'
    })
    .onConflict((oc) => oc
      .column('email')
      .doUpdateSet({
        first_name: (eb) => eb.ref('excluded.first_name'),
        last_name: (eb) => eb.ref('excluded.last_name'),
        age: (eb) => eb.ref('excluded.age')
      })
    )
    .returning(['id', 'first_name'])
    .executeTakeFirst();

  return { newPerson, newPersons, upsertedPerson };
}

// UPDATE - Data updates
async function updateOperations() {
  // Conditional update
  const updateResult = await db
    .updateTable('person')
    .set({
      age: (eb) => eb('age', '+', 1),
      updated_at: new Date().toISOString()
    })
    .where('id', '=', 1)
    .returning(['id', 'age'])
    .executeTakeFirst();

  // Complex conditional update
  await db
    .updateTable('person')
    .set({
      'preferences.theme': 'light'
    })
    .where((eb) => eb.or([
      eb('age', '>', 60),
      eb('preferences', '->', 'notifications').eq(false)
    ]))
    .execute();

  // Update with JOIN
  await db
    .updateTable('person')
    .set({
      last_name: 'VIP'
    })
    .from('pet')
    .where('person.id', '=', (eb) => eb.ref('pet.owner_id'))
    .where('pet.species', '=', 'dog')
    .execute();

  return updateResult;
}

// DELETE - Data deletion
async function deleteOperations() {
  // Conditional deletion
  const deleteResult = await db
    .deleteFrom('person')
    .where('age', '<', 0)
    .returning('id')
    .execute();

  // Complex conditional deletion
  await db
    .deleteFrom('person')
    .where((eb) => eb.and([
      eb('created_at', '<', new Date(Date.now() - 365 * 24 * 60 * 60 * 1000)),
      eb.not(eb.exists(
        eb.selectFrom('pet')
          .select('id')
          .where('owner_id', '=', eb.ref('person.id'))
      ))
    ]))
    .execute();

  return deleteResult;
}

Advanced Queries and Type-Safe Operations

import { ExpressionBuilder, sql } from 'kysely';

// JOINs and subqueries
async function advancedQueries() {
  // INNER JOIN
  const personsWithPets = await db
    .selectFrom('person')
    .innerJoin('pet', 'pet.owner_id', 'person.id')
    .select([
      'person.first_name',
      'person.last_name',
      'pet.name as pet_name',
      'pet.species'
    ])
    .where('pet.species', '=', 'dog')
    .execute();

  // LEFT JOIN with aggregation
  const personsWithPetCount = await db
    .selectFrom('person')
    .leftJoin('pet', 'pet.owner_id', 'person.id')
    .select([
      'person.id',
      'person.first_name',
      'person.last_name',
      (eb) => eb.fn.count<number>('pet.id').as('pet_count')
    ])
    .groupBy(['person.id', 'person.first_name', 'person.last_name'])
    .having((eb) => eb.fn.count('pet.id'), '>', 0)
    .execute();

  // Correlated subquery
  const personsWithLatestMovie = await db
    .selectFrom('person')
    .select([
      'person.first_name',
      'person.last_name',
      (eb) => eb
        .selectFrom('movie')
        .innerJoin('person_movie', 'person_movie.movie_id', 'movie.id')
        .select('movie.title')
        .where('person_movie.person_id', '=', eb.ref('person.id'))
        .orderBy('movie.release_date', 'desc')
        .limit(1)
        .as('latest_movie')
    ])
    .execute();

  return { personsWithPets, personsWithPetCount, personsWithLatestMovie };
}

// Expression Builder utilization
async function expressionBuilderExamples() {
  // Complex condition construction
  const complexCondition = await db
    .selectFrom('person')
    .selectAll()
    .where((eb) => eb.or([
      eb.and([
        eb('age', '>=', 18),
        eb('age', '<=', 65)
      ]),
      eb('gender', '=', 'other'),
      eb.exists(
        eb.selectFrom('pet')
          .select('id')
          .where('owner_id', '=', eb.ref('person.id'))
          .where('species', 'in', ['dog', 'cat'])
      )
    ]))
    .execute();

  // Computed fields
  const personsWithComputedFields = await db
    .selectFrom('person')
    .select([
      'first_name',
      'last_name',
      (eb) => eb.fn('concat', ['first_name', eb.val(' '), 'last_name']).as('full_name'),
      (eb) => eb.case()
        .when('age', '<', 20).then('Young')
        .when('age', '<', 60).then('Adult')
        .else('Senior')
        .end()
        .as('age_group'),
      (eb) => eb('age', '*', 365.25).as('age_in_days')
    ])
    .execute();

  // JSON operations
  const jsonQueries = await db
    .selectFrom('person')
    .select([
      'first_name',
      'last_name',
      (eb) => eb.ref('preferences', '->', 'theme').as('theme'),
      (eb) => eb.ref('preferences', '->>', 'language').as('language')
    ])
    .where((eb) => eb.ref('preferences', '->', 'notifications').eq(true))
    .execute();

  return { complexCondition, personsWithComputedFields, jsonQueries };
}

// Window functions and CTEs
async function windowFunctionsAndCte() {
  // Window functions
  const rankedPersons = await db
    .selectFrom('person')
    .select([
      'first_name',
      'last_name',
      'age',
      (eb) => eb.fn('row_number').over(
        ob => ob.orderBy('age', 'desc')
      ).as('age_rank'),
      (eb) => eb.fn('rank').over(
        ob => ob.partitionBy('gender').orderBy('age', 'desc')
      ).as('gender_age_rank'),
      (eb) => eb.fn('avg', ['age']).over(
        ob => ob.partitionBy('gender')
      ).as('avg_age_by_gender')
    ])
    .execute();

  // Common Table Expression (WITH)
  const cteQuery = await db
    .with('adult_persons', (db) => db
      .selectFrom('person')
      .select(['id', 'first_name', 'last_name', 'age'])
      .where('age', '>=', 18)
    )
    .with('pet_counts', (db) => db
      .selectFrom('pet')
      .select([
        'owner_id',
        (eb) => eb.fn.count<number>('id').as('pet_count')
      ])
      .groupBy('owner_id')
    )
    .selectFrom('adult_persons as ap')
    .leftJoin('pet_counts as pc', 'pc.owner_id', 'ap.id')
    .select([
      'ap.first_name',
      'ap.last_name',
      'ap.age',
      (eb) => eb.fn.coalesce('pc.pet_count', eb.lit(0)).as('pet_count')
    ])
    .execute();

  return { rankedPersons, cteQuery };
}

// Dynamic query building
async function dynamicQueryBuilding() {
  interface SearchFilters {
    name?: string;
    minAge?: number;
    maxAge?: number;
    gender?: 'male' | 'female' | 'other';
    hasPets?: boolean;
  }

  async function searchPersons(filters: SearchFilters) {
    let query = db
      .selectFrom('person')
      .selectAll();

    // Name filter
    if (filters.name) {
      query = query.where((eb) => eb.or([
        eb('first_name', 'ilike', `%${filters.name}%`),
        eb('last_name', 'ilike', `%${filters.name}%`)
      ]));
    }

    // Age filters
    if (filters.minAge !== undefined) {
      query = query.where('age', '>=', filters.minAge);
    }
    if (filters.maxAge !== undefined) {
      query = query.where('age', '<=', filters.maxAge);
    }

    // Gender filter
    if (filters.gender) {
      query = query.where('gender', '=', filters.gender);
    }

    // Pet ownership filter
    if (filters.hasPets !== undefined) {
      if (filters.hasPets) {
        query = query.where((eb) => eb.exists(
          eb.selectFrom('pet')
            .select('id')
            .where('owner_id', '=', eb.ref('person.id'))
        ));
      } else {
        query = query.where((eb) => eb.not(eb.exists(
          eb.selectFrom('pet')
            .select('id')
            .where('owner_id', '=', eb.ref('person.id'))
        )));
      }
    }

    return query.execute();
  }

  // Usage example
  const searchResults = await searchPersons({
    name: 'John',
    minAge: 20,
    maxAge: 50,
    gender: 'male',
    hasPets: true
  });

  return searchResults;
}

Transactions and Batch Processing

// Transaction management
async function transactionExamples() {
  // Basic transaction
  const result = await db.transaction().execute(async (trx) => {
    // Create new person
    const person = await trx
      .insertInto('person')
      .values({
        first_name: 'Transaction',
        last_name: 'Test',
        email: '[email protected]',
        age: 30,
        gender: 'other'
      })
      .returning(['id', 'first_name'])
      .executeTakeFirstOrThrow();

    // Add pet
    const pet = await trx
      .insertInto('pet')
      .values({
        name: 'Transaction Pet',
        species: 'dog',
        owner_id: person.id,
        birth_date: new Date('2020-01-01')
      })
      .returning(['id', 'name'])
      .executeTakeFirstOrThrow();

    // Associate with movie
    await trx
      .insertInto('person_movie')
      .values({
        person_id: person.id,
        movie_id: 1,
        rating: 5,
        watched_at: new Date()
      })
      .execute();

    return { person, pet };
  });

  console.log('Transaction completed:', result);
  return result;
}

// Batch operations and performance optimization
async function batchOperations() {
  // Batch insert
  const batchInsertData = Array.from({ length: 1000 }, (_, i) => ({
    first_name: `User${i}`,
    last_name: `Batch`,
    email: `user${i}@batch.com`,
    age: Math.floor(Math.random() * 80) + 18,
    gender: ['male', 'female', 'other'][Math.floor(Math.random() * 3)] as const
  }));

  const batchResult = await db
    .insertInto('person')
    .values(batchInsertData)
    .returning('id')
    .execute();

  console.log(`Inserted ${batchResult.length} records`);

  // Streaming processing (large data)
  const streamProcessor = db
    .selectFrom('person')
    .selectAll()
    .where('age', '>', 50)
    .stream();

  let processedCount = 0;
  for await (const person of streamProcessor) {
    // Process each record
    console.log(`Processing: ${person.first_name} ${person.last_name}`);
    processedCount++;
    
    if (processedCount % 100 === 0) {
      console.log(`${processedCount} records processed`);
    }
  }

  return { batchCount: batchResult.length, streamProcessed: processedCount };
}

// Query compilation and reuse
async function compiledQueries() {
  // Query compilation
  const findPersonByIdQuery = db
    .selectFrom('person')
    .selectAll()
    .where('id', '=', sql.placeholder<number>())
    .compile();

  // Execute compiled query
  const person1 = await findPersonByIdQuery.execute({ id: 1 });
  const person2 = await findPersonByIdQuery.execute({ id: 2 });

  // Complex query compilation
  const searchQuery = db
    .selectFrom('person')
    .leftJoin('pet', 'pet.owner_id', 'person.id')
    .select([
      'person.id',
      'person.first_name',
      'person.last_name',
      (eb) => eb.fn.count<number>('pet.id').as('pet_count')
    ])
    .where('person.age', '>=', sql.placeholder<number>())
    .where('person.age', '<=', sql.placeholder<number>())
    .groupBy(['person.id', 'person.first_name', 'person.last_name'])
    .compile();

  const searchResults = await searchQuery.execute({
    minAge: 20,
    maxAge: 65
  });

  return { person1, person2, searchResults };
}

Error Handling and Debugging

import { DatabaseError, sql } from 'kysely';

// Error handling
async function errorHandlingExamples() {
  try {
    // Constraint violation error
    await db
      .insertInto('person')
      .values({
        first_name: 'Test',
        last_name: 'User',
        email: '[email protected]', // Existing email
        age: 25,
        gender: 'male'
      })
      .execute();
  } catch (error) {
    if (error instanceof DatabaseError) {
      switch (error.code) {
        case '23505': // Unique violation (PostgreSQL)
          console.log('Unique constraint violation:', error.message);
          break;
        case '23503': // Foreign key violation
          console.log('Foreign key constraint violation:', error.message);
          break;
        case '23502': // Not null violation
          console.log('Not null constraint violation:', error.message);
          break;
        default:
          console.log('Database error:', error.code, error.message);
      }
    } else {
      console.error('Unexpected error:', error);
    }
  }

  // Type-safe error handling
  const safeInsert = async (personData: NewPerson) => {
    try {
      const result = await db
        .insertInto('person')
        .values(personData)
        .returning(['id', 'first_name', 'last_name'])
        .executeTakeFirstOrThrow();
      
      return { success: true, data: result };
    } catch (error) {
      return {
        success: false,
        error: error instanceof Error ? error.message : 'Unknown error'
      };
    }
  };

  const insertResult = await safeInsert({
    first_name: 'Safe',
    last_name: 'Insert',
    email: '[email protected]',
    age: 30,
    gender: 'other'
  });

  if (insertResult.success) {
    console.log('Insert successful:', insertResult.data);
  } else {
    console.log('Insert failed:', insertResult.error);
  }
}

// Query debugging
async function debuggingExamples() {
  // Enable SQL output
  const debugDb = db.withPlugins([
    {
      transformQuery(args) {
        console.log('Executing SQL:', args.query.sql);
        console.log('Parameters:', args.query.parameters);
        return args.query;
      }
    }
  ]);

  await debugDb
    .selectFrom('person')
    .selectAll()
    .where('age', '>', 30)
    .execute();

  // Query execution time measurement
  const startTime = Date.now();
  const result = await db
    .selectFrom('person')
    .leftJoin('pet', 'pet.owner_id', 'person.id')
    .select([
      'person.first_name',
      'person.last_name',
      (eb) => eb.fn.count<number>('pet.id').as('pet_count')
    ])
    .groupBy(['person.id', 'person.first_name', 'person.last_name'])
    .execute();
  
  const executionTime = Date.now() - startTime;
  console.log(`Query execution time: ${executionTime}ms, Result count: ${result.length}`);

  // Debug type assertions
  const typedQuery = db
    .selectFrom('person')
    .select([
      'first_name',
      'last_name',
      (eb) => eb.ref('preferences', '->', 'theme').$asScalar().as('theme')
    ])
    .where((eb) => eb.ref('preferences', '->', 'notifications').eq(true));

  // Query type checking
  type QueryResult = InferResult<typeof typedQuery>[0];
  // QueryResult type: { first_name: string; last_name: string | null; theme: unknown }

  const typedResult = await typedQuery.execute();
  return typedResult;
}

// Performance monitoring
async function performanceMonitoring() {
  // Query execution time statistics
  class QueryStatsPlugin {
    private stats: Array<{ sql: string; time: number }> = [];

    transformQuery(args: any) {
      const startTime = Date.now();
      
      return {
        ...args.query,
        then: (callback: any) => {
          return args.query.then((result: any) => {
            const endTime = Date.now();
            this.stats.push({
              sql: args.query.sql,
              time: endTime - startTime
            });
            
            return callback ? callback(result) : result;
          });
        }
      };
    }

    getStats() {
      return {
        totalQueries: this.stats.length,
        averageTime: this.stats.reduce((sum, stat) => sum + stat.time, 0) / this.stats.length,
        slowestQuery: this.stats.reduce((slowest, stat) => 
          stat.time > slowest.time ? stat : slowest, { sql: '', time: 0 })
      };
    }
  }

  const statsPlugin = new QueryStatsPlugin();
  const monitoredDb = db.withPlugins([statsPlugin]);

  // Execute multiple queries
  await monitoredDb.selectFrom('person').selectAll().execute();
  await monitoredDb.selectFrom('pet').selectAll().execute();
  
  const stats = statsPlugin.getStats();
  console.log('Query statistics:', stats);

  return stats;
}