Desktop applications with node-oracledb and electron

Published in

Oracle Developers

7 min readDec 4, 2023

In conference sessions this year I showed a desktop application that connects to Oracle Database. It was built using HTML, Node.js, the node-oracledb driver, and electron. Via the power of electron’s ecosystem, it is easy to package Node.js, the driver and the application into MSIs for Windows, DMGs for macOS, and RPMs for Linux. These can be shared with your user base and do not need additional software (e.g. Oracle Instant Client) installed.

The demo code is at https://github.com/cjbj/oracle-electron.

The demo is a one-screen application that allows a database connection to be created and then a query to be executed.

Screen shot of the running application showing input fields for a username, password and database connection string. A button can be clicked to log in to the database. A second button can be clicked to query the current date of the database. This date is shown in the application.

I used this app to show how easy it is to create and package an application. Along the way I showed how connections can be managed, and how interaction between the application front end and the database driver code executing SQL statements could be handled in electron.

Connection Management

Let me talk about connection management first.

Notice that the app creates a connection pool. Since this app is a single-user program, it can appear strange to use a pool because only one connection is needed.

The alternatives to a pool that could have been considered are:

Using a standalone connection that is opened when the application starts, and closed when the application exits. This would mean that an idle application holds onto database resources even if the user is not interacting with the application. Also, if the connection because unusable in the period, perhaps because of a firewall timeout, the application might return an error when the timestamp query is execute.
Using a standalone connection that is opened and then closed after each database query is executed. This would add extra load to the database due to the costs of creating and tearing down the connection resources. It will be slow and will impact application performance.

The chosen pool implementation creates a connection pool at application startup, and then acquires and releases a single connection to the pool each time the date query is executed. This has benefits:

The pool keeps a connection open and immediately available when the application is in frequent use (i.e. the user keeps clicking the ‘Query the current date’ button). Getting the already opened connection from the application-side connection pool is fast.
Each time the connection is acquired from the pool to do a query, the pool performs some liveness checks on the connection and internally reconnects if the original connection is unusable. The app logic doesn’t need to be aware of this: it is simply given a usable connection each time it asks for a connection.
The pool shrinks and automatically closes the connection when the connection has been idle and unused for a specified length of time. This frees up database resources for other database applications.
The pool automatically opens a new connection when the application becomes active, again without the application logic needing to know that the connection had ever been closed.

Running the Demo

To run the demo, clone the repository and then install dependencies with:

npm install

The app can be run in development mode with:

npm start

Starting the app opens up a window like the screenshot shown earlier.

The window has fields for database credentials.
– Enter your credentials and click ‘Create connection pool’.
– The window title bar changes to show the current credentials. The. node-oracledb module creates the connection pool in the background. If your credentials are incorrect, you will only get an error when you later try to use a connection from the pool.
The lower part of the application window has a ‘Query the current time’ button.
– Click this button to query the database and display the current time.
– Note the console log output shows that a new connection was opened.

$ npm start

> oracledb-electron-demo@1.0.0 start
> electron .

node-oracledb: 2023-12-04T00:10:25.856Z: connection pool created
node-oracledb: 2023-12-04T00:10:28.460Z: new connection created in the pool

Repeatedly clicking ‘Query the current time’ will update the displayed time.
If you wait for more than 5 seconds and then click again, the page time will get updated but you will notice that the console log output shows a new connection was created. Oracle provides a hook that is called when new connections are created but doesn’t provide a similar hook to that can be used to log when the original connection was closed. The fact that a new connection was opened is the proof that the pool’s connection idle timeout threshold had been reached and the original connection was closed when the application wasn’t being used. The console output below shows the log when I had not interacted with the app for about 14 seconds and clicked the query button:

$ npm start

> oracledb-electron-demo@1.0.0 start
> electron .

node-oracledb: 2023-12-04T00:10:25.856Z: connection pool created
node-oracledb: 2023-12-04T00:10:28.460Z: new connection created in the pool
node-oracledb: 2023-12-04T00:10:42.553Z: new connection created in the pool

Application Architecture

