Many small queries are efficient in SQLite

Re:Many small queries are efficient in SQLite by petre on Tuesday February 16, 2021 @02:24AM (#26151702)

Go ahead and use text files and then have fun with data corruption issues. We use CSV for sending commands to IoT devices and it's an issue. If this had been done with SQLite, then there were at least no data corruption issues. One could even use SQLite as a storage container for JSON if one whises to do so. They even have an extension that aids it with an useful set of functions:

https://www3.sqlite.org/json1.html

[ Parent | on HN ]

Re:Many small queries are efficient in SQLite by JamesSwift on Tuesday February 16, 2021 @09:08AM (#26153967)

The JSON features of SQLite are extremely robust and performant. There is no reason to use raw JSON as the storage when you can just shove it into SQLite and lose almost nothing.

[ Parent | on HN ]

Re:Many small queries are efficient in SQLite by millstone on Tuesday February 16, 2021 @03:02AM (#26151870)

This sounds like your issue is avoiding data corruption: then atomic writes are sufficient, you don't need a SQL parser or query optimizer or etc.

[ Parent | on HN ]

Re:Many small queries are efficient in SQLite by petre on Tuesday February 16, 2021 @06:17AM (#26152890)

Not only that, it enables us to to CRUD operations, list the commands, sort them by time, do limits, pagination, bundle a bunch of commands that enable a certain functionality in a transaction etc. SQLite has all of those and more and also avoids data corruption issues by design. Anyway, the path we took was to move everything to MySQL just because most of the other data is also in a MySQL database. Otherwise we would have definitely used SQLite.

[ Parent | on HN ]