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
Operating SQLite
How to corrupt SQLite

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 talked about how important it is not to delete specific files like the "-wal" file in SQLite databases because they contain important data. I recommended using approved backup methods when moving or copying database files to prevent corruption, and to be cautious of other processes that might be writing to the database. I emphasized that directly writing to the database file is a bad idea and that following these rules can help keep your database safe.

Video Transcript

Okay. Wrapping up this module, I've got a quick one for you and this is how to corrupt your database so that hopefully you don't corrupt it. First things first, do not ever delete the dash wall file. There's a good chance that if you see a dash wall file, it's got data in it so don't delete it. That is a hot journal that hasn't yet been committed to the dot SQLite or or rather the main database.

It hasn't yet been committed And so if you delete that file because you're like, well SQLite, it's all stored in in one file. Yeah. Except for the wall. So, you're gonna have a bunch of data in that dash wall, don't delete it. The dash shm, should you see that hanging around, that's just shared memory between processes and that's how these processes, can coordinate to find things faster.

They use this shared memory file. Way less important. Both of those files should go away when the last connection is closed. So they'll be there and then they won't be there. That is how it's supposed to work.

Do not do not delete it. Do not move one without the other. And in fact, if you're going if you're going to move or copy the files, you better be sure that no one else is doing anything with those files particularly writing because that's how you could end up with a corrupted database. So if you need to if you need to move or copy them, you can start a, an immediate transaction. You can start an immediate transaction.

That's gonna block everybody else from writing and then you can copy. I would recommend using one of the blessed backup methods because those are gonna be a little bit safer and more robust and make me feel a little better. But as long as you're sure that nobody is touching the database and you take the main file and the wall file should it exist, you're fine. It's very portable in that regard, just don't do it wrong. The other way would be write directly to the database file.

It is just a file and if you're brave, you could try to write directly to it. I wouldn't. Don't do that. That's a terrible idea. Do not open it up and write directly to it.

Use a proper approved tool for writing to your SQLite database. Most of the other ways to corrupt your database you're not really gonna run into. Most of the other ways are bad file systems or poor locking implementations that can get you host pretty quickly if 2 two processes start writing at once. You are straight out of luck. That's bad news.

But most of the time, that is just not going to be an issue. There's a very, very, very, very outside, possibility that you are using a file system with a port locking implementation. I just don't think that's really a problem these days. The main problem is somebody looking and seeing a dash wall and thinking, I didn't put that there, I'm gonna delete that. Don't, don't do that.

Beyond that, it's pretty robust. It's been around for a long time. They've tested and tested and tested it. You should be okay as long as you abide by those rules.