### Install and Run Example Project

Source: https://github.com/productdevbook/sumak/blob/main/examples/README.md

Standard commands to install dependencies and start an example project locally after setting up the database connection.

```bash
pnpm install
pnpm dev
```

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

### Install and Run Fastify Project

Source: https://github.com/productdevbook/sumak/blob/main/examples/fastify/README.md

Commands to set up the database, install dependencies, run migrations, and start the development server.

```bash
export DATABASE_URL="postgres://postgres:pg@localhost:5432/postgres"
pnpm install
pnpm migrate
pnpm dev
```

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

### Sumak Driver Setup

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Configure Sumak with a driver for executing SQL queries. This example shows a basic adapter for the 'pg' (PostgreSQL) driver using a connection pool.

```ts
import { sumak, pgDialect, type Driver } from "sumak"
import { Pool } from "pg"

// 10-line adapter — user-provided so sumak has zero runtime deps.
const pool = new Pool({ connectionString: process.env.DATABASE_URL })
const driver: Driver = {
  async query(sql, params) {
    const r = await pool.query(sql, [...params])
    return r.rows
  },
  async execute(sql, params) {
    const r = await pool.query(sql, [...params])
    return { affected: r.rowCount ?? 0 }
  },
}

const db = sumak({ dialect: pgDialect(), driver, tables: { ... } })
```

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

### Start the Development Server

Source: https://github.com/productdevbook/sumak/blob/main/examples/express/README.md

Start the Express development server using pnpm dev. This command assumes all previous setup steps have been completed.

```bash
pnpm dev
```

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

### Interact with Fastify API

Source: https://github.com/productdevbook/sumak/blob/main/examples/fastify/README.md

Example cURL commands to create a new product via POST and retrieve a product by SKU via GET.

```bash
curl -X POST http://localhost:3000/products \
  -H 'content-type: application/json' \
  -d '{"sku":"widget-42","name":"Widget","priceCents":2500}'
curl http://localhost:3000/products/widget-42
```

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

### Setup

Source: https://github.com/productdevbook/sumak/blob/main/AGENTS.md

Initialize the Sumak instance with dialect and table definitions.

```APIDOC
## Setup (single step)

### Description
Initialize the Sumak instance with dialect and table definitions.

### Request Example
```typescript
import { sumak, pgDialect, serial, text, boolean } from "sumak"

const db = sumak({
  dialect: pgDialect(),
  tables: {
    users: { id: serial(), name: text().notNull(), active: boolean().defaultTo(true) },
  },
})
```
```

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

### Install Dependencies and Run Migrations

Source: https://github.com/productdevbook/sumak/blob/main/examples/express/README.md

Install project dependencies using pnpm and then run database migrations to create the necessary tables using 'sumak migrate up'.

```bash
pnpm install
pnpm migrate     # creates tables via `sumak migrate up`
```

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

### Install Sumak using npm

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Install the Sumak package using npm. This is the first step to using the library in your project.

```sh
npm install sumak
```

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

### Install and Run Benchmark

Source: https://github.com/productdevbook/sumak/blob/main/bench/README.md

Installs dependencies and runs the compile-time benchmark using Vitest. This command is used to execute the performance tests defined in the benchmark file.

```bash
pnpm install
pnpm vitest bench --run bench/compile.bench.ts
```

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

### Quick Start: Define Typed Tables with Sumak

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Initialize Sumak with your database dialect and table definitions. This setup provides type safety for all subsequent queries.

```ts
import { sumak, pgDialect, serial, text, boolean, integer, jsonb } from "sumak"

const db = sumak({
  dialect: pgDialect(),
  tables: {
    users: {
      id: serial().primaryKey(),
      name: text().notNull(),
      email: text().notNull(),
      age: integer(),
      active: boolean().defaultTo(true),
      meta: jsonb(),
    },
    posts: {
      id: serial().primaryKey(),
      title: text().notNull(),
      userId: integer().references("users", "id"),
    },
  },
})
```

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

### Prime sumak AST Paths for Cold Starts

Source: https://github.com/productdevbook/sumak/blob/main/examples/aws-lambda/README.md

Call this at module scope to ensure sumak's query compilation paths are initialized before the first actual request, potentially reducing cold-start latency. No specific imports are required beyond the database client setup.

```typescript
db.selectFrom("events").select("id").limit(1).compile()
```

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

### Start Dockerized Databases

Source: https://github.com/productdevbook/sumak/blob/main/test/integration/dockerized/README.md

Starts the MySQL and MSSQL database servers using Docker Compose. Ensure both services show '(healthy)' before proceeding. MSSQL may take around 30 seconds to boot on its first pull.

```bash
# 1. Start the servers. MSSQL takes ~30s to boot on first pull;
#    wait until both services show `(healthy)` in docker ps.
docker compose -f test/integration/dockerized/docker-compose.yml up -d
```

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

### Run Sumak CLI Commands

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Examples of common Sumak CLI commands for managing database migrations, planning changes, and introspecting the schema. Use `--out` to specify output files.

```bash
sumak migrate plan           # preview DDL without running it
sumak migrate up             # apply pending DDL
sumak introspect             # read DB → stdout TypeScript schema
sumak introspect --out src/schema.generated.ts
sumak generate --out ./migrations/001_init.sql
```

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

### List Events with Pagination

Source: https://github.com/productdevbook/sumak/blob/main/examples/nitro/README.md

Example using curl to send a GET request to list events, with an optional limit for pagination. This handler uses file-system routing.

```bash
curl http://localhost:3000/api/events?limit=10
```

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

### Set up PostgreSQL Database URL

Source: https://github.com/productdevbook/sumak/blob/main/examples/express/README.md

