### Instantiate and Use Typed Model

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Demonstrates how to create an instance of a Kysely ORM model (`User`) after correctly setting up its type definitions. This example shows that properties like `name` are recognized with their correct types, ensuring type safety.

```typescript
import User from './User';

const user = new User({
  id: 1,
  name: 'Adam',
});

console.log(user.name); // name has correct type string
```

--------------------------------

### Kysely ORM Lifecycle Hooks for User Model (TypeScript)

Source: https://context7.com/seeden/kysely-orm/llms.txt

Demonstrates pre-insert and pre-update data processing, as well as post-insert, post-update, and post-upsert hooks for the User model in Kysely ORM. It includes examples of data normalization (lowercasing email) and asynchronous side effects like logging and cache invalidation. Assumes existence of `db`, `logAudit`, `clearCache`, and `indexInSearchEngine` functions.

```typescript
import db from './config/db';

class User extends db.model('users', 'id') {
  // Process data before insert
  static processDataBeforeInsert(data) {
    if (Array.isArray(data)) {
      return data.map((item) => ({
        ...item,
        email: item.email.toLowerCase(),
      }));
    }
    return {
      ...data,
      email: data.email.toLowerCase(),
    };
  }

  // Process data before update
  static processDataBeforeUpdate(data) {
    return {
      ...data,
      email: data.email?.toLowerCase(),
    };
  }

  // Hook after single insert
  static async afterSingleInsert(result) {
    console.log(`User created: ${result.id}`);
    await logAudit('user_created', result);
    return result;
  }

  // Hook after single update
  static async afterSingleUpdate(result) {
    console.log(`User updated: ${result.id}`);
    await clearCache(`user:${result.id}`);
    return result;
  }

  // Hook after single upsert
  static async afterSingleUpsert(result) {
    await indexInSearchEngine(result);
    return result;
  }
}

// Hooks are automatically called
const user = await User.insertOne({
  name: 'John',
  email: 'JOHN@EXAMPLE.COM', // Automatically lowercased
});
```

--------------------------------

### JSONB Operations with PostgreSQL in Kysely ORM

Source: https://context7.com/seeden/kysely-orm/llms.txt

Covers how to perform operations on JSONB columns using Kysely ORM with PostgreSQL. It includes examples for incrementing values within a JSONB column using `jsonbIncrement` and querying JSONB data using raw SQL snippets.

```typescript
import { User } from './models';

// Increment values in JSONB column
const updateExpression = User.jsonbIncrement('stats', {
  views: 1,
  likes: 5,
  shares: 2,
});

await User.updateTable()
  .set({ stats: updateExpression })
  .where('id', '=', userId)
  .execute();

// Query JSONB data
const users = await User.selectFrom()
  .selectAll()
  .where(sql`stats->>'views'`, '>', '100')
  .execute();
```

--------------------------------

### Implement `updatedAt` Mixin for Automatic Timestamps

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Shows a refined example of using the `updatedAt` mixin. This mixin automatically sets a specified database field to the current timestamp (`NOW()`) whenever a record is updated, ensuring data is always up-to-date.

```typescript
import { applyMixins, updatedAt } from 'kysely-orm';
import db from './db';

export default class User extends applyMixins(db, 'users', 'id')(
  (model) => <DB, 'users', 'id', typeof model>updatedAt(model, 'updatedAt'),
) {
  findByEmail(email: string) {
    return this.findOne('email', email);
  }
}
```

--------------------------------

### Get Incremented Record by ID or Throw - TypeScript

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Executes the increment query by ID and throws an error if no record is found or updated. Returns the updated record. Dependencies include `findByIdAndIncrementQuery`, Kysely's `executeTakeFirstOrThrow`, and `NoResultError`.

```typescript
static async getByIdAndIncrement(
    id: Id, 
    columns: Partial<Record<keyof Table, number>>,
    func?: (qb: UpdateQueryBuilder<DB, TableName, TableName, UpdateResult>) => UpdateQueryBuilder<DB, TableName, TableName, UpdateResult>,
    error: typeof NoResultError = this.noResultError,
  ) {
    const record = await this
      .findByIdAndIncrementQuery(id, columns, func)
      .executeTakeFirstOrThrow(error);

    return this.afterSingleUpdate(record as Selectable<Table>);
  }
```

--------------------------------

### Get and Update Single Record by Column and Value

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Retrieves and updates a single record based on a specific column's value, with optional query modifications and error handling for no results. It uses Kysely ORM and returns the updated record or throws an error.

```typescript
static async getOneAndUpdate<ColumnName extends keyof Table & string>(
    column: ColumnName,
    value: Readonly<SelectType<Table[ColumnName]>>,
    data: UpdateExpression<DB, TableName, TableName>,
    func?: (qb: UpdateQueryBuilder<DB, TableName, TableName, UpdateResult>) => UpdateQueryBuilder<DB, TableName, TableName, UpdateResult>,
    error: typeof NoResultError = this.noResultError,
  ) {
    const updatedData = this.processDataBeforeUpdate(data);
    const record = await this
      .updateTable()
      // @ts-ignore
      .set(updatedData)
      .where(this.ref(`${this.table}.${column}`), '=', value)
      .$if(!!func, (qb) => func?.(qb as unknown as UpdateQueryBuilder<DB, TableName, TableName, UpdateResult>) as unknown as typeof qb)
      .returningAll()

```

--------------------------------

