Comment by dragly
5 years ago
From experience, I can also recommend using SQLite as an application file format. I landed on SQLite after looking for solutions for a file format for an educational app we made for simulating biological neural networks. The app is cross-platform, written in Qt and the simulations needed to be stored as a JSON describing the network, a thumbnail and some metadata. It was also intended to be extensible with more features and backwards compatible if new versions were released. I considered creating our own simple format, using ZIP files, HDF5, Qt resource files or SQLite.
I landed on SQLite for many of the reasons outlined in this article and in particular because of how easy it was to implement and maintain. SQLite is supported natively in QtSql, which made it extremely easy to write the save and load functions, and later extend these with more data fields. In addition, we did not have to worry about cross-platform support since this was covered by SQLite and Qt already.
It sounds like future schema changes may be a potential concern for your application. One thing you can look into using is the SQLite user_version pragma. We use this right now to roll our own migrators and it's light years better than how migrators work for Entity Framework, et. al.
https://www.sqlite.org/pragma.html#pragma_user_version
Interesting, I used HDF5 in a similar situation because we needed to save a lot of same-sized rows of data (simulation time steps), so a matrix-oriented format seemed to make sense but it wasn't entirely without some need for cross-referencing between tables, so it does make me wonder now if sqlite would have been a comparable or better choice. Any reason for rejecting HDF5 in your case?
Is it cheap to insert/update/delete data in HDF5? If not, that should be the answer. I'm also curious if Parquet would fit your requirements.