Node.js and Db2 for i data

[worksofliam]

Node.js and your Db2 data

I have been working on some rather large Node.js projects to serve up information from a Db2 for I database. Sadly there is no really well supported ORM for Db2 for i so I’ve been following my own standard on how I should access APIs. I am going to use this blog post to write about it.

Data access

There are two main Db2 for i drivers that you can use in Node.js

• idb-connector and idb-pconnector (the pconnector is for Promises). These only run on IBM i, which means development has to take place on the remote system.
• ODBC. I have been using this for everything lately. The driver is available for IBM i, Windows and Linux. I have been using the Mac OS version, which isn’t publicly available yet.

Whether I use either of these drivers, I like to have a single pool of connections in which can be accessed throughout my entire Node.js application. I use a generic object which has two main functions: connect and executeStatement.

They are available here:

• idb-pconnector
• ODBC

It just means we can do this in our code:

const db2 = require('./db2')

work();

async function work() {
  console.log(await db2.executeStatement('select * from schema.mytable'));
}

Data models

For this blog, you can find my Db2 for i sample tables here.

Usually, this is where an ORM can help out. When building APIs, I like to make sure my requests match the models (which may come from documentation). For this, I use JavaScript classes.

Below we have an SQL statement that returns 5 columns.

Since we'll be using a class as an abstraction layer, we can stop using short names with names that make sense instead. Both database connectors return the column names as uppercase too, which is a little bit ugly if you ask me.

Below is a class that represents a Department (row).

module.exports = class Department {
  constructor(row) {
    this.number = row.DEPTNO;
    this.name = row.DEPTNAME;
    this.managerNumber = row.MGRNO;
    this.reportingDepartment = row.ADMRDEPT;
    this.location = row.LOCATION;
  }
}

Now we have created our department class, we can pass in each row object from our select statement into this class constructor.

async function getDepartments() {
  const resultSet = await db2.executeStatement('select * from sample.dept');
  var departments = [];
  resultSet.forEach(dept => departments.push(new Department(dept)));

  return departments;
}

You could also make getDepartments a static method inside of the Department class.

module.exports = class Department {
  constructor(row) {
    //...
  }

  async static getDepartments() {
    const resultSet = await db2.executeStatement('select * from sample.dept');

    var departments = [];
    resultSet.forEach(dept => departments.push(new this(dept)));

    return departments;
  }
}

Like most ORMs, there is usually a method to fetch a single row too, which is more than simple. This method could also be part of the Department class.

  async static getDepartment(number) {
    const resultSet = await db2.executeStatement('select * from sample.dept where deptno = ?', [number]);

    if (resultSet.length === 1) {
      return new this(resultSet[0]);
    } else {
      throw new Error(`Department ${number} does not exist.`);
    }
  }

You may also create methods for deleting and updating the rows too. Of course, you could do a lot more with the model too. Our Department class has a reportingDepartment property and we could create an instance method get the reporting Department as an object. For example:

  async getReportingDepartment() {
    if (this.number === this.reportingDepartment) {
      throw new Error('Reporting department is circular.');
    } else {
      return await Department.getDepartment(this.reportingDepartment);
    }
  }

And could be used like so:

const opsDept = await Department.getDepartment('E11');
console.log(`Department name: ${opsDept.name}`)
const parentDept = await opsDept.getReportingDepartment();
console.log(`Parent department name: ${parentDept.name}`)

Using models in APIs

The really nice thing about JavaScript classes is that you can also use them when building web service responses. Let's say we want to have two endpoints:

1. Return list of Departments
2. Get a single department

Using our Department class/model, it makes fetching this data simple since all the data access is already done for us and we don't have SQL flying all over the place. This is what my express routes may look like:

app.get('/departments', async (req, res) => {
  const depts = await Department.getDepartments();

  res.json(depts);
});

app.get('/departments/:dept', async (req, res) => {
  //May want to add custom validation for the parameter?
  const deptno = req.params.dept; 

  var result = {
    success: true,
    data: undefined
  };

  //getDepartment will error if no department is found
  try {
    result.data = await Department.getDepartment(deptno);
  } catch (e) {
    result.success = false;
    result.data = e.message;
  }

  res.json(result);
});

What is really great about this, is that when our class is returned as a string in the response, it only has the class properties! (which is exactly what we want).

[cole-maxwell1]

@worksofliam Have you ever evaluated the Sequelize ORM project? I looked through their issues on GitHub, and they have added Db2 for i support.

[worksofliam]

@cole-maxwell1 Since this post, yes! It works okay! The downside is that most IBM i shops do not have normalised databases, which makes using it a bit awkward.

[benjaminborgen]

@worksofliam Have you tried using the bindings parameter in the executeStatement function for calling RPGLE programs? Example: statement = 'call library.program(?, ?)' bindings = [1, 2]