菜单
Kysely type-safe SQL query builder - End-to-end type safety from schema to queries, migrations, transactions, plugins
默认使用会先检查来源的 Prompt；你也可以切换为直接命令，或下载本地副本。
决定是否安装前，请先阅读 SKILL.md，以及 SkillsMP 当前展示的配套文件。
用 Codex 或 Claude 帮你安装 复制这段 Prompt，粘贴到 Codex、Claude 或其他助手里，让它检查 Skill 页面并帮你完成安装。
直接命令不会经过审查 Prompt；运行前请先检查来源。
npx skills add https://github.com/bobmatnyc/claude-mpm-skills --skill kysely命令会保持在同一行。复制前请横向滚动并检查完整内容。
想先保存到本地？可下载 SkillsMP 当前能够提供的文件。
正在显示 SKILL.md
LinkedIn automation via the Linked API CLI - fetch profiles, search people and companies, send messages, manage connections, create posts, react, comment, and run Sales Navigator and custom workflows. Use when the user wants to interact with LinkedIn.
Xquik X data automation API - Use REST or MCP for tweet search, user lookup, follower exports, media downloads, monitors, webhooks, giveaway draws, and confirmation-gated X actions.
MCP (Model Context Protocol) - Build AI-native servers with tools, resources, and prompts. TypeScript/Python SDKs for Claude Desktop integration.
基于 SOC 职业分类
| name | kysely |
| description | Kysely type-safe SQL query builder - End-to-end type safety from schema to queries, migrations, transactions, plugins |
| user-invocable | false |
| disable-model-invocation | true |
| version | 1.0.0 |
| category | development |
| author | Claude MPM Team |
| license | MIT |
| progressive_disclosure | {"entry_point":{"summary":"Type-safe SQL query builder: full TypeScript inference, schema generation, migrations, no ORM overhead, raw SQL when needed","when_to_use":"Building type-safe database layers, migrating from ORMs, need raw SQL control with types, PostgreSQL/MySQL/SQLite apps","quick_start":"1. Define schema types 2. Create Kysely instance 3. Chain .selectFrom().where().execute() 4. Full type inference"}} |
| context_limit | 700 |
| tags | ["typescript","database","sql","query-builder","type-safe","kysely","orm-alternative"] |
| requires_tools | [] |
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:
Installation:
npm install kysely
# Database driver (choose one)
npm install pg # PostgreSQL
npm install mysql2 # MySQL
npm install better-sqlite3 # SQLite
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;
: ;
: ;
: <>;
: <>;
}
{
: ;
: ;
}
= <>;
= <>;
= <>;
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,
}),
}),
});
// 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: 'alice@example.com',
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();
// 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();
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();
// 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();
// 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();
# Install
npm install --save-dev kysely-codegen
# Generate types from existing database
npx kysely-codegen --url "postgresql://user:pass@localhost:5432/mydb"
Generated output:
// 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>;
}
// 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
}
import { Kysely, Migrator, FileMigrationProvider } from 'kysely';
import { promises as fs } from 'fs';
import * as path from 'path';
const migrator = new Migrator({
db,
provider: new FileMigrationProvider({
fs,
path,
migrationFolder: path.join(__dirname, 'migrations'),
}),
});
// Run all pending migrations
async function migrateToLatest() {
const { error, results } = await migrator.migrateToLatest();
results?.forEach((it) => {
if (it.status === 'Success') {
console.log(`Migration "${it.migrationName}" executed successfully`);
} else if (it.status === 'Error') {
console.error(`Migration "${it.migrationName}" failed`);
}
});
if (error) {
console.error('Migration failed:', error);
process.exit(1);
}
}
// Rollback last migration
async function migrateDown() {
const { error, results } = await migrator.migrateDown();
// Handle results...
}
// 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('created_at', 'timestamp', (col) =>
col.defaultTo(sql`CURRENT_TIMESTAMP`).notNull()
)
.execute();
await db.schema
.createIndex('users_email_idx')
.on('users')
.column('email')
.execute();
}
export async function down(db: Kysely<any>): Promise<void> {
await db.schema.dropTable('users').execute();
}
// Add foreign key
export async function up(db: Kysely<any>): Promise<void> {
await db.schema
.createTable('posts')
.addColumn('id', 'serial', (col) => col.primaryKey())
.addColumn('user_id', 'integer', (col) =>
col.references('users.id').onDelete('cascade').notNull()
)
.addColumn('title', 'varchar(500)', (col) => col.notNull())
.addColumn('content', 'text')
.execute();
}
// Alter table
export async function up(db: Kysely<any>): Promise<void> {
await db.schema
.alterTable('users')
.addColumn('bio', 'text')
.execute();
await db.schema
.alterTable('users')
.modifyColumn('email', 'varchar(320)')
.execute();
}
// Add enum column (PostgreSQL)
export async function up(db: Kysely<any>): Promise<void> {
await sql`CREATE TYPE user_role AS ENUM ('admin', 'user', 'guest')`.execute(db);
await db.schema
.alterTable('users')
.addColumn('role', sql`user_role`, (col) => col.defaultTo('user'))
.execute();
}
// Automatic rollback on error
await db.transaction().execute(async (trx) => {
await trx
.insertInto('users')
.values({ email: 'alice@example.com', name: 'Alice', updated_at: new Date() })
.execute();
await trx
.insertInto('posts')
.values({ user_id: 1, title: 'First Post', content: 'Hello' })
.execute();
});
// Manual transaction control
const trx = await db.transaction().execute(async (trx) => {
const user = await trx
.insertInto('users')
.values({ email: 'bob@example.com', name: 'Bob', updated_at: new Date() })
.returningAll()
.executeTakeFirstOrThrow();
const post = await trx
.insertInto('posts')
.values({
user_id: user.id,
title: 'Bob\'s Post',
content: 'Content',
})
.returningAll()
.executeTakeFirstOrThrow();
return { user, post };
});
import { IsolationLevel } from 'kysely';
// Read committed (default)
await db.transaction()
.setIsolationLevel('read committed')
.execute(async (trx) => {
// Transaction logic
});
// Serializable (strongest isolation)
await db.transaction()
.setIsolationLevel('serializable')
.execute(async (trx) => {
const balance = await trx
.selectFrom('accounts')
.select('balance')
.where('id', '=', accountId)
.executeTakeFirstOrThrow();
await trx
.updateTable('accounts')
.set({ balance: balance.balance - amount })
.where('id', '=', accountId)
.execute();
});
import { sql } from 'kysely';
// Raw SQL in SELECT
const result = await db
.selectFrom('users')
.select([
'id',
sql<string>`UPPER(name)`.as('uppercase_name'),
sql<number>`EXTRACT(YEAR FROM created_at)`.as('year_created'),
])
.execute();
// Raw SQL in WHERE
const filtered = await db
.selectFrom('posts')
.selectAll()
.where(sql`LOWER(title)`, 'like', '%typescript%')
.execute();
// Complex raw queries
const custom = await sql<{ total: number; avg_age: number }>`
SELECT
COUNT(*) as total,
AVG(EXTRACT(YEAR FROM age(birth_date))) as avg_age
FROM users
WHERE active = true
`.execute(db);
// Execute arbitrary SQL
const result = await sql`
WITH ranked_posts AS (
SELECT
p.*,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY views DESC) as rank
FROM posts p
)
SELECT * FROM ranked_posts WHERE rank <= 3
`.execute(db);
// Parameterized raw queries
const email = 'alice@example.com';
const user = await sql<User>`
SELECT * FROM users WHERE email = ${email}
`.execute(db);
import { jsonBuildObject, jsonArrayFrom } from 'kysely/helpers/postgres';
// Build JSON objects
const usersWithPosts = await db
.selectFrom('users')
.select([
'users.id',
'users.name',
jsonArrayFrom(
db
.selectFrom('posts')
.select(['posts.id', 'posts.title', 'posts.content'])
.whereRef('posts.user_id', '=', 'users.id')
).as('posts'),
])
.execute();
// Result: { id: 1, name: "Alice", posts: [{ id: 1, title: "..." }] }
// JSON aggregation
const nested = await db
.selectFrom('users')
.select([
'users.id',
jsonBuildObject({
name: 'users.name',
email: 'users.email',
postCount: sql<number>`(SELECT COUNT(*) FROM posts WHERE user_id = users.id)`,
}).as('user_data'),
])
.execute();
import { SelectQueryBuilder } from 'kysely';
function paginate<DB, TB extends keyof DB, O>(
query: SelectQueryBuilder<DB, TB, O>,
page: number,
pageSize: number
) {
return query.limit(pageSize).offset((page - 1) * pageSize);
}
// Usage
const page = 2;
const pageSize = 20;
const users = await paginate(
db.selectFrom('users').selectAll(),
page,
pageSize
).execute();
// With total count
async function paginateWithCount<DB, TB extends keyof DB, O>(
query: SelectQueryBuilder<DB, TB, O>,
page: number,
pageSize: number
) {
const [items, { count }] = await Promise.all([
query.limit(pageSize).offset((page - 1) * pageSize).execute(),
query.select(db.fn.count<number>('id').as('count')).executeTakeFirstOrThrow(),
]);
return {
items,
total: count,
page,
pageSize,
totalPages: Math.ceil(count / pageSize),
};
}
// GIN index for full-text search
export async function up(db: Kysely<any>): Promise<void> {
await sql`
ALTER TABLE posts
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
to_tsvector('english', coalesce(title, '') || ' ' || coalesce(content, ''))
) STORED
`.execute(db);
await sql`
CREATE INDEX posts_search_idx ON posts USING GIN (search_vector)
`.execute(db);
}
// Full-text search query
const searchResults = await db
.selectFrom('posts')
.selectAll()
.where(
sql`search_vector`,
'@@',
sql`to_tsquery('english', ${query})`
)
.execute();
| Feature | Kysely | Drizzle | Prisma |
|---|---|---|---|
| Type Safety | Full (schema → queries) | Full (schema → queries) | Full (generated client) |
| SQL Control | ✅ Raw SQL friendly | ✅ Raw SQL friendly | ❌ Limited |
| Bundle Size | ~50kB | ~30kB | ~500kB+ |
| Migration System | ✅ Built-in | ✅ Built-in | ✅ Powerful CLI |
| Query Performance | ✅ Plain SQL | ✅ Plain SQL | ❌ Slower (abstraction) |
| Schema Definition | TypeScript types | TypeScript schema | Prisma schema |
| Codegen Required | Optional | No | ✅ Required |
| ORM Features | ❌ Query builder only | Partial (relational) | ✅ Full ORM |
| Learning Curve | Medium (SQL knowledge) | Medium | Easy (abstracts SQL) |
| Best For | SQL-first, complex queries | Type-safe schemas | Rapid prototyping |
✅ Choose Kysely when:
❌ Choose Drizzle when:
❌ Choose Prisma when:
// Prisma
const users = await prisma.user.findMany({
where: { createdAt: { gte: new Date('2024-01-01') } },
include: { posts: true },
});
// Kysely equivalent
const users = await db
.selectFrom('users')
.select([
'users.id',
'users.email',
jsonArrayFrom(
db.selectFrom('posts')
.selectAll()
.whereRef('posts.user_id', '=', 'users.id')
).as('posts'),
])
.where('created_at', '>=', new Date('2024-01-01'))
.execute();
Generated, Selectable, Insertable, Updateable❌ Forgetting to execute queries:
// WRONG - returns query builder, not results
const users = db.selectFrom('users').selectAll();
// CORRECT
const users = await db.selectFrom('users').selectAll().execute();
❌ Not handling null from LEFT JOIN:
// TypeScript knows posts.title can be null from LEFT JOIN
const result = await db
.selectFrom('users')
.leftJoin('posts', 'posts.user_id', 'users.id')
.select(['users.name', 'posts.title'])
.execute();
// posts.title type: string | null
❌ Missing Generated for auto-increment columns:
// WRONG - TypeScript will require 'id' in INSERT
interface UserTable {
id: number; // Bad!
}
// CORRECT
interface UserTable {
id: Generated<number>; // INSERT doesn't require id
}
When using Kysely, consider these complementary skills:
// Kysely leverages advanced TypeScript features
import { Kysely, Generated, ColumnType } from 'kysely';
// Database interface with Generated types
interface Database {
users: {
id: Generated<number>; // Auto-generated by database
email: string;
created_at: ColumnType<Date, string | undefined, never>;
// ColumnType<SelectType, InsertType, UpdateType>
};
}
// Type inference in queries
const db = new Kysely<Database>({ /* config */ });
// Full type safety - TypeScript knows return type
const users = await db
.selectFrom('users')
.select(['id', 'email'])
.where('created_at', '>', new Date('2025-01-01'))
.execute();
// Type: Array<{ id: number; email: string }>
// Conditional types for dynamic queries
type SelectFields<T> = {
[K in keyof T]: T[K] extends ColumnType<infer S, any, any> ? S : T[K];
};
Safe Migration Principles:
Kysely Migration Example:
// migrations/001_add_full_name.ts
import { Kysely, sql } from 'kysely';
export async function up(db: Kysely<any>): Promise<void> {
// Phase 1: Add new column (nullable initially)
await db.schema
.alterTable('users')
.addColumn('full_name', 'varchar(255)')
.execute();
// Phase 2: Backfill data
await db
.updateTable('users')
.set({
full_name: sql`concat(first_name, ' ', last_name)`
})
.execute();
// Phase 3: Make required (separate migration recommended)
// await db.schema
// .alterTable('users')
// .alterColumn('full_name', (col) => col.setNotNull())
// .execute();
}
export async function down(db: Kysely<any>): Promise<void> {
await db.schema
.alterTable('users')
.dropColumn('full_name')
.execute();
}
Common Safe Migrations:
// Add index (concurrently for PostgreSQL)
await db.schema
.createIndex('idx_users_email')
.on('users')
.column('email')
.execute();
// Rename column (multi-phase approach)
// Phase 1: Add new column
await db.schema
.alterTable('users')
.addColumn('email_address', 'varchar(255)')
.execute();
// Phase 2: Copy data
await db
.updateTable('users')
.set({ email_address: sql`email` })
.execute();
// Phase 3: Drop old column (after deploy)
// await db.schema
// .alterTable('users')
// .dropColumn('email')
// .execute();
// Change column type (add new, migrate, drop old)
await db.schema
.alterTable('products')
.addColumn('price_cents', 'integer')
.execute();
await db
.updateTable('products')
.set({ price_cents: sql`cast(price * 100 as integer)` })
.execute();
Running Migrations:
// migrate.ts
import { Kysely, Migrator, FileMigrationProvider } from 'kysely';
import { promises as fs } from 'fs';
import path from 'path';
const migrator = new Migrator({
db,
provider: new FileMigrationProvider({
fs,
path,
migrationFolder: path.join(__dirname, 'migrations'),
}),
});
// Migrate to latest
const { error, results } = await migrator.migrateToLatest();
// Migrate up/down
await migrator.migrateUp();
await migrator.migrateDown();
// List pending migrations
const migrations = await migrator.getMigrations();
[Full TypeScript patterns and migration workflows available in respective skills if deployed together]