### Install and Run Project Dependencies

Source: https://github.com/drizzle-team/drizzle-kysely/blob/main/README.md

Installs project dependencies using pnpm and starts the development server. Ensure you have pnpm installed or adapt to npm/yarn.

```bash
npm install
npm run dev
```

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

### Integrate Drizzle ORM with Kysely

Source: https://github.com/drizzle-team/drizzle-kysely/blob/main/README.md

Sets up a Kysely database instance integrated with Drizzle ORM for a SQLite database. Migrations are applied using drizzle-kit. This example demonstrates querying with both Kysely and Drizzle ORM.

```typescript
import Database from "better-sqlite3";
import { Kysely, SqliteDialect, sql } from "kysely";
import { Kyselify } from "drizzle-orm/kysely";
import { customers, details, employees, orders, products, suppliers } from "./schema";

import { migrate } from "drizzle-orm/better-sqlite3/migrator";
import { drizzle } from "drizzle-orm/better-sqlite3/driver";

const sqlite = new Database("datapack/_sqlite.db");
const drzldb = drizzle(sqlite);
migrate(drzldb, { migrationsFolder: "drizzle" });

interface Database {
  customer: Kyselify<typeof customers>;
  employee: Kyselify<typeof employees>;
  order: Kyselify<typeof orders>;
  supplier: Kyselify<typeof suppliers>;
  product: Kyselify<typeof products>;
  order_detail: Kyselify<typeof details>;
}

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

const main = async () => {
  // fully typed Kysely result and query builder
  const result = await db.selectFrom("customer").selectAll().execute();

  // you can also query with Drizzle ORM whenever needed!
  const result2 = drzldb.select().from(customers).all()
}

main()
```

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

### Initialize Drizzle and Kysely with SQLite

Source: https://context7.com/drizzle-team/drizzle-kysely/llms.txt

Opens a SQLite connection, applies Drizzle migrations, and constructs a type-safe Kysely instance using Drizzle's Kyselify utility. Ensure Drizzle migrations are in the 'drizzle' folder.

```typescript
import Database from "better-sqlite3";
import { Kysely, SqliteDialect } from "kysely";
import { Kyselify } from "drizzle-orm/kysely";
import { drizzle } from "drizzle-orm/better-sqlite3/driver";
import { migrate } from "drizzle-orm/better-sqlite3/migrator";
import {
  customers, employees,
  orders,
  suppliers, products,
  details,
} from "./schema";

// 1. Open the SQLite file
const sqlite = new Database("datapack/_sqlite.db");

// 2. Create a Drizzle instance and apply pending migrations
const drzldb = drizzle(sqlite);
migrate(drzldb, { migrationsFolder: "drizzle" });

// 3. Declare the Kysely Database interface using Drizzle table types
interface Database {
  customer:     Kyselify<typeof customers>;
  employee:     Kyselify<typeof employees>;
  order:        Kyselify<typeof orders>;
  supplier:     Kyselify<typeof suppliers>;
  product:      Kyselify<typeof products>;
  order_detail: Kyselify<typeof details>;
}

// 4. Build the typed Kysely instance
const db = new Kysely<Database>({
  dialect: new SqliteDialect({ database: sqlite }),
});
// db is now fully typed — every table name and column is checked at compile time

```

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

### Introspect MySQL Database with drizzle-kit

Source: https://context7.com/drizzle-team/drizzle-kysely/llms.txt

Reverse-engineers a live MySQL database to generate a typed schema.ts file. Requires a valid connection string.

```bash
drizzle-kit introspect:mysql --connectionString=mysql://user:pass@localhost/mydb
```

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

### Introspect PostgreSQL Database with drizzle-kit

Source: https://context7.com/drizzle-team/drizzle-kysely/llms.txt

Reverse-engineers a live PostgreSQL database to generate a typed schema.ts file. Requires a valid connection string.

