In-a-nutshell

• An in-process library -> not a server
• A small C library commonly distributed as a single amalgamation source file
• Database is a single file
• can contains 1000s of tables, indexes and views
• space efficient
• Full-featured SQL
• Power-safe serializable transactions
• Fast
• Simple API
• Public domain

"Lite" means "low overhead", but not "low capability"

• One writer at a time; readers can run concurrently, with WAL mode improving read/write overlap
• 1 GB strings and BLOBs
• 281 TB databases
• 64-way joins
• 2000 columns per table or index
• No arbitrary limits on numbers of:
• tables
• indexes
• rows in a table

Not for

• Remote data
• Big data
• Concurrent writers

SQLite is Found in many places (but not limited to)

source

• every ios and androind phone and device
• every mac and windows 10/11 computer
• every firefox, chrome and safari browsers
• most desktop and phone software applications
• TVs, smart appliances, automotive "infotainment" systems
• countless millions of other applications...
• more and more of websites...

Implementation

Interface

https://youtu.be/ZSKLA81tBis?t=1104

Ins and Outs

• Compiler -> translates SQL into bytecode
• Virtual machine -> runs the bytecode

+-----+      +---------------------------+     +--------------------+     +-----------------+     +--------+
| SQL | -->  | compiled SQL into program | --> | prepared statement | --> | run the program | --> | result |
+-----+      +---------------------------+     +--------------------|     +-----------------+     +--------+

             \___________  ______________/                                \_______  _______/
                         \/                                                       \/
                 `sqlite3_prepare()`                                       `sqlite3_step()`

The SQLite Stack

+--------+          -+
| Parser |           |
+--------+           |
     |               |
     v               |------ `sqlite3_prepare()`
+----------------+   |
| Code generator |   |
+----------------+   |
     |              -+
     v
+-----------------+
| Virtual machine |
+-----------------+
     |
     v
+--------+         -+
| B-Tree |          |
+--------+          |
     |              |
     v              |
+--------+          |
| Pager  |          |------- Storage Engine
+--------+          |
     |              |
     v              |
+--------------+    |
| OS Interface |    |
+--------------+    |
                   -+

Parser

• LALR(1) parser generated by "Lemon"
• Reentrant and threadsafe
• Output: is an AST
• Hand-written tokenizer

Code generator

• Semantic analysis
• AST transformations
• Query planning
• Output: prepared statement (bytecode)

Virtual machine

• bytecode interpreter
• 3-address, 5-operand register machine
• Bit switch statement on the opcode and inside a loop
• Special opcodes designed to help implement SQL statements

Storage Engine Summary

• Row store
• Variable length entries
• Forest of B-trees
• 1 b-tree for each table and each index
• table key: PRIMARY KEY or ROWID
• index key: indexed columns + table key
• Transaction control using "rollback-journal" or "write-ahead-log" (WAL)

B-Tree

• B Trees and B+ trees
• multiple B trees per disk file
• variable length entries
• free page tracking and reuse
• access via cursor
• concurrent read/write of the same table using separate cursors

Pager

• atomic commit or rollback
• power safe transactions
• rollback mode or WAL mode
• uniform size pages numbed from 1
• 512 or 65536 bytes per page
• no interpretation of page content
• cache
• concurrency control

OS Interface (os_unix.c / os_win.c)

• platform specific interface to the OS
• runtime changeable
• portability layer
• read()/write() or mmap()
• https://sqlite.org/vfs.html
• direct i/o to hardware: test_onefile.c

VFS Shims

• inserted in-between pager and os interface
• encryption
• compression
• logging
• testing and fault injection
• ...