Creating a REST API Part 2: Database Basics

With the web server in place, it’s time to look into some database basics. As mentioned in the parent post, this series will use the Oracle Database driver/API for Node.js (node-oracledb) to interact with the database. In this post, you’ll create a module that’s responsible for starting up and shutting down a database connection pool. You’ll also add a function that simplifies executing simple statements by getting and releasing connections from the pool automatically.

Please note: This post is part of a series on creating a REST API with Node.js on Oracle Database. See that post for details on the project and links to other parts. Get the code here.

Starting Up the Connection Pool

Generally speaking, there’s some overhead involved with establishing a connection to a database. When apps use many connections for short periods of time, Oracle recommends using a connection pool. Connection pools reduce overhead by establishing groups of connections which are reused many times – this can dramatically increase performance and scalability.

Because node-oracledb is built on top of the OCI client libraries, it has built-in support for creating OCI pools, which work client-side and have excellent performance characteristics. To create a connection pool, start by creating a new configuration file name database.js in the config directory. Copy and paste the following code into the file and save your changes.

module.exports = {
  hrPool: {
    user: process.env.HR_USER,
    password: process.env.HR_PASSWORD,
    connectString: process.env.HR_CONNECTIONSTRING,
    poolMin: 10,
    poolMax: 10,
    poolIncrement: 0
  }
};

As was the case with the config/webserver.js file, this file allows some properties to be set via environment variables. Using environment variables provides flexibility when deploying the app to different environments and helps keep passwords and other sensitive information out of source code. Run the following commands from a terminal to set the required environment variables and ensure they’re available in future terminal sessions.

echo "export HR_USER=hr" >> ~/.bashrc
echo "export HR_PASSWORD=oracle" >> ~/.bashrc
echo "export HR_CONNECTSTRING=0.0.0.0/orcl" >> ~/.bashrc
source ~/.bashrc

You may have noticed that the poolMin and poolMax values were the same and that poolIncrement was set to 0. This will create a pool with a fixed size that requires fewer resources to manage – a good idea for pools that get consistent usage.

While Node.js is often described as single-threaded, it does have a thread pool available for certain operations that would otherwise block the main-thread running the JavaScript code. This thread pool is used by node-oracledb to run all of its asynchronous operations, such as getting connections and executing SQL and PL/SQL code. However, the default size of the thread pool is 4. If you want all ten connections in the pool to be able to work at the same time, you’d have to increase the number of threads accordingly.

The environment variable UV_THREADPOOL_SIZE can be used to adjust the size of the thread pool. The value of UV_THREADPOOL_SIZE can be set before running the Node.js app or from within, but it must be set before the first call that uses the thread pool is made. This is because the thread pool is created when it’s first used and once created, its size is fixed. Open the index.js file in the root of the application and add the following lines after the first line (which brings in the web server module).

// *** line that requires services/web-server.js is here ***
const dbConfig = require('./config/database.js');
const defaultThreadPoolSize = 4;

// Increase thread pool size by poolMax
process.env.UV_THREADPOOL_SIZE = dbConfig.hrPool.poolMax + defaultThreadPoolSize;

Now that the thread pool is sized appropriately, you can move on to the database module. Create a new file in the services directory named database.js. Copy and paste the following code into it and save your changes.

const oracledb = require('oracledb');
const dbConfig = require('../config/database.js');

async function initialize() {
  const pool = await oracledb.createPool(dbConfig.hrPool);
}

module.exports.initialize = initialize;

This module first brings in node-oracledb and the configuration file. Next, an async function named initialize is defined and later exposed via the module.exports object. The initialize function creates a connection pool which is stored in an internal connection pool cache as the “default” pool.

Now you need to wire things up so that the connection pool is started before opening the web server. Return to the index.js file and add the following line below line 1.

//  *** line that requires services/web-server.js is here ***
const database = require('./services/database.js');

Then, add the following try block within the startup function just before the existing try block that starts the web server.

  try {
    console.log('Initializing database module');

    await database.initialize(); 
  } catch (err) {
    console.error(err);

    process.exit(1); // Non-zero failure code
  }

  // *** existing try block in startup here ***

At this point, you can install node-oracledb and test the code so far. Run the following commands in the terminal from the hr_app directory.

npm install oracledb -s
node .

If you see the messages indicating that the database module and the web server started up then, congratulations — you now have a connection pool running! I’ll show you that it’s working in the last part of this post, but before that, you need to add some code to keep the application shutting down cleanly.

Shutting Down the Connection Pool

If you shut down the application now (using ctrl + c as before), the Node.js process will be killed before the connection pool is closed. While all the related database processes should be cleaned up automatically, it’s best to explicitly close the connection pool before exiting the Node.js process.

Return to the services/database.js file, add the following lines of code to the end, and then save your updates.

// *** previous code above this line ***

async function close() {
  await oracledb.getPool().close();
}

module.exports.close = close;

The close function uses the oracledb.getPool() method to synchronously retrieve the default pool and then invokes the close method on the pool to close it.

To invoke the close function at the right time, add the following lines of code to the index.js file inside the shutdown function, just after the existing try block that stops the web server.

  // *** existing try-catch block in shutdown here ***

  try {
    console.log('Closing database module');

    await database.close(); 
  } catch (err) {
    console.log('Encountered error', e);

    err = err || e;
  }

If you rerun and shut down the application again, you should see that the database module closes after the web server closes, but before the process exits.

Simplifying Simple CRUD Operations

Executing SQL or PL/SQL code with node-oracledb is typically a three-step process: get a connection, execute the code, then release the connection. If all you want to do is a single call to execute (no multi-step transaction needed), then getting and releasing a connection can feel like boilerplate code. I like to create a function that does all three operations with a single call. Return to the services/database.js file, add the following code to the bottom, then save your changes.

// *** previous code above this line ***

function simpleExecute(statement, binds = [], opts = {}) {
  return new Promise(async (resolve, reject) => {
    let conn;

    opts.outFormat = oracledb.OBJECT;
    opts.autoCommit = true;

    try {
      conn = await oracledb.getConnection();

      const result = await conn.execute(statement, binds, opts);

      resolve(result);
    } catch (err) {
      reject(err);
    } finally {
      if (conn) { // conn assignment worked, need to close
        try {
          await conn.close();
        } catch (err) {
          console.log(err);
        }
      }
    }
  });
}

module.exports.simpleExecute = simpleExecute;

Typically, you wouldn’t use the database module in the web server module, but you’ll add it now just to ensure it’s working correctly. Open the services/web-server.js file and add the following line under the existing constant declarations at the top.

// line that requires ../config/web-server.js here
const database = require('./database.js');

Next, use the following code to replace the entire app.get handler that responds with “Hello, World!” (all three lines).

    // *** line that adds morgan to app here ***

    app.get('/', async (req, res) => {
      const result = await database.simpleExecute('select user, systimestamp from dual');
      const user = result.rows[0].USER;
      const date = result.rows[0].SYSTIMESTAMP;

      res.end(`DB user: ${user}\nDate: ${date}`);
    });

The new handler is using the database module’s simpleExecute function to fetch the current user and systimestamp values from the database. The values are then used in a template literal to respond to the client with a dynamic message.

Start the application again and navigate Firefox to localhost:3000. You should see something like the following image.

If you see a message like that, then your database module is in good shape. In the next post, you will continue to build out the API by adding routing, controller, and database logic for a GET request.