Set the DATABASE_URL environment variable to connect to your PostgreSQL database. This is required before installing dependencies and running migrations.

```bash
export DATABASE_URL="postgres://postgres:pg@localhost:5432/postgres"
```

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

### Install Driver Dependencies

Source: https://github.com/productdevbook/sumak/blob/main/test/integration/dockerized/README.md

Installs the optional driver dependencies for MySQL and MSSQL. These are not part of the main devDependencies to maintain a zero-dependency stance for the default test suite.

```bash
# 2. Install the driver deps. They're NOT in the main devDependencies —
#    they're optional, and the default test suite must work without
#    them (zero-dep stance).
pnpm add -D mysql2 mssql
```

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

### SQL Dialect Examples

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Illustrates how the same SQL query is rendered differently across PostgreSQL, MySQL, SQLite, and MSSQL dialects.

```sql
// PostgreSQL  → SELECT "id" FROM "users" WHERE ("id" = $1)
// MySQL       → SELECT `id` FROM `users` WHERE (`id` = ?)
// SQLite      → SELECT "id" FROM "users" WHERE ("id" = ?)
// MSSQL       → SELECT [id] FROM [users] WHERE ([id] = @p0)
```

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

### Sumak Execute Methods

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Examples of using Sumak's built-in methods for executing SELECT, INSERT, UPDATE, and DELETE queries when a driver is configured. Methods like .many(), .one(), .first(), and .exec() are available.

```ts
// SELECT
const users = await db.selectFrom("users").many() // Row[]
const user = await db
  .selectFrom("users")
  .where(({ id }) => id.eq(1))
  .one() // Row (throws on ≠1)
const found = await db
  .selectFrom("users")
  .where(({ id }) => id.eq(1))
  .first() // Row | null

// INSERT / UPDATE / DELETE without RETURNING
const r = await db.insertInto("users").values({ name: "Alice" }).exec() // { affected }

// INSERT / UPDATE / DELETE with RETURNING
const rows = await db.insertInto("users").values({ name: "Alice" }).returningAll().many()
const row = await db
  .update("users")
  .set({ active: false })
  .where(({ id }) => id.eq(1))
  .returningAll()
  .one()
```

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

### Integrate Sumak Plugins for Extended Functionality

Source: https://github.com/productdevbook/sumak/blob/main/AGENTS.md

Shows how to incorporate plugins like soft delete, auditing, and multi-tenancy into your Sumak setup. Plugins are passed as an array to the 'plugins' option during Sumak initialization.

```typescript
import { softDelete, audit, multiTenant } from "sumak"

sumak({
  plugins: [
    softDelete({ tables: ["users"] }),
    audit({ tables: ["users"] }),
    multiTenant({ tables: ["users"], tenantId: () => ctx.id }),
  ],
  ...
})
```

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

### API Event GET Handler

Source: https://github.com/productdevbook/sumak/blob/main/examples/nitro/README.md

File-system routed handler for GET requests to /api/events. Auto-imports database utilities.

```typescript
export default defineEventHandler(async (event) => {
  const query = getQuery(event)
  return await db.query.events.findMany({
    limit: query.limit ? Number(query.limit) : undefined,
  })
})
```

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

### Create a New Event Record

Source: https://github.com/productdevbook/sumak/blob/main/examples/nitro/README.md

Example using curl to send a POST request to create a new event with source and payload. This handler uses file-system routing and transactional integrity.

```bash
curl -X POST http://localhost:3000/api/events \
  -H 'content-type: application/json' \
  -d '{"source":"checkout","payload":{"orderId":42}}'
```

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

### Fetch Posts with Pagination

Source: https://github.com/productdevbook/sumak/blob/main/examples/express/README.md

Use curl to send a GET request to the /posts endpoint, specifying a limit for pagination. This demonstrates fetching data from the Express server.

```bash
curl http://localhost:3000/posts?limit=100
```

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

### List Tasks Handler

Source: https://github.com/productdevbook/sumak/blob/main/examples/nuxt/README.md

A file-system-routed handler for GET requests to '/api/tasks'. It retrieves a list of tasks for the current tenant using the `dbFor` utility.

```typescript
import { dbFor } from '~/server/utils/db'

export default eventHandler(async (event) => {
  const tid = getTenantId(event)
  return dbFor(tid).tasks.findMany()
})
```

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

### Database Utility for Multi-Tenancy

Source: https://github.com/productdevbook/sumak/blob/main/examples/nuxt/README.md

This utility manages the database connection pool and provides a factory function `dbFor(tid)` to get a tenant-specific database handle. It caches the pool on `globalThis` to prevent duplication during development server reloads.

```typescript
export const db = new Pool()

export function dbFor(tid: string | number) {
  return db.with(
    { tenantId: tid },
    // @ts-expect-error sumak's type is not generic
    (db) => db.with({ schema: 'public' })
  )
}
```

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

### Initialize Sumak with PostgreSQL Dialect

Source: https://github.com/productdevbook/sumak/blob/main/AGENTS.md

Set up the Sumak instance with a specific dialect (e.g., PostgreSQL) and define table schemas. Ensure all necessary imports are included.

```typescript
import { sumak, pgDialect, serial, text, boolean } from "sumak"

const db = sumak({
  dialect: pgDialect(),
  tables: {
    users: { id: serial(), name: text().notNull(), active: boolean().defaultTo(true) },
  },
})
```

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

### Run Postgres Locally with Docker

Source: https://github.com/productdevbook/sumak/blob/main/examples/README.md

Quickly set up a local PostgreSQL instance for development using Docker. Ensure the DATABASE_URL environment variable is exported.

```bash
docker run --rm -e POSTGRES_PASSWORD=pg -p 5432:5432 postgres:17
export DATABASE_URL="postgres://postgres:pg@localhost:5432/postgres"
```

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

