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
Next video (8)
Introduction
The documentation

Full Course

$
179
$229
USD, one-time fee
High Performance SQLite has been the single best pragmatic guide for SQLite on the Internet. This course is a must have for all developers at any stage in their DB journey.
Tony Mannino
Tony Mannino

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

Explore why the SQLite documentation is one of its strongest assets, offering clear, well-organized guidance for users at any level. You’ll learn how to navigate the docs effectively and see why features like railroad diagrams make understanding SQL query structure much easier. If you run into issues, the documentation is often the best first place to look for reliable answers.

Links

SQLite Documentation

Video Transcript

As we close out this introduction module, I wanna talk about one last thing and that's the SQLite documentation. Y'all, it is it is very good. I've got it right here. I've got most of it right here. Not not all of it, but most of it.

The documentation is very good. I wanna encourage you to go to the docs first when you have some sort of issue. After you go to the docs you can go, I don't know where people go these days, presumably just chat gpt, but check the docs first. They're very thorough, very well written, and they're written in sort of like a conversational tone, and so they're very easy to read. One thing that I found super useful in the docs is what they call railroad diagrams, and I'm gonna show you one right here.

This is a railroad diagram for a select statement. The way that you read these is you just kind of follow the arrows. We're gonna start with select and then you see you've got 3 different spots you can go to. You can say select distinct or select * or you could go all the way over to select result column. This result column is actually, it's a railroad diagram in and of itself.

I'll show you that one here in a second. But you can see select result column makes a loop with a comma and you can go back and do more and more and more result columns. You can enter this loop here until you're done and then come down here and this would lead you to from table or subquery and join clause. Let's scroll down a little bit and look at this result column. This little result column is down here and you can see that is actually a railroad diagram in itself.

It has an expression as a column alias or a star or table name dot star and that is the end of that little sub diagram. That's the one that runs in circle over and over and over. If you're looking for the definitive source of truth on what all can go in, a SQL query, what what is the universe of possibilities? And once you know it, what's the syntax? How do I figure out what the syntax is?

Come here, check these out. We're gonna be looking at these a lot as we go and so I wanted to give you a little primer on these. This is really good. This is high quality documentation and, honestly, I'm pretty grateful that they put the time in to write this. Check these out, and I'll try to teach you everything I can, but the docs are gonna be more thorough.