SQLite 3.9.0 Released with JSON support | Hacker News Reader

SQLite 3.9.0 Released with JSON support

sqlite.org

79 pointsconductor11 comments

This is way cool. I've been wanting to play w/ Postgres's new(er) json features but haven't had use case to set up server and do so. But this can be tried out really easy.

Oh, did I mention how much I love SQLite? Not being a startup in "scale mode" it works really well for the size of apps I build (internal apps with only a handful of simultaneous users). The only drawback I've ever had was with simultaneous writes, but a lock and a server queue easily fixed that. Other than that, it is fast, reliable, and so easy to set up and deal with.

How does this compare to Postgres' jsonb? I use that at work, I would love to be able to run integration tests using SQLite in memory, as opposed to running against Postgres in a container.

Doesn't seem to as advanced as jsonb, but it's still pretty good. https://www.sqlite.org/json1.html

The link should be changed to https://www.sqlite.org/releaselog/3_9_0.html

Thanks, changed from https://www.sqlite.org/news.html.

> Backwards compatibility constraints mean that ... It is not possible to add a sixth "JSON" type.

What? Can someone explain why backward compatibility prevents future versions from added functionality? I guess someone somewhere is doing some case statement on the type of thing in a column? That's a pity... SQLite doesn't even have a datetime type. Now I'm wondering if these are related.

This is just an educated guess, but it could be an issue with the on-disk record formats. A quick look here: https://www.sqlite.org/fileformat2.html shows that variable length types like BLOB and TEXT are denoted by a type code value greater than 12 that is even or odd (respectively). The only wiggle room looks like values 10 and 11, but of course there would have to be some completely separate way of denoting the size.

That doesn't explain backwards compatibility issues unless what they're saying is they want stuff created in SQLite 3 to work with SQLite 2. The policy doesn't seem to say that though. It's probably just hard to figure out.

http://www.sqlite.com/formatchng.html

What ever happened to SQlite 4?

They said it's more of an experimental test bed.

And indexes on expressions! And a new full-text search module, FTS5! And slightly less restrictive partial index support! And some other things that look nice but don't get exclamation marks.

This is a big release.

Reply on news.ycombinator.com