I'm a student and I'm currently working on a project where I need to create an API to manage electrical consumption data. Here's the context:
Data Reading:
I retrieve electrical consumption data using Modbus TCP/IP with Node-RED. These data are requested via a gateway that queries the sensors.
I have successfully retrieved these data in hexadecimal format and converted them to float values.
Data Writing:
I want to store these data in a MySQL database accessible via phpMyAdmin.
API Functions:
Write function: Store the electrical consumption data in the database.
Read function: Retrieve these data to display on Grafana or a user interface that I will develop later.
My problem is that I have no idea how to design my API from scratch to accomplish these tasks.
What I have achieved so far:
Retrieved data in hexadecimal format and converted them to float with Node-RED.
What I need:
Advice and examples on creating a REST API with Node-RED to:
Read and write data to a MySQL database.
Retrieve these data later for display.
Final goal:
Display electrical consumption graphs on Grafana or another user interface.
You don't need the Apache/php stuff at all. Node-RED can do all of that for you. Though you might want a simple reverse proxy instead if you intend additional security and performance for lots of clients.
Also, if the database needs to store time-series data, MySQL is not the best approach. It can do it but something like InfluxDB would be better and is even easier to integrate with Grafana.
Really thank you for your response @Colin and @TotallyInformation . Otherwise, the network architecture was imposed on me in a way because the client does not want the database to be requested directly for security reasons.
Yes, that is perfectly reasonable in a production environment. Node-RED can easily provide the API of course. And Grafana can actually use JSON and can request that via a REST API which Node-RED can easily provide.
maybe it's because the task has been described with this kind of requirement to the OP by the teacher? @Khad is this a learner's exercise of some sort?
@hominidae At the beginning, I could use grafana for a basic visualization but what is required is to design an advanced user interface independent of node red and grafana
I would still see if you could weave in a timeseries db even if the customer needs the data in MySQL as well since that will be easier to work with in Grafana and generally makes time-related summaries easier. e.g. if you have sensor data coming in every minute but you want to report by hour/day/week/month, etc, a timeseries db is likely to be able to do that for you efficiently and with minimal code, doing it using SQL tables and programming will be a lot harder and probably less efficient in terms of resources.
I would also advise dropping Apache and PHP if you can. Apache is an old heavyweight, and if you are only really using it to deliver PHP, Node-RED can do all of the delivery via its built-in ExpressJS web servers. A more efficient reverse proxy such as NGINX or HAPROXY would give increased efficiency for the delivery of the Node-RED web endpoints and would give options for offloading things like TLS encryption and authentication to more efficient tooling.
On the Node-RED side, the use of UIBUILDER with Node-RED would replace PHP. Giving you custom data-driven web interfaces that use modern web design instead of the older PHP tech.
