kysely

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Kysely Development Guidelines

Kysely 开发指南

You are an expert in Kysely, TypeScript, and SQL database design with a focus on type safety and query optimization.

您是Kysely、TypeScript和SQL数据库设计领域的专家，专注于类型安全和查询优化。

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

Kysely是SQL之上的轻量级抽象层，由SQL爱好者为SQL爱好者设计
为表、列和查询结果提供完整的类型安全与自动补全支持
可预测的1:1 SQL编译——所见即所得
无魔法或隐藏行为——查询构建过程清晰透明
支持Node.js、Deno、Bun、Cloudflare Workers及浏览器环境

Database Interface Definition

数据库接口定义

Define Your Database Schema

定义数据库 Schema

typescript

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>;

typescript

import { Generated, ColumnType, Selectable, Insertable, Updateable } from "kysely";

// 定义表接口
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>;
}

// 定义数据库接口
interface Database {
  users: UserTable;
  posts: PostTable;
}

// 导出各表的辅助类型
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 与 ColumnType

```
Generated<T>
```
- Columns auto-generated by the database (auto-increment, defaults)

ColumnType<SelectType, InsertType, UpdateType>

- Different types for different operations

```
Generated<T>
```
- 由数据库自动生成的列（自增、默认值等）

ColumnType<SelectType, InsertType, UpdateType>

- 不同操作使用不同类型

Database Connection

数据库连接

PostgreSQL Setup

PostgreSQL 配置

typescript

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 };

typescript

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

MySQL 配置

typescript

import { Kysely, MysqlDialect } from "kysely";
import { createPool } from "mysql2";

const db = new Kysely<Database>({
  dialect: new MysqlDialect({
    pool: createPool({
      uri: process.env.DATABASE_URL,
    }),
  }),
});

typescript

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

SQLite 配置

typescript

import { Kysely, SqliteDialect } from "kysely";
import Database from "better-sqlite3";

const db = new Kysely<Database>({
  dialect: new SqliteDialect({
    database: new Database("database.db"),
  }),
});

typescript

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

查询语句

typescript

// 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();

typescript

// 查询表中所有列
const users = await db.selectFrom("users").selectAll().execute();

// 查询指定列
const userEmails = await db
  .selectFrom("users")
  .select(["id", "email", "name"])
  .execute();

// 带WHERE条件
const activeUsers = await db
  .selectFrom("users")
  .selectAll()
  .where("is_active", "=", true)
  .execute();

// 多条件
const filteredUsers = await db
  .selectFrom("users")
  .selectAll()
  .where("is_active", "=", true)
  .where("email", "like", "%@example.com")
  .execute();

// OR条件
const users = await db
  .selectFrom("users")
  .selectAll()
  .where((eb) =>
    eb.or([
      eb("name", "=", "John"),
      eb("name", "=", "Jane"),
    ])
  )
  .execute();

Column Aliases

列别名

typescript

// 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

typescript

// Kysely会自动推断别名类型
const result = await db
  .selectFrom("users")
  .select([
    "id",
    "email",
    "name as userName", // 别名会被正确解析并赋予类型
  ])
  .executeTakeFirst();

// result.userName 类型正确

Joins

联表查询

typescript

// 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();

typescript

// 内连接
const postsWithAuthors = await db
  .selectFrom("posts")
  .innerJoin("users", "users.id", "posts.author_id")
  .select([
    "posts.id",
    "posts.title",
    "users.name as authorName",
  ])
  .execute();

// 左连接
const usersWithPosts = await db
  .selectFrom("users")
  .leftJoin("posts", "posts.author_id", "users.id")
  .select([
    "users.id",
    "users.name",
    "posts.title as postTitle",
  ])
  .execute();

Subqueries

子查询

typescript

// 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();

typescript

// 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();

// WHERE中的子查询
const usersWithPosts = await db
  .selectFrom("users")
  .selectAll()
  .where("id", "in", (eb) =>
    eb.selectFrom("posts").select("author_id").distinct()
  )
  .execute();

Insert Operations

插入操作

typescript

// 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();

typescript

// 单条插入
const result = await db
  .insertInto("users")
  .values({
    email: "user@example.com",
    name: "John Doe",
    is_active: true,
  })
  .returning(["id", "email", "created_at"])
  .executeTakeFirstOrThrow();

// 批量插入
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();

// 插入冲突处理（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

更新操作

typescript

