knex-db2

A Knex.js client adapter for IBM Db2 databases (LUW and z/OS) using the ibm_db driver via ODBC.

Installation

npm install knex knex-db2 ibm_db

Usage

const knex = require('knex');
const Db2Client = require('knex-db2');

const db = knex({
  client: Db2Client,
  connection: {
    hostname: 'your-db2-host',
    port: 50000,
    database: 'your-database',
    user: 'your-username',
    password: 'your-password'
  },
  pool: {
    min: 2,
    max: 10
  }
});

// Standard Knex.js API works seamlessly
const users = await db('users').select('*');
const newUser = await db('users').insert({ name: 'John', email: '[email protected]' });
const limitedResults = await db('users').limit(10).offset(20);

Features

• Full Knex.js compatibility - Drop-in replacement for other Knex clients
• DB2 SQL dialect - Automatic translation to DB2-specific syntax (FETCH FIRST, OFFSET)
• Enhanced error handling - Comprehensive DB2 error code mapping and categorization
• Connection management - Robust connection pooling and timeout handling
• Schema operations - Complete DDL support for tables, indexes, and constraints

Error Handling

The client provides enhanced error handling with DB2-specific error categorization:

const { DB2Error, isRetryableError } = require('knex-db2');

try {
  const results = await db('employees').select('*');
} catch (error) {
  if (error instanceof DB2Error) {
    console.log('Error Type:', error.errorType);
    console.log('Category:', error.errorCategory);
    console.log('Is Retryable:', error.isRetryable());
  }
}

Data Type Mapping

| Knex Method | DB2 Type | |-------------|----------| | increments() | INTEGER GENERATED BY DEFAULT AS IDENTITY | | string(n) | VARCHAR(n) | | text() | CLOB | | integer() | INTEGER | | decimal(p,s) | DECIMAL(p,s) | | boolean() | SMALLINT | | timestamp() | TIMESTAMP | | binary() | BLOB |

Limitations

This package provides core Knex.js functionality for Db2, but some advanced features are not yet implemented:

• Custom transactions - Uses Knex default transaction handling
• Stored procedures - No support for calling Db2 stored procedures
• Advanced Db2 features - No support for sequences, CTEs, or bulk operations
• Migration optimizations - Uses standard Knex migrations (not Db2-optimized)
• Performance tuning - No Db2-specific performance optimizations
• Db2 for IBM i - Not supported (use @bdkinc/knex-ibmi instead)

Requirements

• Node.js 14+
• IBM Db2 client libraries installed on the system
• Access to IBM Db2 LUW or z/OS database
• ibm_db driver (automatically installed with this package)

Documentation

• Windows DB2 Client Setup - Installation guide for Windows
• Troubleshooting - Common issues and solutions
• Error Handling - Comprehensive error handling guide

License

MIT