Why writing a database driver is hard

[worksofliam]

For the longest time, I had wanted a native Node.js database driver for Db2 for i. This meant writing the entire socket connection in JavaScript, using net.Socket, Buffer, and other things. There is no documentation on the Db2 for i socket server, other than existing code bases like jtopen.

As a suggestion from ThePrez himself, I actually used the codebase for jtopenlite as it was more precise in what I was trying to do (e.g. not recreate the entirety of jtopen, but just the database portion of it). jtopenlite was a great guide for me getting started.

Creating the connection to the database was generally a lot of work. I got it working to the point where it was creating an 'authentication job', which we can see from this image.

Since the process of creating a valid database connection is messy, I ended up creating this document to outline the flow of how the connection should be made to Db2 for i. As you can probably tell.. there are a lot of steps. Sad times indeed.

There are two separate connections when connecting to the database:

1. One to authenticate and start the database job.
2. One that connects to the database job that was spun up.

After you've got the main connection, you need to set all the connection attributes.

Some key recorded moments of development:

• around 4:16 that I demo the connection being created successfully.
• around 2:48:00 getting the statement to create

The issue with this project

The issue with this project is that it's so big. It's really a lot of work for a one-person job who is only doing it in their spare time. There is too much to implement and just not enough time in the day. These were some of the things that make this project so big

• Different SQL data type implementation
• Binding parameters in queries
• Calling stored procedures (with IN, OUT, INOUT support)
• Supporting (too) many CCSIDs
• Writing a nice API
• Connection pools
• Documenting the socket connection

As much as I would love to continue working on it, I personally don't have a lot of time. This dream will live on. This is as far as I got with the API:

const connection = require('./lib/driver/Connection');

(async () => {
  const conn = await connection.getConnection(false, process.env.SYSTEM, process.env.USER, process.env.PASS);

  const stmt = await conn.createStatement();

  conn.close();
})();

You can find the repository here.