### Get One Record by Column and Value with Error (TypeScript)

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Retrieves a single record based on a column and value, throwing a specified error if no record is found. It constructs a query to select all fields, filters by the given column and value, and applies any provided query builder modifications. Returns the first matching record or throws.

```typescript
static async getOne<ColumnName extends keyof Table & string>(
    column: ColumnName,
    value: Readonly<SelectType<Table[ColumnName]>>,
    func?: (qb: SelectQueryBuilder<DB, TableName, {}>) => SelectQueryBuilder<DB, TableName, {}>,
    error: typeof NoResultError = this.noResultError,
  ) {
    const item = await this
      .selectFrom()
      .selectAll()
      .where(this.ref(`${this.table}.${column}`), '=', value)
      .$if(!!func, (qb) => func?.(qb as unknown as SelectQueryBuilder<DB, TableName, {}>) as unknown as typeof qb)
      .limit(1)
      .executeTakeFirstOrThrow(error);

    return item;
  }
```

--------------------------------

### Transaction Management via Database Instance in Kysely

Source: https://context7.com/seeden/kysely-orm/llms.txt

Demonstrates initiating a transaction directly from a database instance in Kysely ORM, allowing for more granular control. Shows how to check the transaction status and use `afterCommit` hooks within this context.

```typescript
import db from './config/db';
import { User } from './models';

await db.transaction(async ({ afterCommit }) => {
  const user = await User.insertOne({
    name: 'John',
    email: 'john@example.com',
  });

  console.log('In transaction:', db.isTransaction); // true

  afterCommit(async () => {
    console.log('Transaction committed');
  });

  return user;
});

console.log('Outside transaction:', db.isTransaction); // false
```

--------------------------------

### Configure PostgreSQL Database with kysely-orm

Source: https://context7.com/seeden/kysely-orm/llms.txt

Sets up a PostgreSQL database connection using kysely-orm and Kysely's PostgresDialect. It defines the database schema interfaces and creates a `Database` instance with connection pooling and debug logging enabled. Requires the 'pg' and 'kysely' libraries.

```typescript
import { PostgresDialect } from 'kysely';
import { Pool } from 'pg';
import { Database } from 'kysely-orm';
import type { Generated } from 'kysely';

// Define your database schema
interface Users {
  id: Generated<number>;
  name: string;
  email: string;
  createdAt: Generated<Date>;
  updatedAt: Generated<Date>;
}

interface Books {
  id: Generated<number>;
  title: string;
  userId: number;
  createdAt: Generated<Date>;
}

interface DB {
  users: Users;
  books: Books;
}

// Create database instance
const db = new Database<DB>({
  dialect: new PostgresDialect({
    pool: new Pool({
      host: 'localhost',
      database: 'myapp',
      user: 'postgres',
      password: 'password',
      port: 5432,
    }),
  }),
  debug: true, // Logs SQL queries
});

export default db;
```

--------------------------------

### Get and Update Single Record by Fields with Error Handling

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Retrieves and updates a single record by fields, throwing a 'NoResultError' if no record is found. This method uses Kysely ORM and allows for an optional query modification function. It returns the updated record.

```typescript
static async getOneByFieldsAndUpdate(
    fields: Readonly<Partial<{
      [ColumnName in keyof Table & string]: SelectType<Table[ColumnName]> | SelectType<Table[ColumnName]>[];
    }>>,
    data: UpdateExpression<DB, TableName, TableName>,
    func?: (qb: UpdateQueryBuilder<DB, TableName, TableName, UpdateResult>) => UpdateQueryBuilder<DB, TableName, TableName, UpdateResult>,
    error: typeof NoResultError = this.noResultError,
  ) {
    // TODO use with and select with limit 1
    const updatedData = this.processDataBeforeUpdate(data);
    const record = await this
      .updateTable()
      // @ts-ignore
      .set(updatedData)
      .where((qb) => {
        let currentQuery = qb;
        for (const [column, value] of Object.entries(fields)) {
          if (Array.isArray(value)) {
            currentQuery = currentQuery.where(this.ref(`${this.table}.${column}`), 'in', value);
          } else {
            currentQuery = currentQuery.where(this.ref(`${this.table}.${column}`), '=', value);
          }
        }
        return currentQuery;
      })
      .$if(!!func, (qb) => func?.(qb as unknown as UpdateQueryBuilder<DB, TableName, TableName, UpdateResult>) as unknown as typeof qb)
      .returningAll()
      .executeTakeFirstOrThrow(error);

    return this.afterSingleUpdate(record  as Selectable<Table>);
  }
```

--------------------------------

### Define Basic kysely-orm Model with Custom Methods

Source: https://context7.com/seeden/kysely-orm/llms.txt

Demonstrates defining a basic model ('User') for the 'users' table using kysely-orm. It includes custom static methods like `findByEmail` and `getByEmail` for data retrieval and shows how to use these methods with type safety. Assumes `db` and `Selectable` are imported.

```typescript
import db from './config/db';

// Define model interface for type merging
interface User extends Selectable<Users> {}

// Define model class
class User extends db.model('users', 'id') {
  // Custom finder method
  static findByEmail(email: string) {
    return this.findOne('email', email);
  }

  // Get with error throwing
  static getByEmail(email: string) {
    return this.getOne('email', email);
  }
}

export default User;

// Usage
const user = await User.findByEmail('john@example.com');
if (user) {
  console.log(user.id, user.name, user.email); // Fully typed
}

const existingUser = await User.getByEmail('john@example.com'); // Throws if not found
```

--------------------------------

