High Performance
SQLite
Introduction
Introduction to this course
Introduction to SQLite
SQLite is safe
It's just a file
Running SQLite locally
Good uses for SQLite
SQLite's limitations
The documentation
SQLite Internals
SQLite's structure
SQLite's file format
Dot commands
Pragmas
Virtual tables
Schema
Flexible types
Types
Strict types
Dates
Booleans
Floating point
Rowid tables
Auto increments
Without rowid
Generated columns
Optimizing SQLite
Locking
Rollback mode
WAL mode
WAL vs Journal benchmarks
Busy timeout
Transaction modes
Vacuum
Analyze & Optimize
Suggested pragmas
Faster inserts
Indexes
Introduction to indexes
B+ trees
Primary, secondary, and clustered indexes
Without rowid tables
Benchmarking without rowid tables
Imposter tables
Primary key data types
Where to add indexes
Index selectivity
Composite indexes
Composite ordering
Covering indexes
Partial indexes
Indexes on expressions
Automatic indexes
Duplicate indexes
Indexing joins
Advanced SQL
Explain
Index obfuscation
Joins
Subqueries
Unions
CTEs
Recursive CTEs
Window functions
Dealing with NULLs
Row value syntax
Indexed sorting
Upserts
Returning
Aggregates
Triggers
Operating SQLite
Altering schema
Altering schema with tools
Multi-database
Multi-tenancy
Backups
Exports
How to corrupt SQLite
Advanced JSON
Intro to JSON
JSON vs. JSONB
Argument types: path, value, JSON
JSON5
Valid JSON
Creating JSON objects + arrays
JSON Extraction
Updating JSON
JSON aggregates
JSON table functions
Indexing JSON
Full Text Search
Creating FTS tables
Performing searches
Ranking results
Highlighting matches
Bonus Interviews
DHH discusses SQLite in Ruby on Rails
Distributed SQLite with Litestream and LiteFS
Offline-first, multiplayer SQLite
Production SQLite with Turso and libSQL
Migrating from Postgres to SQLite with Kent C. Dodds
Ruby on Rails with SQLite
Moving from Redis to SQLite with Mike Buckbee
Locked video

Please purchase the course to watch this video.

Video thumbnail
Advanced JSON
JSON5

Full Course

$
179
$229
USD, one-time fee
Thank you for the excellent High Performance SQLite course! It’s been incredibly useful in deepening my understanding of SQLite. The sections on SQLite internals, schema, and optimization techniques are particularly insightful and have significantly improved my ability to work with SQLite in production environments.
Ali Kasmani
Ali Kasmani

SQLite for Production

Build with and integrate SQLite into your production applications.

Test Data

I've made my test data available for you to use and follow along.

Download
or
Use on Turso

Summary

I recently learned that SQLite now supports JSON5, which surprised me because SQLite is known for being simple and fast without extra features. JSON5 allows for human-friendly features like comments and unquoted keys, although SQLite only supports it as input and converts it to regular JSON. This flexibility can be helpful if you're using JSON5, but I still prefer writing standard JSON to avoid losing important details when SQLite strips away things like comments.

Links

JSON5 Documentation

Video Transcript

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.