High Performance SQLite Video Course By Aaron Francis
Last night, I finished the High Performance SQLite video course by Aaron Francis. It is one of the best video courses that I've taken to date. Aaron strikes the perfect balance between density of content, depth of discussion, levity, and flow. And the quality of the video production is outstanding—this is obvious from the very first video; and, remains consistent through to the end. Using a distraction-free staging context, Aaron delivers the information in bite-size morsels that are easy to consume. If you're interested in learning more about SQLite, I highly recommend this course.
One excellent aspect of the course is the fact that Aaron spends a great deal of time talking about how indexes work; the implications of compound index column ordering; and, how query performance is heavily affected by index structuring. For a long time in my career, I was confused about database index design (see my article, The not-so-dark art of designing database indexes); and, I'm excited that novice developers are going to have access to a course like this, regardless of whether or not they end up using SQLite. Building a solid mental model for index design is one of the most valuable things you can do as a web developer.
Superficially, I loved that this course was presented in "light mode"—black text on a white background. I know that we developers love to work in "dark mode"; but, I strongly believe that light mode is the optimal mode for presentations. And, in this case, it absolutely brought an increased clarity to the experience.
One thing that was lacking (or perhaps I just didn't see it) was an ability to select the playback speed of the video. In cases like this, I normally use a bookmarklet to increase the playback programmatically:
document.querySelector( "video" ).playbackRate = 2;
Unfortunately, the <video> element is in the shadow DOM of a custom element; so, my bookmarklet wasn't able to query for it. But, I was able to inspect the video using my browser's developer tools; and then adjust the playback speed on the "last selected element" in the DOM (via $0):
$0.playbackRate = 2;
This turned out to be a fine solution because the page never actually reloads. When you finish one video and advance to the next video, the content is loaded via AJAX and the page is rerendered dynamically. Which means that my playbackRate setting remained in place for the duration of the course.
This course covers SQLite specifically; but, the content in many parts is widely applicable in any SQL context. Aaron even makes it a point to call out places in which SQLite either diverges or converges with other SQL platforms. As such, this course is great if you want to learn about SQLite; but, it's also a value-add if you're new to SQL and are just looking for a great primer.
The Mostly Technical Podcast
As an side, Aaron Francis also co-hosts a podcast called Mostly Technical with Ian Landsman. On the show, they discuss many things including the business-side of building video courses (among other types of applications). It's a very entertaining podcast and provides a behind-the-scenes look at web development. I highly recommend if you enjoy good, technical banter.
Reader Comments
Hi Ben
I used to use SQLite as an objective-c developer, when I was building iOS apps for about 10 years.
Although I liked the super portability of the DB, I found that its ability to handle data types, pretty limiting.
As I remember, it only had about 3 different data types? But maybe things have improved in this area? I stopped building iOS apps, several years ago, because I got hacked off with how locked in, the Apple Development system is. When Apple says jump, you have to jump straight away. 🤭
@Charles,
I think it's still quite limited in how many data times there are. In the course, Aaron listed them as:
It also has JSON functions; but, the data is ultimately stored as either text or as blob depending on which JSON functions you call (json vs. jsonb).
The data migration story also seems a little complicated in that there needs to be a lot of creating shadow tables, copying data, dropping, and renaming tables. I've done a lot of that stuff with MySQL, but it was a necessity to maintain uptime in the app (not a constraint of the database). In this case, it seems like it's a constraint of the database.
I think one really cool use of SQLite would be for data export. I could imagine an application giving a user the option to export all their data as a
.dbfile.On a related note, I just took Aaron's Francis' "Mastering Postgres" video course and wrote my review:
www.bennadel.com/blog/4740-mastering-postgres-video-course-by-aaron-francis.htm
At the time of the writing, the course isn't 100% complete, most notably the "popular extensions" module isn't pending.
In the SQLite course, Aaron mentions something called "row values syntax" for comparing multiple column values simultaneously. I had never seen this before and just assumed it was a SQLite feature. But then, he mentioned it once again in his Mastering Postgres course. It turns out, this is a more common feature of SQL; and also exists in MySQL as the "row constructor" syntax:
www.bennadel.com/blog/4741-using-row-constructor-comparisons-in-mysql.htm
Post A Comment — ❤️ I'd Love To Hear From You! ❤️
Post a Comment →