Multiple applications with single database for IoT

Question

I'm using a raspberry pi 2B with raspbian lite. I have made a program that communicates with a LoRa module (RFM95, sx1276 chip) and collects data from a sensor. The data it collects, are stored in a file date.log and goes to Google Drive using third party software. Now I want to make an asynchronous TCP service to collect data remotely but storing data in a single file doesn't sound like a good idea. I'm thinking of using a database instead of simple files, like sqlite3 but I'm not sure about its performance.

Is it possible for a database to perform writes from more than one program?

Is it better to integrate my software into one program? Like the LoRa driver with the TCP listener together.

Answer 1

Basically yes:

We are aware of no other embedded SQL database engine that supports as much concurrency as SQLite. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. (source)

But: As the source says, the file will be locked for the time of a write operation. This will limit the throughput of your application.

Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution. (source)

Of course you can optimize this. If the data comes at 1000 Hz (just to illustrate), you could collect the data from 100 ms and batch write it, but a fully fledged RDBS could suit your use case better. If we are talking 10 Hz or something, SQLite might fit, too. Disclaimer: These are guesses at best. I'd go and measure the performance both with SQLite and MySQL.

Some users are arguing that a fast USB stick would make sense for MySQL@RPi2 since the performance of the SD card is mediocre (see here, but it's German, sry), but this should hold for SQLite, too.