SQLite 3.51 is out, and here are two things that you would find interesting…
3.51 introduces two new JSON functions - jsonb_each() and jsonb_tree(). These functions make working with nested JSON data more efficient.
These functions work like their existing counterparts (json_each() and json_tree()), but with a key difference. When they are provided arrays or objects, they return JSONB format for the value column instead of regular JSON. This makes subsequent operations on nested structures faster since the data is already in binary format.
3.51 also has a few performance improvements…
It now uses fewer CPU cycles when committing read transactions. It can detect early when joins will return no rows, saving unnecessary computation.
One clever optimization: SQLite now skips evaluating scalar subqueries when their result won’t change the overall expression outcome. Here’s an example
SELECT * FROM users
WHERE age > 50 AND
(SELECT COUNT(*) FROM orders) > 1000;If SQLite evaluates age > 50 first and finds no users over 50, the entire WHERE clause is already false. The subquery (SELECT COUNT(*) FROM orders) > 1000 won’t change the outcome, so SQLite skips executing it entirely.
Simple, but effective.