From Legacy to Legendary: Revitalizing a Node-RED Oracle Plugin
Maintaining an open-source project is a rewarding journey. You inherit a foundation, a community, and a codebase with its own history. Recently, I took on the task of modernizing node-red-contrib-oracledb-mod, a popular Node-RED plugin for connecting to Oracle Database. The goal was simple but ambitious: transform it from a functional but aging tool into a modern, resilient, and developer-friendly powerhouse.
This is the story of that transformation—a deep dive into the challenges faced, the solutions discovered, and the lessons learned in bringing a legacy project up to today's standards.
The Starting Point: A Solid but Dated Foundation
The original plugin worked. It connected to Oracle and executed queries. However, it was built on older patterns and tooling:
- A Monolithic Build Process: A single gulpfile.js and tsconfig.json handled everything, bundling frontend and backend code in ways that led to subtle, hard-to-debug conflicts.
- Callback-Driven Logic: The backend was written using the classic Node.js callback pattern, which often resulted in nested code (the "pyramid of doom") and complex error handling.
- Basic Connection Handling: The connection logic was simple, creating and closing a connection for each query. This worked for simple cases but struggled with network interruptions, firewall timeouts, and efficient resource use.
The mission was clear: address these core issues to build a foundation for the future.
Challenge #1: Taming the Brittle Build Process
The first sign of trouble appeared when trying to introduce modern linting and code analysis. The build process was so intertwined that changes in one area would cause unexpected failures in another. We kept hitting a critical Node-RED error: type_already_registered.
The Cause: The Gulp build script was concatenating the compiled backend Node.js code inside the final frontend .html file. Since Node-RED loads both the .js and .html files from the lib directory, it was loading and trying to register our nodes twice, causing it to crash on startup.
The Solution: Isolate and Conquer
We completely rebuilt the development pipeline from the ground up.
- Separate tsconfig.json files: We created two distinct configurations: one for the modern Node.js backend and another for the editor frontend, which requires older JavaScript for browser compatibility.
- A Robust gulpfile.js: The Gulp file was rewritten with a clean, parallel pipeline. It now has separate, isolated tasks for compiling the backend, compiling the frontend, and then assembling the final lib directory.
This separation immediately solved the registration errors and made the build process predictable and reliable.
// A simplified view of the new, robust Gulp build series
const buildLib = series(
compile, // Runs backend and frontend compilation in parallel
parallel(buildHtml, buildJs) // Assembles the final lib directory
);Challenge #2: The async/await Revolution
With the build process stabilized, we turned to the code itself. The old callback-based logic was difficult to read and even harder to debug.
Before (Callback-based connection):
oracledb.createPool(config, function (err, pool) {
if (err) {
node.error("Error creating pool: " + err.message);
// ... more error handling
} else {
node.pool = pool;
node.log("Pool created successfully.");
// ... more nested logic
}
});The Solution: Embrace Modern JavaScript
We refactored the entire backend to use async/await. This was the single most impactful change for code quality.
After (async/await):
try {
node.pool = await oracledb.createPool(poolConfig);
node.status.emit("connected");
node.log(`Oracle connection pool created for ${connectString}`);
} catch (err) {
node.error(`Error creating pool for ${connectString}: ${err.message}`);
node.status.emit("error", err);
}The benefits were immediate:
- Readability: The code now reads like synchronous, sequential instructions.
- Error Handling: We can use standard try...catch blocks, which is cleaner and more powerful.
- Robustness: The logic is simpler and less prone to unhandled exceptions.
Challenge #3: The Red Triangle of Doom and the Seamless Upgrade
After implementing a resilient connection pool, we released version 0.7.0. Then, the bug reports came in. Users upgrading from older versions saw their oracle-server configuration nodes marked with a red triangle, labeled "invalid properties."
The Cause: The old version had a reconnecttimeout property that saved its value as a string (e.g., "5000"). Our new version's definition expected a number. This type mismatch caused Node-RED to flag the existing user configurations as invalid.
The Solution: A Backward-Compatible Definition
The fix was surprisingly simple but required a deep understanding of how Node-RED handles upgrades. We had to make the new node definition aware of the old, obsolete properties and their types.
In src/html/oracle-server.ts, we added the old properties back into the defaults object, but with a lenient validator that accepted the old string-based values.
// In the node's defaults object:
defaults: {
// ... all the new pool properties ...
// --- Kept for backward compatibility ---
reconnect: { value: true },
reconnecttimeout: { value: 5000, validate: function(v) {
// This validator accepts the old string values, making upgrades seamless
return v === "" || v === null || (typeof v === "string" && v.match(/^\d+$/));
}},
}This elegant solution ensures that when users upgrade, Node-RED sees their old configuration as valid. The red triangles vanish, and their flows continue to work without any manual intervention.
The Result: A Modern, Stable, and User-Friendly Plugin
After tackling these challenges, node-red-contrib-oracledb-mod is a completely revitalized tool. The key improvements include:
- Resilient Connection Pooling: The #1 feature, eliminating a whole class of connection-related errors.
- Modern Internals: A clean, maintainable, and robust async/await codebase.
- Stable Build Process: A reliable and isolated development pipeline.
- Seamless Upgrades: A carefully crafted upgrade path that respects existing user configurations.
- Vastly Improved Documentation and UI: The node is now easier to understand and use than ever before.
Looking Ahead: The Path to Perfection
While version 0.7.3 is a huge leap forward, the journey isn't over. Like many open-source maintainers, I balance this project with a day job and the wonderful chaos of keeping up with a toddler's schedule. Time is a precious resource, but I'm committed to making this the best Oracle node for the Node-RED community.
Here are some of the improvements I'm planning for future releases:
- Dynamic "IN" Clause Binding: A common Oracle challenge is binding an array of values to a query like ... WHERE id IN (:my_array). This currently requires complex workarounds. I plan to add first-class support to automatically handle this, making a very common use case trivial.
- Enhanced TypeScript Typing: While the project now uses TypeScript, we can improve the internal type safety, especially around the Node-RED and oracledb library APIs. This would make future development even more robust and help catch bugs before they happen.
- Comprehensive Test Suite: The current tests are good for basic validation, but I want to build a full suite of automated integration tests that run against a real database in a CI/CD environment. This will provide ultimate confidence in every new release.
- More Built-in Examples: The stored procedure example has been well-received. I'd like to add more, covering common patterns like master-detail queries, bulk inserts, and more.
A Final Note
This project is a labor of love, and every bug report, feature request, and "thank you" from the community is a powerful motivator. Revitalizing this plugin has been a fantastic learning experience, and I'm excited to see where we can take it next.
Find the project on GitHub, install it from the Node-RED Flow Library, and check it out on NPM. Your feedback is always welcome!
Importable Example: Calling a Stored Procedure
Here is an example flow you can copy and import directly into Node-RED to see how to call a stored procedure with both input and output parameters.
[
{
"id": "1a2b3c4d.5e6f78",
"type": "inject",
"z": "5a6b7c8d.9e0f12",
"name": "Trigger Stored Procedure",
"props": [
{
"p": "payload"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "{\"employeeId\":101}",
"payloadType": "json",
"x": 200,
"y": 280,
"wires": [
[
"9d8c7b6a.5f4e32"
]
]
},
{
"id": "9d8c7b6a.5f4e32",
"type": "function",
"z": "5a6b7c8d.9e0f12",
"name": "Prepare msg.bindVars",
"func": "// Define the PL/SQL block to execute\nmsg.query = \"BEGIN get_employee_name(:emp_id, :emp_name); END;\";\n\n// Define the bind variables for the procedure\nmsg.bindVars = {\n emp_id: { dir: \"BIND_IN\", val: msg.payload.employeeId, type: \"NUMBER\" },\n emp_name: { dir: \"BIND_OUT\", type: \"STRING\", maxSize: 2000 }\n};\n\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 430,
"y": 280,
"wires": [
[
"e5f4d3c2.1a0b1c"
]
]
},
{
"id": "e5f4d3c2.1a0b1c",
"type": "oracledb",
"z": "5a6b7c8d.9e0f12",
"server": "",
"name": "Execute Procedure",
"usequery": false,
"query": "",
"usemappings": false,
"mappings": "[]",
"resultaction": "single-meta",
"resultlimit": "100",
"x": 650,
"y": 280,
"wires": [
[
"f1e2d3c4.b5a678"
]
]
},
{
"id": "f1e2d3c4.b5a678",
"type": "debug",
"z": "5a6b7c8d.9e0f12",
"name": "Display OUT Parameter",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload.outBinds.emp_name",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 880,
"y": 280,
"wires": []
}
]
