▲ 239 points
back
50 comments
There 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.
SQLite 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.
I 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.
It’s definitely the preferred/recommended PK solution for versions of postgres supporting it.
Yes, 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.
I’ve never used SQLite and this would almost certainly have exploded my brain if I encountered it in dev before reading it here.
I love it. Moore's Law has made SQLite pretty much the only database I ever use. It's always been relatively fast, but with today's CPUs and SSDs, it's fast enough for anything I need.
A decade or so ago, I might have installed MySQL or Postgres to do some log processing or something. I never bother any more. SQLite is just the perfect data tool. You can load it up with gigs of data and it flies.
I still discover little tweaks and features I didn't realize it had as well. It's just a really well done project.
SQLite is awesome.
I've always been a bit confused by the design decision for SQLite to allow you to store any value in any column despite the column's type, but I've never had that been a problem since my applications don't try to store strings in integer columns.
It's a similar situation for storing date types as strings. Seems like an odd choice, but I've never had a problem because I keep my date types consistent.
It just goes to show that seemingly odd design choices might never end up being a problem.
I'd be fascinated to hear from those who may have had problems with SQLite's "flexible" type system.
I mean countless critical systems run on Excel spreadsheets and they're the definition of dynamically typed values, so there's some precedent for that working in the real world
There's also a cottage industry predicated on unbundling and (for the most part) statically typing processes defined in excel spreadsheets.
So, I've thought about this a lot, especially since there are sometimes some challenges with things such as datetimes in SQLite if you're not very careful and specific (thinking about if you actually want to use in query for more than basic comparisons).
However, on the OTHER hand... if we think about SQLite as a general purpose data storage tool, it's actually a really handy feature, letting the in datatypes almost every case be more of an additional type of label.
For example, if I required a high-precision timestamp, I could declare the column type something like 'UTCEpochNanos', providing a reminder to myself and other consumers of the data how it should be interpreted.
SQLite is awesome. I use it for my blog backend. It works really well.
I use it as a backend for markdown tables in my markdown notes. I have a script that runs on every commit that exports the tables as markdown tables. It's amazing!
It needs a transpose/pivot function
What has been your cases about SQLite ? would love to understand never breaks part.
Yep, 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.
LiteStore 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.
Not published unfortunately but I should, really!
In the past I tested importing dumps of the full HTML and CSS documentation docset from MDN and: - Importing files in a directory is relatively fast: I import batches and it can import hundreds of pages in a few seconds - sadly there's no bulk operation yet to insert arbitrary json documents (unless they are stored as files in a folder), so that's quite slow. - full text search and querying indexed (JSON) properties is fast (double-digit milliseconds for searching several hundred of relatively large documents with paged results)
I do need to do some compared benchmarks with similar data stores though.
Just discovered from parent. Great project! Thank you
This 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.
Well 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()
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.
I 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.
Do you have pointers to implementations?
Simon Willison has good posts on implementing full text search on top of sqlite[1]
[1]: https://simonwillison.net/2019/Jan/7/exploring-search-releva...
Does 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
"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#...
Via a loadable extension: https://www.sqlite.org/cksumvfs.html
Thanks for this; should have looked more carefully.
I 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.
I guess the issue there is integration with your programming language of choice?
CrossLine uses the Sqlite backend directly with a binary Json like protocol for this purpose, see https://github.com/rochus-keller/crossline/ and https://github.com/rochus-keller/udb.
I just use fossil for that as kb, code snippet and documentation tool.
For those not familiar, 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.
Do 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.
it 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.
You could skip the VIRTUAL column and add an index directly using an expression index [1] with json_extract().
I've used triggers to extract json data on insert. quite flexible and allows to store the whole JSON without overhead of inserting data twice.
It’s used that way in a lot of mobile applications. A really solid JSON store with easy query ability.
Shameless plug: Dflat wraps SQLite in similar ways for mobile applications: https://dflat.io/
Just 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.
Done! Thanks for the head-up.
Thanks for posting this! Very interested in Realm-like solutions.
Thanks! 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).
Yes 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.
Unfortunately 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).
SQLite 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.
The 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.
Do 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
Looks really cool, albeit a little bit NOSQL everywhere!
If you enjoy python and wanna try a object database check out ZODB with relstorage (now supports SQLite).