Discussion

Modern SQLite: Features You Didn’t Know It Had

subhobroto: None of these are news to the HN community. However, I will concede, far less are aware that you can build HA, cross region replicated SQLite using purely OSS software provided you architect your software around it.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.

happytoexplain: There are plenty of people in the HN community who don't know much about SQLite. Tech is a big, huge, enormous, gigantic domain.

krylon: 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.

cloudpeaklabs: 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.

MoonWalk: That does seem cool. So there are sufficient functions to iterate through collections that might be stored in a single row of a JSON column?

duskwuff: Yes, using the table-valued JSON functions like JSON_EACH and JSON_TREE (which works recursively). Details: https://sqlite.org/json1.html#table_valued_functions_for_par...