SQLite 3.42.0
sqlite.org
sqlite.org
Wait, what's JSON5??
> Object keys may be unquoted identifiers.
> Objects may have a single trailing comma.
> Arrays may have a single trailing comma.
> Strings may be single quoted.
> Strings may span multiple lines by escaping new line characters.
> Strings may include new character escapes.
> Numbers may be hexadecimal.
> Numbers may have a leading or trailing decimal point.
> Numbers may be "Infinity", "-Infinity", and "NaN".
> Numbers may begin with an explicit plus sign.
> Single (//...) and multi-line (/.../) comments are allowed.
> Additional white space characters are allowed.
Oh crap, the levees have broken!
You might as well ask what native numeric or boolean support offer over just jamming stuff in a string in an agreed format. Some might argue that dates are a compound value so differ from atomic types like a number, but they are wrong IMO as a datetime can be treated as a simple numeric¹ with the compound display being just that – a display issue.
Others will point out that JS doesn't have a native date/datetime/time type, but JSON is used for a lot more that persisting JS structures at this point.
--
[1] caveat: this stops being true if you have a time portion with a timezone property
A big difference is that a numeric or a boolean are quite limited datatypes with agreed upon semantics (mostly).
> Others will point out that JS doesn't have a native date/datetime/time type
It does, in fact. And it's absolute shit.
> but JSON is used for a lot more that persisting JS structures at this point.
So what I'm reading here is that you don't need types to be supported natively in order to serialize to JSON.
Maybe it is "just us", maybe it is part of the general shitness you point out, maybe it is the lack of literal representation (even VB and relatives had one) other than an ISO8601 string, but dates don't feel native like simpler types, arrays, objects, fictions, …
> So what I'm reading here is that you don't need types to be supported natively in order to serialize to JSON.
Yes. But not absolutely needing something does not mean it isn't (or wouldn't be) exceptionally useful to have.
Huh, TIL.
Probably more importantly, all of that and still not proper datetimes in sqlite.
Also even more so no domains (for custom datatypes).
Home Assistant recently did a ton of changes to work around the issues caused by this.
The short story is that they stopped storing timestamps as 'timestamp' datatypes and started storing them as unix times stored in numeric columns. Since timestamps turn into strings in SQLite, this was a huge improvement for storage space, performance, etc.
The problem is that this change also affects databases which have a real datetime datatype. So PostgreSQL, which internally stores timestamps as unix times, is now being told to store a numeric value. To treat it as a timestamp you have to convert it while querying. Since I used PostgreSQL for my Home Assistant installation, this feels like a giant step backwards for me.
I wish that they had used this change as an opportunity to refactor the database code a bit so that they could store timestamps as numeric for SQLite, but use a real timestamp datatype for MySQL and PostgreSQL. I'm sure that this isn't a simple thing to do though.
It also belongs in SQLite.
Most of the "popular" publicly available formats at the time were singularly worse, even ignoring commonly limited or inconvenient language support.
SOAP? ASN.1? plists? CSV? uuencode? I'll still take JSON over all of them, especially when it comes to sending shit to the browser (plists might be workable with a library isolating you from it, but it is way too capable for server to browser communications, or even S2S for that matter not all languages expose a URL or an OSet type).
> it was especially natural & fast to serialize/deserialize in JavaScript, on account of being a subset of that language.
That is certainly a factor, specifically that you could parse it "natively", initially via eval, and relatively quickly[0] via built-in JSON support (for more safety as the eval methods needed a few tricks to avoid full RCE).
But an other factor was almost certainly that it's simple to parse and the data model is a lower common denominator for pretty much every dynamically typed language. And you didn't need to waste time on schemas and codegen, which at the time was a breath of fresh air.
> XML also had the "fast" going for it thanks to browser APIs, but not so much the "natural"
XML never has "fast" going on in any situation, the browser XML APIs are horrible, and you had to implement whatever serialization format you wanted in javascript over that, so that was even slower (especially at a time when JS was mostly interpreted)
[0] compared to the time it started being used: Crockford invented / extracted JSON in 2001, but services started using JSON with the rise of webapps / ajax in the mid aughts, and all of Firefox, Chrome, and Safari added native json support mid-2009
Everyone just wrote their own custom clients in code instead :) But it was a gradual process, so it's harder to notice the pain compared to some generator slamming a load of code in your project. coughgRPCcough.
No, people couldn't just eval random strings, especially the ones containing potentially malicious user input. They started writing parsers like any other language, then came the global JSON.parse and .stringify per spec.
I can't say for sure but I doubt JSON being a JS subset helped at all.
hehehehehe
> I can't say for sure but I doubt JSON being a JS subset helped at all.
Oh it very much did, both because it Just Worked in a browser context, and because the semantics fit dynamic languages very nicely, and those were quite popular when it broke through (it was pretty much the peak of Rails' popularity).
Tried several "json replacement" formats (bson, cbor, msgpack, ion...) and json5 won by being the smallest after compression (on my data) while also having the nice bonus of retaining human-readability.
Ensuring that comments and their locations in the text are preserved by processors is quite difficult if not impossible. Reformatting a JSON text alone can "break" comments by not necessarily placing them where they belong. Any schema transformation means comments must be dropped.
{
// start
x: "y",
// the following is foo
foo: "hey",
// the following is bar
bar: "there"
// end
}
If I reformat to change indentation, should I change the indentation of the comments too? How would the comment that reads "the following is bar" be re-indented?
What if there are duplicate names? JSON does allow duplicates. If I attach comments to preceding (or following) names, then while parsing I find a dup... what should I do with the preceding comment and the new name?
As far as indentation goes, if I were writing a pretty printer, I’d make indentation handled by the object/array node not by the comment/kv pair node. So that doesn’t matter: the comment will start printing at whatever the current column is just like the kv pairs would.
Anyways, among other tools, prettier has already solved this problem.
Many implementations parse objects into hash tables and lose duplicates and even order of appearance of name/value pairs, and all of that is allowed. Such implementations will not be able to keep relative order of comments.
Comments were considered an anti-feature by Douglas Crockford, the creator of JSON:
> I removed comments from JSON because I saw people were using them to hold parsing directives, a practice which would have destroyed interoperability. I know that the lack of comments makes some people sad, but it shouldn't.
> Suppose you are using JSON to keep configuration files, which you would like to annotate. Go ahead and insert all the comments you like. Then pipe it through JSMin before handing it to your JSON parser.
* https://web.archive.org/web/20120507093915/https://plus.goog...
It always made perfect sense, given JSON was invented as a data exchange format. It was never intended to be written out by hand.
> I find adding property like __pragma: nicer than comments. It has the same problem as he stated
It does not, because that is data which every JSON parser will at least parse if not interpret.
A comment is something a parser has no reason to yield, which means smuggling metadata in comments makes the document itself inconsistent between parsers which do and parsers which don't[0] expose comments.
And this was not a fancy Crockford made up, smuggling processing instructions in comments was ubiquitous in Java at the time, as well as HTML/XML, where it literally still is a thing: https://en.wikipedia.org/wiki/Conditional_comment.
[0] and possibly can't, how do you deserialize a comment in the middle of an object to JS or Ruby
Feel free to create your own data interchange file format. (Perhaps with Blackjack and hookers. :)
> You can still add pragmas trivially in JSON, this doesn't prevent that problem, it just makes it more convoluted at the expense of a powerful documentation feature.
Computers/systems do not need comments to exchange data between themselves, and that is the problem JSON is trying to solve: data exchange.
If you're using it for configuration and other human-centric tasks, and wish to communicate human-centric things in the file, you're trying to fit a square peg into a round hole. Use some other format that was designed with those things in mind.
If you're saying the designers of a hammer didn't think of being able to handle Robertson screws you're unfairly critiquing the tool.
This is such a lazy argument to make. We are both allowed to criticize flaws in data exchange formats. As far as what JSON is trying to solve, it's far beyond that, and the creator knew this (which is why he went out of his way to fight it). If you as a human have ever debugged anything dealing with a json handling, you've already proven that comments can have value.
Hmmm... It's super convenient that SQL uses single quotes for string literals while JSON uses double quotes. Changing that is going to cause pain.
> > Strings may span multiple lines by escaping new line characters.
I really can't recommend this. Yeah it's annoying to have to write \n, but still.
Surely you're not constructing queries by concatenating JSON to text?
> I really can't recommend this. Yeah it's annoying to have to write \n, but still.
I can only disagree, the ability to just put newlines in a string literal in languages like rust is refreshing.
Of course not, but I do have code that generates SQL and which uses JSON. (And no, the code in question is not subject to SQL injection.)
> JSON text generated by [JSON] routines will always be strictly conforming to the canonical definition of JSON.
We can now have different parsers have pragmas that specify different behaviour depending on whether those pragmas are recognized or not.
In case anyone was wondering, the history is that comments were considered an anti-feature by Douglas Crockford, the creator of JSON:
> I removed comments from JSON because I saw people were using them to hold parsing directives, a practice which would have destroyed interoperability. I know that the lack of comments makes some people sad, but it shouldn't.
> Suppose you are using JSON to keep configuration files, which you would like to annotate. Go ahead and insert all the comments you like. Then pipe it through JSMin before handing it to your JSON parser.
* https://web.archive.org/web/20150105080225/https://plus.goog...
The problem is that people have started using JSON for configuration files and the like, which IMHO has always been – and continues to be – the wrong tool for the job.
And XML remained dominant for a long time— for example, the original Google Maps from 2005 received its server responses as XML blobs, and API v2 even exposed the relevant parsing functionality as the GXml JavaScript class. By around 2007, it was all JSONp I think, and GXml was deprecated and removed in API v3 and v4 respectively.
And editor will rightfully mark them as errors.
Every problem can be solved by introducing a build step, except for the problem of having too many build steps.
Any arguments against this one? My knee jerk reaction is 'yay'
JSON isn't English. The comma doesn't mean what it does in English, so I don't see why a period would be appropriate either.
Useless from the perspective of a wire format, but nice for things like config files, which seem to be the use cases json5 is targeting.
[0] Bullet pt 3: https://betterprogramming.pub/5-essential-takeaways-from-the...
I guess it facilitates low effort scripting that doesn't have to treat the last element differently.
It turns out that there is a lot of "JSON" data in the wild that is not pure and proper JSON, but instead includes some of the extensions of JSON5. The point of this enhancement is to enable SQLite to read and process most of that wild JSON.
This feature was requested by multiple important users of SQLite.
Off topic: would you mind sharing any info on potential timing of begin-concurrent-pnu-wal2 branch being merged into main (or consideration of forking sqlite to have a "client/server" version)?
https://www.sqlite.org/src/timeline?r=begin-concurrent-pnu-w...
(Love what you have created. Thank you so much for all the years of amazing work)
Curious, would you recommend using this bedrock branch?
Why / why not?
I appreciate that SQLite can’t write the format, because those changes are human afordances
-- EDIT: mainly back when we started using JSON to configure, well, lot of things.
ALTER TABLE "foo" ADD FOREIGN KEY...They don't store the parsed representation of the schema, since that would lock the table to a specific version. They store the create command itself, and each time you open a DB the schema is generated from that. Allows for flexible upgrades to the schema system without the need for migrations, and lets you use the same DB file with multiple sqlite versions.
The downside is that alter table commands are just edits to the create table string, which when accounting for all the different versions is fairly difficult and risky, so alter operations are limited.
It doesn't really jibe all that well with things like Python though, where you typically don't do that. But it's certainly not a "foreign concept" or "almost meaningless".
For e.g. Python you can still just set the desired parameters when connecting, which should usually be handled by the database connector/driver for you.
SQLite is both.
Setting a compiler flag is reasonable for the common case where it is included as a header file in a C/C++ application.
It is certainly inconvenient when you design with that feature in mind, but can't rely on it being part of the standard binary distributions.
Even using SERIALIZED mode, sqlite has multiple APIs which are completely broken if two clients touch the same connection (https://github.com/rusqlite/rusqlite/issues/342#issuecomment...).
Don't bother, just don't share connections between threads and use the regular multi-thread mode (do use that though).
You can use a connection pool and move connections from task to task (and thread to thread), just not use connections concurrently.
Ideally, all of the above would be covered by a library, and not up to the app developer.
I wrote a blog post covering some of this recently: https://www.powersync.co/blog/sqlite-optimizations-for-ultra...
https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf
“To accelerate searching the WAL, SQLite creates a WAL index in shared memory. This improves the performance of read transactions, but the use of shared memory requires that all readers must be on the same machine [and OS instance]. Thus, WAL mode does not work on a network filesystem.”
“It is not possible to change the page size after entering WAL mode.”
“In addition, WAL mode comes with the added complexity of checkpoint operations and additional files to store the WAL and the WAL index.”
https://www.sqlite.org/lang_attach.html
SQLite does not guarantee ACID consistency with ATTACH DATABASE in WAL mode.
“Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.”
Inefficient queries, no reader gaps and a bad manual checkpointing system can bring the system down.
1. Queue up writes to a single connection in a given thread.
2. Retry the write after a short sleep when you get a SQLITE_BUSY error. SQLite will do this for you, see busy_timeout docs.
WAL is single write, multiple reader, so if you are doing SELECT queries they should not return SQLITE_BUSY
Just to clarify, busy_timeout = 0 by default