### Apply Multiple Mixins with `applyMixins` Helper

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Illustrates using the `applyMixins` helper to combine multiple mixins (`updatedAt`, `slug`) into a single model definition. This approach simplifies the syntax when applying several mixins, improving code readability.

```typescript
import { applyMixins, updatedAt, slug } from 'kysely-orm';
import db from './db';

class User extends applyMixins(db, 'users', 'id')(
  (model) => <DB, 'users', 'id', typeof model>updatedAt(model, 'updatedAt'),
  (model) => <DB, 'users', 'id', typeof model>slug(model, {
    field: 'username',
    sources: ['name', 'firstName', 'lastName'],
    slugOptions: {
      truncate: 15,
    },
  }),
) {
  findByEmail(email: string) {
    return this.findOne('email', email);
  }
}
```

--------------------------------

### Basic Transaction Management with AsyncLocalStorage in Kysely

Source: https://context7.com/seeden/kysely-orm/llms.txt

Demonstrates how to execute a series of database operations within a transaction using Kysely ORM and AsyncLocalStorage. Ensures atomicity for operations like user creation and associated data insertion.

```typescript
import { User, Book } from './models';

// Execute transaction via model
await User.transaction(async () => {
  const user = await User.findByEmail('john@example.com');
  if (user) {
    throw new Error('User already exists');
  }

  const newUser = await User.insertOne({
    name: 'John Doe',
    email: 'john@example.com',
  });

  await Book.insert([
    { title: 'Book 1', userId: newUser.id },
    { title: 'Book 2', userId: newUser.id },
  ]);

  return newUser;
});

// All operations inside transaction are automatically wrapped
// No need to pass transaction object around
```

--------------------------------

### Configure SQLite Database with kysely-orm

Source: https://context7.com/seeden/kysely-orm/llms.txt

Configures a SQLite database connection using kysely-orm and Kysely's SqliteDialect. It utilizes the 'better-sqlite3' library and creates a `Database` instance, allowing for optional request-level isolation and custom query logging. Requires 'better-sqlite3' and 'kysely' libraries.

```typescript
import { SqliteDialect } from 'kysely';
import SQLiteDatabase from 'better-sqlite3';
import { Database } from 'kysely-orm';

const db = new Database<DB>({
  dialect: new SqliteDialect({
    database: new SQLiteDatabase('database.db'),
  }),
  isolated: false, // Set to true for request-level isolation
  log: (event) => {
    if (event.level === 'query') {
      console.log('Query:', event.query.sql);
    }
  },
});

export default db;
```

--------------------------------

### Get Related Data by ID (Kysely ORM)

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Retrieves related data by ID, ensuring a result is returned or throwing an error. It differentiates between single and multiple relation types, returning a single record or an array of records respectively. An optional error type can be provided for cases where no results are found.

```typescript
static async getRelatedById<FromTableName extends TableName,
    FromColumnName extends keyof DB[TableName] & string,
    ToTableName extends keyof DB & string,
    ToColumnName extends keyof DB[ToTableName] & string>(
    relation: OneRelation<DB, FromTableName, FromColumnName, ToTableName, ToColumnName>,
    id: Id,
    error?: typeof NoResultError,
  ): Promise<Selectable<DB[ToTableName]>>;

  static async getRelatedById<FromTableName extends TableName,
    FromColumnName extends keyof DB[TableName] & string,
    ToTableName extends keyof DB & string,
    ToColumnName extends keyof DB[ToTableName] & string>(
    relation: ManyRelation<DB, FromTableName, FromColumnName, ToTableName, ToColumnName>,
    id: Id,
    error?: typeof NoResultError,
  ): Promise<Selectable<DB[ToTableName]>[]>;

  static async getRelatedById<FromTableName extends TableName,
    FromColumnName extends keyof DB[TableName] & string,
    ToTableName extends keyof DB & string,
    ToColumnName extends keyof DB[ToTableName] & string>(
    relation: AnyRelation<DB, FromTableName, FromColumnName, ToTableName, ToColumnName>,
    id: Id,
    error: typeof NoResultError = this.noResultError,
  ): Promise<Selectable<DB[ToTableName]> | Selectable<DB[ToTableName]>[]> {
    const { type } = relation;
    const oneResult = type === RelationType.HasOneRelation || type === RelationType.BelongsToOneRelation;
    if (oneResult) {
      return await this.relatedQuery(relation, id).executeTakeFirstOrThrow(error) as Selectable<DB[ToTableName]>;
    }

    return await this.relatedQuery(relation, id).execute() as Selectable<DB[ToTableName]>[];
  }
```

--------------------------------

### Get One Record by Multiple Fields with Error (TypeScript)

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Retrieves a single record matching multiple fields, throwing a specified error if no record is found. It dynamically builds the WHERE clause based on the provided fields, supporting array values with the 'in' operator. An optional function can further refine the query.

```typescript
static async getOneByFields(
    fields: Readonly<Partial<{
      [ColumnName in keyof Table & string]: SelectType<Table[ColumnName]>;
    }>>,
    func?: (qb: SelectQueryBuilder<DB, TableName, {}>) => SelectQueryBuilder<DB, TableName, {}>, 
    error: typeof NoResultError = this.noResultError,
  ) {
    return this
      .selectFrom()
      .where((qb) => {
        let currentQuery = qb;
        for (const [column, value] of Object.entries(fields)) {
          const isArray = Array.isArray(value);
          currentQuery = currentQuery.where(this.ref(`${this.table}.${column}`), isArray ? 'in' : '=', value);
        }
        return currentQuery;
      })
      .selectAll()
      .$if(!!func, (qb) => func?.(qb as unknown as SelectQueryBuilder<DB, TableName, {}>) as unknown as typeof qb)
      .executeTakeFirstOrThrow(error);
  }
```

