Once in memory, the flatfile’s data can be mutated in place, and creating the equivalent of a WAL-like system or oplog to protect against sudden failure & loss of in-memory data is a pretty easy part to solve.
That's a pretty bold claim. Judging by the number of applications (including "real" databases) which fail to get this right, I'd have to say it's probably harder than you think.
Besides, SQLite has done that work already, and has done so very thoroughly. I would definitely trust SQLite over something home-grown.
- crashes/power failures
- multiple processes opening the same file
- transactional updates: it rolls back if you throw an exception in the middle of writing the update. This seems like a common failure mode while under development
- scaling to huge size: many gigabytes are no problem. These get slow with a flatfile
- manual surgery on data: it comes with handy command line tools for manipulating its databases
- upgrading format: you can check a version number when opening the file and do "alter table add column" for any new features.
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.
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.
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.
With SQLite you can have multiple processes access the same database and they can make changes to it that are immediately seen by the other processes. The nature of SQLite locks means this doesn't scale well if you have lots of processes all wanting to make heavy updates, but so long as you're not in that situation it works very well.