Creating a REST API: Database Basics

By March 29, 2018 Uncategorized

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.

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 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 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 named database.js in the config directory. Copy and paste the following code into the file and save your changes.

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.

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 10 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).

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.

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 starts before the web server opens. Return to the index.js file and add the following line below line 1.

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

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.

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 up 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.

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.

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 3-step process: get a connection, execute the code, then release the connection. If all you want to do is make 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.

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.

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

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.

19 Comments

  • Saran says:

    That’s a wonderful article.!

  • Ken says:

    I really appreciate how you structure the service and your explanations that support it. I’m going to the GET post next and am anticipating the full CRUD. I hope you’re going to have authentications as well. Thanks for the great work!

  • YOUB says:

    Hello Dan,
    All my respect and appreciation from morocco.
    Thanks for the great work!
    Best regards,
    YOUB AYOUB.

  • mr T says:

    thank you

  • Satya says:

    Hi Dan,

    Thanks for the great post.
    One question…. why did we create connection directly from *******conn = await oracledb.getConnection();*************
    We already have the pool created correct? is there any specific reason?

  • Sebi says:

    Thank you for this series! It is of such help!

    There’s a typo in the config part, in config/database.js you specify the connection string with this name:
    connectString: process.env.HR_CONNECTIONSTRING,

    But then when adding it to the bashrc, you add it like this:
    echo “export HR_CONNECTSTRING=0.0.0.0/orcl” >> ~/.bashrc

  • Phil says:

    I agree with all the other comments: this tutorial is fantastic, and helped me get up and running with node-oracledb in no time. Great work, and thanks for sharing!

    Regards,
    Phil

  • Hari says:

    Thanks for the excellent post. Regards, Hari

  • Kevin Curran says:

    Great tute! One question:

    “When apps use many connections for short periods of time, Oracle recommends using a connection pool.”

    How many is “many?” Hundreds, thousands, millions?

    • danmcghan says:

      Hi Kevin,

      Thanks for your question! The term “many” is somewhat subjective (depending on your number of CPUs, memory, etc.). It’s probably better to focus on the style of application, or how the application makes use of connections, as that’s more concrete. If the application is a job that’s scheduled to run every minute, then it’s likely a good candidate for dedicated connections (or just one connection for the duration of the job). However, if you have an application supporting many users then you may ask: does each user need its own dedicated connection?

      In the client/server days this was often how things worked and that model does have it’s advantages (pessimistic row locking, scrollable cursors, etc.). However, in the web world, we’re often trying to scale to many more users than was often the case with client/server. Because each connection requires its own resources (process, memory, etc.), a one-to-one mapping of users to connections doesn’t work without ever-larger servers.

      If your application doesn’t need the advantages that come with persistent connections (many apps do not) then a connection pool starts to make a lot of sense as a smaller set of resources can be shared by a larger group of users.

  • Sunil says:

    Really great work Danmcghan. Such an organized tutorial. Though i am a database guy, i can clearly understand the explanations. Thanks a lot for this.

  • Harold Tucker says:

    YOU ARE THE GREATEST!!! Thanks so much for this

Leave a Reply