--------------------------------

### Get Record by ID with Error (TypeScript)

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Retrieves a single record by its primary key ID, throwing a specified error if the record is not found. This is a helper method that calls `getOne` using the predefined `this.id` column and the provided `id`. It allows for optional query builder modifications and a custom error.

```typescript
static getById(
    id: Id,
    func?: (qb: SelectQueryBuilder<DB, TableName, {}>) => SelectQueryBuilder<DB, TableName, {}>,
    error: typeof NoResultError = this.noResultError,
  ) {
    return this.getOne(this.id, id, func, error);
  }
```

--------------------------------

### Dynamic References and SQL Functions in Kysely ORM

Source: https://context7.com/seeden/kysely-orm/llms.txt

Explains the use of dynamic references and SQL functions within Kysely ORM queries. `User.ref()` allows dynamic referencing of columns, while `User.fn()` enables the use of built-in SQL functions like `count`, `max`, and `avg` for flexible and powerful data querying.

```typescript
import { User } from './models';

// Use dynamic references
const users = await User.selectFrom()
  .selectAll()
  .where(User.ref('users.email'), 'like', '%@example.com')
  .execute();

// Use SQL functions
const result = await User.selectFrom()
  .select(User.fn.count('id').as('total'))
  .where('status', '=', 'active')
  .executeTakeFirst();

console.log(`Total active users: ${result.total}`);

// Aggregate functions
const stats = await User.selectFrom()
  .select([
    User.fn.count('id').as('count'),
    User.fn.max('createdAt').as('latest'),
    User.fn.avg('age').as('avgAge'),
  ])
  .executeTakeFirst();
```

--------------------------------

### Kysely ORM Model Table Query Builders

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Offers static methods for initiating table-specific queries: 'selectFrom', 'updateTable', 'insertInto', and 'deleteFrom'. These methods are tied to the model's defined table and include checks for isolated mode.

```typescript
static selectFrom() {
  if (this.db.isolated && !this.isolated) {
    throw new Error('Cannot use selectFrom() in not isolated model. Call isolate({ Model }) first.');
  }
  return this.db.selectFrom(this.table);
}

static updateTable() {
  if (this.db.isolated && !this.isolated) {
    throw new Error('Cannot use updateTable() in not isolated model. Call isolate({ Model }) first.');
  }
  return this.db.updateTable(this.table);
}

static insertInto() {
  if (this.db.isolated && !this.isolated) {
    throw new Error('Cannot use insertInto() in not isolated model. Call isolate({ Model }) first.');
  }
  return this.db.insertInto(this.table);
}

static deleteFrom() {
  if (this.db.isolated && !this.isolated) {
    throw new Error('Cannot use deleteFrom() in not isolated model. Call isolate({ Model }) first.');
  }
  return this.db.deleteFrom(this.table);
}
```

--------------------------------

### Define Database Connection (Postgres)

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Establishes a database connection using the Kysely ORM with the PostgreSQL dialect. It requires connection details and integrates with the 'pg' pool for managing connections.

```typescript
import { PostgresDialect } from 'kysely';
import { Pool } from 'pg';
import { Database } from 'kysely-orm';
import type DB from './@types/Database';

export default new Database<DB>({
  dialect: new PostgresDialect({
    pool: new Pool({
      connectionString,
    }),
  }),
});
```

--------------------------------

### Kysely ORM Model Dynamic Querying

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Provides access to dynamic query building capabilities of Kysely ORM through static getters 'dynamic', 'ref', and 'fn'. These allow for more advanced and flexible query construction.

```typescript
static get dynamic() {
  return this.db.dynamic;
}

static ref(reference: string) {
  return this.db.dynamic.ref(reference);
}

static get fn() {
  return this.db.fn;
}
```

--------------------------------

### Handle Multiple Transactions

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Shows how to execute multiple independent transactions concurrently using Promise.all. Each transaction operates on its own isolated context.

```typescript
import { User } from '../models';

async function createUsers(userData1, userData2) {
  const [user1, user2] = await Promise.all([
    User.transaction(() => User.insert(userData1)),
    User.transaction(() => User.insert(userData2)),
  ]);
  ...
}
```

--------------------------------

### Multiple Mixins Integration (TypeScript)

Source: https://context7.com/seeden/kysely-orm/llms.txt

Demonstrates combining multiple Kysely ORM mixins (`updatedAt`, `globalId`, `slug`) using `applyMixins`. This allows for the concurrent application of various reusable behaviors to a single model, simplifying complex data management logic. Each mixin is applied as a separate function within `applyMixins`.

```typescript
import { applyMixins, updatedAt, slug, globalId } from 'kysely-orm';
import db from './config/db';

class User extends applyMixins(db, 'users', 'id')(
  (base) => updatedAt<DB, 'users', 'id', typeof base>(base, 'updatedAt'),
  (base) => globalId<DB, 'users', 'id', typeof base>(base, Number),
  (base) =>
    slug<DB, 'users', 'id', typeof base>(base)({
      field: 'username',
      sources: ['name'],
      slugOptions: { truncate: 20 },
    })
) {
  static findByEmail(email: string) {
    return this.findOne('email', email);
  }
}

// All mixins work together
const user = await User.insertOneWithSlug({
  name: 'Alice Johnson',
  email: 'alice@example.com',
});

console.log(user.id); // Auto-generated ID
console.log(user.username); // 'alice-johnson'
console.log(user.globalId); // Global ID from globalId mixin

// Updates automatically set updatedAt
await User.getByIdAndUpdate(user.id, { name: 'Alice Smith' });

```

