Aaron Francis has done it again with High Performance SQLite! You'll learn how to make SQLite fast and some important database fundamentals. There's a ton of lifetime value you won't want to miss out on!Austin Karren
Build with and integrate SQLite into your production applications.
I've made my test data available for you to use and follow along.
One of my favorite things that SQLite has done is they've added support for JSON5, which honestly makes no sense to me and goes totally against the philosophy that I thought SQLite stood for, which is like small, minimal, no extra features. Fast-fast-fast. And then they went and added support for JSON5, which I love, but it doesn't make a ton of sense to me. If you're not familiar with JSON5 it is an alternative JSON spec that allows for more affordances for the human. So you can have comments and white space and unquoted keys and that sort of thing. And SQLite supports it as a type of input, but it doesn't preserve it and it doesn't output it. Okay, so if we were to do this and we'll say select JSON, we're gonna open it up and open up an object and then we'll come down here and we can put in a comment, which if you have used JSON anywhere else ever, you're like, "This is not gonna work". But this is JSON5. So we can come down here on a new line and we can do unquoted and say, "Whoa", I can't believe that works. We can also add a trailing comma just for giggles. And then let's go ahead and close it out, close it out, close it out and see what happens. There you go. So it fully supported JSON5 as an input, but this JSON function does convert it to real JSON, whatever that is. I forget the ISO or the ECMA script or the whatever it is, but it converts it to real JSON and it compresses it. So if you have extra white space in there, it's gonna remove the extra white space. It removed the comment and it removed the trailing comma. One other thing that you can do with JSON5 which I don't know if this will ever be useful, you can say number and you can type in capital I, Infinity, and that is valid JSON5. Of course, it converts it into something that is valid JSON, but that is another affordance that is available to you. I will leave a link down in the description because there are maybe 12 different things that SQLite supports when it comes to JSON5. And this can be, listen this, this can be really helpful if you already have JSON5 or you have something that provides a little bit more flexible JSON. I wouldn't hand write it this way. I personally would hand write honest to goodness JSON, because that's what it's gonna end up being. And I don't want to make a mistake and I don't want to write a bunch of stuff that I think is important that's just gonna get stripped away by SQLite. SQLite makes one promise about malformed JSON, and the promise that it makes about malformed JSON is that it will never crash SQLite. You may get errors, you may get unexpected results back, but they guarantee that it's never going to crash. And so you can pass it in JSON5 to any of the functions that accept JSON, and it will be converted into honest to goodness JSON.