### Sumak Architecture Overview

Source: https://github.com/productdevbook/sumak/blob/main/AGENTS.md

Illustrates the 7-layer pipeline of Sumak, from user code interacting with the builder to the final parameterized SQL output. This flow highlights the transformation steps involved in generating SQL.

```plaintext
User Code
  │
  ├─ sumak({ dialect, tables })     ← DB type auto-inferred
  │
  ├─ db.selectFrom("users")       ← TypedSelectBuilder<DB, "users", O>
  │    .select("id", "name")       ← O narrows to Pick<O, "id"|"name">
  │    .where(typedEq(...))        ← Expression<boolean> enforced
  │    .build()                    ← SelectNode (frozen AST)
  │
  ├─ db.compile(node)             ← Full pipeline:
  │    1. Plugin AST transforms
  │    2. Lifecycle hooks (before)
  │    3. Normalize (NbE)           ← Predicate simplification, constant folding
  │    4. Optimize (rewrite rules)  ← Predicate pushdown, subquery flattening
  │    5. Printer → SQL
  │    6. Plugin query transforms
  │    7. Lifecycle hooks (after)
  │
  └─ { sql, params }              ← Parameterized output
```

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

### Create Typed Sumak Database Instance

Source: https://context7.com/productdevbook/sumak/llms.txt

Initialize a Sumak instance with schema definition, dialect configuration (e.g., PostgreSQL), and an optional driver for query execution. Ensure necessary types and functions are imported.

```typescript
import { sumak, pgDialect, serial, text, integer, boolean, jsonb } from "sumak"
import { pgDriver } from "sumak/drivers/pg"
import { Pool } from "pg"

const pool = new Pool({ connectionString: process.env.DATABASE_URL })

const db = sumak({
  dialect: pgDialect(),
  driver: pgDriver(pool),
  tables: {
    users: {
      id: serial().primaryKey(),
      name: text().notNull(),
      email: text().notNull(),
      age: integer(),
      active: boolean().defaultTo(true),
      meta: jsonb(),
    },
    posts: {
      id: serial().primaryKey(),
      title: text().notNull(),
      userId: integer().references("users", "id"),
    },
  },
})
```

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

### Sumak Result Transformation Hook

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Transforms query results after they are fetched from the database. Example shows converting snake_case keys to camelCase.

```typescript
// Transform results
db.hook("result:transform", (rows) => {
  return rows.map(toCamelCase)
})
```

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

### HAVING Clause with Aggregates

Source: https://context7.com/productdevbook/sumak/llms.txt

Filter groups based on aggregate function results using the `having()` clause. This example filters users with more than 5 orders.

```typescript
// HAVING clause
db.selectFrom("orders")
  .select("userId", { total: count() })
  .groupBy("userId")
  .having(({ total }) => total.gt(5))
  .toSQL()
```

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

### Compile BEGIN Transaction SQL with Options

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Generate dialect-aware BEGIN transaction SQL statements using the `tx.begin()` builder. Pass options to specify isolation levels, read-only status, and deferrability.

```ts
import { sumak, pgDialect, tx } from "sumak"

const db = sumak({ dialect: pgDialect(), tables: { ... } })

db.compile(tx.begin())
// { sql: "BEGIN", params: [] }

db.compile(tx.begin({ isolation: "SERIALIZABLE", readOnly: true }))
// { sql: "BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY", params: [] }

db.compile(tx.begin({ isolation: "SERIALIZABLE", readOnly: true, deferrable: true }))
// { sql: "BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE", params: [] }
```

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

### COUNT Aggregate Function

Source: https://context7.com/productdevbook/sumak/llms.txt

Use the `count()` aggregate function to count rows. This example shows a simple count of all rows and a count grouped by department.

```typescript
import { count, countDistinct, sum, avg, min, max, coalesce, Col } from "sumak"

// COUNT with GROUP BY
db.selectFrom("users")
  .select({ total: count() })
  .toSQL()
// SELECT COUNT(*) AS "total" FROM "users"

// COUNT with column
db.selectFrom("users")
  .select({ uniqueDepts: countDistinct(new Col("dept").toExpr()) })
  .groupBy("dept")
  .toSQL()
```

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

### Configure Sumak CLI for Migrations and Introspection

Source: https://github.com/productdevbook/sumak/blob/main/README.md

This configuration file sets up the Sumak CLI for database operations. It defines the dialect, driver, and schema source. Ensure your database connection string is set in the environment variables.

```typescript
// sumak.config.ts
import { Pool } from "pg"
import { defineConfig } from "sumak/cli"
import { pgDriver } from "sumak/drivers/pg"

import { tables } from "./src/schema.ts"

export default defineConfig({
  dialect: "pg",
  driver: () => pgDriver(new Pool({ connectionString: process.env.DATABASE_URL })),
  schema: () => ({ tables }),
})
```

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

### Build and Run Node Server

Source: https://github.com/productdevbook/sumak/blob/main/examples/nitro/README.md

Commands to build the Nitro project for Node.js deployment and run the output server.

```bash
pnpm build
node .output/server/index.mjs
```

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

### MySQL Consistent Snapshot Transaction

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Generate a `START TRANSACTION` statement with `CONSISTENT SNAPSHOT` and `READ ONLY` options for MySQL. This ensures a transaction operates on a consistent snapshot of the database.

```ts
// MySQL: START TRANSACTION WITH CONSISTENT SNAPSHOT
tx.begin({ consistentSnapshot: true, readOnly: true })
// START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY
```

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

### Configure Sumak with Plugins

Source: https://context7.com/productdevbook/sumak/llms.txt

Extend Sumak's functionality by configuring built-in plugins during initialization. Plugins handle common patterns like soft deletes, auditing, and multi-tenancy.

