kysely

What this skill does

# Kysely - Type-Safe SQL Query Builder

## Overview

Kysely is a type-safe TypeScript SQL query builder that provides end-to-end type safety from database schema to query results. Unlike ORMs, it generates plain SQL and gives you full control while maintaining perfect TypeScript inference.

**Key Features**:
- Complete type inference (schema → queries → results)
- Zero runtime overhead (compiles to SQL)
- Database-agnostic (PostgreSQL, MySQL, SQLite, MSSQL)
- Migration system included
- Plugin ecosystem (CTEs, JSON, geospatial)
- Raw SQL integration when needed

**Installation**:
```bash
npm install kysely
# Database driver (choose one)
npm install pg              # PostgreSQL
npm install mysql2          # MySQL
npm install better-sqlite3  # SQLite
```

## Quick Start

### 1. Define Database Schema Types

```typescript
import { Generated, Selectable, Insertable, Updateable } from 'kysely';

// Table interface (all columns)
interface UserTable {
  id: Generated<number>;
  email: string;
  name: string | null;
  created_at: Generated<Date>;
  updated_at: Date;
}

interface PostTable {
  id: Generated<number>;
  user_id: number;
  title: string;
  content: string;
  published: Generated<boolean>;
  created_at: Generated<Date>;
}

// Database interface
interface Database {
  users: UserTable;
  posts: PostTable;
}

// Type-safe query result types
type User = Selectable<UserTable>;
type NewUser = Insertable<UserTable>;
type UserUpdate = Updateable<UserTable>;
```

### 2. Create Database Instance

```typescript
import { Kysely, PostgresDialect } from 'kysely';
import { Pool } from 'pg';

const db = new Kysely<Database>({
  dialect: new PostgresDialect({
    pool: new Pool({
      host: process.env.DB_HOST,
      database: process.env.DB_NAME,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      max: 10,
    }),
  }),
});
```

### 3. Type-Safe Queries

```typescript
// SELECT with full type inference
const users = await db
  .selectFrom('users')
  .select(['id', 'email', 'name'])
  .where('created_at', '>', new Date('2024-01-01'))
  .execute();
// Type: Array<{ id: number; email: string; name: string | null }>

// INSERT with type checking
const newUser: NewUser = {
  email: '[email protected]',
  name: 'Alice',
  updated_at: new Date(),
};

const inserted = await db
  .insertInto('users')
  .values(newUser)
  .returningAll()
  .executeTakeFirstOrThrow();
// Type: User

// UPDATE
await db
  .updateTable('users')
  .set({ name: 'Alice Updated', updated_at: new Date() })
  .where('id', '=', 1)
  .execute();

// DELETE
await db
  .deleteFrom('users')
  .where('email', 'like', '%@spam.com')
  .execute();
```

## Advanced Query Patterns

### Joins with Type Safety

```typescript
// INNER JOIN
const usersWithPosts = await db
  .selectFrom('users')
  .innerJoin('posts', 'posts.user_id', 'users.id')
  .select([
    'users.id',
    'users.name',
    'posts.title',
    'posts.content',
  ])
  .execute();
// Type: Array<{ id: number; name: string | null; title: string; content: string }>

// LEFT JOIN with null handling
const usersWithOptionalPosts = await db
  .selectFrom('users')
  .leftJoin('posts', 'posts.user_id', 'users.id')
  .select([
    'users.id',
    'users.email',
    'posts.title',  // Type: string | null (from LEFT JOIN)
  ])
  .execute();

// Multiple joins
const complexQuery = await db
  .selectFrom('posts')
  .innerJoin('users', 'users.id', 'posts.user_id')
  .leftJoin('comments', 'comments.post_id', 'posts.id')
  .select([
    'posts.id as postId',
    'posts.title',
    'users.name as authorName',
    'comments.id as commentId',
  ])
  .execute();
```

### Aggregations and Grouping

