High Performance SQLite

1 Introduction to this course
2 Introduction to SQLite
3 SQLite is safe
4 It’s just a file
5 Running SQLite locally
6 Good uses for SQLite
7 SQLite’s limitations
8 The documentation
9 SQLite’s structure
10 SQLite’s file format
11 Dot commands
12 Pragmas
13 Virtual tables
14 Flexible types
15 Types
16 Strict types
17 Dates
18 Booleans
19 Floating point
20 Rowid tables
21 Auto increments
22 Without rowid
23 Generated columns
24 Locking
25 Rollback mode
26 WAL mode
27 WAL vs Journal benchmarks
28 Busy timeout
29 Transaction modes
30 Vacuum
31 Analyze & Optimize
32 Suggested pragmas
33 Faster inserts
34 Introduction to indexes
35 B+ trees
36 Primary, secondary, and clustered indexes
37 Without rowid tables
38 Benchmarking without rowid tables
39 Imposter tables
40 Primary key data types
41 Where to add indexes
42 Index selectivity
43 Composite indexes
44 Composite ordering
45 Covering indexes
46 Partial indexes
47 Indexes on expressions
48 Automatic indexes
49 Duplicate indexes
50 Indexing joins
51 Explain
52 Index obfuscation
53 Joins
54 Subqueries
55 Unions
56 CTEs
57 Recursive CTEs
58 Window functions
59 Dealing with NULLs
60 Row value syntax
61 Indexed sorting
62 Upserts
63 Returning
64 Aggregates
65 Triggers
66 Altering schema
67 Altering schema with tools
68 Multi-database
69 Multi-tenancy
70 Backups
71 Exports
72 How to corrupt SQLite
73 Intro to JSON
74 JSON vs. JSONB
75 Argument types- path, value, JSON
76 JSON5
77 Valid JSON
78 Creating JSON objects + arrays
79 JSON Extraction
80 Updating JSON
81 JSON aggregates
82 JSON table functions
83 Indexing JSON
84 Creating FTS tables
85 Performing searches
86 Ranking results
87 Highlighting matches
88 DHH discusses SQLite in Ruby on Rails
89 Distributed SQLite with Litestream and LiteFS
90 Offline-first, multiplayer SQLite
91 Production SQLite with Turso and libSQL
92 DHH discusses SQLite in Ruby on Rails_2
93 Ruby on Rails with SQLite