```typescript
import {
  sumak, pgDialect, softDelete, audit, multiTenant,
  queryLimit, withSchema, camelCase, optimisticLock
} from "sumak"

const db = sumak({
  dialect: pgDialect(),
  plugins: [
    // Auto-filter soft-deleted rows
    softDelete({ tables: ["users"], column: "deleted_at" }),

    // Auto-inject created_at/updated_at
    audit({ tables: ["users", "posts"] }),

    // Multi-tenant isolation
    multiTenant({
      tables: ["users", "posts"],
      tenantId: () => getCurrentTenantId()
    }),

    // Default LIMIT on unbounded SELECTs
    queryLimit({ maxRows: 1000 }),

    // Schema prefix
    withSchema("public"),

    // Transform snake_case to camelCase
    camelCase(),

    // Optimistic locking
    optimisticLock({
      tables: ["users"],
      currentVersion: () => rowVersion
    }),
  ],
  tables: { /* ... */ },
})
```

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

### Create View DDL Statement

Source: https://context7.com/productdevbook/sumak/llms.txt

Generates a DDL statement to create a view based on a select query. This example creates a view named 'active_users' that selects all active users.

```typescript
// CREATE VIEW
const selectQuery = db.selectFrom("users").where(({ active }) => active.eq(true)).build()
db.schema.createView("active_users").asSelect(selectQuery).build()
```

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

### Manage Database Transactions with Sumak

Source: https://github.com/productdevbook/sumak/blob/main/AGENTS.md

Illustrates how to initiate, commit, and manage savepoints within transactions using Sumak's transaction utilities. Import the 'tx' object to access these functions.

```typescript
import { tx } from "sumak"

db.compile(tx.begin({ isolation: "SERIALIZABLE", readOnly: true }))
```

```typescript
db.compile(tx.commit())
```

```typescript
db.compile(tx.savepoint("sp1"))
```

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

### Alter Table DDL Statements

Source: https://context7.com/productdevbook/sumak/llms.txt

Provides examples for altering existing tables, including adding a column, dropping a column, and renaming a column. Ensure the table exists before attempting alterations.

```typescript
// ALTER TABLE
db.schema.alterTable("users").addColumn("age", "integer", c => c.notNull()).build()
db.schema.alterTable("users").dropColumn("age").build()
db.schema.alterTable("users").renameColumn("name", "full_name").build()
```

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

### Sumak Build and Script Commands

Source: https://github.com/productdevbook/sumak/blob/main/AGENTS.md

A collection of command-line scripts for building, testing, linting, formatting, and releasing the Sumak project. These commands utilize pnpm for package management and common development tools.

```bash
pnpm build          # obuild (rolldown)
```

```bash
pnpm dev            # vitest watch
```

```bash
pnpm lint           # oxlint + oxfmt --check
```

```bash
pnpm lint:fix       # oxlint --fix + oxfmt
```

```bash
pnpm fmt            # oxfmt
```

```bash
pnpm test           # pnpm lint && pnpm typecheck && vitest run
```

```bash
pnpm typecheck      # tsgo --noEmit
```

```bash
pnpm release        # pnpm test && pnpm build && bumpp && npm publish && git push --follow-tags
```

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

### Combining Multiple Sumak Plugins

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Demonstrates how to combine various Sumak plugins, such as schema management, soft delete, auditing, multi-tenancy, and subject type, in a single configuration.

```typescript
import {
  sumak, pgDialect,
  withSchema, softDelete, audit, multiTenant, queryLimit, subjectType,
} from "sumak"

const db = sumak({
  dialect: pgDialect(),
  plugins: [
    withSchema("public"),
    softDelete({ tables: ["users"] }),
    audit({ tables: ["users", "posts"] }),
    multiTenant({ tables: ["users", "posts"], tenantId: () => currentTenantId }),
    subjectType({ tables: { users: "User", posts: "Post" } }),
    queryLimit({ maxRows: 5000 }),
  ],
  tables: { ... },
})
```

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

### Upsert (ON CONFLICT DO UPDATE)

Source: https://context7.com/productdevbook/sumak/llms.txt

Handle potential conflicts during insertion using the `onConflict` method. This example demonstrates updating a row if a unique constraint (email) is violated, using `excluded` to reference the values that would have been inserted.

```typescript
// ON CONFLICT / Upsert (PostgreSQL)
import { excluded } from "sumak"

db.insertInto("users")
  .values({ name: "Alice", email: "a@b.com" })
  .onConflict({
    columns: ["email"],
    do: { update: [{ column: "name", value: excluded("name") }] }
  })
  .toSQL()
```

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

### Run Sumak Benchmarks with Vitest

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Execute Sumak's performance benchmarks using Vitest. This command runs a specific benchmark file to compare Sumak's query compiler against other ORMs.

```bash
pnpm vitest bench --run bench/compile.bench.ts
```

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

### Sumak 7-Layer Pipeline

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Illustrates the flow of a query through Sumak's 7-layer pipeline, from schema definition to SQL printing.

