SQLite can hold and query JSON documents through its built-in JSON functions, letting one file act as a small document store. It is not a drop-in replacement for a dedicated document database, but it covers a useful range of cases.
Key takeaways
- SQLite includes JSON functions that can read values out of a text column holding a JSON document, so schemaless data can be stored and queried without a separate database server.
- The pattern usually involves one table with an identifier and a JSON payload column, plus indexes built on expressions that extract specific fields from that payload.
- Generated columns, added to SQLite after the original JSON support, make it easier to expose a field from a document as if it were an ordinary column.
- The approach trades away the distributed features of dedicated document databases, which is acceptable for single-node applications and awkward for anything that needs sharding or replication.
- Whether this counts as a genuine document database or a convenience layer over a relational engine is a matter of long-running disagreement among developers.
What does using SQLite as a document database actually mean?
The idea is straightforward. Instead of designing a table with a column for every field, you create a table with very few columns: typically an identifier and a single column holding a JSON document as text. Reads and writes then operate on whole documents, and queries reach inside them using SQLite’s JSON functions, which can extract a value at a given path, test whether a path exists, iterate over arrays, or rebuild a document with a field changed.
That gives you something close to the developer experience of a document store: you can add a field to some records and not others, you can nest objects and arrays, and you do not need a migration every time the shape of your data changes. What you keep is the rest of SQLite — transactions, ordinary SQL joins against conventional tables, and a database that is a single file on disk with no server process to run.
The important qualification is that SQLite is still a relational engine underneath. The JSON functions are functions, not a native document type in the sense that a purpose-built document database has one. Everything is built on top of that.
Why this approach keeps resurfacing
Discussion of SQLite as a document store tends to return periodically rather than arriving as news, and the current attention comes from an older write-up circulating again on a technology aggregator. The underlying reason it resurfaces is that the constraints that made it attractive have not gone away and have arguably strengthened.
Several trends push in the same direction. Application developers have become more willing to run a database in-process rather than as a separate service, partly because deployment complexity is a real cost and partly because single-machine hardware handles workloads that once required a cluster. At the same time, the appetite for operating a separate document database purely to get flexible schemas has cooled, since most relational engines now ship JSON support of their own.
There is also a practical trigger: SQLite has continued to add features that make the pattern more comfortable, including generated columns and, in a later release, a binary representation of JSON intended to make repeated parsing cheaper. Each addition renews the argument that the gap between SQLite and a dedicated document store is narrower than it appears.
The background a newcomer needs
SQLite is an embedded relational database engine. It runs inside the host application as a library rather than as a separate server, stores a database in a single file, and is released into the public domain. It is one of the most widely deployed pieces of software in existence, appearing in mobile operating systems, browsers, and countless desktop and embedded applications.
JSON support arrived as an extension, commonly referred to as the JSON1 extension, and later became part of the standard build. It provides functions that parse a text value as JSON and operate on it: extracting scalars, producing objects and arrays, and expanding a document into rows so that array elements can be treated as a table.
Two other features matter for this pattern. SQLite supports indexes on expressions, not only on plain columns, which means you can index the result of extracting a field from a JSON document. And generated columns let you declare a column whose value is computed from an expression over other columns; it can be stored on disk or computed on read, and can then be indexed and queried like any other column.
Who is affected and how
The people most directly served by this pattern are developers of applications that run on a single machine or a single instance: desktop tools, command-line utilities, mobile apps, embedded devices, internal services, and small web applications. For them, avoiding a second database process is a meaningful simplification in packaging, deployment, backup and testing.
It also matters for prototyping. Early in a project, the shape of the data is frequently unsettled, and storing documents avoids repeated schema migrations while the design stabilises. Some teams then progressively pull fields out into real columns as the schema firms up, which SQLite’s generated columns make comparatively easy.
Teams running at larger scale are less affected. If an application already needs horizontal scaling, multi-node replication, or concurrent write access from many separate processes across a network, SQLite’s design does not target that, and the JSON functions do not change it. SQLite’s own documentation has long framed it as an alternative to writing files rather than an alternative to a client-server database, and that framing still applies here.
Where informed people disagree
The clearest disagreement is definitional. One camp argues that a database with JSON functions and expression indexes is functionally a document database for most purposes, since the observable behaviour — store a document, query inside it, index a path — matches. The other argues that a document database is characterised by more than query syntax: by its storage format, its concurrency model, its clustering and its tooling, and that calling SQLite one blurs a distinction worth keeping.
There is a related technical dispute about performance. Storing JSON as text means the engine parses the document when a query reaches inside it, which critics say is wasteful compared with a native binary format. Supporters counter that indexes on extracted fields avoid most of that parsing, and that the newer binary JSON representation narrows the gap further. Precise costs depend heavily on document size, query shape and workload, and general claims in either direction should be treated with caution.
A third disagreement is about discipline. Some developers see schemaless storage in a relational engine as a way to defer necessary modelling decisions indefinitely, producing data that no constraint enforces. Others see it as a pragmatic staging area.
What this means in practice
If you want to try the pattern, the shape is small. Create a table with an identifier column and a text column for the document. Insert documents as JSON text, ideally validating them first, since a text column will happily accept malformed JSON unless a check constraint using SQLite’s JSON validity function prevents it.
For any field you filter or sort by regularly, do not rely on scanning every document. Either create an index on the expression that extracts that field, or declare a generated column for it and index that. Fields you only read back as part of the whole document need no index at all.
Watch for a few sharp edges. Sorting and comparison follow SQLite’s type rules applied to whatever the extraction returns, so numbers stored as strings behave differently from numbers stored as numbers. Updating one field of a large document generally rewrites the whole document. And full-text search over document contents is a separate mechanism, using SQLite’s full-text search extension rather than the JSON functions.
What to watch next
The direction of travel to follow is SQLite’s own release notes. JSON handling has been an area of continued work, and the practical viability of this pattern depends on how far that continues — particularly around storage representation, indexing ergonomics and function coverage.
It is also worth watching the surrounding ecosystem rather than the engine alone. Tooling that replicates SQLite files, exposes them over a network, or runs them in serverless environments changes the calculation about when a single-file database is enough, though the maturity and suitability of any particular tool for a given workload is something to evaluate directly rather than assume.
Finally, watch what other embedded and relational engines do with JSON. If comparable support becomes routine everywhere, the interesting question stops being whether SQLite can act as a document store and becomes when a dedicated document database is actually required.
Frequently asked questions
Can SQLite really replace MongoDB?
For a single-machine application with modest concurrency, SQLite with JSON functions can cover a similar set of tasks: storing documents with varying fields, querying inside them, and indexing selected paths. It does not replace the distributed capabilities of a client-server document database, such as replication across nodes, sharding, or many separate clients writing over a network. The honest answer depends entirely on which of those features you actually use.
How do you index a field inside a JSON column in SQLite?
Two routes exist. You can create an index directly on the expression that extracts the field from the document column, so queries filtering on that same expression can use the index. Alternatively, you can declare a generated column whose value is the extracted field and create an ordinary index on that column, which some developers find clearer to read and easier to query against later.
Does storing JSON in SQLite slow queries down?
It can, because reaching inside a text document requires parsing it, and a query with no usable index will parse every row it examines. Indexes on extracted fields avoid most of that work for filtered queries. Actual performance depends on document size, how many fields you query, and how much of each document you read, so measuring your own workload is more reliable than general rules.
What is the difference between JSON text and binary JSON in SQLite?
Text storage keeps the document as ordinary characters, which is human-readable and portable but must be parsed each time a function reads into it. A binary representation, added in a later SQLite release, stores a pre-parsed form intended to reduce that repeated work. The trade-off is readability and interoperability against parsing cost; which matters more depends on how often documents are queried versus inspected directly.
Should I use a JSON column or normal columns?
Use normal columns for fields that are always present, frequently queried, and part of the stable core of your data model, because constraints and indexes work most naturally there. Use a JSON column for fields that are optional, sparse, deeply nested, or still changing. Many projects mix both in one table, and move fields from the document into real columns as the schema settles.
Is this pattern suitable for production systems?
It is used in production, but suitability is workload-specific rather than general. It fits applications with a single writing process, data that fits comfortably on one machine, and a deployment where avoiding a separate database service is valuable. It fits poorly where you need multiple machines writing concurrently, network-level access control at the database, or operational features that only a server-based system provides.
Sources and further reading
- The official SQLite documentation, which covers the JSON functions, generated columns, expression indexes and the project’s own guidance on appropriate uses.
- SQLite release notes, which record when JSON-related features were introduced and changed.
- Technology aggregator discussion threads, where developers compare this pattern with dedicated document databases and report practical experience.
- General database engineering literature on schemaless storage in relational systems, covering the trade-offs between flexible documents and enforced schemas.
Surfaced from the hackernews signal “embedded database JSON storage”. AI-assisted draft, editorially reviewed.