const result = await db
  .updateTable("users")
  .set({
    name: "Jane Doe",
    updated_at: new Date(),
  })
  .where("id", "=", 1)
  .returning(["id", "name", "updated_at"])
  .executeTakeFirst();

typescript

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

删除操作

typescript

const result = await db
  .deleteFrom("users")
  .where("id", "=", 1)
  .returning(["id", "email"])
  .executeTakeFirst();

typescript

const result = await db
  .deleteFrom("users")
  .where("id", "=", 1)
  .returning(["id", "email"])
  .executeTakeFirst();

Transactions

事务

typescript

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();
});

typescript

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

使用 kysely-codegen 生成类型

Use kysely-codegen to generate types from your existing database:

bash

undefined

使用kysely-codegen从现有数据库生成类型：

bash

undefined

Install

安装

npm install -D kysely-codegen

Generate types (reads from DATABASE_URL environment variable)

生成类型（从DATABASE_URL环境变量读取配置）

npx kysely-codegen

Specify output file

指定输出文件

npx kysely-codegen --out-file src/db/types.ts


Regenerate types whenever the database schema changes.

npx kysely-codegen --out-file src/db/types.ts


数据库Schema变更时，重新生成类型。

Plugins

插件

CamelCase Plugin

驼峰命名插件

Transform snake_case column names to camelCase:

typescript

import { Kysely, PostgresDialect, CamelCasePlugin } from "kysely";

const db = new Kysely<Database>({
  dialect: new PostgresDialect({ pool }),
  plugins: [new CamelCasePlugin()],
});

将snake_case列名转换为camelCase：

typescript

import { Kysely, PostgresDialect, CamelCasePlugin } from "kysely";

const db = new Kysely<Database>({
  dialect: new PostgresDialect({ pool }),
  plugins: [new CamelCasePlugin()],
});

Custom Plugins

自定义插件

typescript

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;
  }
}

typescript

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

动态查询构建

typescript

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();
}

typescript

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

原生SQL

typescript

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();

typescript

import { sql } from "kysely";

// SELECT中的原生表达式
const result = await db
  .selectFrom("users")
  .select([
    "id",
    sql<string>`CONCAT(first_name, ' ', last_name)`.as("fullName"),
  ])
  .execute();

// WHERE中的原生表达式
const users = await db
  .selectFrom("users")
  .selectAll()
  .where(sql`LOWER(email)`, "=", "user@example.com")
  .execute();

Common Table Expressions (CTEs)

公共表表达式（CTEs）

typescript

const result = await db
  .with("active_users", (db) =>
    db.selectFrom("users").selectAll().where("is_active", "=", true)
  )
  .selectFrom("active_users")
  .selectAll()
  .execute();

typescript

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

TypeScript 配置

Enable strict mode in tsconfig.json:

json

{
  "compilerOptions": {
    "strict": true,
    "strictNullChecks": true,
    "target": "ES2020"
  }
}

Use TypeScript 5.4 or later for best type inference.

在tsconfig.json中启用严格模式：

json

{
  "compilerOptions": {
    "strict": true,
    "strictNullChecks": true,
    "target": "ES2020"
  }
}

使用TypeScript 5.4或更高版本以获得最佳类型推断效果。

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

仅查询需要的列 - 仅需特定字段时避免使用
```
selectAll()
```
使用合适的索引 - 确保WHERE和JOIN列存在数据库索引
批量操作 - 多条记录插入时使用批量插入
连接池 - 生产环境始终使用连接池
预编译语句 - Kysely会自动使用预编译语句

Error Handling

错误处理

typescript

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;
}

typescript

import { NoResultError } from "kysely";

try {
  const user = await db
    .selectFrom("users")
    .selectAll()
    .where("id", "=", 999)
    .executeTakeFirstOrThrow();
} catch (error) {
  if (error instanceof NoResultError) {
    // 处理未找到的情况
  }
  throw error;
}

Query Composability

查询可组合性

typescript

// 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();

typescript

// 创建可复用的查询片段
function withActiveUsers(db: Kysely<Database>) {
  return db.selectFrom("users").where("is_active", "=", true);
}

// 在查询中使用
const activeUsers = await withActiveUsers(db).selectAll().execute();

Migration Management

迁移管理

Kysely provides a simple migration system:

typescript

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:

typescript

// 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();
}

Kysely提供简单的迁移系统：

typescript

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"),
  }),
});

// 执行迁移
await migrator.migrateToLatest();

迁移文件示例：

typescript

// 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();
}