```text
┌─────────────────────────────────────────────────────────────────┐
│  1. SCHEMA                                                      │
│     sumak({ dialect, tables: { users: { id: serial(), ... } } })│
│     → DB type auto-inferred, zero codegen                       │
├─────────────────────────────────────────────────────────────────┤
│  2. BUILDER                                                     │
│     db.selectFrom("users").select("id").where(...)              │
│     → Immutable, chainable, fully type-checked                  │
├─────────────────────────────────────────────────────────────────┤
│  3. AST                                                         │
│     .build() → SelectNode (frozen, discriminated union)         │
│     → ~40 node types, Object.freeze on all outputs              │
├─────────────────────────────────────────────────────────────────┤
│  4. PLUGIN / HOOK                                               │
│     Plugin.transformNode() → Hook "query:before"                │
│     → AST rewriting, tenant isolation, soft delete, logging     │
├─────────────────────────────────────────────────────────────────┤
│  5. NORMALIZE (NbE)                                             │
│     Predicate simplification, constant folding, deduplication   │
│     → Canonical form via Normalization by Evaluation            │
├─────────────────────────────────────────────────────────────────┤
│  6. OPTIMIZE (Rewrite Rules)                                    │
│     Predicate pushdown, subquery flattening, user rules         │
│     → Declarative rules applied to fixpoint                     │
├─────────────────────────────────────────────────────────────────┤
│  7. PRINTER                                                     │
│     .toSQL() → { sql: "SELECT ...", params: [...] }             │
│     → Dialect-specific: PG ($1), MySQL (?), MSSQL (@p0)        │
└─────────────────────────────────────────────────────────────────┘
```

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

### Execute Basic SQL Queries with Sumak

Source: https://github.com/productdevbook/sumak/blob/main/AGENTS.md

Demonstrates how to construct and compile common SQL operations like SELECT, INSERT, UPDATE, and DELETE using Sumak's fluent API. The .compile(db.printer()) method is used to generate the SQL string.

```typescript
db.selectFrom("users").select("id", "name").where(...).compile(db.printer())
```

```typescript
db.insertInto("users").values({ name: "Alice" }).returningAll().compile(db.printer())
```

```typescript
db.update("users").set({ active: false }).where(...).compile(db.printer())
```

```typescript
db.deleteFrom("users").where(...).compile(db.printer())
```

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

### Sumak ORM - PostgreSQL Array Operators

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Utilizes PostgreSQL array operators within Sumak ORM queries for filtering. Examples include checking for array containment (`@>`), being contained by (`<@`), and overlapping elements (`&&`). Requires `unsafeRawExpr` for array literals.

```ts
import { arr, unsafeRawExpr } from "sumak"

.where(({ tags }) => arr.contains(tags, unsafeRawExpr("ARRAY['sql']")))    // @>
.where(({ tags }) => arr.containedBy(tags, unsafeRawExpr("ARRAY[...]")))   // <@
.where(({ tags }) => arr.overlaps(tags, unsafeRawExpr("ARRAY['sql']")))    // &&
```

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

### Sumak Unsafe Raw Expression

Source: https://context7.com/productdevbook/sumak/llms.txt

Employ unsafe raw expressions for direct SQL string injection when necessary. Use with caution and ensure the expression is correctly parameterized to prevent SQL injection vulnerabilities. This example selects users older than 18 and extracts the year from the creation date.

```typescript
// Unsafe raw expression (use with caution)
db.selectFrom("users")
  .where(() => unsafeRawExpr<boolean>("age > 18"))
  .select({ year: unsafeRawExpr<number>("EXTRACT(YEAR FROM created_at)") })
  .toSQL()
```

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

### Sumak Migration Planning and Application

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Plan and apply database migrations using Sumak. `planMigration` generates the SQL steps without execution, while `applyMigration` executes them within a transaction.

```ts
// Returns the plan (compiled SQL per step) without touching the database.
const plan = planMigration(db, before, after)
console.log(plan.steps.map((s) => s.sql))
console.log("destructive:", plan.hasDestructiveSteps)

// Plan + execute in a single transaction (rolls back on any failure).
const { applied, statements } = await applyMigration(db, before, after)
```

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

### WHERE Clause: Pattern Matching (LIKE)

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Shows how to use the `.like()` method for pattern matching in WHERE clauses. Options include `negate`, `insensitive`, and `symmetric` for different SQL operators like NOT LIKE, ILIKE, and NOT ILIKE.

```typescript
.where(({ name }) => name.like("%ali%"))                                // LIKE
```

```typescript
.where(({ name }) => name.like("%bob%", { negate: true }))              // NOT LIKE
```

```typescript
.where(({ name }) => name.like("%alice%", { insensitive: true }))       // ILIKE (PG)
```

```typescript
.where(({ email }) => email.like("%spam%", { negate: true, insensitive: true }))  // NOT ILIKE
```

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

### Import Driver Adapters

Source: https://context7.com/productdevbook/sumak/llms.txt

Imports driver adapters for various database systems, allowing Sumak to interact with different database drivers. Choose the adapter that matches your database and driver library.

```typescript
// Driver adapters
import { pgDriver } from "sumak/drivers/pg"
import { mysql2Driver } from "sumak/drivers/mysql2"
import { betterSqlite3Driver } from "sumak/drivers/better-sqlite3"
import { mssqlDriver } from "sumak/drivers/mssql"
```

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

### Sumak Window Functions

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Demonstrates various window functions like ROW_NUMBER, RANK, DENSE_RANK, SUM with frame clauses, and LAG/LEAD/NTILE. Ensure necessary functions are imported from 'sumak'.

```typescript
import { over, rowNumber, rank, denseRank, lag, lead, ntile, count, sum } from "sumak"

// ROW_NUMBER
db.selectFrom("employees")
  .select({
    DESC: over(rowNumber(), (w) => w.partitionBy("dept").orderBy("salary")),
    "rn",
  })
  .toSQL()
```

```typescript
over(rank(), (w) => w.orderBy("score", "DESC"))
```

```typescript
over(denseRank(), (w) => w.orderBy("score", "DESC"))
```

```typescript
over(sum(col.amount), (w) =>
  w
    .partitionBy("userId")
    .orderBy("createdAt")
    .rows({ type: "unbounded_preceding" }, { type: "current_row" }),
)
```

```typescript
over(count(), (
  w
) =>
  w.orderBy("salary").range({ type: "preceding", value: 100 }, { type: "following", value: 100 }),
)
```

