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.

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:

1. Store the JSON document raw
2. Create virtual generated columns using json_extract
3. Add indexes to those generated columns
4. Query JSON at full B-tree index speed

This means you never have to choose your indexing strategy up front. If you later realise you need to query on a new JSON field, you simply add another generated column, add an index, and you’re done.

No data migration. No schema rewrite. No ETL. Just pure flexibility.

1. Store Raw JSON

First, create a simple table with a JSON column:

⚡

Loading SQL environment...

Your JSON documents are stored naturally, exactly as they arrive. No schema gymnastics required either!

2. Add Virtual Generated Columns

Here’s where bambax is saying the magic happens. Let’s add virtual generated columns. Generated columns compute values on demand. They don’t actually store data:

⚡

Loading SQL environment...

I believe that no writes occur. There’s no backfilling. It’s instant. The virtual columns are computed on-the-fly from your JSON data whenever you query them.

3. Add Indexes for Full Performance

Now is the icing on the cake. We add indexes to make these virtual columns blazing fast:

⚡

Loading SQL environment...

Suddenly your JSON behaves like normal relational columns with full index support.

4. Query at Full Speed

Now your queries are blazing fast. So let’s give it a go with some examples:

⚡

Loading SQL environment...

5. Need a New Query Pattern Later?

I believe this is one of the strongest points to this pattern. If at a later date your JSON shape changes (expected), you can just add another column and create another index.

For example, you realise you need to query by user_id:

Boom. optimised without touching existing rows.

Why This Pattern is So Powerful

This pattern completely changed how I think about working with JSON in SQLite. You get the flexibility of schemaless data, combined with the performance and ergonomics of a relational database, without committing yourself too early or painting yourself into a corner.

So, thanks bambax!

There are plenty more of these little SQLite superpowers hiding in plain sight. This is just the first one I wanted to share.

Thanks for reading!

Jay

---