--------------------------------

### Implement `slug` Mixin for URL-Friendly Identifiers

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Details the implementation of the `slug` mixin, which automatically generates URL-friendly slugs for fields based on provided source fields. This is useful for creating SEO-friendly URLs and unique identifiers during database inserts.

```typescript
import { applyPlugins, slug } from 'kysely-orm';
import type DB from './@types/DB';

export default class User extends applyMixins(db, 'users', 'id')(
  (model) => <DB, 'users', 'id', typeof model>slug(model, {
    field: 'username',
    sources: ['name', 'firstName', 'lastName'],
    slugOptions: {
      truncate: 15,
    },
  }),
) {
  findByEmail(email: string) {
    return this.findOne('email', email);
  }
}
```

--------------------------------

### Kysely ORM Model Data Processing Hooks

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Defines static methods 'afterSingleInsert', 'afterSingleUpdate', 'afterSingleUpsert', 'processDataBeforeUpdate', and 'processDataBeforeInsert' to hook into the ORM's data lifecycle. These allow for custom logic before or after database operations.

```typescript
static async afterSingleInsert(singleResult: Selectable<Table>) {
  return singleResult;
}

static async afterSingleUpdate(singleResult: Selectable<Table>) {
  return singleResult;
}

static async afterSingleUpsert(singleResult: Selectable<Table>) {
  return singleResult;
}

static processDataBeforeUpdate(data: UpdateExpression<DB, TableName, TableName>): UpdateExpression<DB, TableName, TableName>;
static processDataBeforeUpdate(data: UpdateExpression<OnConflictDatabase<DB, TableName>, OnConflictTables<TableName>, OnConflictTables<TableName>>): UpdateExpression<OnConflictDatabase<DB, TableName>, OnConflictTables<TableName>, OnConflictTables<TableName>>;
static processDataBeforeUpdate(data: UpdateExpression<DB, TableName, TableName> | UpdateExpression<OnConflictDatabase<DB, TableName>, OnConflictTables<TableName>, OnConflictTables<TableName>>) {
  return data;
}

static processDataBeforeInsert(data: InsertObjectOrList<DB, TableName>) {
  return data;
}
```

--------------------------------

### Find Records by Primary Key and Fields in Kysely

Source: https://context7.com/seeden/kysely-orm/llms.txt

Demonstrates finding records by primary key, ID, or specific fields using Kysely ORM. Supports single and multiple record retrieval, and custom queries with callbacks for advanced filtering.

```typescript
// Find by primary key
const user = await User.findById(1);

// Get by ID (throws if not found)
const existingUser = await User.getById(1);

// Find by column
const users = await User.find('email', 'john@example.com');

// Find one by column
const user = await User.findOne('email', 'john@example.com');

// Find by multiple IDs
const users = await User.findByIds([1, 2, 3]);

// Find by multiple fields
const users = await User.findByFields({
  name: 'John',
  email: 'john@example.com',
});

// Find one by multiple fields
const user = await User.findOneByFields({
  email: 'john@example.com',
  name: 'John',
});

// Custom query with callback
const user = await User.findOne('email', 'john@example.com', (qb) =>
  qb.where('createdAt', '>', new Date('2024-01-01'))
);

// Advanced query using selectFrom
const users = await User
  .selectFrom()
  .selectAll()
  .where('email', 'like', '%@example.com')
  .orderBy('createdAt', 'desc')
  .limit(10)
  .execute();
```

--------------------------------

### Define Database Connection (SQLite)

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Configures a database connection for SQLite using the Kysely ORM. It utilizes the 'better-sqlite3' library to interact with the SQLite database file.

```typescript
import { SqliteDialect } from 'kysely';
import SQLLiteDatabase from 'better-sqlite3';
import { Database } from 'kysely-orm';
import type DB from './@types/Database';

export default new Database<DB>({
  dialect: new SqliteDialect({
    database: new SQLLiteDatabase('test.db'),
  }),
});
```

--------------------------------

### Executing Parallel Transactions in Kysely

Source: https://context7.com/seeden/kysely-orm/llms.txt

Illustrates how to initiate and manage multiple independent Kysely ORM transactions concurrently using `Promise.all`. This is useful for operations that do not depend on each other and can benefit from parallel execution.

```typescript
import { User } from './models';

// Execute multiple independent transactions in parallel
const [user1, user2] = await Promise.all([
  User.transaction(() =>
    User.insertOne({
      name: 'Alice',
      email: 'alice@example.com',
    })
  ),
  User.transaction(() =>
    User.insertOne({
      name: 'Bob',
      email: 'bob@example.com',
    })
  ),
]);

console.log(`Created users: ${user1.id}, ${user2.id}`);
```

--------------------------------

### Perform kysely-orm Insert and Upsert Operations

Source: https://context7.com/seeden/kysely-orm/llms.txt

Illustrates various insertion and upsert operations using kysely-orm models. It covers inserting single and multiple records, performing an upsert operation based on a conflict on the 'email' field, and inserting a record only if it doesn't already exist. Assumes the 'User' model and `sql` tag are imported.