The application page layout is simply created in HTML, see index.html:

  <h1>node-oracledb Electron Oracle Database Demo</h1>

  <h3>Database Credentials</h3>

    <p>
    Username: <input id="un" value="cj" />
    Password: <input type="password" id="pw" value="cj" />
    Connect String: <input id="cs" value="localhost/freepdb1" />
    </p>
    <p>
    <button class="button" type="button" id="logon">Create connection pool</button>
    </p>

  <h3>Date</h3>

    <p>
    The date is: <strong id="dateid">???</strong>
    </p>
    <p>
    <button class="button" type="button" id="datebutton">Query the current date</button>
    </p>

The file main.js starts the window of a specified size:

  const mainWindow = new BrowserWindow({
    width: 1000,
    height: 500,
    webPreferences: {
      defaultFontFamily: {standard:'Arial'},
      preload: path.join(__dirname, 'src/preload.js')
    }
  });

It also sets up the IPC channels between the front end, and the node-oracledb logic which is in src/myoracle.js. I found the electron documentation and examples invaluable to work out the IPC usage. I won’t try to replicate their descriptions here.

The connection pool createPool() call in in myoracle.js is configured with these parameters:

  await oracledb.createPool({
    user: un,
    password: pw,
    connectString: cs,
    sessionCallback: initSession,
    connectTimeout: 3,  // only wait this long if e.g. the network is unusable
    poolTimeout: 5      // balance the cost of connection recreation vs keeping it open
                        // (N.b: artificially low for demo purposes)
    });

The connectTimeout setting is an upper bound on how long to wait for a connection to be established between the pool and the database.

The poolTimeout value has been set artificially low to 5 seconds to show how the connections can be closed when the app is idle.

The sessionCallback function is simply used to log when a new connection is created in the pool:

function initSession(connection, requestedTag, callbackFn) {
  console.log(`node-oracledb: ${new Date().toISOString()}: new connection created in the pool`);
  callbackFn();
}

I didn’t set pool size parameters: the default values, coupled with the app being single-user means that only the desired one connection will be created.

Refer to the createPool() documentation for option descriptions.

Packaging the Application

The application’s package.json file installs electron-builder and has script targets to build packages for various platforms.

On macOS

Simply run:

npm run packmac

This creates a universal DMG package release/OracleDBExampleApp-1.0.0-universal.dmg for distribution and installation.

On Linux

First install the rpmbuild program:

sudo dnf install rpm-build

and then run

npm run packlinux

This creates an RPM in release/oracledb-electron-demo-1.0.0.x86_64.rpm

Files in this package can be shown with

rpm -qlp release/*rpm

Install the package with:

sudo dnf install -y release/oracledb-electron-demo-1.0.0.x86_64.rpm

Run the application by searching for it in the installed applications, or run it directly from a terminal window:

/opt/OracleDBExampleApp/oracledb-electron-demo

When finished, the package can be removed with:

sudo dnf remove -y oracledb-electron-demo

On Windows

To create an MSI on Windows, run:

npm run packwin

This creates the MSI in the release directory.

Database Resident Connection Pooling (DRCP)

Not shown in this application is the use of DRCP which is a pooling solution on the database tier. DRCP is useful when the database server is under memory pressure due to the number of application connections. Because it provides a pool of already-running database server processes, it also has some benefits for applications that need to connect quickly even when there is no memory shortage.

For the electron app this means that DRCP is an even better solution than shown in the demo code, because the initial connection and the reconnection-after-idle costs will be minimized.

To use DRCP, add a line to myoracle.js like:

oracledb.connectionClass = 'MYDEMO'

See the oracledb.connectionClass documentation.

Remove the poolTimeout setting, or better still, set it 0 since resource usage is controlled by the DRCP configuration and there is little point in shrinking the application connection pool.

Also use a connection string that requests a DRCP server process. All this is explained in my earlier blog post Oracle Database DRCP and Node.js: How to enable and use it.

Conclusion

Electron and the node-oracledb driver make it easy to create desktop applications. The electron-builder module makes it trivial to package those applications for distribution without needing users to install additional software.

The use of an application-side connection pool in node-oracledb, with or without DRCP in the database, provides reliability and allows efficient use of database resources for single-user applications that may have extended periods of idleness.

If you are writing multi-user applications you may also be interested in my blog post Always Use Connection Pools — and How.