Sqlite seems like it is only appropriate for the knife’s edge boundary between small data, low reliability, in-memory situations (better served by server applications using tools like pandas or R) and bigger data, transactional structure, reliability constraints (better served by Postgres).
I just can’t understand what use cases live in between them and are better served by sqlite.
It's honestly bizarre to me to see someone claim that Sqlite has few use cases when there are likely hundreds of Sqlite files sitting in the phone in your pocket.
Sometimes you do not want to run an additional process/daemon like postgres. Your state is in essence now a file on some file system that you can atomically (full ACID) update using multi processing without the need for more complex machinery - you can get very far with this architecture.
They’re not massive differences but i definitely appreciate the snappiness. Interactive work is just nicer that way.
I could do postgres - the docker version is pretty handy i find but it’s just more faffing about (-v /path/to/wherever:/var/postgres password, cleanup when you’re done etc.)
Sure beats regexing my way to the next minute marker or whatever.
Why would I use flatfile (aside from the fact I've never heard of it) over SQLite? So far you've just said SQLite has some features you can't imagine I'd ever need, but even if that were true then it wouldn't be a problem in itself. What are things that are actually bad about SQLite compared to the alternative you're suggesting?
Looking back, you seem to be right. I thought mlthoughts2018 was referring to a specific file format, or at least a specific library because (1) They made up that weird terminology "flatfile" which looks like product name instead of just saying "plain text file" ... I wonder why they did that? (2) They made claims about transactional integrity with a WAL-like log. Now I realise they were suggesting you homebrew that anew for each fresh project that needs to store data! The idea that this would be easier than just using SQLite's existing mechanisms is so bizarre it didn't even enter my head they could've meant that.
There are advantages over SQLite, eg: the ability to use unix command line tools against the file directly.
In my view SQLite has more advantages though.
Of course principled command-line tools for text (e.g. awk) and the possibility of running arbitrary SQL queries on a database blur the line, but the distinction between files that are read or written once as a step in a transformation pipeline or as something to load or save and databases that represent a persistent state, with transactions, remains sharp.
Not sure why the sibling posters determined s/flatfile/flatfiles/ the most interesting part of your question.