While preparing my JSON talk for VoxxedDays Zurich I noticed that the (async) iterable protocol wasn’t available for node-oracledb’s SodaDocumentCursor.

SODA, or short for Simple Oracle Document Access, is a set of NoSQL-style APIs that let you create and store collections of documents (in particular JSON) in Oracle Database, retrieve them, and query them, without needing to know Structured Query Language (SQL) or how the documents are stored in the database.

Document cursors are commonly used when you search for something in a collection (of JSON documents). Search predicates are provided as JSON as well, so-called QBEs.

Iterating à la node-oracledb 6.3

So, if you wanted to iterate over a set of documents you fetched from a collection you had to use the next() method, as described in the node-oracledb 6.3 docs. For example:

  let docCursor = await myCollection
    .find()
    .filter({ employeeId: { $lt: 105 } })
    .getCursor();
  let myDocument;
  while ((myDocument = await docCursor.getNext())) {
    console.log(myDocument.getContent());
  }
  docCursor.close();

This works perfectly fine!

Iterating, the new way

I’m a little lazy and that’s a bit too much for me to type. I would much rather have used the following syntax:

  let docCursor = await myCollection
    .find()
    .filter({ employeeId: { $lt: 105 } })
    .getCursor();
  for await (const doc of docCursor) {
    console.log(doc.getContent());
  }
  docCursor.close();

Thankfully this is now available in node-oracledb-6.4.0 and later. Here’s the complete example. It requires

• Oracle Database Free
• A compatible Oracle Instant Client
• Node LTS (20.11.1)
• (node) oracledb-6.4.0 or later

Have a look at the landing page for Oracle Database Free to get the database and instant client.

import oracledb from "oracledb";

/**
 * A tiny demo showing how to iterate over a SodaDocumentCursor.
 * Requires Oracle Database Free, oracledb-6.4.0 or later
 * and a compatible instant client. Tested using node LTS (20.11.1)
 * on Linux x86-64
 */
(async () => {
  if (process.platform !== "linux") {
    throw new Error("this script must be run on Linux");
  }

  // SODA requires "thick" mode.
  // See https://node-oracledb.readthedocs.io/en/v6.4.0/user_guide/installation.html
  // for details
  if (process.env.LD_LIBRARY_PATH === undefined) {
    // eslint-disable-next-line no-console
    console.log(
      "you might need to set LD_LIBRARY_PATH if you get errors concerning SodaDatabase",
    );
  }

  // switch to "thick" mode
  oracledb.initOracleClient({});

  // connect to the database. Credentials must be provided as environment
  // variables (the app is supposed to run in a container)
  let dbConnection;
  try {
    dbConnection = await oracledb.getConnection({
      user: process.env.USERNAME,
      password: process.env.PASSWORD,
      connectionString: process.env.CONNSTRING,
    });
  } catch (err) {
    throw new Error(
      "cannot connect - provide username, password, connection string as environment variables",
    );
  }

  // get a handle to the SODA Database
  const soda = dbConnection.getSodaDatabase();
  // Oracle recommends setting auto-commit to true when working with
  // SODA collections
  oracledb.autoCommit = true;

  // create a new collection, or get a handle to the connection
  // in case it exists already
  const myCollection = await soda.createCollection("myCollection");

  // add some sample documents to the database. Generated using a simple SQL query:
  // select json_arrayagg( json { 'employeeId' : employee_id, 'name' : first_name || ' ' || last_name } returning clob)
  // from employees where employee_id < 111;
  const payload = [
    { employeeId: 100, name: "Steven King" },
    { employeeId: 101, name: "Neena Yang" },
    { employeeId: 102, name: "Lex Garcia" },
    { employeeId: 103, name: "Alexander James" },
    { employeeId: 104, name: "Bruce Miller" },
    { employeeId: 105, name: "David Williams" },
    { employeeId: 106, name: "Valli Jackson" },
    { employeeId: 107, name: "Diana Nguyen" },
    { employeeId: 108, name: "Nancy Gruenberg" },
    { employeeId: 109, name: "Daniel Faviet" },
    { employeeId: 110, name: "John Chen" },
  ];

  // add the documents if they haven't already been added
  const numdocs = await myCollection.find().count();
  if (numdocs.count === 0) {
    await myCollection.insertMany(payload);
  }

  // find all documents in the collection with an employee ID < 105
  // and iterate over them
  let docCursor = await myCollection
    .find()
    .filter({"employeeId" : {"$lt" : 105}})
    .getCursor();
  for await (const doc of docCursor) {
    // eslint-disable-next-line no-console
    console.log(doc.getContent())
  }

  // clean up and close
  docCursor.close();
  await dbConnection.close();
})();

After exporting LD_LIBRARY_PATH pointing to your instant client and providing username, password and connection string via environment variables you’re good to go.