```typescript
import { sql } from 'kysely';

// COUNT, AVG, SUM
const stats = await db
  .selectFrom('posts')
  .select([
    'user_id',
    db.fn.count<number>('id').as('post_count'),
    db.fn.avg<number>('views').as('avg_views'),
  ])
  .groupBy('user_id')
  .having(db.fn.count('id'), '>', 5)
  .execute();
// Type: Array<{ user_id: number; post_count: number; avg_views: number }>

// Complex aggregations with raw SQL
const advanced = await db
  .selectFrom('users')
  .select([
    'users.id',
    sql<number>`COUNT(DISTINCT posts.id)`.as('total_posts'),
    sql<Date>`MAX(posts.created_at)`.as('latest_post'),
  ])
  .leftJoin('posts', 'posts.user_id', 'users.id')
  .groupBy('users.id')
  .execute();
```

### Subqueries

```typescript
// Scalar subquery
const usersWithPostCount = await db
  .selectFrom('users')
  .select([
    'users.id',
    'users.name',
    (eb) =>
      eb
        .selectFrom('posts')
        .select(eb.fn.count<number>('id').as('count'))
        .whereRef('posts.user_id', '=', 'users.id')
        .as('post_count'),
  ])
  .execute();

// EXISTS subquery
const activeUsers = await db
  .selectFrom('users')
  .selectAll()
  .where((eb) =>
    eb.exists(
      eb
        .selectFrom('posts')
        .select('id')
        .whereRef('posts.user_id', '=', 'users.id')
        .where('created_at', '>', new Date('2024-01-01'))
    )
  )
  .execute();

// IN subquery
const usersInTopTier = await db
  .selectFrom('users')
  .selectAll()
  .where(
    'id',
    'in',
    db.selectFrom('posts')
      .select('user_id')
      .groupBy('user_id')
      .having(db.fn.count('id'), '>', 100)
  )
  .execute();
```

### Common Table Expressions (CTEs)

```typescript
// WITH clause
const result = await db
  .with('popular_posts', (db) =>
    db
      .selectFrom('posts')
      .select(['id', 'user_id', 'title'])
      .where('views', '>', 1000)
  )
  .with('active_users', (db) =>
    db
      .selectFrom('users')
      .select(['id', 'email'])
      .where('last_login', '>', new Date('2024-01-01'))
  )
  .selectFrom('popular_posts')
  .innerJoin('active_users', 'active_users.id', 'popular_posts.user_id')
  .selectAll()
  .execute();

// Recursive CTE (organizational hierarchy)
interface OrgNode {
  id: number;
  name: string;
  parent_id: number | null;
  level: number;
}

const hierarchy = await db
  .withRecursive('org_tree', (db) =>
    db
      .selectFrom('departments')
      .select(['id', 'name', 'parent_id', sql<number>`0`.as('level')])
      .where('parent_id', 'is', null)
      .unionAll(
        db
          .selectFrom('departments')
          .innerJoin('org_tree', 'org_tree.id', 'departments.parent_id')
          .select([
            'departments.id',
            'departments.name',
            'departments.parent_id',
            sql<number>`org_tree.level + 1`.as('level'),
          ])
      )
  )
  .selectFrom('org_tree')
  .selectAll()
  .execute();
```

## Schema Generation from Database

### Using kysely-codegen

```bash
# Install
npm install --save-dev kysely-codegen

# Generate types from existing database
npx kysely-codegen --url "postgresql://user:pass@localhost:5432/mydb"
```

Generated output:
```typescript
// Generated by kysely-codegen
import type { ColumnType, Generated } from 'kysely';

export interface Database {
  users: UsersTable;
  posts: PostsTable;
  comments: CommentsTable;
}

export interface UsersTable {
  id: Generated<number>;
  email: string;
  name: string | null;
  created_at: Generated<Date>;
}

export interface PostsTable {
  id: Generated<number>;
  user_id: number;
  title: string;
  content: string;
  published: Generated<boolean>;
  created_at: Generated<Date>;
}
```

### Custom Type Mapping

```typescript
// Map database types to TypeScript types
interface CustomTypes {
  timestamp: Date;
  jsonb: unknown;
  numeric: string; // Preserve precision
  uuid: string;
}

interface ProductTable {
  id: ColumnType<string, string | undefined, string>; // SELECT, INSERT, UPDATE types
  metadata: ColumnType<Record<string, unknown>, string, string>; // JSON column
  price: ColumnType<number, number, number | undefined>; // Numeric
}
```

## Migrations

### Migration Setup

```typescript
import { Kysely, Migrator, FileMigrationProvider } from 'kysely';
import { promises as fs } from 'fs';
import * as