Modern SQLite: Features You Didn't Know It Had

In the past I've used the backup API - https://sqlite.org/backup.html - in order to load in memory a copy of sqlite db, and have another live one. I would do this after certain user action, and then by doing a diff, I would know what changed... I guess poor way of implementing PostgreSQL events... but it worked!

Granted it was small DB (few megabytes), I also wanted to avoid collecting changes one by one, I simply wanted a diff over last time.

Surprised no one has mentioned Turso yet!

They recently landed multi-writer support for their rust SQLite re-implementation, which is personally the biggest issue I've had with using SQLite for high concurrency applications.

`PRAGMA journal_mode = 'mvcc';`

https://docs.turso.tech/tursodb/concurrent-writes

Very excited to see if SQLite responds by adding native support, I'm hoping competition here will spur improvements on both sides.

STRICT tables are something I appreciate very much, even though I cannot recall running into a problem that would have prevented by its presence in the before-time. But it's good to have all the same.

I don't think I've ever done much with SQLite's JSON functions, but I have on one or two occasions used a constraint to enforce a TEXT column contains valid JSON, which would have been very tedious to do otherwise.

The JSON functions are the sleeper hit for me. I've used them extensively in ETL scripts where input data is semi-structured - being able to do json_extract and json_each directly in SQL instead of writing a Python preprocessing step saved a surprising amount of complexity. Strict tables are also worth calling out more. The lack of type enforcement was always the thing that made me reach for PostgreSQL instead, and strict mode closes that gap nicely for smaller projects.

Theres also spellfix1 which is an extension you can enable to get fuzzy search.

And ON CONFLICT which can help dedupe among other things in a simple and performant way.

I've found FTSE5 not useful for serious fuzzy or subword full text search. For example I have documents saying "DaemonSet". But if the user searches for "Daemon" then there will be no results.

None of these are news to the HN community. Write-ahead logging and concurrency PRAGMAs have been a given for a decade now. IIRC, FTS5 doesn't often come baked in and you have to compile the SQLite amalgamation to get it. If you do need better typing, you should really use PostgreSQL.

However, I will concede, and the article doesn't mention at all, far less are aware that you can build HA, cross region replicated SQLite using purely OSS software provided you architect your software around it. Now that would be a really good `Modern SQLite: Features You Didn't Know It Had` article!

Another interesting discussion point is how far self hosted PostgreSQL and pgBackRest can get you to a near-zero data loss high RPO, RTO setup. Its simply amazing we can self host all this.