# A Production-Ready Knex Dialect for IBM i DB2

> Streaming queries, emulated RETURNING, multi-row inserts, and a custom migration runner. This is the Knex dialect I wish existed when I started building on IBM i.

- Author: Brian Kimball
- Published: 2025-09-20
- Tags: typescript, development, ibmi
- Canonical: https://brian-kimball.com/blog/knex-ibmi/
- Markdown: https://brian-kimball.com/blog/knex-ibmi/index.md

[Github Repo](https://github.com/bdkinc/knex-ibmi)

[NPM Package](https://www.npmjs.com/package/@bdkinc/knex-ibmi)

When building Node.js applications on IBM i, I traditionally used the low-level [node-odbc](https://github.com/IBM/node-odbc) driver. While functional, I prefer the expressiveness of a query builder or ORM. Knex is a popular, lightweight query builder used by frameworks like [Feathers](https://feathersjs.com/).

This project implements a first-class Knex dialect for IBM i DB2 over ODBC. Starting as a fork of the unmaintained [knex-db2](https://github.com/henryjw/knex-db2), it evolved into a ground-up TypeScript rewrite with modern packaging (ESM + CJS) and IBM i-specific behavior.

The current version includes a custom migration runner, multi-row insert strategies, `RETURNING` emulation, streaming with adaptive fetch sizing, and improved error handling.

## Quick Start

```ts
import { knex } from 'knex'
import { DB2Dialect, DB2Config } from '@bdkinc/knex-ibmi'

const config: DB2Config = {
  client: DB2Dialect,
  connection: {
    database: '*LOCAL',
    host: '127.0.0.1',
    port: 8471,
    user: 'user',
    password: 'password',
    driver: 'IBM i Access ODBC Driver',
    connectionStringParams: {
      DBQ: 'MYLIB',
      CMT: 0,
      NAM: 1,
      ALLOWPROCCALLS: 1,
    },
  },
  pool: { min: 2, max: 10 },
  ibmi: { multiRowInsert: 'auto' },
}

export const db = knex(config)
```

---

## Feature Highlights

- **Knex 3 Integration:** Fully integrated query building and execution.
- **Transactions:** Includes a custom transaction class.
- **Streaming:** Adaptive streaming (`.stream({ fetchSize })`) with cursor and async iteration support.
- **Multi-row Inserts:** Strategies include `auto`, `sequential`, and `disabled`.
- **Emulated Returning:** Support for `INSERT`, `UPDATE`, and `DELETE`.
- **Identity Retrieval:** Sequential retrieval using `IDENTITY_VAL_LOCAL()`.
- **Migration Runner:** Custom runner to bypass fragile Knex locking on DB2.
- **Developer Experience:** Fully typed (TypeScript), lenient identifier handling, and enhanced error classification.

---

## Multi‑Row Insert Strategies

Configure via the `ibmi.multiRowInsert` option:

```ts
const db = knex({
  client: DB2Dialect,
  connection: {
    /* ... */
  },
  ibmi: { multiRowInsert: 'auto' }, // 'auto' | 'sequential' | 'disabled'
})
```

- `auto`: Single `INSERT` with multiple `VALUES`. Returns all rows when safe.
- `sequential`: Inserts rows individually, capturing identity each time.
- `disabled`: Backwards compatible single-row behavior.

Use `sequential` for deterministic identity values per row, and `auto` for maximum throughput.

---

## Emulated Returning

Since ODBC lacks native `RETURNING` support, this dialect emulates it:

- **INSERT (auto):** Wraps insert to surface inserted rows.
- **INSERT (sequential):** Per-row insert + `IDENTITY_VAL_LOCAL()`.
- **UPDATE:** Executes update, then re-selects affected rows using the original `WHERE` clause.
- **DELETE:** Selects rows first, then deletes them, returning the data.

Select only necessary columns (`.returning(['ID','STATUS'])`) to reduce overhead.

---

## Streaming

Consume large result sets via a cursor stream with adaptive fetch sizing:

```ts
const stream = await db('LARGETABLE').select('*').stream({ fetchSize: 200 })
for await (const row of stream) {
  // process row
}
```

Complex queries (joins, aggregates) automatically increase fetch size, which you can also override manually.

---

## Migration Runner (IBM i Specific)

Standard Knex migrations often fail on IBM i due to auto-commit DDL and locking. This dialect includes a purpose-built runner:

```ts
import { createIBMiMigrationRunner } from '@bdkinc/knex-ibmi'
const runner = createIBMiMigrationRunner(db, {
  directory: './migrations',
  tableName: 'KNEX_MIGRATIONS',
  schemaName: 'MYSCHEMA',
})
await runner.latest()
```

CLI usage:

```bash
npx ibmi-migrations migrate:latest
npx ibmi-migrations migrate:rollback
npx ibmi-migrations migrate:status
```

Supports JS, TS, MJS, and CJS migration files.

---

## Error Handling & Debugging

Errors are classified by type (connection, timeout, SQL). Set `DEBUG=true` for concise output, including timing and statement diagnostics.

---

## Current Maturity

This library is production-ready and stable. Future improvements include richer diagnostic logging and tuning for large batch sequential inserts.

If you hit an edge case, please open an issue—real-world feedback helps solidify the API.

---

## Closing Thoughts

Knex becomes a compelling option on IBM i with correct streaming, transactions, returning emulation, and safe migrations. This dialect provides those building blocks without enforcing heavy abstractions.

Give it a spin and let me know what you build with it.