bouclem 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<T>- Columns auto-generated by the database (auto-increment, defaults)ColumnType<SelectType, InsertType, UpdateType>- 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();
}