```typescript
over(lag(col.price, 1), (w) => w.orderBy("date"))
```

```typescript
over(lead(col.price, 1), (w) => w.orderBy("date"))
```

```typescript
over(ntile(4), (w) => w.orderBy("salary", "DESC"))
```

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

### Create Index DDL Statements

Source: https://context7.com/productdevbook/sumak/llms.txt

Demonstrates how to generate DDL statements for creating indexes, including unique indexes and indexes using specific storage types like 'gin'.

```typescript
// CREATE INDEX
db.schema.createIndex("idx_users_email").unique().on("users").column("email").build()
db.schema.createIndex("idx_tags").on("posts").column("tags").using("gin").build()
```

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

### EXPLAIN Query

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Generate EXPLAIN statements for queries to analyze execution plans. Supports options like `analyze` and `format` for detailed output.

```ts
db.selectFrom("users").select("id").explain().toSQL()
// EXPLAIN SELECT "id" FROM "users"
```

```ts
db.selectFrom("users").select("id").explain({ analyze: true }).toSQL()
// EXPLAIN ANALYZE SELECT ...
```

```ts
db.selectFrom("users").select("id").explain({ format: "JSON" }).toSQL()
// EXPLAIN (FORMAT JSON) SELECT ...
```

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

### Sumak ORM - Aggregate with ORDER BY

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Demonstrates ordering within aggregate functions in Sumak ORM, specifically for `stringAgg` and `arrayAgg`. Allows control over the order of elements when concatenating strings or collecting arrays.

```ts
import { stringAgg, arrayAgg } from "sumak"

// STRING_AGG with ORDER BY
db.selectFrom("users")
  .select({ names: stringAgg(col.name, ", ", [{ expr: col.name, direction: "ASC" }]) })
  .toSQL()
// STRING_AGG("name", ', ' ORDER BY "name" ASC)

// ARRAY_AGG
db.selectFrom("users")
  .select({ ids: arrayAgg(col.id) })
  .toSQL()
```

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

### Run Sumak Regression Benchmarks

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Use this command to run regression tests for Sumak's performance benchmarks. Setting PERF_GUARD=1 ensures that significant slowdowns are detected.

```bash
PERF_GUARD=1 pnpm vitest run bench/regression.test.ts
```

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

### Sumak Raw SQL with Tagged Template

Source: https://context7.com/productdevbook/sumak/llms.txt

Use tagged templates for raw SQL with automatic parameterization. Ensure proper imports from 'sumak'.

```typescript
import { sql, unsafeRawExpr, unsafeSqlFn, setUnsafeWarnHandler } from "sumak"

// Tagged template with auto-parameterization
db.selectFrom("users")
  .select({ today: sql`CURRENT_DATE` })
  .where(() => sql`"age" > ${18}`)
  .toSQL()
```

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

### Run Integration Tests

Source: https://github.com/productdevbook/sumak/blob/main/test/integration/dockerized/README.md

Executes the integration test suite. The INTEGRATION_DB=1 environment variable is required to enable these tests.

```bash
# 3. Run the suite.
INTEGRATION_DB=1 pnpm vitest run test/integration/dockerized/
```

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

### WHERE Clause: Equality Comparisons

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Demonstrates various equality and inequality comparisons for WHERE clauses, including equals, not equals, greater than, greater than or equal to, less than, and less than or equal to.

```typescript
.where(({ age }) => age.eq(25))        // = 25
```

```typescript
.where(({ age }) => age.neq(0))        // != 0
```

```typescript
.where(({ age }) => age.gt(18))        // > 18
```

```typescript
.where(({ age }) => age.gte(18))       // >= 18
```

```typescript
.where(({ age }) => age.lt(65))        // < 65
```

```typescript
.where(({ age }) => age.lte(65))       // <= 65
```

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

### Auto-Generate CREATE TABLE SQL from Schema

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Define table structures using a schema object and let Sumak generate the corresponding SQL CREATE TABLE statements. Supports dialect-specific syntax.

```typescript
const db = sumak({
  dialect: pgDialect(),
  tables: {
    users: {
      id: serial().primaryKey(),
      name: text().notNull(),
      email: text().notNull(),
    },
    posts: {
      id: serial().primaryKey(),
      title: text().notNull(),
      userId: integer().references("users", "id"),
    },
  },
})

const ddl = db.generateDDL()
// [
//   { sql: 'CREATE TABLE "users" ("id" serial PRIMARY KEY NOT NULL, "name" text NOT NULL, "email" text NOT NULL)', params: [] },
//   { sql: 'CREATE TABLE "posts" ("id" serial PRIMARY KEY NOT NULL, "title" text NOT NULL, "userId" integer REFERENCES "users"("id"))', params: [] },
// ]

// With IF NOT EXISTS
const safeDDL = db.generateDDL({ ifNotExists: true })
```

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

### Build for AWS Lambda

Source: https://github.com/productdevbook/sumak/blob/main/examples/nitro/README.md

Command to build the Nitro project for deployment on AWS Lambda.

```bash
NITRO_PRESET=aws-lambda pnpm build
```

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

### Configure Sumak with Options

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Configure Sumak by enabling or disabling features like normalization and query optimization. Default behavior enables both.

```typescript
// Default: both enabled
const db = sumak({ dialect: pgDialect(), tables: { ... } })
```

```typescript
// Disable normalization
const db = sumak({ dialect: pgDialect(), normalize: false, tables: { ... } })
```

```typescript
// Disable optimization
const db = sumak({ dialect: pgDialect(), optimizeQueries: false, tables: { ... } })
```

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

### Configure Multi-Tenant Plugin

Source: https://github.com/productdevbook/sumak/blob/main/README.md