```bash
drizzle-kit introspect:pg --connectionString=postgres://user:pass@localhost/mydb
```

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

### Product with Supplier Join (Subquery JOIN)

Source: https://context7.com/drizzle-team/drizzle-kysely/llms.txt

Fetches a product by ID and joins the full supplier record using an inline subquery to prevent column name collisions. Imports product IDs from './meta'.

```typescript
import { productIds } from "./meta";

for (const id of productIds) {
  const result = await db
    .selectFrom("product")
    .selectAll()
    .where("product.id", "=", id)
    .leftJoin(
      db
        .selectFrom("supplier")
        .select([
          "id as s_id",
          "company_name",
          "contact_name",
          "contact_title",
          "address",
          "city",
          "region",
          "postal_code",
          "country",
          "phone",
        ])
        .as("s1"),
      "s1.s_id",
      "product.supplier_id"
    )
    .execute();
  // result[0] = { id, name, unit_price, ..., company_name, contact_name, ... }
}
```

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

### LIKE / Full-Text Search with sql Template Tag

Source: https://context7.com/drizzle-team/drizzle-kysely/llms.txt

Performs a substring search using LIKE. Use the `sql` tag for column references that Kysely cannot statically verify. Imports `sql` from 'kysely' and search terms from './meta'.

```typescript
import { sql } from "kysely";
import { customerSearches } from "./meta"; // ["ve", "ey", "or", ...]

for (const term of customerSearches) {
  const results = await db
    .selectFrom("customer")
    .selectAll()
    .where(sql`company_name`, "like", `%${term}%`)
    .execute();
  // e.g. term="ve" => matches "Alfreds Futterkiste", "Save-a-lot Markets", ...
}
```

```typescript
// Same pattern for products
import { productSearches } from "./meta";
for (const term of productSearches) {
  await db
    .selectFrom("product")
    .selectAll()
    .where(sql`name`, "like", `%${term}%`)
    .execute();
}
```

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

### Order Summary with Aggregates (count, sum, raw SQL)

Source: https://context7.com/drizzle-team/drizzle-kysely/llms.txt

Selects orders grouped by ID, computing product count, total quantity, and total price. Uses Kysely's built-in `fn` helpers and the `sql` tag for raw SQL expressions. Imports `sql` from 'kysely'.

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

const orderSummaries = await db
  .selectFrom("order")
  .select([
    "order.id",
    "order.shipped_date",
    "order.ship_name",
    "order.ship_city",
    "order.ship_country",
    db.fn.count("product_id").as("products_count"),    // COUNT(product_id)
    db.fn.sum("quantity").as("quantity_sum"),           // SUM(quantity)
    sql`SUM(quantity * unit_price)`.as("total_price"), // raw SQL expression
  ])
  .leftJoin("order_detail", "order_detail.order_id", "order.id")
  .groupBy("order.id")
  .orderBy("order.id", "asc")
  .execute();
// => [{ id: 10248, ship_name: "Vins et alcools...", products_count: "3", quantity_sum: "72", total_price: 440, ... }]
```

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

### Run Drizzle Migrations

Source: https://context7.com/drizzle-team/drizzle-kysely/llms.txt

Applies pending SQL migrations from the 'drizzle/' folder to the database. The journal file tracks executed migrations.

```typescript
import { migrate } from "drizzle-orm/better-sqlite3/migrator";
import { drizzle } from "drizzle-orm/better-sqlite3/driver";
import Database from "better-sqlite3";

const sqlite = new Database("datapack/_sqlite.db");
const drzldb = drizzle(sqlite);

// Applies any new *.sql files in the drizzle/ folder
migrate(drzldb, { migrationsFolder: "drizzle" });

