skip to content

SQLite

SQLite is a library, not a server: the whole database is one file opened inside your process. Interviews probe its typing, file format, single-writer model and limits.

on this pageshow

explore

questions

page 2 of 2

Should a mobile app rely on the platform's bundled SQLite or ship its own SQLite build, and how would you decide?

level: principalimportance: should knowfreq 25%

basics

~20 s

Use the bundled SQLite when the oldest supported device's version and build options cover your features; ship your own when you need newer features or uniform behaviour, accepting the app size, the patching duty and the one-copy-per-process rule.

open as a page

In SQLite, when does declaring a table WITHOUT ROWID speed up lookups, and what do you give up by doing it?

level: middleimportance: nice to knowfreq 24%

basics

~20 s

WITHOUT ROWID stores a table as one B-tree keyed by its primary key, so a lookup on a non-integer or composite key takes one search instead of two. It suits small rows and costs AUTOINCREMENT, rowid functions and the update hook.

open as a page

What is inside a SQLite database file: what does its 100-byte header record, and how are tables and indexes stored in its pages?

level: middleimportance: nice to knowfreq 22%

basics

~20 s

The file is a run of equal-size pages, 4096 bytes by default since 3.12.0. Page 1 opens with a 100-byte header (magic string, page size, counters, freelist head) and roots sqlite_schema, which records the root page of every table and index B-tree.

open as a page

What is SQLite's VFS layer, and when would you choose or write a VFS other than the platform default?

level: seniorimportance: nice to knowfreq 22%

basics

~20 s

The VFS is SQLite's operating-system interface: every file open, read, write, sync and lock goes through it. The defaults are unix and win32; alternatives change locking, add tracing or quotas, or put pages somewhere that is not an ordinary file.

open as a page

In SQLite, a join of contacts c and call_log l shows `SEARCH l USING AUTOMATIC COVERING INDEX (phone=?)`; what does that mean, and what should you change?

level: seniorimportance: nice to knowfreq 28%

basics

~20 s

SQLite found no index on call_log.phone, so for this one statement it builds a transient index, uses it for the join, and discards it. The plan works but pays the build cost on every run; a persistent index on call_log(phone) is the fix.

open as a page

showing 31–35 of 35