Build REST APIs for Node.js, Part 2
Add database connection pooling to your new REST API.
By Dan McGhan
In the previous article in this series, you started a new REST API app and created a web server module to handle incoming HTTP requests. In this article, you’ll focus on database basics by adding 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.
Note: The instructions and steps in this article assume that you have completed the steps in Part 1 in this article series.
Starting Up the Connection Pool
Generally speaking, there’s some overhead involved when establishing a connection to a database. When apps use many connections for short periods of time, Oracle recommends using a connection pool. Connection pools can dramatically increase performance and scalability by creating groups of connections that stay open and are reused many times.
Because node-oracledb is built on top of the Oracle Call Interface (OCI) client libraries, it has built-in support for creating OCI pools, which work on the client side and have excellent performance characteristics.
To create a connection pool, start by creating a new configuration file named 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 with the config/webserver.js file (in Part 1 of this series), this file enables 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 that 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.
Although 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 four threads. If you want all 10 connections in the pool to be able to work at the same time, you must increase the number of threads accordingly.
The environment variable UV_THREADPOOL_SIZE adjusts the size of the thread pool. The value of UV_THREADPOOL_SIZE can be set before the Node.js app runs or from within the app, but it must be set before the app makes the first call that uses the thread pool. 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. In the services directory, create a new file 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 that is stored in an internal connection pool cache as the “default” pool.
Now you need to wire things up so that the connection pool starts before the web server opens. Return to the index.js file, and add the following below line 1.
// *** line that requires services/web-server.js is here ***
const database = require('./services/database.js');
And now 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 messages indicating that the database module and the web server started up, congratulations—you now have a connection pool running! I’ll show you that it’s working in the last part of this article, but before that, you need to add some code to make the application shut down cleanly.
Shutting Down the Connection Pool
If you shut down the application now (using Ctrl + c as in Part 1), the Node.js process will be killed before the connection pool is closed. Although all of 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 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 connection 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, and release the connection. If all you want to do is make a single call to execute (no multistep transaction needed), getting and releasing a connection can feel like using 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, and 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 that 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 Figure 1. If you see a message like the one in Figure 1, your database module is in good shape.
Figure 1: Database module in good shape
In the next article, you will continue to build out the API by adding routing, controller, and database logic for a GET request.Next Steps
READ Part 1 in this article series.
LEARN more about JavaScript and Oracle.
GET more about this article’s code from GitHub.
DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.