Kysely Mastery: Automating TypeScript Types from Data

JSON to Kysely: Type-Safe SQL Query Building Without the ORM Layer

Kysely occupies a specific niche: you want to write real SQL — including window functions, recursive CTEs, lateral joins, and database-specific syntax — but you also want TypeScript to catch typos in column names, incorrect column types in WHERE clauses, and broken JOIN references at compile time. It achieves this without adding a runtime query engine or generating code. The TypeScript types you define from your JSON shape become the contract your entire query layer is checked against.

Live Example: Database Interface from JSON Shape

// Input JSON
{
  "user_id": "uuid-123",
  "email": "dev@kysely.dev",
  "is_active": true,
  "subscription_tier": "pro",
  "profile": {
    "bio": "SQL Enthusiast",
    "avatar_url": "https://cdn.example.com/avatars/dev.jpg"
  },
  "created_at": "2024-01-15T08:30:00Z"
}

// Generated Kysely Database Interface
import { Generated, Insertable, Selectable, Updateable, JSONColumnType } from 'kysely';

// Table row shape — what SELECT queries return
interface UserTable {
  user_id:           string;
  email:             string;
  is_active:         boolean;
  subscription_tier: 'free' | 'pro' | 'enterprise';
  profile:           JSONColumnType<
                       { bio: string; avatar_url: string },  // SELECT type
                       { bio?: string; avatar_url?: string }, // INSERT type
                       { bio?: string; avatar_url?: string }  // UPDATE type
                     >;
  created_at: Generated<Date>;  // auto-set by DB, optional on insert
}

// Derived types for each operation
export type User       = Selectable<UserTable>;
export type NewUser    = Insertable<UserTable>;
export type UserUpdate = Updateable<UserTable>;

// Root database interface — all tables
export interface Database {
  users: UserTable;
}

JSONColumnType<Select, Insert, Update> lets you express that a JSON column's SELECT type (non-null, fully typed) differs from its INSERT type (optional fields) — because the database might set defaults that aren't in the INSERT payload. This is more precise than any ORM that uses a single type for all three operations.

Type-Safe Queries: SELECT, INSERT, UPDATE, DELETE

import { Kysely, PostgresDialect } from 'kysely';
import { Pool } from 'pg';
import { Database } from './schema';

const db = new Kysely<Database>({
  dialect: new PostgresDialect({ pool: new Pool({ connectionString: process.env.DATABASE_URL }) }),
});

// SELECT — TypeScript knows every column name and type
const activeUsers = await db
  .selectFrom('users')
  .select(['user_id', 'email', 'subscription_tier'])
  .where('is_active', '=', true)
  .orderBy('created_at', 'desc')
  .limit(20)
  .execute();
// activeUsers: Array<{ user_id: string; email: string; subscription_tier: 'free'|'pro'|'enterprise' }>

// INSERT — NewUser type enforced at compile time
const newUser: NewUser = {
  user_id: crypto.randomUUID(),
  email:   'newuser@example.com',
  is_active: true,
  subscription_tier: 'free',
};
await db.insertInto('users').values(newUser).execute();

// UPDATE — UpdateUser type (all fields optional)
await db
  .updateTable('users')
  .set({ subscription_tier: 'pro', is_active: true })
  .where('user_id', '=', userId)
  .execute();

// DELETE with returning
const deleted = await db
  .deleteFrom('users')
  .where('user_id', '=', userId)
  .returning(['user_id', 'email'])
  .executeTakeFirst();

Joins with Full Type Safety

Kysely's type system tracks the tables in scope and validates column references across joined tables:

interface PostTable {
  id:        Generated<number>;
  author_id: string;
  title:     string;
  body:      string;
  status:    'draft' | 'published';
  created_at: Generated<Date>;
}

// Database interface with both tables
interface Database {
  users: UserTable;
  posts: PostTable;
}

// JOIN — column names from both tables are type-checked
const results = await db
  .selectFrom('posts')
  .innerJoin('users', 'users.user_id', 'posts.author_id')
  .select([
    'posts.id',
    'posts.title',
    'posts.status',
    'users.email as author_email',   // aliased — typed as string
    'users.subscription_tier',
  ])
  .where('posts.status', '=', 'published')
  .where('users.is_active', '=', true)
  .orderBy('posts.created_at', 'desc')
  .execute();

// results[0].title — string
// results[0].author_email — string
// results[0].subscription_tier — 'free' | 'pro' | 'enterprise'
// Typo in any column name: TypeScript compile error

CTEs and Window Functions

This is where Kysely outshines every ORM — complex SQL is fully type-safe:

// Common Table Expression with typed CTE reference
const result = await db
  .with('active_users', (db) =>
    db.selectFrom('users')
      .select(['user_id', 'email', 'created_at'])
      .where('is_active', '=', true)
  )
  .selectFrom('active_users')
  .innerJoin('posts', 'posts.author_id', 'active_users.user_id')
  .select([
    'active_users.email',
    db.fn.count('posts.id').as('post_count'),
  ])
  .groupBy('active_users.user_id')
  .having(db.fn.count('posts.id'), '>=', 5)
  .execute();
// result[0].email — string
// result[0].post_count — string (PostgreSQL COUNT returns bigint as string)

// Window function
const ranked = await db
  .selectFrom('posts')
  .select([
    'id',
    'author_id',
    'created_at',
    db.fn.agg<number>('ROW_NUMBER').over(b =>
      b.partitionBy('author_id').orderBy('created_at', 'desc')
    ).as('rank_in_author'),
  ])
  .execute();

kysely-codegen: Generate Types from Your Live Database

For existing databases, generate the TypeScript interfaces automatically:

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

# Generate interfaces from PostgreSQL
npx kysely-codegen --dialect postgres --url $DATABASE_URL --out-file src/db/types.ts

# Output: src/db/types.ts
# export interface DB {
#   users: UsersTable;
#   posts: PostsTable;
#   ...
# }
# export interface UsersTable {
#   user_id: string;
#   email: string;
#   is_active: Generated<boolean>;
#   ...
# }

This eliminates schema drift entirely — regenerate after every migration and your TypeScript types always match the actual database schema. Works with PostgreSQL, MySQL, SQLite, and MSSQL.

Transactions

// Transaction — all queries share a connection and roll back together on error
await db.transaction().execute(async (trx) => {
  const user = await trx
    .insertInto('users')
    .values(newUser)
    .returning('user_id')
    .executeTakeFirstOrThrow();

  await trx
    .insertInto('posts')
    .values({ author_id: user.user_id, title: 'First Post', body: '...', status: 'draft' })
    .execute();
  // If this throws, the user insert is automatically rolled back
});

Kysely vs. Drizzle vs. Knex

• Kysely vs. Knex: Knex is the pioneer of JavaScript query builders but has minimal TypeScript support — column names are strings with no compile-time validation. Kysely is the type-safe successor designed from the ground up for TypeScript.
• Kysely vs. Drizzle: Drizzle defines schemas in TypeScript and derives types + migrations from them. Kysely only needs a TypeScript interface — it doesn't own your schema. Use Kysely when your schema lives elsewhere (another tool, a DBA's migrations), or when you want fine-grained SQL control. Use Drizzle when you want TypeScript to be the source of truth for your schema definition and migrations.
• Kysely vs. Prisma: Prisma generates a client and abstracts SQL behind a high-level API. Kysely writes SQL that you can read directly — there's no hidden query plan. For complex reporting queries or databases Prisma doesn't support well, Kysely is more flexible.

Best Practices for Production

• Use executeTakeFirstOrThrow() when you expect exactly one row: executeTakeFirst() returns T | undefined. If undefined would be a bug (fetching by primary key), use executeTakeFirstOrThrow() to throw a NoResultError instead of silently passing undefined into business logic.
• Generate types from the live database with kysely-codegen: Hand-written interfaces and real schemas drift over time. Automate regeneration in your CI pipeline after migrations run.
• Centralize the db instance: Create one db instance, export it from a dedicated module, and import it everywhere. Multiple instances each maintain their own connection pool.
• Use .selectAll() only in codegen-driven codebases: selectAll() returns all columns but requires your interface to be exactly correct. For hand-written interfaces, enumerate selected columns explicitly — it makes query intent clear and avoids over-fetching.

FAQ

Q: Does Kysely handle migrations?
A: Kysely has a Migrator class that runs migration files (JavaScript/TypeScript functions), but it doesn't auto-generate migrations from a schema definition. Use Kysely's Migrator with hand-written SQL or TypeScript migration files, or use kysely-codegen to generate types from a schema managed by another migration tool (Flyway, Liquibase, etc.).

Q: Can I use Kysely with connection pooling in serverless environments?
A: Yes. Use @neondatabase/serverless for Neon, @planetscale/database for PlanetScale, or the standard pg pool for regular PostgreSQL. Each has a Kysely dialect package. In Lambda/Edge environments, configure the pool with max: 1 to avoid connection exhaustion from concurrent function instances.

Q: How do I handle database-generated fields like auto-increment IDs?
A: Wrap the column type in Generated<T> (e.g., Generated<number> for serial, Generated<Date> for default now()). Kysely's Insertable<T> utility automatically makes Generated fields optional on insert — you don't pass them in, and the SELECT result type has the actual value.

Q: Can Kysely validate query results at runtime?
A: No — Kysely is TypeScript-only with no runtime component. Pair it with Zod or Valibot for runtime validation of query results if you need to guard against schema drift between your TypeScript types and the actual database schema.