// Generated migration (drizzle/0000_steady_dormammu.sql) creates:
// CREATE TABLE customer ( id text PRIMARY KEY NOT NULL, ... );
// CREATE TABLE employee ( ... FOREIGN KEY (reports_to) REFERENCES employee(id) );
// CREATE TABLE product  ( ... FOREIGN KEY (supplier_id) REFERENCES supplier(id) ON DELETE cascade );
// etc.
```

```bash
npm run generate
# internally runs: drizzle-kit generate:sqlite --schema=src/schema.ts
```

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

### Introspect SQLite Database File with drizzle-kit

Source: https://context7.com/drizzle-team/drizzle-kysely/llms.txt

Reverse-engineers a SQLite database file to generate a typed schema.ts file. Requires the path to the database file.

```bash
drizzle-kit introspect:sqlite --db=datapack/_sqlite.db
```

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

### Introspect Database Schema with Drizzle-Kit

Source: https://github.com/drizzle-team/drizzle-kysely/blob/main/README.md

Commands to introspect existing databases for PostgreSQL, MySQL, and SQLite to generate Drizzle ORM schema files. This is useful for migrating existing projects.

```bash
> drizzle-kit introspect:pg ...
> drizzle-kit introspect:mysql ...
> drizzle-kit introspect:sqlite ...
```

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

### Select All Rows with Kysely and Drizzle ORM

Source: https://context7.com/drizzle-team/drizzle-kysely/llms.txt

Fetches all rows from a table using Kysely's `selectAll` and shows the equivalent Drizzle ORM call on the same connection.

```typescript
// Kysely — fully typed, returns Customer[]
const customers = await db
  .selectFrom("customer")
  .selectAll()
  .execute();
// => [{ id: "ALFKI", company_name: "Alfreds Futterkiste", city: "Berlin", ... }, ...]

// Drizzle ORM — interchangeable on the same sqlite connection
const customers2 = drzldb.select().from(customers).all();
```

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

### Fetch Order Detail with Double Subquery Join

Source: https://context7.com/drizzle-team/drizzle-kysely/llms.txt

Fetches order detail rows and enriches them with parent order and product information using chained left joins against inline subqueries. Ensure 'orderIds' are defined and imported.

```typescript
import { orderIds } from "./meta"; // 100 random IDs in [10248, 27065]

for (const id of orderIds) {
  const result = await db
    .selectFrom("order_detail")
    .selectAll()
    .where("order_id", "=", id)
    .leftJoin(
      db
        .selectFrom("order")
        .select([
          "order.id as o_id", "order_date", "required_date", "shipped_date",
          "ship_via", "freight", "ship_name", "ship_city", "ship_region",
          "ship_postal_code", "ship_country", "customer_id", "employee_id",
        ])
        .as("o"),
      "o.o_id",
      "order_detail.order_id"
    )
    .leftJoin(
      db
        .selectFrom("product")
        .select([
          "product.id as p_id", "name", "quantity_per_unit",
          "product.unit_price as p_unit_price", "units_in_stock",
          "units_on_order", "reorder_level", "discontinued", "supplier_id",
        ])
        .as("p"),
      "p.p_id",
      "order_detail.product_id"
    )
    .execute();
  // Each row contains order_detail columns + o_* order columns + p_* product columns
}
```

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

### Self-Join on Employee (Subquery as Table)

Source: https://context7.com/drizzle-team/drizzle-kysely/llms.txt

Joins the 'employee' table against itself to fetch each employee with their manager's details. Uses a named subquery alias via `.as("e2")`. Imports employee IDs from './meta'.

```typescript
import { employeeIds } from "./meta"; // [1, 2, ..., 9]