```typescript
import { User } from './models';

// Insert single record
const newUser = await User.insertOne({
  name: 'John Doe',
  email: 'john@example.com',
});
console.log(`Created user with ID: ${newUser.id}`);

// Insert multiple records
const users = await User.insert([
  { name: 'Alice', email: 'alice@example.com' },
  { name: 'Bob', email: 'bob@example.com' },
]);
console.log(`Created ${users.length} users`);

// Upsert (insert or update on conflict)
const upsertedUser = await User.upsertOne(
  { email: 'john@example.com', name: 'John Updated' },
  { name: 'John Updated', updatedAt: sql`CURRENT_TIMESTAMP` }, // Update data
  ['email'], // Conflict columns
);

// Insert only if not exists (no update on conflict)
const insertedUser = await User.insertOneIfNotExists(
  { email: 'jane@example.com', name: 'Jane Doe' },
  'email', // Field to return if exists
  'email', // Conflict column
);
```

--------------------------------

### Isolate Models for Request Handling

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Demonstrates how to create isolated instances of models using the 'isolate' function from 'kysely-orm'. This is crucial for managing state and data loaders effectively, especially in request-response cycles, ensuring that each request has its own independent model context.

```typescript
import { isolate } from 'kysely-orm';
import { User } from '../models';

const { User: IsolatedUser } = isolate({ User });
```

--------------------------------

### Define User Model

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Creates a model for the 'users' table, inheriting from the base Kysely ORM model. It includes a custom static method 'findByEmail' for convenient data retrieval.

```typescript
import db from './db';

export default class User extends db.model('users', 'id') {
  static findByEmail(email: string) {
    return this.findOne('email', email);
  }
}
```

--------------------------------

### Isolated Database Configuration (TypeScript)

Source: https://context7.com/seeden/kysely-orm/llms.txt

Shows how to configure an isolated Kysely ORM database instance. Using `isolated: true` enforces that model operations must be performed on isolated instances, preventing accidental use of shared configurations. Attempting to use non-isolated models will result in an error.

```typescript
// Create isolated database instance
const isolatedDb = new Database<DB>({
  dialect: new SqliteDialect({
    database: new SQLiteDatabase('test.db'),
  }),
  isolated: true, // Require explicit isolation
});

class User extends isolatedDb.model('users', 'id') {
  static findByEmail(email: string) {
    return this.findOne('email', email);
  }
}

// This will throw an error
await User.findByEmail('test@example.com');
// Error: Cannot use selectFrom() in not isolated model

// Must isolate first
const [IsolatedUser] = isolate([User]);
await IsolatedUser.findByEmail('test@example.com'); // Works

```

--------------------------------

### Kysely ORM Model Class Definition and Properties

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Defines the base Model class for Kysely ORM, including static properties for database connection, table name, ID column, and error handling. It also includes a constructor for initializing model instances.

```typescript
class Model {
  static readonly db: Database<DB> = db;
  static readonly table: TableName = table;
  static readonly id: IdColumnName = id;
  static readonly noResultError: typeof NoResultError = noResultError;
  static isolated: boolean = false;

  // constructor(data: Data);
  constructor(...args: any[]) {
    Object.assign(this, args[0]);
  }
}
```

--------------------------------

### Use Common Table Expressions (CTEs) with Kysely ORM

Source: https://context7.com/seeden/kysely-orm/llms.txt

Demonstrates how to utilize Common Table Expressions (CTEs) in Kysely ORM for complex queries. The `with` method allows defining temporary, named result sets that can be referenced in subsequent queries, enabling more structured and efficient data retrieval.

```typescript
import { User } from './models';

// Using WITH clause for complex queries
const results = await User.with('active_users', (db) =>
  db
    .selectFrom('users')
    .where('status', '=', 'active')
    .select(['id', 'name', 'email'])
)
  .selectFrom('active_users')
  .innerJoin('comments', 'active_users.id', 'comments.userId')
  .selectAll('active_users')
  .select('comments.message')
  .execute();
```

--------------------------------

### Define Model Relations with Kysely ORM

Source: https://context7.com/seeden/kysely-orm/llms.txt

Demonstrates how to define one-to-one, one-to-many, and belongs-to relations between models using Kysely ORM. This involves extending the base `db.model` and defining a `relations` static property with specific relation types and foreign key mappings.

```typescript
import { RelationType } from 'kysely-orm';
import db from './config/db';

class User extends db.model('users', 'id') {
  static relations = {
    // One-to-one relation
    profile: this.relation(
      RelationType.HasOneRelation,
      'users.id',
      'profiles.userId'
    ),

    // One-to-many relation
    comments: this.relation(
      RelationType.HasManyRelation,
      'users.id',
      'comments.userId'
    ),

    // Belongs to relation
    organization: this.relation(
      RelationType.BelongsToOneRelation,
      'users.organizationId',
      'organizations.id'
    ),
  };
}

class Comment extends db.model('comments', 'id') {
  static relations = {
    user: this.relation(
      RelationType.BelongsToOneRelation,
      'comments.userId',
      'users.id'
    ),
  };
}
```

--------------------------------

### Export All Models

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Exports all defined models from the models directory, making them easily importable in other parts of the application.

```typescript
export { default as User } from './User';
export { default as Book } from './Book';
```

--------------------------------

### Batch Load Relations with Kysely ORM (N+1 Prevention)

Source: https://context7.com/seeden/kysely-orm/llms.txt

Explains how to prevent N+1 query problems by batch loading relations using `findRelatedAndCombine`. This method fetches all related data for multiple parent records in a single query and attaches it to the parent objects, improving performance.