The `multiTenant` plugin automatically injects a `tenant_id` into all queries for specified tables. Use a callback for per-request tenant resolution.

```typescript
// Auto-inject tenant_id on all queries
// Use a callback for per-request tenant resolution:
const db = sumak({
  plugins: [
    multiTenant({
      tables: ["users", "posts"],
      tenantId: () => getCurrentTenantId(),  // called per query
    }),
  ],
  ...
})

db.selectFrom("users").select("id").toSQL()
// SELECT "id" FROM "users" WHERE ("tenant_id" = $1)

db.insertInto("users").values({ name: "Alice" }).toSQL()
// INSERT INTO "users" ("name", "tenant_id") VALUES ($1, $2)
```

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

### Importing Specific Dialects

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Shows how to import only the necessary dialect and schema components to enable tree shaking and reduce bundle size.

```typescript
import { pgDialect } from "sumak/pg"
import { mysqlDialect } from "sumak/mysql"
import { sqliteDialect } from "sumak/sqlite"
import { mssqlDialect } from "sumak/mssql"
```

```typescript
import { sumak } from "sumak"
import { pgDialect } from "sumak/pg"
import { serial, text } from "sumak/schema"
```

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

### Compile Basic Transaction Control SQL

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Generate SQL for basic transaction control commands like COMMIT, ROLLBACK, and SAVEPOINT using Sumak's `tx` namespace. These builders create the SQL string, which is then executed by the driver.

```ts
db.compile(tx.commit())
// COMMIT
db.compile(tx.rollback())
// ROLLBACK
db.compile(tx.commit({ chain: true }))
// COMMIT AND CHAIN

db.compile(tx.savepoint("sp1"))
// SAVEPOINT "sp1"
db.compile(tx.releaseSavepoint("sp1"))
// RELEASE SAVEPOINT "sp1"
db.compile(tx.rollbackTo("sp1"))
// ROLLBACK TO SAVEPOINT "sp1"
```

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

### Sub-paths

Source: https://github.com/productdevbook/sumak/blob/main/AGENTS.md

Import specific modules or dialects from the Sumak library.

```APIDOC
## Sub-paths

### Description
Import specific modules or dialects from the Sumak library.

### Details
`sumak`, `sumak/pg`, `sumak/mssql`, `sumak/mysql`, `sumak/sqlite`, `sumak/schema`

### Request Example
```typescript
import { pgDialect } from "sumak/pg"
```
```

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

### Use withSchema Plugin

Source: https://github.com/productdevbook/sumak/blob/main/README.md

The `withSchema` plugin allows specifying a schema for all queries. Ensure the plugin is registered with the Sumak instance.

```typescript
const db = sumak({
  plugins: [withSchema("public")],
  ...
})
// SELECT * FROM "public"."users"
```

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

### Plugins

Source: https://github.com/productdevbook/sumak/blob/main/AGENTS.md

Extend Sumak's functionality with custom plugins.

```APIDOC
## Plugins (factory fns)

### Description
Extend Sumak's functionality with custom plugins.

### Request Example
```typescript
import { softDelete, audit, multiTenant } from "sumak"

sumak({
  plugins: [
    softDelete({ tables: ["users"] }),
    audit({ tables: ["users"] }),
    multiTenant({ tables: ["users"], tenantId: () => ctx.id }),
  ],
  ...
})
```
```

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

### Generate Schema with Sumak CLI

Source: https://github.com/productdevbook/sumak/blob/main/examples/README.md

Use the Sumak CLI to introspect the database and generate TypeScript schema files. This is an opt-in step for projects requiring generated files.

```bash
sumak introspect --out src/schema.generated.ts
```

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

### Pre-bake SQL with `.toCompiled()`

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Create reusable, pre-compiled query functions by calling `.toCompiled()` on a query builder. This avoids AST traversal at runtime, improving performance. Requires `placeholder` import.

```typescript
import { placeholder } from "sumak"

const findUser = db
  .selectFrom("users")
  .select("id", "name")
  .where(({ id }) => id.eq(placeholder("userId")))
  .toCompiled<{ userId: number }>()

findUser({ userId: 42 })
// → { sql: 'SELECT "id", "name" FROM "users" WHERE "id" = $1', params: [42] }

findUser({ userId: 99 })
// → { sql: 'SELECT "id", "name" FROM "users" WHERE "id" = $1', params: [99] }

findUser.sql // pre-baked SQL string

// Also works on UPDATE / INSERT / DELETE:
const renameUser = db
  .update("users")
  .set({ name: placeholder("newName") })
  .where(({ id }) => id.eq(placeholder("id")))
  .toCompiled<{ id: number; newName: string }>()
```

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

### WHERE Clause: Column-to-Column Comparisons

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Shows how to compare columns directly within a WHERE clause using the same comparison methods (e.g., `.eq`, `.gt`) by passing another column proxy as the argument.

```typescript
.where(({ price, cost }) => price.gt(cost))       // "price" > "cost"
```

```typescript
.where(({ a, b }) => a.eq(b))                     // "a" = "b"
```

```typescript
.where(({ a, b }) => a.neq(b))                    // "a" != "b"
```

```typescript
.where(({ a, b }) => a.gte(b))                    // "a" >= "b"
```

```typescript
.where(({ a, b }) => a.lt(b))                     // "a" < "b"
```

```typescript
.where(({ a, b }) => a.lte(b))                    // "a" <= "b"
```

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

### SELECT with WHERE, ORDER BY, LIMIT, and OFFSET

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Build a complex SELECT query that includes filtering with `where`, sorting with `orderBy`, and pagination with `limit` and `offset`. The `where` clause uses a callback for type-safe column access.

