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
Locked video

Please purchase the course to watch this video.

Video thumbnail
Schema
Booleans

Full Course

$
179
USD
plus local taxes
Fantastic! So much packed into one course. Full-text search was a cherry on top of what's possible with SQLite. The course opened my mind to completely new ideas, and gave me perspective on databases in general.
Przemyslaw Figura
Przemyslaw Figura
TursoTurso

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

In SQLite, I can store boolean values as integers, using 1 for true and 0 for false. Creating a table with a boolean column as an integer allows me to easily insert and query true and false values. By using ones, zeros, or the keywords true and false, I can effectively manage boolean data without any issues.

Video Transcript

Not having a boolean type is not a problem. You can just store it all as an integer. SQLite understands select true, that's gonna give us 1, and select false, that's gonna give us 0. So we can say, create table example and we can put boolean as an integer. And if we were to insert into example values 1 and true and 0 and false, we should be okay.

Select * from example. There you go. It it will transform it into an integer and you can use those keywords if you want. Select * from example where bool equals true. Select * from example where bool equals 1.

Totally fine. Not a big deal. If you need to store boolean values just declare it as an integer column, then use ones or zeros or true or false. Either way, you're gonna be fine.