Lesson 7 · Node.js on IBM i — From RPG Developer to Modern IBM i Developer

Run Your First SQL Query from Node.js

Query Db2 for i from Node.js, inspect the returned rows, and add safe filtering with parameter markers.

Saurabh Arora

IBM i Solution Architect | Technical Lead | Modernization & Integration Specialist

Published September 4, 20263 min read

Now that Node.js can reach Db2 for i, we can run a read-only query and use the returned rows as normal JavaScript data.

We will query the Db2 catalog rather than an application table. That makes the example useful without assuming your system has a particular business schema.

Create the query program

In the same project directory, create list-tables.js:

const { Connection, Statement } = require('idb-pconnector');

async function main() {
  const connection = new Connection({ url: '*LOCAL' });
  const statement = new Statement(connection);

  const sql = `
    SELECT TABLE_SCHEMA,
           TABLE_NAME,
           TABLE_TYPE
      FROM QSYS2.SYSTABLES
     WHERE TABLE_SCHEMA = 'QSYS2'
     ORDER BY TABLE_NAME
     FETCH FIRST 5 ROWS ONLY
  `;

  try {
    const rows = await statement.exec(sql);
    console.table(rows);
  } finally {
    await statement.close();
  }
}

main().catch((error) => {
  console.error('Query failed.');
  console.error(error);
  process.exitCode = 1;
});

Run it:

node list-tables.js

The program should display up to five catalog entries from the QSYS2 schema.

Follow the flow

The program has four main steps:

Create local connection
        ↓
Create statement
        ↓
Execute SELECT
        ↓
Receive rows as JavaScript data

The SQL itself is ordinary Db2 for i SQL. The new part is how the result crosses into Node.js.

statement.exec(sql) resolves to an array of rows. Each row can be inspected, transformed, or later returned as JSON from an API.

For example:

for (const row of rows) {
  console.log(`${row.TABLE_SCHEMA}.${row.TABLE_NAME}`);
}

SQL remains responsible for selecting and shaping the data. Node.js receives the result and can later expose it to a browser, mobile application, partner, or cloud service.

Do not build SQL with user input

The first query contains a fixed schema name. Real applications often receive filters from a request.

Do not concatenate untrusted input into SQL:

// Do not do this.
const sql = `SELECT * FROM QSYS2.SYSTABLES
              WHERE TABLE_SCHEMA = '${schema}'`;

Use a parameter marker instead:

const sql = `
  SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
    FROM QSYS2.SYSTABLES
   WHERE TABLE_SCHEMA = ?
   ORDER BY TABLE_NAME
   FETCH FIRST 5 ROWS ONLY
`;

await statement.prepare(sql);
await statement.bindParameters(['QSYS2']);
await statement.execute();

const rows = await statement.fetchAll();
console.table(rows);

Parameter markers keep data separate from SQL syntax. They are an essential defense against SQL injection and also make value handling more predictable.

Validation is still required. A parameterized query does not decide whether a caller should be allowed to request a particular schema or record.

Keep work in the right layer

Avoid retrieving an entire table and filtering it in JavaScript when Db2 can do the work efficiently.

Prefer:

SELECT TABLE_SCHEMA, TABLE_NAME
  FROM QSYS2.SYSTABLES
 WHERE TABLE_SCHEMA = ?
 ORDER BY TABLE_NAME
 FETCH FIRST 5 ROWS ONLY

over selecting every row and then applying JavaScript filters.

Db2 is built to filter, join, aggregate, and sort data. Node.js should request the data the interface actually needs.

Habits worth establishing now

Using SELECT * everywhere

Select the columns the application needs. This makes the contract clearer and can reduce unnecessary work and data exposure.

Forgetting row limits

An API should not accidentally return millions of rows. Add deliberate filters, limits, or pagination.

Logging sensitive data

console.table(rows) is helpful while learning. Production logging should not expose customer data, credentials, tokens, or other sensitive values.

Hiding the real database error

Return a safe message to an external caller, but retain useful diagnostics in controlled application logs. Do not discard the SQL state and error details needed for support.

Try one change

Change the bound schema from QSYS2 to a schema your profile is authorized to inspect:

await statement.bindParameters(['MYAPPLIB']);

Keep the query read-only and use a development environment.

Ready to build an application

You have completed the first course module:

Node.js on IBM i
      ↓
Local Db2 connection
      ↓
Parameterized SQL
      ↓
JavaScript rows ready for an application interface

These seven lessons were intentionally small. You now know where Node.js runs, how to execute a program, how npm manages a project, and how to read Db2 data safely.

In the next lesson, we will leave the disposable examples behind and start one application that we will carry through the rest of the course: a tools rental system.