7 ms·
SQLite as a Document Database
- chews 6y agoIt’s used that way in a lot of mobile applications. A really solid JSON store with easy query ability.
- liuliu 6y agoShameless plug: Dflat wraps SQLite in similar ways for mobile applications: https://dflat.io/ https://dflat.io/
- tksb 6y agoThanks for posting this! Very interested in Realm-like solutions.
- liuliu 6y agoThanks! Realm is really easy to start, but not far from Core Data in its API. Dflat focuses on writing correct code easy, thus, its API is fairly different from these two. Its API resembles from Apple's PhotosKit (which I liked the interface a lot), but generalized it and makes sure you can have concurrent and synchronous read, managed write threads, strict-serializable multi-reader / multi-writer. Also paid extra attention to edge cases such as schema upgrade upon disk full (hence, read-only schema upgrade).
- tksb 6y agoSounds awesome. I've saved it and hope to find some time to dig in a little bit soon. Thanks for the additional details!
- kevinday 6y agoJust a suggestion: "If you are familiar with Core Data or Realm, Dflat occupies the same space as these two". I'm not familiar with either of these, even a brief description of what this is would be super helpful.
- liuliu 6y agoDone! Thanks for the head-up.
- triangleman 6y agoYes it seems the json() function is good enough for most applications, and in fact you probably want to use it anyway as it will minify the JSON input before inserting. Now, a dedicated JSONB datatype (and indexes!) like Postgres would be awesome, but sqlite3's "poor man's JSON" seems appropriate for many use cases.
- formerly_proven 6y agoUnfortunately can't be "universally relied on", as it remains disabled by default and in many distributions (mostly Linux, but e.g. Python for Windows has an SQLite with few extensions, which you can fix quite easily though by replacing the sqlite3.dll).
- samatman 6y agoSQLite has the rare combination of being superbly stable and backwards compatible (thus, little motive for systems to keep it up to date) and regularly shipping new features I want. As a result, I like to statically link it into the binary, when possible. Life is too short to write a bunch of feature discovery and graceful degradation code.
- nix23 6y agoI just use fossil for that as kb, code snippet and documentation tool.
- Twirrim 6y agoFor those not familiar, https://www.fossil-scm.org/home/doc/trunk/www/index.wiki https://www.fossil-scm.org/home/doc/trunk/www/index.wiki, created by D. Richard Hipp, the primary author of SQLite. Fossil can be used for source control, bug/work tracking, wiki, blogging tool, and so on. I'm somewhat tempted to use it at work. The idea of having wiki and targeted job/improvement tickets in the same repo as the code is very tempting.
- nix23 6y agoDo it! I use it for a project documentation tool, for every customer/project one db, i write howtos/documentation into the wiki, code or patches to the repo and tickets too, at the end of the project i send that one db file and that's it. I just love it, because tickets and documentation belongs to code+comments. And whats always important, the tooling is good, change to git and md's...no problem.
- ausjke 6y agoit does not seem to have syntax highlight for code snippets. I use gitea to host code which has tickets/wiki/bug-tracking but no forum, no blogging. I used to use redmine + git which has more than what I wanted. I wish gitea adds forum and blogging someday.
- Rochus 6y agoCrossLine uses the Sqlite backend directly with a binary Json like protocol for this purpose, see https://github.com/rochus-keller/crossline/ https://github.com/rochus-keller/crossline/ and https://github.com/rochus-keller/udb https://github.com/rochus-keller/udb.
- bityard 6y agoI guess "Document" in this context means "blob of JSON" but I came here to mention that SQLite makes a terrific personal wiki/notes backend thanks to the very nice built-in FTS5 full-text search engine.
- eitland 6y agoDo you have pointers to implementations?
- adonese 6y agoSimon Willison has good posts on implementing full text search on top of sqlite[1] [1]: https://simonwillison.net/2019/Jan/7/exploring-search-relevance-algorithms-sqlite/ https://simonwillison.net/2019/Jan/7/exploring-search-releva...
- noxer 6y agoI've used triggers to extract json data on insert. quite flexible and allows to store the whole JSON without overhead of inserting data twice.
- h3rald 6y agoYep, SQLite is awesome. I did actually build exactly that: a document database powered by SQLITE [1], and it works well, especially considering that SQLite has built-in support for full text search and indexes for JSON content. [1]: https://h3rald.com/litestore/ https://h3rald.com/litestore/
- triangleman 6y agoThis looks great, and way more capable than simply "prototyping and testing" as you put it on your web site. How did you get sqlite3 to index JSON? I don't see anything in the product that does that. I'm searching through your source code now but I don't see anything specific.
- h3rald 6y agoWell yes it started off for prototyping and then I kept adding more features like JavaScript middleware and token validation... I have used it as a backend for quite a few private projects of mine. Regarding indexing json... here's how I do it: proc createIndex*(store: Datastore, indexId, field: string) = let query = sql("CREATE INDEX json_index_$1 ON documents(json_extract(data, ?) COLLATE NOCASE) WHERE json_valid(data)" % [indexId]) store.begin() store.db.exec(query, field) store.commit() Basically, SQLite supports indexes in arbitrary expressions and so you can use it in conjunction with json_extract. In this case, field is an arbitrary json path. I then added a REST API in LiteStore to create (JSON) indexes... in that way you can make your queries more performant based on the specific json documents that you store in the data store.
- pupdogg 6y agoLiteStore looks fantastic! Do you have any performance metrics (specifically limits) for the various use cases you mention in the documentation? Considering it's written in Nim, I wouldn't be surprised if it actually outperformed bulk of the other documentDB solutions that currently exist out there.
- h3rald 6y ago
- bambax 6y agoThere is no tool that I love more than SQLite. It's perfect. It does exactly what it says it will do, it never breaks (NEVER), is so small as to be invisible, is lightning fast, and packs many features a lot of the big guys still lack. It is incredible.
- sradman 6y agoSQLite is incredible but it is sometimes quirky and the new GENERATED ALWAYS syntax unexpectedly disappointed me when I read about it with the 3.31.0 release. GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS is IBM DB2 syntax that became part of the SQL standard. It is meant to be the unifying syntax for all the various flavors of AUTOINCREMENT that exist (GENERATED ALWAYS AS IDENTITY is now the standard way). It also makes a distinction between ALWAYS generated and BY DEFAULT which can be bypassed with a user supplied value. SQLITE's GENERATED ALWAYS doesn't apply to keys/indexes so it doesn't work in the main use case in the SQL standard; primary key generation. Hopefully the AS IDENTITY syntax will eventually be added to SQLite to fix the goofy INT PRIMARY KEY vs. INTEGER PRIMARY KEY (long-form INTEGER is a synonym for IDENTITY/AUTOINCREMENT) syntax. The JSON Document use case is useful but nowhere near as useful as supporting a clean and standard GENERATED ALWAYS AS IDENTITY for primary keys, in my opinion. Maybe I'm missing something.
- triangleman 6y agoYes, I managed to get tripped up on the INT/INTEGER primary key distinction as a new sqlite3 user, and the idea of using the SQL standard to replace it would be great.
- eyelidlessness 6y agoI’ve never used SQLite and this would almost certainly have exploded my brain if I encountered it in dev before reading it here.
- tehbeard 6y agoI will admit to being less well traveled with the SQL family, mainly sticking to news on sqlite and mysql with some curiosity of postgres, but I'd only known of the GENERATED AS syntax being primarily for virtual columns that use a computed value.
- electricshampo1 6y agoDoes sqlite have per page checksums (for bit rot detection)? Only thing per page I see in the docs is https://www.sqlite.org/fileformat2.html https://www.sqlite.org/fileformat2.html "SQLite has the ability to set aside a small number of extra bytes at the end of every page for use by extensions. These extra bytes are used, for example, by the SQLite Encryption Extension to store a nonce and/or cryptographic checksum associated with each page." Postgres (and other databases that actually care about data integrity) have per page checksums: https://www.postgresql.org/docs/13/storage-page-layout.html#PAGEHEADERDATA-TABLE https://www.postgresql.org/docs/13/storage-page-layout.html#...
- jnetterf 6y agoVia a loadable extension: https://www.sqlite.org/cksumvfs.html https://www.sqlite.org/cksumvfs.html
- electricshampo1 6y agoThanks for this; should have looked more carefully.
- cdcarter 6y agoThe technique the article presents is clever. I like exposing the fields I need to query as columns, and the way that making an extracted field NON NULL can supplement/replace complicated json check constraints is neat.
- racingmars 6y agoI don't know why this bugs me, but from the article: "(Aside: The hard bit may be getting a new enough SQLite, at the time of writing Homebrew on macOS has it, else you likely need to use an unstable source like nixpkgs-unstable.)" I feel like the obvious answer is to just get the stable, released source from the SQLite web site and build it yourself.
- tehbeard 6y agoI guess the issue there is integration with your programming language of choice?
- infogulch 6y agoYou could skip the VIRTUAL column and add an index directly using an expression index [1] with json_extract(). [1]: https://sqlite.org/expridx.html https://sqlite.org/expridx.html
- rafaelturk 6y agoLooks really cool, albeit a little bit NOSQL everywhere!
- l8mr4 6y agoIf you enjoy python and wanna try a object database check out ZODB with relstorage (now supports SQLite).
- ReD_CoDE 6y agoDo you know any awesome project like SQLite that is based on combination of raw-based as well as columnar? Something like Apache Parquet, but textual, instead of binary