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.
faizshah 55 minutes ago [-]
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.
subhobroto 60 minutes ago [-]
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.
sgbeal 35 minutes ago [-]
> Write-ahead logging and concurrency PRAGMAs have been a given for a decade now.
All of the listed features except for strict tables and generated columns have been in SQLite for 10+ years, and those two are certainly not new. The JSON APIs were not made part of the standard distribution until 3.38 (2022-02) but were added in 3.9 (2015-10) and widely used long before they were upgraded from an optional extension to a core feature.
- Generated columns: 3.31 (2020-01)
- Strict tables: 3.37 (2021-11)
happytoexplain 38 minutes ago [-]
There are plenty of people in the HN community who don't know much about SQLite. Tech is a big, huge, enormous, gigantic domain.
Rendered at 18:01:21 GMT+0000 (Coordinated Universal Time) with Vercel.
And ON CONFLICT which can help dedupe among other things in a simple and performant way.
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.
All of the listed features except for strict tables and generated columns have been in SQLite for 10+ years, and those two are certainly not new. The JSON APIs were not made part of the standard distribution until 3.38 (2022-02) but were added in 3.9 (2015-10) and widely used long before they were upgraded from an optional extension to a core feature.
- Generated columns: 3.31 (2020-01)
- Strict tables: 3.37 (2021-11)