for (const id of employeeIds) {
  // Build a subquery for the manager (e2)
  const e2 = db
    .selectFrom("employee as e2")
    .select([
      "id as e2_id",
      "last_name as e2_last_name",
      "first_name as e2_first_name",
      "title as e2_title",
      "birth_date as e2_birth_date",
      "hire_date as e2_hire_date",
      "address as e2_address",
      "city as e2_city",
      "postal_code as e2_postal_code",
      "country as e2_country",
      "home_phone as e2_home_phone",
      "extension as e2_extension",
      "notes as e2_notes",
      "reports_to as e2_reports_to",
    ])
    .as("e2");

  const result = await db
    .selectFrom("employee as e1")
    .selectAll()
    .where("e1.id", "=", id)
    .leftJoin(e2, "e2.e2_id", "e1.reports_to")
    .execute();
  // result[0] contains all e1 columns + e2_* manager columns (null if no manager)
}
```

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

### Define SQLite Schema with Drizzle ORM

Source: https://context7.com/drizzle-team/drizzle-kysely/llms.txt

Defines tables for a SQLite database using Drizzle ORM's column helpers. Ensure `npm run generate` is run after schema changes to create SQL migrations.

```typescript
// src/schema.ts
import { InferModel, text, integer, numeric, foreignKey, sqliteTable } from "drizzle-orm/sqlite-core";

export const customers = sqliteTable("customer", {
  id:            text("id").primaryKey(),
  company_name:  text("company_name").notNull(),
  contact_name:  text("contact_name").notNull(),
  contact_title: text("contact_title").notNull(),
  address:       text("address").notNull(),
  city:          text("city").notNull(),
  postal_code:   text("postal_code"),
  region:        text("region"),
  country:       text("country").notNull(),
  phone:         text("phone").notNull(),
  fax:           text("fax"),
});
export type Customer = InferModel<typeof customers>;

export const employees = sqliteTable(
  "employee",
  {
    id:                integer("id").primaryKey(),
    last_name:         text("last_name").notNull(),
    first_name:        text("first_name"),
    title:             text("title").notNull(),
    birth_date:        integer("birth_date", { mode: "timestamp" }).notNull(),
    hire_date:         integer("hire_date", { mode: "timestamp" }).notNull(),
    address:           text("address").notNull(),
    city:              text("city").notNull(),
    postal_code:       text("postal_code").notNull(),
    country:           text("country").notNull(),
    home_phone:        text("home_phone").notNull(),
    extension:         integer("extension").notNull(),
    notes:             text("notes").notNull(),
    reports_to:        integer("reports_to"),
  },
  (table) => ({
    // self-referential FK: employee.reports_to → employee.id
    reportsToFk: foreignKey(() => ({
      columns:        [table.reports_to],
      foreignColumns: [table.id],
    })),
  })
);
export type Employee = InferModel<typeof employees>;

export const products = sqliteTable("product", {
  id:                integer("id").primaryKey({ autoIncrement: true }),
  name:              text("name").notNull(),
  quantity_per_unit: text("quantity_per_unit").notNull(),
  unit_price:        numeric("unit_price").notNull(),
  units_in_stock:    integer("units_in_stock").notNull(),
  units_on_order:    integer("units_on_order").notNull(),
  reorder_level:     integer("reorder_level").notNull(),
  discontinued:      integer("discontinued").notNull(),
  supplier_id:       integer("supplier_id")
                       .notNull()
                       .references(() => suppliers.id, { onDelete: "cascade" }),
});
export type Product = InferModel<typeof products>;
```

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

### Filter by Primary Key with Kysely

Source: https://context7.com/drizzle-team/drizzle-kysely/llms.txt

Looks up a single record by its primary key using Kysely's typed `.where()` clause. Kysely enforces valid columns and operators.

```typescript
// Kysely enforces that "customer.id" is a valid column and "=" is a valid operator
const result = await db
  .selectFrom("customer")
  .selectAll()
  .where("customer.id", "=", "ALFKI")
  .execute();
// => [{ id: "ALFKI", company_name: "Alfreds Futterkiste", contact_name: "Maria Anders", ... }]

// Bulk lookup across many IDs using a loop
import { customerIds } from "./meta"; // ["LAZYK", "FOLIG", ...]

for (const id of customerIds) {
  const row = await db
    .selectFrom("customer")
    .selectAll()
    .where("customer.id", "=", id)
    .execute();
}
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.