Save temperature data to local database

[RichPiano]

Hi guys

I want to create a database on my ESP32 where I collect all incoming information about temperature, humidity, ... etc. It should then send the information to a webserver when a connection is established. How would you go about it?

[tommeyers]

I would run nodered on my server. Easy to use.

Or MQTT.

I have used both.

Or a service: many choices available.

Tom

[RichPiano]

Very interesting, thank you!

That leaves me with a question though: Can node red also be run on esp32? Because I need the database to operate ON my esp32.

[PeterR]

mqtt or nodered are buzz words. I could add ajax, cgi, json or any number of tla/flas.

Breaking your problem down:
1) You are without upstream connection and so must save data ready for upload. How much data? how long are you disconnected? Do you mind if you get switched off and loose data before upload?
2) Approach depends on above. Basically you will need to buffer data. RAM is easy if you dont have much data & power interruption is not an issue. Else you need local non volatile storage. NVS adds more complexity. NVS is only good for so many write cycle. Wear leveling helps (it says so on the tin) but will only go so far. 'So far' depends on how much storage and how much data/how often you save - the duty cycle as well as the NVS device technology (read cost).
Or you could just not worry and brick your device after a year.
3) Upload. That is easier. There are lots of IoT solutions out there. I think the stock ESP approach is AWS so follow the worked examples!
Nodered may be better but you need an example to run with. IDF has AWS examples.
Again server side depends on what you want to achieve. Manage your own server in front of your one client. Hmmm, best have a good backup/redundancy. Or rent. Rolling your own is cheaper until you fail. Oops, then hunt the backup pen drive which your kids have erased and downloaded 'Expanse' onto. EDIT: Not that that is not a bad box set to find on your backup drive, just your client may already have watched.
PS
MQTT is a good protocol which allows you to upload data to your server. MQTT has a concept of quality of service which in essence means does MQTT forget your data when faced with a problem? AFAIK ESP MQTT client will buffer in RAM but once that is used data will be lost. So your requirements matter.
PPS You do not need a 'database'. An array might work just fine. If you need NVS then a file works sort off except for wear issues.

[RichPiano]

Thanks you really helped me out there! I think I will go for arrays to store data, use the flash memory and MQTT for submiting the data to the server. If that very basic setup doesn't hold, I will opt for something else. But I don't want to unnecessary overcomplicate it at first

Thanks again! I will check back if another situation arises.

[nvannote]

Thanks you really helped me out there! I think I will go for arrays to store data, use the flash memory and MQTT for submiting the data to the server. If that very basic setup doesn't hold, I will opt for something else. But I don't want to unnecessary overcomplicate it at first

Thanks again! I will check back if another situation arises.

Exactly, especially if this is a “one-off”. I would just wire in a storage card, open a file and start appending structs to it via. C or posix I/O (It’s all there). Simple and reliable, and if the SD card goes bad after a number of years, just toss it. A “database” is way over the top for what sounds like a sequential overflow queue.

Regards

[PeterR]

For a one off home 'burner' you should be ok although I have a thing about waste.
To put in context (heat & manufacturer detail excluded) then the spreadsheet says that 4KB/s with one write every second might give you 6 years on a consumer 8GB using basic FAT. Most commercial cards will not quote write cycles (& for good reason) but will say something like '5 years with normal use' wtf that means. Well 4KB/s is more than most normal use IMHO so I figure you won't get 6 years on commercial - the card cannot be achieving even 300 per sector. Check my figures, easy to loose a 0

The cheapest card with promised write life (3000) will cost you around $30 for 8GB. That gives you 60 years write endurance @4KB/s but promised, you can sue them after 50 years!

Also remember that writing frequently exposes you to power fail issues. FAT is not your friend. Perhaps you remember having to properly shut down your PC to avoid corruption. Try an embedded FLASH FAT system if feeling nostalgic This is not an ESP issue, it is the very nature of FAT.
EDIT: So maybe raw might be better for you. You can recover from power fail corruption but would loose data, dual partitions and buffering aside.

[nvannote]

EDIT: So maybe raw might be better for you. You can recover from power fail corruption but would loose data, dual partitions and buffering aside.

Yes, NVS has its own issues, but as you concluded fairly easy to "reasonably" recover from (IF it were to actually fail). I sill prefer an NVS solution (insert your preferred medium there) because the original requirements were to "collect information" and "should then send the information to a webserver when a connection is established".

Well, one can only assume the MQTT broker might not be available if the "webserver" is not available, because the entire network is not available. At which point you're back to RAM, which is finite.

Regards

[PeterR]

Cool, happy to help.
Just keep an eye on you're write strategy, unless you are happy to brick!

[RichPiano]

Hi there, that are some advanced thoughts, for my part i'm happy when the system is running in the simplest possible configuration. I will think about updating it later.

Anyway, I wanted to ask if it is possible to hook up the ESP32 to more than one network using its wifi capabilities? However, I'm strongly guessing that this would be an issue, as I don't even know how to do it with a computer..