Save temperature data to local database

RichPiano
Posts: 123
Joined: Mon May 18, 2020 2:51 pm

Save temperature data to local database

Postby RichPiano » Mon May 18, 2020 2:57 pm

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
Posts: 184
Joined: Tue Apr 17, 2018 1:51 pm
Location: Santiago, Dominican Republic

Re: Save temperature data to local database

Postby tommeyers » Mon May 18, 2020 11:12 pm

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

Or MQTT.

I have used both.

Or a service: many choices available.

Tom
IT Professional, Maker
Santiago, Dominican Republic

RichPiano
Posts: 123
Joined: Mon May 18, 2020 2:51 pm

Re: Save temperature data to local database

Postby RichPiano » Wed May 20, 2020 9:16 am

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
Posts: 621
Joined: Mon Jun 04, 2018 2:47 pm

Re: Save temperature data to local database

Postby PeterR » Thu May 21, 2020 12:33 am

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.
& I also believe that IDF CAN should be fixed.

RichPiano
Posts: 123
Joined: Mon May 18, 2020 2:51 pm

Re: Save temperature data to local database

Postby RichPiano » Fri May 22, 2020 3:08 pm

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
Posts: 51
Joined: Thu Nov 14, 2019 10:42 pm

Re: Save temperature data to local database

Postby nvannote » Fri May 22, 2020 11:48 pm

RichPiano wrote:
Fri May 22, 2020 3:08 pm
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
Posts: 621
Joined: Mon Jun 04, 2018 2:47 pm

Re: Save temperature data to local database

Postby PeterR » Sat May 23, 2020 1:00 am

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.
& I also believe that IDF CAN should be fixed.

nvannote
Posts: 51
Joined: Thu Nov 14, 2019 10:42 pm

Re: Save temperature data to local database

Postby nvannote » Mon May 25, 2020 8:25 am

PeterR wrote:
Sat May 23, 2020 1:00 am
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
Posts: 621
Joined: Mon Jun 04, 2018 2:47 pm

Re: Save temperature data to local database

Postby PeterR » Mon May 25, 2020 9:42 pm

Cool, happy to help.
Just keep an eye on you're write strategy, unless you are happy to brick!
& I also believe that IDF CAN should be fixed.

RichPiano
Posts: 123
Joined: Mon May 18, 2020 2:51 pm

Re: Save temperature data to local database

Postby RichPiano » Tue May 26, 2020 2:08 pm

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.. :D

Who is online

Users browsing this forum: Bing [Bot], lpinter, Majestic-12 [Bot] and 114 guests