Beginner question - best approach for a table / database?

Hi folks,

I'm working on my first Arduino project, and while I'm very familiar with IT, I'm by no means a trained programmer.

I've got all the basics working (responding to inputs, controlling RGB LEDs, webserver, saving data to EEPROM and SPIFFS), but could use some advice on the best approach to handle data before I really dig into this project.

I've a bunch of information I need to keep track of, essentially organised as a relational database. In database terms there would be 8 separate tables of information, and I need to be able to work on records efficiently as the system runs, as well as save and load this to permanent storage, and send data to & from the system via HTTP GET & POST. The data will be read several times a day, but after initial setup, writes will be very rare (likely a few times a year).

Now I'm totally new to Arduino and JSON, but from what I've seen so far a JSON schema that is saved to or loaded from the SPIFFS storage would seem like a potential solution, and would seem that it could give me an internal data structure, can help me save to permanent storage, and can be used as an API to bring data into the system.

Can anybody advise me on whether that's a good approach, whether there are better options, and if there are any examples you can think of to show me a good way to both store this data, and handle it while the system is running?

Many thanks,

Myx

what type of chip are you using? ESP32?

you could have a look at the Extended Database Library and the SPIFFS example

Hiya, yeah, looking at using the ESP32 for this.

Would the database library be a better fit than JSON for this? The number of records is going to be small, typically less than 20 per table. I just don't know the toolset well enough to know which approach this needs.

JSON (with typical library) will be in memory so if that fits you'll be fine, if it does not then you need alternative storage

If you are already making GET requests, then the "best" approach, IMHO, is to put the database on the server that you are already talking to.

Minor problem with that.... the GET is to the ESP32. Installing a full blown database on there is probably a little excessive :wink:

I'm basically creating a HTTP accessible API using GET & POST for this ESP32 project, and I'll have a cross platform smartphone app using that API to control the ESP32 and monitor its status.