IBM i (DB2) Dialect for Knex

Github Repo

NPM Package

When building Node.js applications on IBM i, I traditionally used the low-level 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.

This project implements a first-class Knex dialect for IBM i DB2 over ODBC. Starting as a fork of the unmaintained 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

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:

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:

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:

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

CLI usage:

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.