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).
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.
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.
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.
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.
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.
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()
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.
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.
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.
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.
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.
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 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.
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!
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.
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
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 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:
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.
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.
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
50 comments
[ 5.3 ms ] story [ 119 ms ] threadNow, a dedicated JSONB datatype (and indexes!) like Postgres would be awesome, but sqlite3's "poor man's JSON" seems appropriate for many use cases.
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.
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.
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.
[1]: https://simonwillison.net/2019/Jan/7/exploring-search-releva...
[1]: https://h3rald.com/litestore/
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.
Regarding indexing json... here's how I do it:
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.
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.
It is incredible.
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.
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.
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.
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.
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#...
I feel like the obvious answer is to just get the stable, released source from the SQLite web site and build it yourself.
[1]: https://sqlite.org/expridx.html