Menu

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

Build a Small Db2 Data-Access Module

Move Db2 code into one module, list available tools, and retrieve one tool safely with a parameterized query.

IBM iNode.jsDb2 for iJavaScriptSQL

Our first Db2 programs opened a connection directly in each file. A real application is easier to maintain when database work has one clear home.

We will create a small module with two operations: list available tools and find one tool by its identifier.

Install the connector in this project

The connector used in the earlier lessons was installed in the disposable eraofi-node directory. The tools-rental application is a separate project, so install the dependency here as well:

cd "$HOME/tool-rental"
npm install idb-pconnector

Run this command on IBM i. The package contains IBM i-specific components and should not be copied from another platform.

Create the database module

Create src/db.js:

const { DBPool } = require('idb-pconnector');

const pool = new DBPool();

async function listAvailableTools() {
  const sql = `
    SELECT TOOL_ID, NAME, CATEGORY, DAILY_RATE, STATUS
      FROM TOOLRENT.TOOLS
     WHERE STATUS = 'AVAILABLE'
     ORDER BY NAME
  `;

  return (await pool.runSql(sql)) ?? [];
}

async function findToolById(toolId) {
  const sql = `
    SELECT TOOL_ID, NAME, CATEGORY, DAILY_RATE, STATUS
      FROM TOOLRENT.TOOLS
     WHERE TOOL_ID = ?
  `;

  const result = await pool.prepareExecute(sql, [toolId]);
  return result?.resultSet?.[0] ?? null;
}

module.exports = {
  findToolById,
  listAvailableTools
};

The pool manages reusable local database connections. Each of its two helper methods borrows a connection, does the work, and returns the connection to the pool, even when the query fails. That is why this module has no close calls of its own.

runSql suits the fixed query. It can return null when there is nothing to return, so ?? [] guarantees that callers always receive an array.

prepareExecute performs the prepare, bind, execute, and fetch steps from Lesson 7 in one call. The array supplies one value for each ?. Its result is an object, and the rows are in its resultSet property. The ?. operator means “continue only if the value on the left exists”, so a missing result becomes null rather than an error.

Values arrive as text

By default the connector returns every column value as a string. TOOL_ID arrives as '1' and DAILY_RATE as '14.00', not as JavaScript numbers.

That is safe for decimal data, because JavaScript numbers cannot represent every decimal value exactly. Keep it in mind when you compare or calculate: convert deliberately with Number(value) at the point where you need a number.

Use the module from the application

Replace src/app.js with:

const {
  findToolById,
  listAvailableTools
} = require('./db');

async function main() {
  const tools = await listAvailableTools();
  console.table(tools);

  const tool = await findToolById(1);

  if (tool) {
    console.log(`Found: ${tool.NAME}`);
  } else {
    console.log('Tool 1 was not found.');
  }
}

main().catch((error) => {
  console.error('Unable to read tools from Db2.');
  console.error(error);
  process.exitCode = 1;
});

Run it:

npm start

The table should contain the four rows loaded in the previous lesson.

Why return null?

findToolById returns null when no row exists. That gives the caller a predictable result and avoids treating “not found” as a database failure.

These are different outcomes:

No matching row      Normal application result
SQL authority error  Operational failure
Connection error     Operational failure

Later, the API will translate null into an HTTP 404 response. It will translate an unexpected database problem into a controlled 500 response and keep the diagnostic detail in the server log.

Keep SQL readable

The SQL selects named columns instead of SELECT *. That makes the data returned by the module deliberate. A future column containing internal notes or sensitive values will not automatically appear in the API.

We also let Db2 filter and order the rows. Fetching the whole table and sorting it in JavaScript would waste work and become slower as the table grows.

Commit a working checkpoint

git add package.json package-lock.json src
git commit -m "Add tools data access"

npm install changed package.json and created package-lock.json, so they belong in the same commit as the code that needs the connector.

The project can now read its own business data. Next we will put a small HTTP API in front of these functions.

References

Documentation and references used for this lesson.

← Return to Node.js on IBM i

Comments

Share your thoughts, questions, or real-world IBM i experiences related to this article.