```ts
db.selectFrom("users")
  .select("id", "name")
  .where(({ age }) => age.gte(18))
  .orderBy("name")
  .limit(10)
  .offset(20)
  .toSQL()
```

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

### Create NTILE Window Function for Bucketing

Source: https://context7.com/productdevbook/sumak/llms.txt

Distribute rows into a specified number of buckets using `over` with `ntile`. Requires `sumak` import.

```typescript
// NTILE for bucket distribution
db.selectFrom("employees")
  .select({
    quartile: over(ntile(4), w => w.orderBy("salary", "DESC"))
  })
  .toSQL()
```

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

### Select All Columns and Execute

Source: https://context7.com/productdevbook/sumak/llms.txt

Retrieve all columns for all rows from a table using `selectAll()` and execute the query to fetch multiple rows with `many()`. The return type is an array of row objects.

```typescript
// Select all columns
const allUsers = await db.selectFrom("users").selectAll().many()
// Returns: Row[]
```

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

### Sumak Namespace Imports and Usage

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Import and utilize various helper functions from Sumak's namespaces for window, string, math, array operations, and low-level AST manipulation. Ensure all necessary modules are imported.

```ts
import { win, str, num, arr, ast, tx, over, val } from "sumak"

// Window functions
over(win.rowNumber(), (w) => w.partitionBy("dept").orderBy("salary", "DESC"))
over(win.rank(), (w) => w.orderBy("score", "DESC"))
over(win.lag(col.price, 1), (w) => w.orderBy("date"))

// String functions
str.upper(col.name)
str.concat(col.first, val(" "), col.last)
str.length(col.email)

// Math
num.abs(col.balance)
num.round(col.price, 2)
num.greatest(col.a, col.b)

// PostgreSQL array operators
arr.contains(col.tags, unsafeRawExpr("ARRAY['sql']")) // @>
arr.overlaps(col.tags, unsafeRawExpr("ARRAY['sql','ts']")) // &&

// Low-level AST (plugin authors, advanced use)
ast.binOp("=", ast.col("id"), ast.lit(1))
ast.visit(node, visitor)
```

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

### Cursor Pagination with Sumak

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Implement forward and backward cursor pagination. Supports first page retrieval and integration with existing WHERE clauses. Ensure `pageSize` is set appropriately for `hasNextPage` detection.

```typescript
// Forward pagination (after cursor)
db.selectFrom("users")
  .select("id", "name")
  .cursorPaginate({ column: "id", after: 42, pageSize: 20 })
  .toSQL()
// SELECT "id", "name" FROM "users" WHERE ("id" > $1) ORDER BY "id" ASC LIMIT 21
// params: [42] — pageSize + 1 for hasNextPage detection
```

```typescript
// Backward pagination (before cursor)
db.selectFrom("users")
  .select("id", "name")
  .cursorPaginate({ column: "id", before: 100, pageSize: 20 })
  .toSQL()
// WHERE ("id" < $1) ORDER BY "id" DESC LIMIT 21
```

```typescript
// First page (no cursor)
db.selectFrom("users").select("id", "name").cursorPaginate({ column: "id", pageSize: 20 }).toSQL()
// LIMIT 21
```

```typescript
// With existing WHERE — ANDs together
db.selectFrom("users")
  .select("id", "name")
  .where(({ active }) => active.eq(true))
  .cursorPaginate({ column: "id", after: lastId, pageSize: 20 })
  .toSQL()
```

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

### Sumak ORM - Aggregate Functions

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Applies various aggregate functions in Sumak ORM, including `count`, `countDistinct`, `sum`, `sumDistinct`, `avg`, `avgDistinct`, `min`, and `max`. Demonstrates basic usage and `coalesce` for handling nulls.

```ts
import { count, countDistinct, sum, sumDistinct, avg, avgDistinct, min, max, coalesce } from "sumak"

db.selectFrom("users").select({ total: count() }).toSQL()
db.selectFrom("users")
  .select({ uniqueDepts: countDistinct(col.dept) })
  .toSQL()
db.selectFrom("orders")
  .select({ uniqueSum: sumDistinct(col.amount) })
  .toSQL()
db.selectFrom("orders")
  .select({ avgAmount: avg(col.amount) })
  .toSQL()

// COALESCE (variadic)
db.selectFrom("users")
  .select({ displayName: coalesce(col.nick, col.name, val("Anonymous")) })
  .toSQL()
```

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

### Database Pool and Singleton

Source: https://github.com/productdevbook/sumak/blob/main/examples/nitro/README.md

Defines the PostgreSQL connection pool and a singleton instance. Auto-imported by Nitro.

```typescript
import { neon } from '@neondatabase/serverless'
import { drizzle } from 'drizzle-orm/neon-http'
import { eq } from 'drizzle-orm'
import * as schema from './schema'

const sql = neon(process.env.DATABASE_URL!)
export const db = drizzle(sql, { schema })

// Pool sits on globalThis.__pgPool so HMR doesn't duplicate it.
// @ts-ignore
if (!globalThis.__pgPool) {
  // @ts-ignore
  globalThis.__pgPool = db
}

// @ts-ignore
export const pool = globalThis.__pgPool

export const tables = {
  events: schema.events,
}
```

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

### Compile Raw AST with `compileQuery()`

Source: https://github.com/productdevbook/sumak/blob/main/README.md

Compile a raw query AST into an executable query object using the `compileQuery` function. This is useful when working with AST nodes directly. Requires `compileQuery` and `placeholder` imports.

```typescript
import { compileQuery, placeholder } from "sumak"

const findUser = compileQuery<{ userId: number }>
(
  db
    .selectFrom("users")
    .select("id", "name")
    .where(({ id }) => id.eq(placeholder("userId")))
    .build(),
  db.printer(),
)
```