```typescript
// Find users
const users = await User.find('status', 'active');

// Load all comments for all users (single query)
const usersWithComments = await User.findRelatedAndCombine(
  User.relations.comments,
  users,
  'comments' // Field name to attach results
);

// Access combined data
usersWithComments.forEach((user) => {
  console.log(`${user.name} has ${user.comments.length} comments`);
  user.comments.forEach((comment) => {
    console.log(`  - ${comment.message}`);
  });
});

// Works with one-to-one relations too
const usersWithProfiles = await User.findRelatedAndCombine(
  User.relations.profile,
  users,
  'profile'
);

usersWithProfiles.forEach((user) => {
  console.log(`${user.name}'s bio: ${user.profile?.bio}`);
});
```

--------------------------------

### Update Record by Fields and Execute Kysely Query

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Updates records in a table based on provided fields and applies an optional Kysely query builder function. It processes data before updating and returns all updated records. Dependencies include Kysely ORM and type definitions for database and table.

```typescript
static async findByFieldsAndUpdate(
    fields: Readonly<Partial<{
      [ColumnName in keyof Table & string]: SelectType<Table[ColumnName]> | SelectType<Table[ColumnName]>[];
    }>>,
    data: UpdateExpression<DB, TableName, TableName>,
    func?: (qb: UpdateQueryBuilder<DB, TableName, TableName, UpdateResult>) => UpdateQueryBuilder<DB, TableName, TableName, UpdateResult>,
  ) {
    const updatedData = this.processDataBeforeUpdate(data);

    return await this
      .updateTable()
      // @ts-ignore
      .set(updatedData)
      .where((qb) => {
        let currentQuery = qb;
        for (const [column, value] of Object.entries(fields)) {
          if (Array.isArray(value)) {
            currentQuery = currentQuery.where(this.ref(`${this.table}.${column}`), 'in', value);
          } else {
            currentQuery = currentQuery.where(this.ref(`${this.table}.${column}`), '=', value);
          }
        }
        return currentQuery;
      })
      .$if(!!func, (qb) => func?.(qb as unknown as UpdateQueryBuilder<DB, TableName, TableName, UpdateResult>) as unknown as typeof qb)
      .returningAll()
      .execute();
  }
```

--------------------------------

### Perform Database Transaction

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Demonstrates how to perform database operations within a transaction using the 'Model.transaction' or 'db.transaction' method. This ensures atomicity for a series of database changes. It leverages Node.js AsyncLocalStorage for automatic transaction context management.

```typescript
import { User, Book } from '../models';

async function createUser(data) {
  const newUser = User.transaction(async () => {
    const user = await User.findByEmail(email);
    if (user) {
      throw new Error('User already exists');
    }

    return User.insert(data);
  });

  ...
}

import db from '../config/db';

async function createUserWithDbTransaction(data) {
  const newUser = db.transaction(async () => {
    const user = await User.findByEmail(email);
    if (user) {
      throw new Error('User already exists');
    }

    return User.insert(data);
  });

  ...
}
```

--------------------------------

### Execute Increment by ID and Return Record - TypeScript

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Executes the increment query for a record by its ID and returns the first updated record. Handles cases where no record is found. Dependencies include the `findByIdAndIncrementQuery` method and Kysely's execution methods.

```typescript
static async findByIdAndIncrement(
    id: Id, 
    columns: Partial<Record<keyof Table, number>>,
    func?: (qb: UpdateQueryBuilder<DB, TableName, TableName, UpdateResult>) => UpdateQueryBuilder<DB, TableName, TableName, UpdateResult>,
  ) {
    const record = await this.findByIdAndIncrementQuery(id, columns, func).executeTakeFirst();

    return record ? this.afterSingleUpdate(record as Selectable<Table>) : record;
  }
```

--------------------------------

### Query Related Data with Kysely ORM

Source: https://context7.com/seeden/kysely-orm/llms.txt

Illustrates various methods for querying related records in Kysely ORM. This includes fetching single or multiple related records, handling errors, and performing custom queries on relations using methods like `findRelatedById`, `getRelatedById`, and `relatedQuery`.

```typescript
// Find related records
const user = await User.getByEmail('john@example.com');

// Get single related record (HasOne/BelongsToOne)
const profile = await User.findRelatedById(User.relations.profile, user.id);

// Get multiple related records (HasMany)
const comments = await User.findRelatedById(User.relations.comments, user.id);
console.log(`User has ${comments.length} comments`);

// Get related with error throwing
const comments = await User.getRelatedById(User.relations.comments, user.id);

// Query related with custom conditions
const recentComments = await User.relatedQuery(User.relations.comments, user.id)
  .where('createdAt', '>', new Date('2024-01-01'))
  .orderBy('createdAt', 'desc')
  .execute();
```

--------------------------------

### Slug Mixin for URL-Friendly Identifiers (TypeScript)

Source: https://context7.com/seeden/kysely-orm/llms.txt

Applies a `slug` mixin to generate URL-friendly identifiers for records based on specified source fields. It supports customization of the slug generation process, including separators, truncation, and custom dictionaries. The mixin automatically handles duplicate slug generation.

```typescript
import { applyMixins, slug } from 'kysely-orm';
import db from './config/db';

interface Users {
  id: Generated<number>;
  name: string;
  username: string; // Slug field
  firstName?: string;
  lastName?: string;
}

