Run IoT Backend with a Free Oracle 23c DB on OCI using Node-Red and Mosquitto server

In the fast-paced era of the Internet of Things (IoT), where connected devices and systems are revolutionizing industries, the need for efficient and robust backend solutions has become paramount. Enter Node-RED, an open-source flow-based programming tool that empowers developers to create, deploy, and manage IoT applications with ease. Paired with a Free Oracle 23c DB, an enterprise-grade database solution, this dynamic duo offers an unparalleled combination of flexibility, scalability, and reliability for building and running IoT backends. In this article, we will explore the exciting possibilities that arise when Node-RED and Oracle 23c DB join forces, revolutionizing the way we handle and process IoT data.

In this article I’ll go on how to provision Oracle 23c db (free edition), install node-red and install Mosquitto server on an linux machine hosted on OCI.

Data Flow Diagram

And to make it all just a bit more fun I’m using my seeed WIO terminal to flood it with real life data (temperature, humidity, sound and vibration) to see how far it’d go before breaking (took it up to 10 readings per second and it was as steady as could be).

node-red inserting into Oracle DB

I’ve published the Github repo that automates the provisioning of the environment as well as installing the dependencies which you can find here.

It does the following:

I. Provision the infrastructure: ( terraform file )

1. Creates a VCN, Subnets and opens up the required ports
2. Provision a OCI compute instance with Oracle Linux as the operating system

II. Install the software — if you like you can use that on a machine you’ve provisioned manually

1. Install Oracle Database 23 free on the instance
2. Create a schema & table for node-red and provide the schema user with the required privileges. (createuser.sql)
3. Install node-red dependencies (node, npm…etc)
4. Install Node-red
5. Install Mosquitto server
6. Update the sfw rules to allow these ports to be accessed externally
7. Install the Oracle client and the required node-red oracle database plugins “node-red-contrib-oracledb-mod”
8. Deploy a node-red flow that subscribes to a topic on mosquitto and put the data into the database. (node-red flow)

Once everything is up and running (takes around 15 minutes) you’ll need to add in the user name and password into node-red to connect to the database.

1. Log into node red <Machine IP>:1880
2. double click on the oracledb node, then edit the server

Edit the server

3. Go to the security tab and copy the user name and password

Thats it now you’ve got an IoT backend that can handle some serious load.

Future Work

1. Use Jmeter to push the system to its breaking point
2. In my next entry I’m going to show you how to replace 23c with autonomous and add autoscalling to the application layer.

PLEASE CHANGE YOUR PASSWORDS and secure the system once it is up and running, for ease of deployment I designed it to be wide open, you’ll need to install https certificates and enable authentication on both Mosquitto and Node-Red

The optimum architecture should look something like this :

Deployment Diagram