We absolutely love SQLite here at DB Pro. You’d be hard-pressed to find anyone who actively dislikes it. Sure, it has limitations, and I do mean limitations, not weaknesses. SQLite can absolutely be used in production when it’s deployed properly and tuned with care.
SQLite has also seen something of a resurgence over the past few years. From being forked into projects like libSQL and Turso, to powering popular backend frameworks such as PocketBase, it’s clearly having a moment again.
As I said though, we love it. It even powers the local database inside DB Pro itself. For our use case, there really isn’t a better alternative.
Because we’ve been using SQLite in anger over the past three months, we’ve learnt a huge amount about it, including plenty of things we didn’t know before.
So I’m planning to write a short series of blog posts covering some of the cooler, more interesting features and nuances of SQLite that we’ve discovered along the way. This is the first of those posts.
First of all. Did you know SQLite has JSON functions and operators? I didn't until recently! I came across this Hacker News comment when researching SQLite's JSON operators.
HACKERNEWS The cool thing for working with json is to store each json document as is in one column, then make virtual columns that store some specific information you want to query, using some combination of json_extract, then index those columns. This makes for super-fast search, and the best part is you don't have to choose what to index at insert time; you can always make more virtual columns when you need them. (You can still also search non-indexed, raw json, although it may take a long time for large collections). I love SQLite so much - bambax
I read that, then read it again, then again until I understood what bambax was saying. I was sort of in disbelief.
So I had to give it a try to see if it works. We've got an embedded SQLite-in-the-browser component on our blog and so I wanted to throw together some working examples for you guys (but mostly for me).
Let's break down what bambax is saying:
... continue reading