I have a question about how to set up a database with sensor data. This question is probably asked a million times and the answer is probably easy. But this is the first time I have done this.
The goal of this project:
This project is started to get more insight of our pond at home. I would like to store the temperature, pressure of the pomp, when a certain filter is active. And in the near future there will probable more sensor. The “stored” data is eventually showed in a nice webpage. Also other sensor based on a ESP32 could be used in this project later on. And a ESP32 could also be used to show some details of a sensor on a small screen.
The hardware side of this project is not a real issue for me and the first prototype is already running. And the data for all the sensors is stored into a text file. But I would like to make the following:
And here is where the “problem” start. I found on the internet that there are a lot of options to handle this issue. But which one the most suitable for my project.
Possible solutions I found:
I found that the MQTT protocol and the mySQL are often used for this kind of application. But I also read that the MQTT protocol cannot store the data directly to the mySQL database. So, is it not easier to store the data from the sensors directly to the mySQL database?
The following packages I found that could be used for this project:
On the NAS
- MariaDB 10
- node-red (to make a nice dashboard for the data)
Is what I had in mind feasible for this kind of project. To make a SQL database with MariaDB and store all the sensor data on my NAS. And use that data in node-red to make a nice web page for it.
Or are there better options that I could use for this kind of application?