Thats true but gives you enough to get started.

Yes, the litestream stuff is exciting and if they finally remove the single-writer limitation with hc-tree, it would be huge. I do have a question though: how do you deal with backups in such cases? Because there is an argument for simpler deployments with sqlite3 but that inevitably pushes some concerns to you, no?

Yes. I could not give you a good answer now. Maybe a comment on HN, now the discussion is still active?

FYI Moved these sqlite specific bits out to their own thread, I think the suitability of Sqlite in production is a super interesting topic right now, but I think is a bit tangential to the original thread.

Apparently Litestream can replicate to S3/Google/etc: Install - Litestream

I also found this for a lightweight cron-based approach: Cron-based backup - Litestream

There are also some sqlite options without litestream. From the point of having a separate file of the db it’s mostly a matter of putting that file somewhere safe.

The only thing that scares me is the fact that you need to lock the file, it would be nice to be able to backup partially without having to lock.

SQLite has its own backup functionality as linked by @LostKobrakai – it’s not just copying the files though – and a lot of energy has been expended to prove that it works stably.

Additionally, it also has the concepts of patch-sets and change-sets (section 2.1) – somewhat to the confusion of us in the Elixir space due to the naming clash – that allow you to accumulate changes since a last known snapshot, in a dedicated file.

SQLite has a strong backup apparatus even without litestream. The latter seeks to make backups over the network more or less transparent. The rest is well-covered by sqlite itself.

That’s already covered. You issue a backup command and SQLite itself makes new file(s) while you’re using the same session. It’s fully atomic.

People get needlessly scared there, their team is very aware of the potential issues and took care of them a while ago.

It is not recommend to backup sqlite by just copying the file. You want to use the backup and restore commands.

From command line backup can be done like this, super simple.

$ sqlite /path/to/db.sqlite ".backup /path/to/file.bkp"

For most of my pet projects it is absolutely fine to use sqlite, it is plenty fast and super reliable. I cant say postgres ever caused me any big troubles, but the simplicity of sqlite is just great when you can get away with it