Top
Best
New

Posted by upmostly 12/12/2025

SQLite JSON at full index speed using generated columns(www.dbpro.app)
381 points | 114 commentspage 3
oars 12/12/2025|
Great article with clear instructions - could be quite useful if I need to do stuff with storing JSON in SQLite in the future.
zackify 12/13/2025||
I love jsonb support in sqlite.

Particularly with drizzle, it means I can use sqlite on device with expo-sqlite, and store our data format in a single field, with very little syntax, and the schema and queries all become fully type safe.

Also being able to use the same light orm abstraction server side with bun:sqlite is huge.

rcarmo 12/12/2025||
I've been using this trick for a while, and it actually got me to do quite a bit without an ORM (just hacking a sane models.py with a few stable wrappers and calling it a day)
morshu9001 12/12/2025|
json columns pretty much obviated the need for ORMs. It used to be that you'd sometimes have a deep nested thing you really only ever query all at once rather than in pieces, so you'd use an ORM to automate that, but now you can just shove it into json. And then use regular SQL for the relations you actually care about.
bushbaba 12/12/2025||
For smaller datasets (100s of thousands of rows) I don’t see why you wouldn’t just use json columns with generated column/index where needed
bilekas 12/12/2025|
Regardless of the number of rows, it doesn't really matter, there are useful cases for where you might be consuming json directly, so instead of parsing it out into a schema for your database, why not just keep it raw and utilize the tools of the database.

It's a feature, not a replacement.

mring33621 12/12/2025||
IIRC, Vertica had/has a similar feature.
kwillets 12/12/2025||
It's been around for quite while, but DB people hate to explain where they got an idea. For all I know Vertica got it from somewhere else; I think postgres got jsonb around the same time.
xp84 12/12/2025||
Now there’s a name I haven’t heard in 10 years. (I’m only tenuously connected to the kinds of teams that use/would have used that, so it doesn’t mean much.)
N_Lens 12/12/2025||
What a neat trick, I love SQLite as well.
baq 12/12/2025||
My understanding is Snowflake works kinda like that behind the scenes right?
javantanna 12/12/2025||
Your website looks like supermemory.ai , BTW its pretty cool
pipe01 12/12/2025||
MongoDB is dead, long live MongoDB
stacktraceyo 12/12/2025|
Can I do this with pocket base?
More comments...