class User extends applyMixins(db, 'users', 'id')(
  (base) =>
    slug<DB, 'users', 'id', typeof base>(base)({
      field: 'username',
      sources: ['name', 'firstName', 'lastName'],
      slugOptions: {
        separator: '-',
        truncate: 15,
        dictionary: {
          admin: '', // Remove 'admin' from slugs
          test: '', // Remove 'test' from slugs
        },
      },
    })
) {}

// Automatically generates username slug
const user = await User.insertOneWithSlug({
  name: 'John Doe',
  email: 'john@example.com',
  // username will be generated as 'john-doe'
});

console.log(user.username); // 'john-doe'

// Handles duplicates automatically
const user2 = await User.insertOneWithSlug({
  name: 'John Doe',
  email: 'john2@example.com',
});

console.log(user2.username); // 'john-doe2'

// Find by slug
const found = await User.findBySlug('john-doe');

```

--------------------------------

### Kysely ORM Model Transaction Management

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Enables transactional database operations using the 'transaction' static method. It accepts a callback function that executes within a database transaction, ensuring atomicity.

```typescript
static transaction<Type>(callback: TransactionCallback<DB, Type>) {
  return this.db.transaction(callback);
}
```

--------------------------------

### Delete Records by ID and Fields in Kysely

Source: https://context7.com/seeden/kysely-orm/llms.txt

Explains how to delete records using Kysely ORM, including deletion by ID, single record deletion by fields, and batch deletion by IDs or fields. Supports conditional deletion using callbacks.

```typescript
// Delete by ID
const numDeleted = await User.deleteById(1);

// Delete one by column
const numDeleted = await User.deleteOne('email', 'john@example.com');

// Delete by multiple fields
const numDeleted = await User.deleteOneByFields({
  email: 'john@example.com',
  name: 'John',
});

// Delete many by column
const numDeleted = await User.deleteMany('id', [1, 2, 3]);

// Delete with callback condition
const numDeleted = await User.deleteOne(
  'email',
  'john@example.com',
  (qb) => qb.where('createdAt', '<', new Date('2024-01-01'))
);
```

--------------------------------

### Kysely ORM Model Find Method

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Implements a 'find' static method for retrieving records based on a column and specific values. It supports finding a single value or multiple values using the 'in' operator and allows for optional query customization via a callback function.

```typescript
static async find<ColumnName extends keyof Table & string>(
  column: ColumnName,
  values: Readonly<SelectType<Table[ColumnName]>[]> | Readonly<SelectType<Table[ColumnName]>>,
  func?: (qb: SelectQueryBuilder<DB, TableName, {}>) => SelectQueryBuilder<DB, TableName, {}>,
  ) {
  const isArray = Array.isArray(values);

  return this
    .selectFrom()
    .selectAll()
    .where(this.ref(`${this.table}.${column}`), isArray ? 'in' : '=', values)
    .$if(!!func, (qb) => func?.(qb as unknown as SelectQueryBuilder<DB, TableName, {}>) as unknown as typeof qb)
    .execute();
}
```

--------------------------------

### Kysely Transaction with afterCommit Hook

Source: https://context7.com/seeden/kysely-orm/llms.txt

Shows how to use the `afterCommit` hook within a Kysely ORM transaction to execute asynchronous operations (like sending emails or notifications) only after the transaction has been successfully committed.

```typescript
import { User } from './models';

const user = await User.transaction(async ({ afterCommit }) => {
  const newUser = await User.insertOne({
    name: 'John Doe',
    email: 'john@example.com',
  });

  // Register callback to execute after commit
  afterCommit(async () => {
    await sendWelcomeEmail(newUser.email);
    await notifyAdmins(newUser);
  });

  // afterCommit is NOT called if transaction rolls back
  return newUser;
});

// afterCommit callbacks execute here, after successful commit
console.log('User created and notifications sent');
```

--------------------------------

### Find One Record by Column and Value (TypeScript)

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Retrieves a single record from the database that matches a specific column and value. It allows for optional query builder modifications. Dependencies include Kysely's SelectQueryBuilder. Returns the first matching record or undefined.

```typescript
static async findOne<ColumnName extends keyof Table & string>(
    column: ColumnName,
    value: Readonly<SelectType<Table[ColumnName]>>,
    func?: (qb: SelectQueryBuilder<DB, TableName, {}>) => SelectQueryBuilder<DB, TableName, {}>, 
  ) {
    return this
      .selectFrom()
      .selectAll()
      .where(this.ref(`${this.table}.${column}`), '=', value)
      .$if(!!func, (qb) => func?.(qb as unknown as SelectQueryBuilder<DB, TableName, {}>) as unknown as typeof qb)
      .limit(1)
      .executeTakeFirst();
  }
```

--------------------------------

### Insert Multiple Records in Kysely ORM

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Inserts multiple records into the database. This method processes the provided values before insertion and returns an array of the inserted records. It's suitable for bulk insert operations.

```typescript
static async insert(
    values: InsertObjectOrList<DB, TableName>,
  ) {
    return this
      .insertInto()
      .values(this.processDataBeforeInsert(values))
      .returningAll()
      .execute();
  }
```

--------------------------------

### Kysely ORM Model Common Table Expression (CTE)

Source: https://github.com/seeden/kysely-orm/blob/main/README.md

Allows the definition of Common Table Expressions (CTEs) using the 'with' static method. This method takes a name and an expression to define a temporary, named result set for use within a query.

```typescript
static with<Name extends string, Expression extends CommonTableExpression<DB, Name>>(name: Name, expression: Expression) {
  return this.db.with(name, expression);
}
```