Kysely Development Guidelines You are an expert in Kysely, TypeScript, and SQL database design with a focus on type safety and query optimization. Core Principles Kysely is a thin abstraction layer over SQL, designed by SQL lovers for SQL lovers Full type safety with autocompletion for tables, columns, and query results Predictable 1:1 compilation to SQL - what you write is what you get No magic or hidden behavior - explicit and transparent query building Works with Node.js, Deno, Bun, Cloudflare Workers, and browsers Database Interface Definition Define Your Database Schema import { Generated , ColumnType , Selectable , Insertable , Updateable } from "kysely" ; // Define table interfaces interface UserTable { id : Generated < number

; email : string ; name : string | null ; is_active : boolean ; created_at : Generated < Date

; updated_at : ColumnType < Date , Date | undefined , Date

; } interface PostTable { id : Generated < number

; title : string ; content : string | null ; author_id : number ; published_at : Date | null ; created_at : Generated < Date

; } // Define the database interface interface Database { users : UserTable ; posts : PostTable ; } // Export helper types for each table export type User = Selectable < UserTable

; export type NewUser = Insertable < UserTable

; export type UserUpdate = Updateable < UserTable

; export type Post = Selectable < PostTable

; export type NewPost = Insertable < PostTable

; export type PostUpdate = Updateable < PostTable

; Generated vs ColumnType Generated - Columns auto-generated by the database (auto-increment, defaults) ColumnType - Different types for different operations Database Connection PostgreSQL Setup import { Kysely , PostgresDialect } from "kysely" ; import { Pool } from "pg" ; const db = new Kysely < Database

( { dialect : new PostgresDialect ( { pool : new Pool ( { connectionString : process . env . DATABASE_URL , } ) , } ) , } ) ; export { db } ; MySQL Setup import { Kysely , MysqlDialect } from "kysely" ; import { createPool } from "mysql2" ; const db = new Kysely < Database

( { dialect : new MysqlDialect ( { pool : createPool ( { uri : process . env . DATABASE_URL , } ) , } ) , } ) ; SQLite Setup import { Kysely , SqliteDialect } from "kysely" ; import Database from "better-sqlite3" ; const db = new Kysely < Database

( { dialect : new SqliteDialect ( { database : new Database ( "database.db" ) , } ) , } ) ; Query Patterns Select Queries // Select all columns from a table const users = await db . selectFrom ( "users" ) . selectAll ( ) . execute ( ) ; // Select specific columns const userEmails = await db . selectFrom ( "users" ) . select ( [ "id" , "email" , "name" ] ) . execute ( ) ; // With WHERE conditions const activeUsers = await db . selectFrom ( "users" ) . selectAll ( ) . where ( "is_active" , "=" , true ) . execute ( ) ; // Multiple conditions const filteredUsers = await db . selectFrom ( "users" ) . selectAll ( ) . where ( "is_active" , "=" , true ) . where ( "email" , "like" , "%@example.com" ) . execute ( ) ; // OR conditions const users = await db . selectFrom ( "users" ) . selectAll ( ) . where ( ( eb ) => eb . or ( [ eb ( "name" , "=" , "John" ) , eb ( "name" , "=" , "Jane" ) , ] ) ) . execute ( ) ; Column Aliases // Kysely automatically infers alias types const result = await db . selectFrom ( "users" ) . select ( [ "id" , "email" , "name as userName" , // Alias parsed and typed correctly ] ) . executeTakeFirst ( ) ; // result.userName is typed correctly Joins // Inner join const postsWithAuthors = await db . selectFrom ( "posts" ) . innerJoin ( "users" , "users.id" , "posts.author_id" ) . select ( [ "posts.id" , "posts.title" , "users.name as authorName" , ] ) . execute ( ) ; // Left join const usersWithPosts = await db . selectFrom ( "users" ) . leftJoin ( "posts" , "posts.author_id" , "users.id" ) . select ( [ "users.id" , "users.name" , "posts.title as postTitle" , ] ) . execute ( ) ; Subqueries // Subquery in select const usersWithPostCount = await db . selectFrom ( "users" ) . select ( [ "users.id" , "users.name" , ( eb ) => eb . selectFrom ( "posts" ) . select ( eb . fn . count < number

( "posts.id" ) . as ( "count" ) ) . whereRef ( "posts.author_id" , "=" , "users.id" ) . as ( "postCount" ) , ] ) . execute ( ) ; // Subquery in where const usersWithPosts = await db . selectFrom ( "users" ) . selectAll ( ) . where ( "id" , "in" , ( eb ) => eb . selectFrom ( "posts" ) . select ( "author_id" ) . distinct ( ) ) . execute ( ) ; Insert Operations // Single insert const result = await db . insertInto ( "users" ) . values ( { email : "user@example.com" , name : "John Doe" , is_active : true , } ) . returning ( [ "id" , "email" , "created_at" ] ) . executeTakeFirstOrThrow ( ) ; // Bulk insert await db . insertInto ( "users" ) . values ( [ { email : "user1@example.com" , name : "User 1" , is_active : true } , { email : "user2@example.com" , name : "User 2" , is_active : true } , ] ) . execute ( ) ; // Insert with on conflict (upsert) await db . insertInto ( "users" ) . values ( { email : "user@example.com" , name : "John" , is_active : true , } ) . onConflict ( ( oc ) => oc . column ( "email" ) . doUpdateSet ( { name : "John Updated" , updated_at : new Date ( ) , } ) ) . execute ( ) ; Update Operations const result = await db . updateTable ( "users" ) . set ( { name : "Jane Doe" , updated_at : new Date ( ) , } ) . where ( "id" , "=" , 1 ) . returning ( [ "id" , "name" , "updated_at" ] ) . executeTakeFirst ( ) ; Delete Operations const result = await db . deleteFrom ( "users" ) . where ( "id" , "=" , 1 ) . returning ( [ "id" , "email" ] ) . executeTakeFirst ( ) ; Transactions await db . transaction ( ) . execute ( async ( trx ) => { const user = await trx . insertInto ( "users" ) . values ( { email : "user@example.com" , name : "User" , is_active : true , } ) . returning ( [ "id" ] ) . executeTakeFirstOrThrow ( ) ; await trx . insertInto ( "posts" ) . values ( { title : "First Post" , author_id : user . id , } ) . execute ( ) ; } ) ; Type Generation with kysely-codegen Use kysely-codegen to generate types from your existing database:

Install

npm install -D kysely-codegen

Generate types (reads from DATABASE_URL environment variable)

npx kysely-codegen

Specify output file

npx kysely-codegen --out-file src/db/types.ts Regenerate types whenever the database schema changes. Plugins CamelCase Plugin Transform snake_case column names to camelCase: import { Kysely , PostgresDialect , CamelCasePlugin } from "kysely" ; const db = new Kysely < Database

( { dialect : new PostgresDialect ( { pool } ) , plugins : [ new CamelCasePlugin ( ) ] , } ) ; Custom Plugins import { KyselyPlugin , PluginTransformQueryArgs , PluginTransformResultArgs } from "kysely" ; class LoggingPlugin implements KyselyPlugin { transformQuery ( args : PluginTransformQueryArgs ) : RootOperationNode { console . log ( "Query:" , args . node ) ; return args . node ; } async transformResult ( args : PluginTransformResultArgs ) : Promise < QueryResult < unknown

{ console . log ( "Result:" , args . result ) ; return args . result ; } } Advanced Patterns Dynamic Query Building function findUsers ( filters : { email ? : string ; isActive ? : boolean ; name ? : string ; } ) { let query = db . selectFrom ( "users" ) . selectAll ( ) ; if ( filters . email ) { query = query . where ( "email" , "=" , filters . email ) ; } if ( filters . isActive !== undefined ) { query = query . where ( "is_active" , "=" , filters . isActive ) ; } if ( filters . name ) { query = query . where ( "name" , "like" , % ${ filters . name } % ) ; } return query . execute ( ) ; } Raw SQL import { sql } from "kysely" ; // Raw expression in select const result = await db . selectFrom ( "users" ) . select ( [ "id" , sql < string

CONCAT(first_name, ' ', last_name) . as ( "fullName" ) , ] ) . execute ( ) ; // Raw expression in where const users = await db . selectFrom ( "users" ) . selectAll ( ) . where ( sql LOWER(email) , "=" , "user@example.com" ) . execute ( ) ; Common Table Expressions (CTEs) const result = await db . with ( "active_users" , ( db ) => db . selectFrom ( "users" ) . selectAll ( ) . where ( "is_active" , "=" , true ) ) . selectFrom ( "active_users" ) . selectAll ( ) . execute ( ) ; Best Practices TypeScript Configuration Enable strict mode in tsconfig.json: { "compilerOptions" : { "strict" : true , "strictNullChecks" : true , "target" : "ES2020" } } Use TypeScript 5.4 or later for best type inference. Performance Tips Select only needed columns - Avoid selectAll() when you only need specific fields Use proper indexes - Ensure database indexes exist for WHERE and JOIN columns Batch operations - Use bulk inserts for multiple records Connection pooling - Always use connection pools for production Prepared statements - Kysely automatically uses prepared statements Error Handling import { NoResultError } from "kysely" ; try { const user = await db . selectFrom ( "users" ) . selectAll ( ) . where ( "id" , "=" , 999 ) . executeTakeFirstOrThrow ( ) ; } catch ( error ) { if ( error instanceof NoResultError ) { // Handle not found } throw error ; } Query Composability // Create reusable query parts function withActiveUsers ( db : Kysely < Database

) { return db . selectFrom ( "users" ) . where ( "is_active" , "=" , true ) ; } // Use in queries const activeUsers = await withActiveUsers ( db ) . selectAll ( ) . execute ( ) ; Migration Management Kysely provides a simple migration system: import { Migrator , FileMigrationProvider } from "kysely" ; import path from "path" ; import { promises as fs } from "fs" ; const migrator = new Migrator ( { db , provider : new FileMigrationProvider ( { fs , path , migrationFolder : path . join ( __dirname , "migrations" ) , } ) , } ) ; // Run migrations await migrator . migrateToLatest ( ) ; Migration file example: // migrations/001_create_users.ts import { Kysely , sql } from "kysely" ; export async function up ( db : Kysely < any

) : Promise < void

{ await db . schema . createTable ( "users" ) . addColumn ( "id" , "serial" , ( col ) => col . primaryKey ( ) ) . addColumn ( "email" , "varchar(255)" , ( col ) => col . notNull ( ) . unique ( ) ) . addColumn ( "name" , "varchar(255)" ) . addColumn ( "is_active" , "boolean" , ( col ) => col . defaultTo ( true ) ) . addColumn ( "created_at" , "timestamp" , ( col ) => col . defaultTo ( sql now() ) . notNull ( ) ) . execute ( ) ; } export async function down ( db : Kysely < any

) : Promise < void

{ await db . schema . dropTable ( "users" ) . execute ( ) ; }