kysely

What this skill does

# 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

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

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

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

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

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

### 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
```

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

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

### Insert Operations

```typescript
// Single insert
const result = await db
  .insertInto("users")
  .values({
    email: "[email protected]",
    name: "John Doe",
    is_active: true,
  })
  .returning(["id", "email", "created_at"])
  .executeTakeFirstOrThrow();

// Bulk insert
await db
  .insertInto("users")
  .values([
    { email: "[email protected]", name: "User 1", is_active: true },
    { email: "[email protected]", name: "User 2", is_active: true },
  ])
  .execute();

// Insert with on conflict (upsert)
await db
  .insertInto("users")
  .values({
    email: "[email protected]",
    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();
```

### Delete Operations

```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: "[email protected]",
      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:

```bash
# 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:

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

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

### Raw SQL

```typescript
import { sql } from "kysely"