SQLite as a Document Database (2020)

In the grand theater of data storage, we've been told there are two camps: the rigid relationalists who demand schemas like a strict librarian, and the free-spirited documentarians who let their JSON flow like a river. But here's the twist: SQLite, that unassuming library that's probably running your browser's local storage or your favorite app's cache, has been moonlighting as a document database all along. The Reddit post (linked from the Hacker News community) points to a 2020 piece that explains how SQLite's JSON1 extension lets you store, query, and index JSON documents, effectively turning a single-file SQL database into a NoSQL-style store with a surprisingly robust set of features. It's weird, it's wild, and it's the kind of convergence that makes you question why we ever drew such hard lines. What's truly delightful here is the philosophical chasm this bridges. Relational databases thrive on normalization—breaking data into neat, predefined tables with rigid relationships. Document databases celebrate denormalization, letting each record be a tiny snowflake with its own schema. SQLite's JSON support doesn't pick a side; it welds them together, letting you query inside JSON structures with SQL, build indexes on JSON fields, and even join between the structured and semi-structured worlds. This is like finding a fossilized animal that has both gills and lungs—an impossible creature that turns out to be the missing link we never knew we needed. The broader lesson for anyone staring into the quantum foam of data science is that "either/or" often becomes "both/and." We've seen it in physics—wave and particle, space and time—and now we see it in software. The universe of technology is not about fundamentalism but about pragmatic synthesis. The fact that a minuscule, zero-configuration database can compete with heavyweight document stores for certain workloads is a testament to the elegance of simplicity and the power of constraint. It's the underdog story we root for in every tech saga, but here the underdog has been sitting on our desktops for years. Of course, this isn't a call to abandon dedicated document databases—they still shine for massive scale, sharding, and advanced aggregation pipelines. But the insight that we can blur boundaries using the tools we already have is both liberating and humbling. It echoes the spirit of scientific discovery: the familiar becomes strange when you look at it with fresh eyes. So the next time you open a SQLite file, remember—you're not just peering into a relational database; you're looking at a shape-shifting entity that defies categorization. And that, my friends, is weird and wild in the best possible way. (Source: https://www.reddit.com/r/hackernews/comments/1w1yrze/sqlite_as_a_document_database_2020/)