SQLite Interview Questions and Answers

Answer

SQLite is a self-contained, serverless, zero-configuration SQL database engine. The entire database, schema, data, indexes, triggers, lives in a single ordinary file on disk. Unlike Postgres or MySQL, there is no separate server process; SQLite is a library that your application links against and calls directly.

That means there is no network round trip, no authentication layer, no user management, and no daemon to keep running. Your reads and writes are just function calls that operate on a file. This makes SQLite ideal for embedded use cases (mobile apps, browsers, IoT devices) and for any application where the data only ever needs to be accessed by a single process at a time.

The trade-off: SQLite intentionally does not solve the problems that client/server databases solve. There is no high-concurrency parallel-write story, no built-in replication, no role-based permissions. For an iOS app storing local user data, this is irrelevant, the OS already isolates the file.

For a multi-tenant SaaS handling thousands of concurrent writes, you would pick Postgres instead. Roughly: SQLite is to Postgres what an in-process cache is to Redis, same data shapes, completely different operational model.

Answer

You do not 'create' a database the way you would in Postgres, opening a file path that does not exist creates an empty database file automatically. From there, you run standard `CREATE TABLE` DDL. The CLI (`sqlite3`) is shipped with most operating systems.

A detail that trips people up: nothing is written to disk until you create the first object. Run `sqlite3 new.db` and immediately `.quit` and you get a zero-byte file, because SQLite defers writing the 100-byte header until there is a schema to describe. That header is where `page_size`, the text encoding, the journal mode and the `user_version` integer used for migrations actually live, and you can dump it without running a query using the `.dbinfo` meta-command.

The same create-on-open rule applies from application code: `sqlite3.connect('app.db')` in Python and `new Database('app.db')` in better-sqlite3 both create the file if it is missing. This is the single most common cause of 'my data disappeared' bug reports in production, because a wrong working directory or a typo in the path silently produces a fresh empty database instead of raising an error. Guard against it with the URI form: `file:app.db?mode=rw` fails with `SQLITE_CANTOPEN` when the file does not exist, while the default `mode=rwc` creates it.

Pass `?immutable=1` for a read-only database shipped inside your app bundle so SQLite skips locking entirely. On Android, Room and `openOrCreateDatabase` do the same thing under an app-private path, which is why a fresh install starts with an empty database and no error.

$ sqlite3 mydata.db
sqlite> CREATE TABLE users (
  ...>   id INTEGER PRIMARY KEY AUTOINCREMENT,
  ...>   email TEXT NOT NULL UNIQUE,
  ...>   created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  ...> );
sqlite> .tables
users
sqlite> .schema users
sqlite> .dbinfo

-- Fail loudly instead of creating an empty DB:
$ sqlite3 'file:mydata.db?mode=rw'

Answer

SQLite has only five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB. Notably, there is no native BOOLEAN, no DATE, no TIMESTAMP, no UUID, and no JSON type as a storage class. Booleans are stored as 0/1 INTEGERs.

Dates are stored as TEXT (ISO-8601), REAL (Julian day), or INTEGER (Unix epoch) and you pick the convention. SQLite also uses 'type affinity' rather than strict typing, a column declared as `INTEGER` will happily store a TEXT value unless you opt into the STRICT mode (added in 3.37). This dynamic typing is one of the biggest surprises for developers coming from Postgres.

The consequences show up in three predictable places. Dates: `ORDER BY created_at` over ISO-8601 TEXT works only because ISO-8601 happens to sort lexicographically, and it breaks the day someone inserts '12/05/2026'. Booleans: `WHERE is_active = TRUE` compiles only from 3.23 onward, when TRUE and FALSE were added as keywords aliasing 1 and 0, so older code and older engines need `= 1`.

Money: REAL is IEEE-754 binary floating point, so summing 0.1 and 0.2 does not give exactly 0.3, and payment reconciliation jobs built on REAL columns drift by paise over millions of rows. Store money as INTEGER paise. BLOB is the storage class people forget, and it is genuinely useful on mobile, because SQLite reads small blobs faster than the filesystem opens small files, which is why storing thumbnails inline usually beats storing file paths. The follow-up a senior interviewer asks is what `typeof(x)` returns: it reports the storage class of the value actually stored, not the declared column type, which makes it the fastest way to audit a legacy table for mixed types before a migration.

-- Audit a legacy column for mixed storage classes
SELECT typeof(age) AS t, count(*) FROM users GROUP BY t;
-- integer|48213
-- text|97        <- these break WHERE age > 18

-- Money as integer paise, never REAL
CREATE TABLE payments (id INTEGER PRIMARY KEY, amount_paise INTEGER NOT NULL) STRICT;
SELECT amount_paise / 100.0 AS rupees FROM payments;

SELECT typeof(1), typeof(1.0), typeof('1'), typeof(x'01'), typeof(NULL);
-- integer|real|text|blob|null

Answer

Type affinity is SQLite's loose typing system. A column has an 'affinity' (TEXT, NUMERIC, INTEGER, REAL, or BLOB) that influences but does not force the type of values you can insert. For example, a column declared `age INTEGER` will accept `INSERT INTO t (age) VALUES ('twenty')` without error, SQLite stores the string verbatim.

This is fine for prototypes but a real bug magnet in production: you can write `WHERE age > 18` and the comparison silently returns nothing when `age` is a TEXT value like 'twenty'. The fix: declare the table with `STRICT` (SQLite 3.37+) which enforces declared types and raises an error on bad inserts. Affinity is assigned by substring rules on the declared type name, not by a fixed list: anything containing 'INT' gets INTEGER affinity, anything containing 'CHAR', 'CLOB' or 'TEXT' gets TEXT, 'BLOB' or an empty declaration gets BLOB, 'REAL', 'FLOA' or 'DOUB' gets REAL, and everything else falls through to NUMERIC.

That last bucket is why `BOOLEAN`, `DATETIME` and `DECIMAL(10,2)` columns behave nothing like their names suggest. NUMERIC affinity also silently converts, so inserting the string '42' stores the integer 42 and the value you read back is not the value you wrote. The production failure mode is a join or lookup returning zero rows because one side holds TEXT '42' and the other INTEGER 42: SQLite never treats those as equal, and its type ordering places all INTEGER and REAL values before all TEXT values, so even `ORDER BY` output looks scrambled.

STRICT tables close the hole but restrict you to INT, INTEGER, REAL, TEXT, BLOB or ANY as declared types, which means `VARCHAR(255)` becomes a parse error and you cannot add STRICT to an existing table without a full rebuild. The senior follow-up is how you would find affinity damage in a 50 GB table already in production: a `typeof()` count per column, then a rebuild with STRICT plus CHECK constraints inside one transaction.

-- Loose typing (default)
CREATE TABLE products (id INTEGER, price REAL);
INSERT INTO products VALUES ('abc', 'free');  -- no error, both stored as TEXT

-- Strict typing (SQLite 3.37+)
CREATE TABLE products (id INTEGER, price REAL) STRICT;
INSERT INTO products VALUES ('abc', 'free');  -- ERROR: cannot store TEXT value in INTEGER column

Answer

Declaring a column `INTEGER PRIMARY KEY` makes it an alias for the table's built-in `rowid`, the internal 64-bit row identifier that every SQLite table has by default. This is faster than a normal primary key because no separate index is needed. If you also add `AUTOINCREMENT`, SQLite guarantees never to reuse a rowid even after deletes (it uses the `sqlite_sequence` table to track the high-water mark).

Most ORM-generated schemas omit `AUTOINCREMENT` for speed, since reusing rowids is harmless for the vast majority of apps. The keyword `AUTOINCREMENT` in SQLite is also case-sensitive in older docs but accepted case-insensitively by the parser. Three details separate people who have shipped this from people who have read about it.

First, the alias applies only to the exact spelling `INTEGER PRIMARY KEY`. Write `INT PRIMARY KEY`, `BIGINT PRIMARY KEY` or `INTEGER PRIMARY KEY DESC` and you get an ordinary indexed column plus a hidden rowid, two B-trees instead of one, and SQLite's long-standing bug-compatibility rule even lets that column hold NULL. Second, without AUTOINCREMENT a new row takes max(rowid)+1, so deleting the highest row and inserting again reuses its id.

If any external system has already seen that id (a mobile client cache, an analytics pipeline, a printed invoice number) you now have two different records sharing one identifier. That, not performance, is the real reason to pay for AUTOINCREMENT. Third, `WITHOUT ROWID` tables store rows directly in a B-tree keyed on the declared primary key, removing a level of indirection for tables with a natural TEXT or composite key.

The cost is that every secondary index then carries the full primary key as its pointer, so it is a bad trade when the key is wide. A common interview follow-up is whether to use an integer id or a TEXT UUID for a sync-heavy mobile app. The honest answer is both: an INTEGER PRIMARY KEY for compact local joins, plus a UUID column with a UNIQUE index for global identity across devices.

-- Fast and standard:
CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT);

-- Slower but guarantees monotonic IDs even across deletes:
CREATE TABLE posts (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT);

-- NOT an alias for rowid: creates a second B-tree and allows NULL
CREATE TABLE bad (id INT PRIMARY KEY, title TEXT);
INSERT INTO bad VALUES (NULL, 'oops');  -- accepted

-- Natural-key table with no rowid at all:
CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT) WITHOUT ROWID;

Answer

SQLite is fully ACID-compliant. You wrap statements with `BEGIN` / `COMMIT` (or `ROLLBACK` on error). If you don't explicitly open a transaction, every statement is implicitly its own transaction, which is correct but extremely slow for bulk inserts.

Wrapping 10,000 inserts in a single transaction takes milliseconds; the same inserts without a transaction can take minutes because each one forces an fsync. This is the #1 SQLite performance gotcha and the most common 'SQLite is slow!' complaint on forums turns out to be exactly this. SQLite supports three transaction modes: DEFERRED (default, lock acquired lazily when first write happens), IMMEDIATE (write lock acquired right at BEGIN, avoids deadlock-like races), and EXCLUSIVE (locks the entire DB so no other readers/writers either).

For concurrent-writer apps, BEGIN IMMEDIATE is strongly recommended to avoid the upgrade-deadlock pattern. Being able to describe that upgrade deadlock precisely is what separates a good answer from a memorised one. A DEFERRED transaction that opens with a SELECT holds a read lock; when it later issues its first UPDATE it must upgrade to a write lock.

If another connection already holds the write lock and is waiting on your read snapshot, SQLite cannot resolve the cycle, so it fails immediately with SQLITE_BUSY and ignores `busy_timeout` entirely (in WAL mode the specific result code is SQLITE_BUSY_SNAPSHOT, and the only fix is to roll back and retry the whole transaction). BEGIN IMMEDIATE takes the write lock up front, converting a failure into a wait. A second production detail: a statement that errors does not roll back the surrounding transaction, only an explicit ROLLBACK does, so an exception handler that logs and continues can happily COMMIT half a unit of work.

BEGIN cannot nest, so use SAVEPOINT and RELEASE for nested units. Drivers add another layer on top: Python's sqlite3 module historically opened transactions implicitly before INSERT, UPDATE and DELETE but not before DDL or SELECT, which is why a CREATE TABLE inside an apparent transaction commits early, and Python 3.12 added the `autocommit` attribute to make that behaviour explicit rather than magic.

-- Slow: 10,000 separate transactions, 10,000 fsyncs
for row in rows: cursor.execute("INSERT INTO logs VALUES (?, ?)", row)

-- Fast: one transaction
cursor.execute("BEGIN")
for row in rows: cursor.execute("INSERT INTO logs VALUES (?, ?)", row)
cursor.execute("COMMIT")

Answer

Indexes are created the standard way (`CREATE INDEX ... ON table(col)`). SQLite's query planner is decent but smaller in scope than Postgres's, there is no parallel scan, no bitmap index scan, and no cost-based join reordering for complex queries.

Use `EXPLAIN QUERY PLAN` to see which index a query is using. Partial indexes (`WHERE` clause on the index) and expression indexes are both supported and very useful: a partial index on `WHERE deleted_at IS NULL` is far smaller than a full index when most rows are deleted. Two things decide whether an index is used at all.

First, column order in a composite index: SQLite can use any leading prefix, so an index on `(user_id, created_at)` serves `WHERE user_id = ?` and `WHERE user_id = ? ORDER BY created_at`, but does nothing for `WHERE created_at > ?` on its own. Second, whether the predicate is sargable: wrapping the column in a function, as in `WHERE date(created_at) = '2026-08-01'`, or using a leading wildcard in `LIKE '%raj'`, disables the index, and the fix is an expression index or a rewrite to a range predicate such as `created_at >= '2026-08-01' AND created_at < '2026-08-02'`.

A covering index contains every column the query touches, letting SQLite answer from the index alone, which `EXPLAIN QUERY PLAN` reports as USING COVERING INDEX. The warning sign in that same output is USING TEMP B-TREE FOR ORDER BY, meaning SQLite is sorting the entire result set because no index matched the ordering. Indexes are not free: each one is another B-tree updated on every INSERT, UPDATE and DELETE, so on write-heavy tables unused indexes are the first thing to drop. `ANALYZE` fills the `sqlite_stat1` table with distribution statistics the planner uses to choose between candidate indexes, and running `PRAGMA optimize` before closing a long-lived connection keeps those statistics current without a full ANALYZE.

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_active_orders ON orders(user_id) WHERE status = 'active';
CREATE INDEX idx_lower_email ON users(LOWER(email));  -- expression index

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'x';
-- SEARCH users USING INDEX idx_users_email (email=?)

Answer

The CLI supports `.import` for CSV and TSV files. For export, `.dump` produces a SQL script that recreates the entire database. Both work without leaving the `sqlite3` shell.

For binary backups, use `VACUUM INTO 'backup.db'` or the C-level Online Backup API, these produce consistent snapshots even while the DB is being written. Three CSV flags matter in practice. `.import --csv --skip 1 data.csv users` skips a header row; without `--skip` the header is inserted as a data row, which is the origin of the classic 'why is there a user whose email is email' bug. If the target table does not exist, `.import` creates it with every column typed TEXT, so create the table yourself first if you care about affinity.

Values arrive verbatim, meaning empty fields become empty strings rather than NULL, and the standard cleanup is an `UPDATE t SET col = NULL WHERE col = ''` pass afterwards. For large loads, wrap the import in BEGIN/COMMIT and drop secondary indexes first, then recreate them, because building an index once over the finished table is far cheaper than maintaining it row by row. On the export side, `.dump` is portable text that survives an architecture change but reloads slowly for large databases, while `VACUUM INTO` writes a compact binary file that is usable immediately. `.mode json`, `.mode markdown` and `.mode insert` cover the cases where the output feeds an API fixture, a document or another database, and `.once out.csv` redirects only the next query rather than leaving `.output` pointed at a file you forget about.

sqlite> .mode csv
sqlite> .import data.csv users
sqlite> .output backup.sql
sqlite> .dump
sqlite> .output stdout

-- Header-aware, fast bulk import
sqlite> .bail on
sqlite> BEGIN;
sqlite> .import --csv --skip 1 users.csv users
sqlite> COMMIT;
sqlite> UPDATE users SET phone = NULL WHERE phone = '';

-- Online backup, safe while DB is in use:
VACUUM INTO 'backup-2026-05-12.db';

Answer

`sqlite_master` (also exposed as `sqlite_schema` in modern versions) is the system catalog, a table SQLite maintains automatically that describes every other table, index, view, and trigger in the database. You can query it like a normal table to introspect a schema, find all tables containing a column, or list every index on a table. This is how ORMs and migration tools discover the current schema.

It has five columns: `type` (table, index, view or trigger), `name`, `tbl_name`, `rootpage`, and `sql`, which holds the exact CREATE statement text you typed, whitespace and comments included. It is read-only in normal operation; writing to it needs `PRAGMA writable_schema = ON` and is a reliable way to corrupt a database, which is precisely why interviewers bring it up. Two behaviours bite in real code.

Indexes created implicitly by UNIQUE or PRIMARY KEY constraints show up with generated names like `sqlite_autoindex_users_1` and a NULL `sql` value, so schema-diff tools that assume `sql` is never null crash on real databases. And `sqlite_master` describes only the connection's main database; each ATTACHed database has its own, addressed as `other.sqlite_master`. For most introspection the pragma functions are easier: `PRAGMA table_info('users')` returns columns with declared type, not-null flag and primary-key position, `PRAGMA foreign_key_list('books')` returns the FK relationships, and `PRAGMA index_list` with `PRAGMA index_info` walks the indexes.

Since 3.16 each is also a table-valued function, so `SELECT * FROM pragma_table_info('users')` composes into ordinary SQL with joins and WHERE clauses. That is exactly how a migration runner checks whether a column already exists before issuing ALTER TABLE, since SQLite has no `ADD COLUMN IF NOT EXISTS`.

-- List all user tables
SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';

-- See the CREATE statement for a table
SELECT sql FROM sqlite_master WHERE name='users';

-- List all indexes for a table
SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='users';

-- Idempotent migration guard (no ADD COLUMN IF NOT EXISTS in SQLite)
SELECT count(*) FROM pragma_table_info('users') WHERE name = 'created_at';

-- Find every table that has a given column
SELECT m.name FROM sqlite_master m
JOIN pragma_table_info(m.name) p
WHERE m.type = 'table' AND p.name = 'tenant_id';

Answer

Foreign keys exist in SQLite but are NOT enforced by default, declaring `REFERENCES other_table(id)` parses cleanly but ignores violations unless you turn enforcement on with `PRAGMA foreign_keys = ON`. This pragma is per-connection and resets on every new connection, so most application code runs it as the first statement after opening the DB. Forgetting this is one of the most common SQLite bugs, your schema looks correct but the constraints are silently disabled.

The follow-up details an interviewer will push on: the pragma is a no-op inside an open transaction, so it must run before BEGIN. Turning it on does not retroactively validate rows written while it was off, so run `PRAGMA foreign_key_check` immediately after enabling it to list the orphans already in the file. Enforcement also has an indexing requirement that people miss: the parent column must be a PRIMARY KEY or carry a UNIQUE index or the schema is rejected outright, while the child column is not indexed for you.

An unindexed child column turns every parent DELETE into a full scan of the child table, which is why a cascade delete that is instant on a test database takes forty seconds on a phone with 200,000 rows. ON DELETE CASCADE, SET NULL, SET DEFAULT and RESTRICT all work, and `DEFERRABLE INITIALLY DEFERRED` postpones the check to COMMIT when you must insert rows in a temporarily inconsistent order. Two platform traps: foreign key enforcement is suppressed during a legacy ALTER TABLE rebuild under `PRAGMA legacy_alter_table`, and on Android, Room controls this through `setForeignKeyConstraintsEnabled` on the database builder rather than reading a raw pragma you executed yourself, so you end up configuring it in two places.

PRAGMA foreign_keys = ON;  -- run on every connection, outside any transaction

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

-- The child column is NOT indexed for you; without this every
-- DELETE FROM authors scans all of books:
CREATE INDEX idx_books_author ON books(author_id);

-- Find orphans written while enforcement was off:
PRAGMA foreign_key_check;
-- books|17|authors|0   <- rowid 17 in books points at a missing author

Answer

PRAGMAs are SQLite's configuration statements. The handful that materially affect production behavior: `PRAGMA journal_mode = WAL` (enable Write-Ahead Logging for much better concurrency, persists in the file), `PRAGMA foreign_keys = ON` (enforce FKs, per-connection), `PRAGMA synchronous = NORMAL` (faster commits than FULL while still safe with WAL), `PRAGMA busy_timeout = 5000` (wait up to 5s when another writer holds the lock instead of failing immediately), `PRAGMA cache_size = -64000` (use 64MB of page cache, negative values are KiB, positive values are pages), `PRAGMA temp_store = MEMORY` (keep temp tables in RAM), and `PRAGMA mmap_size = 268435456` (use memory-mapped IO for reads up to 256MB). Most apps run these as a startup script on every new connection.

Some PRAGMAs (like `journal_mode`) persist in the file header and only need to be set once; others (like `foreign_keys`) are per-connection and must be set every time. Knowing which is which is the actual test. Stored in the file: `journal_mode` (unless set to MEMORY or OFF), `auto_vacuum`, `page_size` (only settable before the first table exists or immediately before a VACUUM), `user_version` and `application_id`.

Reset on every new connection: `foreign_keys`, `busy_timeout`, `synchronous`, `cache_size`, `temp_store`, `mmap_size` and `recursive_triggers`. Because pools open connections lazily, a pool without a per-connection init hook ends up with some connections enforcing foreign keys and others not, producing bugs that reproduce roughly one request in ten and vanish under a debugger. Every mature driver exposes that hook: `better-sqlite3` takes `db.pragma(...)` calls right after construction, and SQLAlchemy uses `@event.listens_for(engine, 'connect')`. Two diagnostic pragmas are worth memorising. `PRAGMA compile_options` lists what the binary was actually built with, which is how you discover that a distro or Alpine build shipped without FTS5 or without `SQLITE_ENABLE_MATH_FUNCTIONS`. `PRAGMA database_list` prints main, temp and every ATTACHed database with its file path, the fastest way to prove you are connected to the file you think you are rather than a stray one created by a bad relative path.

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -64000;  -- negative = KiB; 64000 = 64MB

Answer

LocalStorage (web) and AsyncStorage (React Native) are key-value stores, you store one string per key. SQLite gives you a full relational database: joins, indexes, transactions, query language, schema migrations. For trivial preferences (theme, language) a key-value store is fine.

For anything resembling app state (cached API responses, offline-first data, chat history) you'll quickly outgrow a KV store because you can't query 'all messages from user X between dates Y and Z' without loading and parsing every key. SQLite is what AsyncStorage runs on top of on iOS, and Android Room and iOS Core Data both use SQLite as the backing store, so going to SQLite directly just removes a layer and gives you query power. Modern React Native apps in India often use `op-sqlite` or `expo-sqlite` directly.

For Flutter, the standard library is `sqflite`. The platform compatibility is excellent: the same SQL file written by an Android app can be opened by an iOS app or a desktop tool without any conversion. The comparison a mobile interviewer actually wants is against MMKV and Realm, not just AsyncStorage.

MMKV is mmap-backed key-value storage and beats SQLite on single-key reads, so it is the right pick for settings and feature flags, but it has no query language, so the first 'show me all unread messages from this group' requirement sends you back to SQL. Realm and WatermelonDB add object mapping and reactive queries, and WatermelonDB is SQLite underneath anyway. A practical trap: the community AsyncStorage implementation on Android is SQLite-backed with a default size ceiling you must raise explicitly through `AsyncStorage_db_size_in_MB` in `gradle.properties`, which is why caching a few megabytes of JSON starts failing writes with no obvious error.

On performance, `op-sqlite` and `expo-sqlite` both offer JSI-based synchronous access, removing the React Native bridge serialisation cost that made the older `react-native-sqlite-storage` feel slow on large result sets. Finally, none of these are encrypted by default: the file sits in app-private storage, which is readable on a rooted or jailbroken device, so regulated Indian fintech and health apps layer SQLCipher on top or rely on iOS Data Protection classes.

Answer

SQLite has no DATE, TIME or TIMESTAMP storage class, so you pick one of three conventions and enforce it yourself: ISO-8601 TEXT such as '2026-08-12 09:30:00', Unix epoch seconds as INTEGER, or Julian day numbers as REAL. The built-in functions `date()`, `time()`, `datetime()`, `julianday()`, `unixepoch()` and `strftime()` accept all three, which is exactly why mixed conventions inside one table survive unnoticed for months. ISO-8601 TEXT is the common default because it sorts lexicographically, so an ordinary B-tree index serves both ORDER BY and range predicates, and the values are readable when someone opens the file in a GUI.

INTEGER epoch is usually the better pick on mobile: eight bytes instead of nineteen, cheaper comparisons, and no ambiguity about what timezone the stored value is in. Two behaviours cause most of the bugs. `DEFAULT CURRENT_TIMESTAMP` writes UTC in the form 'YYYY-MM-DD HH:MM:SS' with no 'T' separator and no offset suffix, so any client that parses it as local time shifts every row by five and a half hours for an India-based user, and the bug only shows up near midnight. `date('now')` is UTC as well, and the `'localtime'` modifier resolves against the machine's timezone rather than the user's, so a user-facing 'today' boundary should be computed with an explicit offset instead. Version notes an interviewer may probe: `unixepoch()` arrived in 3.38, `timediff()` in 3.43, and 3.46 added ISO-8601 style time-shift modifiers such as '+05:30' alongside the older '+330 minutes' form. Whatever you store, keep predicates sargable: a half-open range uses the index, while wrapping the column in `strftime()` forces a full scan of every row in the table.

-- Three conventions, all understood by the date functions
CREATE TABLE events (
  id       INTEGER PRIMARY KEY,
  iso_at   TEXT    NOT NULL DEFAULT (datetime('now')),  -- UTC, sorts correctly
  epoch_at INTEGER NOT NULL DEFAULT (unixepoch())        -- 3.38+
) STRICT;

SELECT datetime(epoch_at, 'unixepoch') FROM events;
SELECT datetime(iso_at, '+330 minutes') AS ist FROM events;  -- IST = UTC+5:30

-- Sargable: uses an index on iso_at
SELECT * FROM events
 WHERE iso_at >= '2026-08-01' AND iso_at < '2026-09-01';

-- Not sargable: scans the whole table
SELECT * FROM events WHERE strftime('%Y-%m', iso_at) = '2026-08';

Answer

`INSERT OR REPLACE` (and its shorthand `REPLACE INTO`) is a conflict-resolution clause, not an update. When the new row collides with a UNIQUE or PRIMARY KEY constraint, SQLite deletes the existing row and inserts a fresh one. Three consequences follow, and interviewers test all of them: columns you did not supply fall back to their DEFAULT or NULL instead of keeping the old values, the rowid changes unless you supplied it explicitly, and the delete is a genuine delete, so `ON DELETE CASCADE` fires and can remove child rows while AFTER DELETE triggers run.

A surprising share of 'the comments vanished when the post re-synced' bug reports are exactly this. `ON CONFLICT (...) DO UPDATE`, the real UPSERT, landed in 3.24 and modifies the existing row in place. Untouched columns keep their values, the rowid is stable, and the pseudo-table `excluded` exposes the row you tried to insert, so `SET hits = hits + excluded.hits` gives you an atomic accumulate with no read-modify-write race.

The conflict target you name must be backed by a UNIQUE index or a primary key, otherwise you get a parse error; only the `DO NOTHING` form may omit the target. A WHERE clause after SET makes the update conditional, which is how offline sync implements last-write-wins by comparing timestamps. Version detail: 3.32 allowed multiple ON CONFLICT clauses in one statement, and 3.35 added RETURNING so an upsert can hand back the resulting row, including a generated id, without a second SELECT. The remaining clauses (`OR IGNORE`, `OR ABORT`, `OR FAIL`, `OR ROLLBACK`) differ in whether the row, the statement or the whole transaction is undone. `OR IGNORE` silently discards NOT NULL and CHECK violations too, so using it as a generic de-duplicator hides real data problems.

-- Destructive: deletes then inserts. Other columns reset, CASCADE fires.
INSERT OR REPLACE INTO users (id, email) VALUES (7, 'raj@example.com');

-- True upsert: updates in place, keeps every column you did not name
INSERT INTO users (id, email, updated_at)
VALUES (7, 'raj@example.com', '2026-08-12 09:30:00')
ON CONFLICT(id) DO UPDATE SET
  email      = excluded.email,
  updated_at = excluded.updated_at
WHERE excluded.updated_at > users.updated_at   -- last-write-wins guard
RETURNING id, email, updated_at;               -- 3.35+

-- Atomic counter, no read-modify-write race
INSERT INTO daily_hits(day, hits) VALUES ('2026-08-12', 1)
ON CONFLICT(day) DO UPDATE SET hits = hits + excluded.hits;

Answer

Opening the filename `:memory:` creates a database that lives entirely in the process heap and disappears when the connection closes. It speaks identical SQL to a file database, which makes it the standard way to run SQL-backed tests with no disk I/O and no fixture cleanup between cases. The detail that catches people is that every connection to `:memory:` gets its own separate database.

Open two connections through a pool and your test writes rows into one database and reads from another that is empty, which presents as a test that passes alone and fails in the suite. The fix is the URI form `file:testdb?mode=memory&cache=shared`, where all connections using the same name share one in-memory database that lives until the last one closes. In Python that is `sqlite3.connect('file:testdb?mode=memory&cache=shared', uri=True)`; in better-sqlite3 a single `new Database(':memory:')` per test is usually simpler than sharing.

An in-memory database is not a faithful stand-in for production, and saying so is what separates a considered answer. `PRAGMA journal_mode = WAL` is rejected on it and returns 'memory', so nothing that depends on WAL (reader and writer concurrency, checkpoint behaviour, SQLITE_BUSY handling) can be reproduced. Disk-full errors, permission failures and fsync costs do not exist either, and the shared-cache mode that multi-connection tests require uses table-level locking with different semantics from normal file locking. The pattern that holds up: fast unit tests against `:memory:`, plus a smaller suite against a real temp file created with `tempfile.mkdtemp()` or `fs.mkdtempSync()` configured with the exact pragmas production uses. Migration tests in particular need a real file: seed it with a production-shaped dump, run the migration, then assert that `PRAGMA integrity_check` and `PRAGMA foreign_key_check` both come back clean.

# Each connection gets its OWN empty database
c1 = sqlite3.connect(':memory:')
c2 = sqlite3.connect(':memory:')   # a different database entirely

# Shared in-memory DB: same name, same data, until the last close
uri = 'file:testdb?mode=memory&cache=shared'
c1 = sqlite3.connect(uri, uri=True)
c2 = sqlite3.connect(uri, uri=True)
c1.execute('CREATE TABLE t(x)')
c2.execute('SELECT count(*) FROM t')   # works

-- WAL is not available on an in-memory database:
PRAGMA journal_mode = WAL;   -- returns 'memory', not 'wal'

Answer

WAL (Write-Ahead Logging) is an alternative to the default rollback-journal mode. Without WAL, every write blocks all readers and vice versa, a single long-running write starves your reads. With WAL, writers append to a `.db-wal` file while readers continue reading from the main DB at their consistent snapshot.

The result: readers do not block writers and writers do not block readers. The trade-offs: there is a small write amplification (the WAL is periodically checkpointed back into the main DB), and you now have three files (`.db`, `.db-wal`, `.db-shm`) instead of one, which complicates backup scripts that copy raw files. Enable it once per database (it persists in the file header): `PRAGMA journal_mode = WAL`.

In 2026, WAL is the default recommendation for almost every workload except for read-only deployments. Three things go wrong with WAL in production, and naming them is what an interviewer is listening for. First, WAL needs shared memory through the `-shm` file, so it does not work over network filesystems: put a WAL database on NFS, SMB or certain container volume drivers and it either refuses to open or corrupts under concurrent access.

That is the source of most 'works on my laptop, breaks in the cluster' SQLite incidents. Second, the WAL only shrinks at a checkpoint, and a checkpoint cannot pass any reader still holding an older snapshot, so a single long-lived read transaction lets `.db-wal` grow until the disk fills. Third, deleting a `.db-wal` file that has not been checkpointed discards committed transactions, which is how well-meaning cleanup scripts destroy data.

Crash recovery itself is automatic: the next connection to open the file replays the WAL. The controls worth knowing are `PRAGMA wal_checkpoint(PASSIVE|FULL|RESTART|TRUNCATE)` for forcing a checkpoint and `PRAGMA journal_size_limit` for capping the size the file keeps after one. For a read-only database you ship inside an app bundle, plain `journal_mode = DELETE` remains the correct choice since there are no writers to unblock.

PRAGMA journal_mode = WAL;
-- result: 'wal'

PRAGMA wal_autocheckpoint = 1000;  -- checkpoint every 1000 pages (default)
PRAGMA wal_checkpoint(TRUNCATE);    -- force a checkpoint now and shrink WAL file

Answer

SQLite supports many concurrent readers and exactly one writer at a time, per database file. This 'single-writer' model is the most important constraint to understand. In default journal mode, a write also blocks readers, so contention can be brutal.

WAL mode improves this so readers and the single writer don't block each other, but you still can't have two writers in flight at the same moment. If two processes try to write simultaneously, the second gets `SQLITE_BUSY` and must retry. For low-to-moderate write throughput (up to ~1000 writes/sec on commodity hardware), this is fine.

For high-throughput multi-writer workloads (a SaaS with thousands of concurrent users each writing), this is where you'd choose Postgres instead. The workaround for the in-between case: serialise writes through a single connection or worker thread (the pattern `better-sqlite3` recommends in Node), and use a `busy_timeout` to handle transient contention. Two refinements turn this into a senior answer.

The write lock is per database file, not per table, so splitting hot writes into a second file and ATTACHing it is a legitimate way to buy concurrency: an append-only events table in `events.db` stops contending with user profile writes in `app.db`, at the cost of losing cross-file foreign keys and atomic commits across both. And the single-writer rule limits concurrent write transactions, not throughput, because a commit costs roughly one fsync no matter how many rows it touched, so batching lifts the real ceiling while still using one writer. What genuinely kills a deployment is holding a write transaction open across a network call or a user interaction; almost every SQLITE_BUSY storm traces back to someone wrapping an HTTP request or a file upload inside BEGIN IMMEDIATE.

Note also that a reader which started before a write keeps its older snapshot, so read-your-own-writes is guaranteed only on the same connection, a subtlety that surfaces when a pool hands the follow-up read to a different connection. The escape hatches short of moving to Postgres are `BEGIN CONCURRENT` (available in SQLite's begin-concurrent branch and in libSQL, which lets transactions touching disjoint pages commit in parallel) and sharding into one database file per tenant.

Answer

When you DELETE rows in SQLite, the disk space isn't reclaimed, the page is marked free and reused for future inserts. Over time, especially after big deletes, the file can become much larger than the live data and pages become fragmented. `VACUUM` rebuilds the entire database into a fresh file: defragmented, no free pages, smaller on disk. It is expensive (rewrites the whole DB and needs roughly 2× the disk space during the operation) so most apps run it occasionally, not after every delete. `auto_vacuum` is an alternative mode set at DB creation time that incrementally reclaims space, useful for embedded devices where you can't afford a long VACUUM stall. `VACUUM INTO 'backup.db'` is also the simplest way to take a consistent backup of a live database.

The operational constraints are what get asked about. VACUUM cannot run inside a transaction, needs free disk space roughly equal to the database plus its journal, and holds an exclusive lock for the entire rewrite, so on a 20 GB file it is an outage rather than a maintenance job. It also renumbers rowids in any table without an explicit INTEGER PRIMARY KEY, which silently breaks external systems that stored those rowids as identifiers.

Measure before you schedule one: `PRAGMA freelist_count` multiplied by `PRAGMA page_size` is exactly how many bytes you stand to reclaim, and if that is 3 percent of the file the VACUUM is not worth the lock. With `auto_vacuum = FULL` reclamation happens at every commit, paying extra write amplification plus the pointer-map page overhead; INCREMENTAL is the usual compromise because you choose when to pay by calling `PRAGMA incremental_vacuum(N)`. Switching auto_vacuum on an existing database requires setting the pragma and then running one full VACUUM to rewrite the file. The pattern that works on mobile is INCREMENTAL plus a bounded `incremental_vacuum` call when the app is backgrounded on charge and Wi-Fi, never a blocking VACUUM on the main thread, where Android will ANR the app.

VACUUM;  -- defragment current DB, full rewrite

-- At DB creation only:
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA incremental_vacuum(100);  -- reclaim 100 pages

-- Backup / snapshot:
VACUUM INTO 'snapshot-2026-05-12.db';

Answer

SQLite ships with FTS5, an extension that creates inverted-index 'virtual tables' for full-text search. You declare a virtual table, insert text into it, then run `MATCH` queries that are orders of magnitude faster than LIKE on big text. FTS5 supports prefix queries (`abc*`), phrase queries (`'exact phrase'`), boolean operators (`AND`/`OR`/`NOT`), NEAR queries (`NEAR(foo bar, 10)`), and BM25 ranking.

The killer feature for mobile apps is that you can build a fully offline search (chat history, document library, recipes) without any server. The tokenizer can be customised, `porter` for English stemming so 'running' matches 'run', `unicode61` for Unicode handling, and `trigram` (3.34+) for substring/CONTAINS-style queries that catch typos. For Indian-language apps, the trigram tokenizer works reasonably well across Hindi, Tamil, Telugu, etc. since it operates on character-grams rather than language-specific stemming.

In production, FTS5 problems are almost always about index sync or query syntax. With an external content table (`content='notes'`) the FTS table stores only the index, so you maintain it yourself with AFTER INSERT, AFTER UPDATE and AFTER DELETE triggers, and the delete trigger must insert a special 'delete' command row rather than issue a DELETE. Skip that and search keeps returning rows that no longer exist.

Repairs are `INSERT INTO notes_fts(notes_fts) VALUES('rebuild')`, and `('optimize')` merges segments after a bulk load. The second trap is that MATCH parses FTS5 query syntax rather than a literal string, so user input containing a quote, a colon or a stray hyphen surfaces as 'fts5: syntax error near' straight from your search box. Wrap the term in double quotes to force a literal phrase, or tokenise the input yourself, and never interpolate raw text.

Highlighting comes from the `snippet()` and `highlight()` auxiliary functions, and relevance tuning from `bm25(notes_fts, 10.0, 1.0)`, where the trailing weights per column let a title match outrank a body match. Budget for storage too: the index roughly doubles the footprint of text-heavy tables, which matters when the database ships inside an app download.

-- Create an FTS5 virtual table
CREATE VIRTUAL TABLE notes_fts USING fts5(
  title, body,
  content='notes',  -- external content table
  content_rowid='id',
  tokenize='porter unicode61'
);

-- Populate from main table
INSERT INTO notes_fts(rowid, title, body)
  SELECT id, title, body FROM notes;

-- Search
SELECT id, title FROM notes_fts WHERE notes_fts MATCH 'budget AND 2026'
  ORDER BY rank LIMIT 20;

Answer

The JSON1 extension is built into the SQLite amalgamation since 3.38 (no separate install needed). It adds functions like `json_extract`, `json_set`, `json_array`, `json_each`, and the convenient `->`/`->>` operators (similar syntax to Postgres). Internally JSON is stored as TEXT, there is no binary JSONB storage class.

Performance is fine for small documents (a few KB) but degrades for large blobs because every read parses the JSON. Generated columns + indexes give you the best of both worlds: store JSON, expose a frequently-queried path as a generated column, index it. The details that decide whether your queries work: `->` returns a JSON representation while `->>` returns a plain SQL value, so `payload->'name'` yields the quoted text with its JSON quotes intact and `payload->>'name'` yields the bare string.

Comparing with the wrong operator is the most common reason a WHERE clause silently matches nothing. SQLite 3.45 added JSONB, a binary encoding held in a BLOB that avoids reparsing text on every read; reach for `jsonb_extract` when the same document is read repeatedly, but note the format is internal to SQLite and not wire-compatible with Postgres JSONB despite the shared name. `json_each` and `json_tree` are table-valued functions that unnest arrays into rows, which is how you answer 'every event whose tags array contains upi' with a join instead of a LIKE hack. Add `CHECK (json_valid(payload))` so malformed documents fail at insert time rather than at read time.

On generated columns, STORED occupies disk but is read without recomputation while VIRTUAL costs nothing on disk and is recomputed per read; SQLite can index either, which is unusual since Postgres indexes only stored ones. One migration constraint to remember: `ALTER TABLE ADD COLUMN` can add a VIRTUAL generated column but never a STORED one, so retrofitting requires a table rebuild.

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  payload TEXT,
  user_id INTEGER GENERATED ALWAYS AS (json_extract(payload, '$.user_id')) STORED
);
CREATE INDEX idx_events_user ON events(user_id);

INSERT INTO events(payload) VALUES ('{"user_id": 42, "action": "click"}');

SELECT payload->>'action' AS action FROM events WHERE user_id = 42;

Answer

A prepared statement is a parsed and compiled SQL query that you bind parameters into and execute repeatedly. The benefits are twofold. First, performance: parsing SQL is non-trivial, and reusing a prepared statement skips that step every time.

For tight insert loops this can be 5-10× faster. Second, security: parameters are passed separately from the SQL string, so there is no way for user input to inject SQL. Always use `?` placeholders (positional) or `:name` (named) rather than string concatenation.

Every mature SQLite driver supports this, `better-sqlite3` in Node returns a prepared statement object you can call `.run()`, `.get()`, `.all()` on repeatedly. The limits are where interviews go next. Placeholders bind values only, never identifiers, so a sort column or table name taken from a query string still needs an allowlist; there is no `ORDER BY ?` that does what people hope.

Binding into LIKE works, but the wildcards belong in the bound parameter (`'%' + term + '%'`), not glued onto the SQL. SQLite caps parameters per statement at `SQLITE_MAX_VARIABLE_NUMBER`, historically 999 and 32766 in modern builds, so a generated `IN (?, ?, ...)` over a large array throws 'too many SQL variables' on some platforms and not others, with older Android system SQLite being the usual offender. The portable fixes are chunking the IN list or binding one JSON array and joining `json_each`.

A stepping statement also holds a read snapshot, so a statement that is never reset or finalised keeps a transaction open and blocks WAL checkpointing, which shows up as a `.db-wal` file that never shrinks. Statement caches are per connection, so a pool that opens a fresh connection per request captures none of the reuse benefit, one concrete reason many Node services deliberately keep a single long-lived connection.

// better-sqlite3 (Node)
const insert = db.prepare('INSERT INTO logs (level, msg) VALUES (?, ?)');
const insertMany = db.transaction((rows) => {
  for (const r of rows) insert.run(r.level, r.msg);
});
insertMany(rows);  // single transaction, prepared statement reused

// Python sqlite3
cursor.executemany('INSERT INTO logs (level, msg) VALUES (?, ?)', rows)

Answer

Both are Node.js bindings for SQLite, but they take opposite approaches. `node-sqlite3` (the older one) is fully asynchronous, every call returns a callback or Promise. `better-sqlite3` is synchronous and runs SQLite operations on the calling thread. Counter-intuitively, `better-sqlite3` is faster for typical Node workloads because (1) SQLite operations against a local file are already extremely fast (microseconds), and (2) async wrapping adds overhead and prevents the engine from doing batching. The official `better-sqlite3` README has benchmarks showing 5-10× speedups on inserts.

The trade-off: a slow query blocks the event loop, so for queries that take more than a few ms you should run them in a worker thread. In 2026, `better-sqlite3` is the default choice for Node apps using SQLite, it powers Linear's sync engine, much of Vercel's edge tooling, and most of the local-first JS ecosystem. Bun ships with its own `bun:sqlite` module that's API-compatible with better-sqlite3 and similarly synchronous.

Two more points a hiring manager expects you to raise unprompted. `better-sqlite3` is a native addon compiled against a specific Node ABI, so your image needs either a prebuilt binary or a build toolchain, and the classic deployment failure is a `NODE_MODULE_VERSION` mismatch after a Node upgrade, or a module built on glibc failing to load on Alpine's musl. Node now also ships a built-in `node:sqlite` module, which removes the native dependency for straightforward cases while still lagging on features and extension support. Second, because the API is synchronous, a transaction wrapper built with `db.transaction(fn)` must not contain `await`: the transaction commits when the synchronous function returns, so an async callback commits before your promise settles, and the writes that follow land outside the transaction. That is a data-integrity bug that never throws an error, which is exactly why it gets asked about.

Answer

SQLite uses a `user_version` PRAGMA, an integer stored in the DB header, as a built-in migration sentinel. On startup, your app reads it, compares against the current migration version, and runs any pending DDL inside a transaction. SQLite supports most ALTER TABLE operations (ADD COLUMN, RENAME, DROP COLUMN since 3.35) but not all (changing a column type, adding NOT NULL with no default, reordering columns).

For unsupported migrations, the standard trick is the 'twelve-step' rebuild: create a new table, copy data, drop the old, rename the new, all inside a transaction. Libraries like `umzug`, `Knex migrations`, and `drizzle-kit` abstract this for you. Be ready to recite the rebuild in order, because interviewers ask for it directly: disable foreign keys before starting (the pragma is a no-op inside a transaction), BEGIN, create the new table under a temporary name, copy with `INSERT INTO new SELECT ...

FROM old` mapping old columns to new, drop the old table, rename the new one into place, recreate every index, trigger and view, run `PRAGMA foreign_key_check`, COMMIT, then re-enable foreign keys. Getting that order wrong, typically by toggling the pragma inside the transaction or forgetting a trigger, is how a migration silently breaks referential integrity while reporting success. The other real constraints: DROP COLUMN (3.35+) refuses when the column is referenced by an index, a view or a partial-index expression; RENAME COLUMN (3.25+) rewrites references inside trigger and view bodies, which is usually desirable but surprises people; and ADD COLUMN cannot add a NOT NULL column without a constant default, nor a STORED generated column at all. On mobile there is one more constraint that server developers forget: the migration runs at app launch on a user's device with nobody watching, so it must be idempotent, fully transactional, and benchmarked against a production-sized database, because rebuilding a 500 MB table on a low-end Android phone can blow past the ANR window and get your process killed mid-migration.

// On app startup (better-sqlite3 example)
const current = db.pragma('user_version', { simple: true });

if (current < 1) db.exec(`
  CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL);
  PRAGMA user_version = 1;
`);

if (current < 2) db.exec(`
  ALTER TABLE users ADD COLUMN created_at DATETIME;
  PRAGMA user_version = 2;
`);

Answer

D1 is Cloudflare's managed SQLite-on-the-edge service, GA since 2024. Each D1 database is a full SQLite database running on Cloudflare's edge network with automatic replication to read replicas in multiple regions. You access it from Cloudflare Workers via the Workers binding API, the SQL syntax and semantics are SQLite's exactly.

The use case is read-heavy global apps (content sites, dashboards, app config) where you want sub-50ms latency from anywhere in the world without operating a database. Writes go through a single 'primary' region, so write-heavy workloads still have the same single-writer ceiling SQLite always had. D1 is part of a broader 'SQLite-at-the-edge' trend in 2026, Turso (libSQL) is the other major player and is fully open-source.

What makes D1 an interview topic rather than trivia is that its billing and its limits change how you write SQL. D1 charges on rows read and rows written rather than CPU time, so a query that scans a million rows to return ten costs money on every request, which turns `EXPLAIN QUERY PLAN` into a cost-control tool rather than a performance one. Each database has a storage cap and each Worker invocation has a wall-clock budget, so an unbounded query fails instead of merely running slowly.

The surface is Worker bindings only: `env.DB.prepare(...).bind(...)` with `.first()`, `.all()` or `.run()`, plus `batch()` to send several statements in a single round trip. There is no interactive transaction spanning awaits, because a Worker can be evicted between them, so atomicity across statements goes through `batch()` or through a Durable Object when you need real serialisation. The Sessions API pins a sequence of reads to a replica at least as fresh as your last write, which is how you avoid read-after-write anomalies against replicas. Migrations run through Wrangler with SQL files versioned in the repo, and local development uses a real SQLite file under Miniflare, so the same code path works offline on a laptop.

// Cloudflare Worker calling D1
export default {
  async fetch(req, env) {
    const { results } = await env.DB
      .prepare('SELECT id, title FROM posts WHERE published = 1 LIMIT ?')
      .bind(20)
      .all();
    return Response.json(results);
  }
};

Answer

Litestream is an open-source sidecar process that streams a SQLite database's WAL to object storage (S3, R2, GCS, MinIO) in near-real-time. It runs alongside your application, no driver changes, no library to import, no SDK changes in your app code. Every WAL segment is uploaded as it is generated, typically with a one-second delay.

If the host fails, you restore the DB to a new host by replaying the WAL from S3. This gives you point-in-time recovery and disaster recovery without giving up SQLite's simplicity. The newer sibling project, LiteFS, goes further, it implements a FUSE filesystem with built-in replication for read replicas across multiple hosts, where exactly one is primary and the rest are read-only followers.

In 2026, the Litestream + SQLite combo is a popular alternative to running a managed Postgres for small-to-medium apps where the operational simplicity is worth more than multi-writer support, typical cost is a few cents per month for S3 storage of a multi-GB database. The operational specifics: Litestream requires WAL mode and needs to be the only thing checkpointing, which is why the usual setup sets `wal_autocheckpoint = 0` and lets Litestream drive checkpoints so it never misses WAL frames. It replicates one database file, so a multi-database app needs one replication target per file.

Recovery is not instant either: `litestream restore` downloads and replays, which for a large database is minutes, so your RTO is a download and your RPO is the replication interval, typically about a second of writes. Two failure modes worth naming in an interview. First, silent replication failure after an S3 credential rotation, which is why you scrape the metrics endpoint instead of assuming a live process means a live backup.

Second, an app that starts before a restore completes, creating a fresh empty database and then faithfully replicating that emptiness over your good backup. LiteFS solves read replicas but introduces a genuine distributed system with a primary elected through Consul and writes forwarded from replicas, so failover stops being a shell command. For an India-hosted product, the pragmatic shape is one VM in ap-south-1 with Litestream shipping to an S3 bucket in the same region, which keeps cross-region egress out of the bill entirely.

Answer

libSQL is an open-source fork of SQLite started by the Turso team in 2023 because SQLite's upstream is closed-contribution (the SQLite team accepts bug reports and patches but not feature contributions). libSQL adds features the upstream rejected: server mode (network protocol so you can use SQLite without a local file), native replication, vector search for AI embeddings, and HTTP/WebSocket access. Turso is the managed cloud service running libSQL on a global edge network, competitor to Cloudflare D1. You can run libSQL locally as a regular SQLite-compatible engine, then push it to Turso for production without code changes since the wire-compatible client libraries (`@libsql/client`) work against both local and remote DBs.

Notable in 2026: Turso's `embedded replicas` mode runs a libSQL replica inside your application process for read-from-local performance, then async-replicates writes to the remote primary, a very interesting architecture for AI-first apps where you co-locate the DB with the inference workload. The vector search feature lets you query embeddings with cosine similarity directly in SQL, avoiding a separate vector DB like Pinecone or Weaviate for small-to-medium RAG workloads. Two caveats are worth raising unprompted. libSQL is a fork, so `BEGIN CONCURRENT` and native vector types do not exist in upstream SQLite, and any schema or query that uses them is no longer portable back to a stock `sqlite3` binary or to a Room database on Android. And embedded replicas give read-your-writes only through the same client instance: writes travel to the remote primary and return on the next sync, so a stale local read immediately after a write is expected behaviour that your UI has to account for, not a bug to file.

Answer

`SQLITE_BUSY` ('database is locked') happens when two writers are competing for the write lock, or in WAL-less mode when a writer needs to upgrade past a reader. Three things to do, in order. (1) Enable WAL mode, removes the reader/writer conflict. (2) Set `PRAGMA busy_timeout = 5000` (milliseconds), SQLite will wait up to 5 seconds for the lock instead of failing instantly. (3) Serialise writes through a single worker if you control all writers, the cleanest pattern in Node/Bun apps. If you still see contention after all three, that's the signal to look at Postgres.

A subtle gotcha: `busy_timeout` only helps when the lock is held briefly. Long-running BEGIN IMMEDIATE transactions on one connection will starve writes on another connection regardless of busy_timeout. It also pays to separate the result codes instead of treating them as one error.

SQLITE_BUSY means someone else holds the lock and a retry may succeed. SQLITE_LOCKED is a conflict inside your own connection, usually a write attempted while a SELECT on the same table is still stepping, so retrying can never help because you are blocking yourself. SQLITE_BUSY_SNAPSHOT in WAL mode means a deferred transaction cannot upgrade because the database advanced past your read snapshot, and the only correct response is to ROLLBACK and replay the whole transaction rather than retry the statement.

A diagnosis checklist: confirm `PRAGMA journal_mode` actually returns 'wal' rather than what you meant to set, verify `busy_timeout` is applied to every connection including ones the pool opened after startup, hunt for cursors left open by a partially consumed iterator (a Python `for row in cursor` that breaks early keeps the read transaction alive), and look for an analytics query holding a long snapshot. Retries need jitter, since a fixed backoff just makes the same writers collide again in lockstep. If all of that is clean, measure how long your write transactions hold the lock: p99 lock-hold time, not error count, is the honest signal that it is time for Postgres.

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;

-- In application code, serialize writes:
const writeQueue = new PQueue({ concurrency: 1 });
app.post('/items', async (req, res) => {
  await writeQueue.add(() => insertStmt.run(req.body));
  res.json({ ok: true });
});

Answer

Copying the `.db` file with `cp` while the database is being written can produce a corrupted backup, the file may be in the middle of a write. There are three safe options. (1) `VACUUM INTO 'backup.db'`, a single SQL command that produces a consistent snapshot, simple and works everywhere. (2) The Online Backup API, a C-level API that all major drivers expose; copies pages incrementally while allowing concurrent writes. (3) Litestream, continuous streaming backup to S3. For most apps in 2026, option 1 run via a cron is more than enough; large or high-write apps go straight to Litestream.

The details that separate a working backup from a comforting one: `VACUUM INTO` writes a defragmented copy, so it needs free disk for a second full database and its cost scales with size, fine at 3am and painful at noon. It also refuses to overwrite an existing destination, so the script must generate a unique filename or it will fail silently in cron. The Online Backup API suits a large or busy database better because it copies in page batches and restarts when a concurrent writer changes pages underneath it, which lets you trade backup duration against write throughput; a backup that never completes means your write rate is outrunning the batch size.

Copying `.db`, `.db-wal` and `.db-shm` with `cp` is still not a backup, because the three files are captured at slightly different instants. Recent SQLite releases also ship the `sqlite3_rsync` utility, which synchronises only changed pages between two databases over SSH and is far cheaper than shipping a whole file nightly. The step teams skip is verification: run `PRAGMA integrity_check` on the restored copy and boot the application against it on a schedule, because an untested backup is a hypothesis, not a backup.

-- Snapshot from SQL
VACUUM INTO '/backups/myapp-2026-05-12.db';

-- better-sqlite3 (Node) using Online Backup API
db.backup(`/backups/myapp-${Date.now()}.db`)
  .then(() => console.log('backup done'));

# Verify before trusting it
$ sqlite3 /backups/myapp-2026-05-12.db 'PRAGMA integrity_check;'
ok

# Page-level sync of only what changed
$ sqlite3_rsync /var/lib/app/app.db backup-host:/backups/app.db

Answer

`EXPLAIN QUERY PLAN` prints one line per loop and a small vocabulary tells you nearly everything. `SCAN t` is a full table scan. `SEARCH t USING INDEX ix (col=?)` is an index seek, and the parenthesised part shows which columns were actually usable as constraints, so a composite index listed with only its first column means the rest of your predicate is being applied after the fact. `USING COVERING INDEX` means the index answered the query and the table B-tree was never touched. `USE TEMP B-TREE FOR ORDER BY` or `FOR GROUP BY` means SQLite materialised and sorted the entire intermediate result, which is the single most common cause of a query that is fine at 1,000 rows and terrible at a million. `CORRELATED SCALAR SUBQUERY` and `MATERIALIZE` inside a CTE plan both flag repeated work. When the plan differs between machines, the cause is almost always statistics. The planner reads `sqlite_stat1`, which exists only after someone ran `ANALYZE`; a dev database seeded with 200 rows and a production file with two million rows will legitimately choose different indexes, and a production file where ANALYZE never ran is guessing.

Run `ANALYZE` after bulk loads and call `PRAGMA optimize` before closing long-lived connections so statistics stay current cheaply. When the plan is identical and only the timing differs, the difference is I/O rather than planning: check `PRAGMA cache_size`, whether `mmap_size` is set, and how many rows you are actually returning, because 50,000 rows crossing a driver boundary is slow regardless of the plan. `.timer on` and `.eqp full` in the CLI narrow it down, and `.expert` will propose candidate indexes. On mobile, also run `PRAGMA index_list` on a real device, because a migration that failed halfway leaves production missing an index your laptop has.

sqlite> .timer on
sqlite> EXPLAIN QUERY PLAN
   ...> SELECT o.id, o.total FROM orders o
   ...> WHERE o.user_id = 42 AND o.status = 'paid'
   ...> ORDER BY o.created_at DESC LIMIT 20;
QUERY PLAN
|--SEARCH o USING INDEX idx_orders_user (user_id=?)
`--USE TEMP B-TREE FOR ORDER BY     <- sorting every matching row

-- Composite index that serves the filter, the ordering AND the select list
CREATE INDEX idx_orders_user_created
  ON orders(user_id, created_at DESC, status, total);

ANALYZE;          -- populate sqlite_stat1
PRAGMA optimize;  -- refresh stats before closing a long-lived connection

Answer

SQLite's SQL surface is far wider than its reputation. Common table expressions landed in 3.8.3, including `WITH RECURSIVE` for hierarchies and generated series. Window functions arrived in 3.25 with the full frame syntax: `ROW_NUMBER`, `RANK`, `DENSE_RANK`, `LAG`, `LEAD`, `FIRST_VALUE`, `NTILE`, plus `OVER (PARTITION BY ...

ORDER BY ... ROWS BETWEEN ...)` and named windows. `RETURNING` came in 3.35 on INSERT, UPDATE and DELETE, the same release that added `DROP COLUMN` and the math functions such as `pow` and `log` (which are compiled in only when the build sets `SQLITE_ENABLE_MATH_FUNCTIONS`). Filtered aggregates with `count(*) FILTER (WHERE ...)` came in 3.30, generated columns in 3.31, UPSERT in 3.24 and STRICT tables in 3.37.

What is genuinely absent: there are no stored procedures, no `ALTER TABLE ALTER COLUMN`, no materialised views, and no user-defined functions written in SQL, since you register those in the host language instead. `TRUNCATE TABLE` does not exist either; a bare `DELETE FROM t` with no WHERE triggers an internal truncate optimisation that drops the whole B-tree in one step. `RIGHT JOIN` and `FULL OUTER JOIN` only appeared in 3.39, so anything older rejects them and you rewrite as a LEFT JOIN with the tables swapped, or a UNION. That last point is the practical one, because version drift is the real trap: your Mac may run a 3.4x build while an older Android device runs a system SQLite from years earlier and a Python install links whatever the OS shipped. Always confirm with `SELECT sqlite_version();` on the actual target, and if you need modern SQL on old Android, bundle the engine with `androidx.sqlite:sqlite-bundled` or the requery `sqlite-android` artifact rather than trusting the platform copy.

-- Window functions: 3.25+
SELECT user_id, created_at, total,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn,
       SUM(total) OVER (PARTITION BY user_id ORDER BY created_at
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM orders;

-- Recursive CTE: 3.8.3+
WITH RECURSIVE subtree(id, manager_id, depth) AS (
  SELECT id, manager_id, 0 FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.manager_id, s.depth + 1
    FROM employees e JOIN subtree s ON e.manager_id = s.id
)
SELECT * FROM subtree;

-- RETURNING: 3.35+
DELETE FROM sessions WHERE expires_at < unixepoch() RETURNING id;

SELECT sqlite_version();  -- check the engine you actually got

Answer

Stock SQLite has no encryption. The file is plaintext, and on a rooted Android device or a jailbroken iPhone everything in app-private storage is readable, which is why regulated fintech and health apps in India do not rely on filesystem permissions alone. Three options exist in practice.

SQLCipher, the open-source one from Zetetic, is a fork of the amalgamation that encrypts every page with AES-256, adds a per-page HMAC so tampering is detected, and derives the key with PBKDF2. You open the database and then run `PRAGMA key = 'passphrase'` as the very first statement, before any other SQL, or supply raw key bytes as a blob literal to skip key derivation. SQLite's own SEE is a commercially licensed extension from the SQLite authors.

On mobile you can instead lean on the platform: iOS Data Protection with `NSFileProtectionComplete`, or Android's `EncryptedFile` and the Keystore. The operational details matter more than the algorithm choice. The passphrase must never be hardcoded, because a key in the APK is a key the attacker already has; derive it from user credentials or store it in Keychain or Android Keystore. `PRAGMA cipher_page_size` and `PRAGMA kdf_iter` change the on-disk format, so a mismatch between writer and reader gives you `file is not a database`, which is also the error a wrong passphrase produces, since SQLCipher cannot read the header to tell you anything better.

There is a genuine cost: every page read decrypts, so cold reads slow measurably and the encrypted header means plain `sqlite3` or DB Browser cannot open the file at all without a SQLCipher build. Converting an existing plaintext database is not an in-place toggle: ATTACH an encrypted target, call `sqlcipher_export()`, then swap files. Rotating a key is `PRAGMA rekey`, which rewrites every page.

-- SQLCipher: the key must be set before ANY other statement
PRAGMA key = 'passphrase-from-the-keystore';
PRAGMA cipher_page_size = 4096;  -- changing this changes the file format
PRAGMA kdf_iter = 256000;
SELECT count(*) FROM sqlite_master;
-- wrong key or mismatched settings => 'file is not a database'

-- Convert an existing plaintext DB (there is no in-place toggle)
$ sqlcipher plaintext.db
sqlite> ATTACH DATABASE 'encrypted.db' AS enc KEY 'passphrase';
sqlite> SELECT sqlcipher_export('enc');
sqlite> DETACH DATABASE enc;

-- Rotate the key on an already-open encrypted database
PRAGMA rekey = 'new-passphrase';

Answer

SQLite wins decisively in five scenarios. (1) **Embedded / mobile**, there is no real alternative; you can't run a Postgres server on a phone. (2) **Single-process apps**, internal tools, desktop apps, CLI tools, where the operational cost of running a separate database is pure overhead. (3) **Read-heavy edge workloads**, Cloudflare D1, Turso, where co-locating data with compute gives sub-10ms reads globally. (4) **Local-first apps**, Notion local sync, Linear's offline cache, Obsidian, where data lives on the user's device. (5) **Test environments**, a `:memory:` SQLite database is the fastest way to run SQL-backed integration tests.

You should not pick SQLite when: (a) you need many concurrent writers (real SaaS multi-user write workload), (b) you need true horizontal scale-out for writes, (c) you need fine-grained role-based access control at the DB layer, (d) you need synchronous multi-region replication. For these, Postgres is the boring correct choice. A common India-context pattern in 2026: use SQLite locally on mobile (offline-first) and sync to a Postgres server when online, best of both worlds.

The follow-up that catches people is what actually breaks first as you grow. Not file size, since a hundred-GB database is routine, and not read throughput, which stays excellent as long as the working set fits the page cache. What breaks first is write latency under contention. Then comes the absence of connection-level authentication, the moment a second service needs the same file. Then the operational awkwardness of a database that lives on one machine's disk when you want two application servers behind a load balancer. Every one of those is a systems constraint rather than a SQL constraint, which is exactly why the usual migration path keeps the schema and swaps the engine.

Answer

10k writes/sec on a single SQLite file is feasible on modern NVMe hardware, but only with the right setup. The pillars: (1) **WAL mode** with `synchronous = NORMAL`, saves an fsync per commit while remaining durable enough for most apps. (2) **Batch in transactions**, never one write per BEGIN/COMMIT; group 100-1000 writes per transaction. fsync cost is amortised across the batch. (3) **Single-writer architecture**, funnel all writes through one worker process or thread that owns the connection. Other processes send write requests over a queue (Redis, in-memory). This sidesteps SQLITE_BUSY entirely. (4) **Prepared statements**, reuse parsed SQL. (5) **mmap I/O**, `PRAGMA mmap_size = 268435456` (256MB) for memory-mapped reads. (6) **Disable auto-checkpoint storms** by tuning `wal_autocheckpoint` so the WAL doesn't grow unboundedly but checkpoints don't pause writers. (7) **Use BLOB-typed columns** for binary data rather than encoding to base64. With this stack, Rqlite, dqlite, and similar Raft-replicated SQLite layers regularly hit 10-50k writes/sec per node. Beyond that scale, you're in Postgres territory.

Two things to measure rather than assume. First, whether you are fsync-bound or CPU-bound: set `synchronous = OFF` in a throwaway benchmark and if throughput barely moves, durability was never your bottleneck and the real cost is statement overhead or index maintenance. Second, checkpoint stalls. Under WAL a large checkpoint briefly blocks writers, so p99 write latency spikes on a period that lines up with `wal_autocheckpoint`, and the fix is a background thread calling `wal_checkpoint(PASSIVE)` more often, not a bigger threshold. Also drop or defer non-essential indexes during bulk loads, and give each writer thread its own connection rather than sharing one, because SQLite serialises internally in the default threading mode and a shared connection throws away the parallelism you thought you had.

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA wal_autocheckpoint = 1000;
PRAGMA mmap_size = 268435456;
PRAGMA cache_size = -200000;  -- 200MB
PRAGMA temp_store = MEMORY;
PRAGMA busy_timeout = 5000;

Answer

SQLite is fully ACID. Atomicity and Consistency come from the journal/WAL: a transaction either fully writes its pages or rolls back. Isolation is SERIALIZABLE in single-writer mode (only one writer at a time, readers see consistent snapshots).

Durability is controlled by the `synchronous` PRAGMA. The three levels: `FULL` (the default for rollback-journal mode) issues an fsync after every write to guarantee data survives a power loss, slowest but bulletproof. `NORMAL` (recommended for WAL mode) issues an fsync at each checkpoint but not at every commit, you can lose the last few committed transactions in a sudden power failure, but the DB stays consistent (no corruption). `OFF` skips fsync entirely, the DB can be corrupted on power loss. In 2026, the universal recommendation is WAL + NORMAL for almost everything: you gain 5-10× write throughput and the only risk is losing seconds-worth of transactions, not the database itself.

For truly critical workloads (regulated finance, healthcare), use WAL + FULL. Never use OFF in production. Two clarifications a senior interviewer listens for.

NORMAL protects you against a process crash and an OS crash, not against a power cut: with WAL + NORMAL a sudden power loss can drop the last committed transactions while leaving the file consistent, and on consumer SSDs or cheap Android flash a lying write cache can cost you more than that regardless of the pragma. Second, every durability guarantee assumes the VFS below you honours POSIX locking and real fsync semantics, which network filesystems routinely break, so a database on NFS can corrupt even with `synchronous = FULL`. There is also a fourth level, EXTRA, which additionally syncs the containing directory when a rollback journal is deleted, relevant only in journal mode. To confirm what is actually in effect, note that `PRAGMA synchronous` returns a number rather than a keyword: 0 is OFF, 1 is NORMAL, 2 is FULL, 3 is EXTRA, and plenty of teams discover their careful FULL setting never applied because it was run on the wrong connection.

Answer

Three patterns, each with different trade-offs. (1) **One database file per tenant.** Total isolation, trivial 'export tenant data', easy to GDPR-delete. Scales to tens of thousands of files on modern filesystems (Cloudflare D1 takes this approach). The catch: cross-tenant analytics requires attaching multiple DBs or running queries in a loop. (2) **Shared database, `tenant_id` column on every table.** Simpler operationally, one connection, one file. But you must enforce tenant isolation in every query, a single missing `WHERE tenant_id = ?` leaks data. Wrap every model with a repository that auto-injects the tenant_id, or use a SQLite preupdate hook to verify. (3) **Shared database with views per tenant.** Create a `users_t42` view defined as `SELECT * FROM users WHERE tenant_id = 42`, give each tenant connection-scoped access only to its views. Adds maintenance burden but harder to leak.

In 2026, the modern pattern in mobile/edge SaaS is option 1 (one SQLite file per user/tenant) combined with central metadata in Postgres. Turso explicitly markets 'a database per user' as a flagship pattern.

Whichever pattern you argue for, the interviewer will test it with three operational questions. How do you migrate a schema across 20,000 tenant files? The answer is a resumable queue that checks `user_version` per file and records progress, never a for-loop in a deploy script that dies halfway. How do you back it up? Either one Litestream target per file or a snapshot job that iterates and verifies. And how do you produce a cross-tenant report? Through a nightly job that reads each file and writes into one analytics database, because ATTACH will not save you: `SQLITE_LIMIT_ATTACHED` defaults to 10 databases per connection and the compile-time ceiling is 125. That single limit is what pushes many teams from file-per-tenant to a shared `tenant_id` schema once the tenant count passes a few hundred.

-- Pattern 2: never write a bare query, always go through a scoped helper
CREATE VIEW v_orders AS
  SELECT * FROM orders WHERE tenant_id = CAST(current_tenant() AS INTEGER);

-- Pattern 1: cross-tenant reporting hits the ATTACH ceiling fast
ATTACH DATABASE 'tenants/42.db' AS t42;
ATTACH DATABASE 'tenants/43.db' AS t43;
SELECT 42 AS tenant, count(*) FROM t42.orders
UNION ALL SELECT 43, count(*) FROM t43.orders;
DETACH DATABASE t42;
-- SQLITE_LIMIT_ATTACHED: 10 by default, 125 maximum

-- Per-file migration guard, resumable across crashes
PRAGMA user_version;  -- run per tenant file, migrate only if behind

Answer

Offline-first with SQLite is the dominant mobile pattern in India because of patchy connectivity, payment apps, government apps, learning apps all rely on it. Architecture: (1) **Local SQLite** on the device holds the source of truth for the user's data. Use Room (Android) or Core Data (iOS), both of which are SQLite under the hood, or `op-sqlite`/`expo-sqlite` for React Native. (2) **Sync engine** runs on the device and pushes local changes to a server (Postgres) when the network is available, and pulls server changes back. The common patterns are: append-only event log (every change is a row, server replays), CRDTs (conflict-free types for collaborative apps), or last-write-wins with version vectors. (3) **Server-side** maintains a global ordering using a sequence number or hybrid logical clock so devices can fetch 'changes since seq=N'. (4) **Conflict resolution** strategy must be explicit, silent overwrites are the usual cause of complaints. For most consumer apps, last-write-wins with user warnings is acceptable; for financial data, server-side authoritative validation is mandatory.

Real examples in India: PhonePe and Paytm cache transaction history locally in SQLite for offline viewing, Hotstar caches downloaded videos with SQLite metadata, government health apps (e.g. CoWIN-style apps) use SQLite for offline form filling that uploads when connected. Libraries like PowerSync and ElectricSQL provide this sync layer out of the box in 2026.

Answer

First establish scope with `PRAGMA integrity_check`, which walks every page, index and B-tree and returns either 'ok' or a list of specific problems. On a phone or a very large file, `PRAGMA quick_check` skips the expensive index-versus-table cross-check and finishes in a fraction of the time. If foreign key enforcement was ever off, add `PRAGMA foreign_key_check`.

Before touching anything, copy the file plus its `-wal` and `-shm` siblings, because recovery attempts are destructive and you only get one clean starting point. Recovery order: run `.recover` in the sqlite3 CLI, which reads page fragments directly and emits SQL for everything it can reconstruct. It is strictly better than the old `.dump` route, because `.dump` aborts at the first unreadable page while `.recover` keeps going and puts unattached rows into a `lost_and_found` table.

Pipe its output into a fresh database, then `REINDEX` and re-run `integrity_check`. Only if that yields little do you reach for `PRAGMA writable_schema = ON` surgery, which is as dangerous as it sounds. The more useful half of the answer is cause, because SQLite corruption is nearly always an environment bug rather than an engine bug.

The recurring culprits: a database on NFS, SMB or a container volume driver that fakes POSIX locking; two processes opening the same file through different paths or a symlink so the locking layer does not recognise them as the same file; `synchronous = OFF` plus a power cut; deleting or truncating a `-wal` file that was never checkpointed; an OS killing the process while cheap flash lies about fsync; and simply failing storage. Name which one applies before blaming SQLite. Prevention is unglamorous: WAL with `synchronous = NORMAL` on local disk only, one process owning writes, scheduled integrity checks, and restore drills so you learn your backup works before you need it.

$ cp app.db app.db.bak
$ sqlite3 app.db 'PRAGMA integrity_check;'
*** in database main ***
Page 4291: btreeInitPage() returns error code 11
row 1042 missing from index idx_orders_user

# Cheaper check, safe to run on a mobile device at startup
$ sqlite3 app.db 'PRAGMA quick_check;'

# Salvage into a fresh file: unlike .dump, this does not stop at the first bad page
$ sqlite3 app.db '.recover' | sqlite3 recovered.db
$ sqlite3 recovered.db 'REINDEX; PRAGMA integrity_check;'
ok
$ sqlite3 recovered.db 'SELECT count(*) FROM lost_and_found;'

Answer

Since 3.8.0 SQLite uses the Next Generation Query Planner, which estimates a cost for every candidate plan and picks the cheapest. For joins it runs an N-nearest-neighbour search over join orders rather than an exhaustive search, so with many tables it can land on a good plan rather than the optimal one. Costs come from `sqlite_stat1`, populated by `ANALYZE`, whose rows look like 'orders idx_orders_user 100000 25', meaning the index covers 100,000 rows and each distinct user_id matches about 25 of them.

Without ANALYZE the planner falls back to fixed guesses, roughly that an equality match on any index returns ten rows, which is precisely why a low-selectivity index on a status column gets chosen and wrecks a query. Overrides, in the order you should reach for them. Run `ANALYZE` first, because a wrong plan is usually a statistics problem, not a planner problem. `likely(expr)` and `unlikely(expr)` adjust the estimated probability of a WHERE term without changing its meaning.

A unary plus in front of a column, as in `WHERE +status = 'paid'`, strips the term's affinity and makes it unusable by any index, which is the clean way to stop SQLite picking a bad one. `INDEXED BY ix` forces a specific index and, usefully, makes the statement fail with 'no query solution' if that index is ever dropped, so it doubles as a schema guard your tests will catch. `NOT INDEXED` forbids index use on a table entirely. Writing `CROSS JOIN` instead of `JOIN` freezes the join order exactly as written, the standard fix when the planner picks the wrong outer table. Two more knobs: `PRAGMA automatic_index = OFF` stops SQLite silently building a transient index mid-query, which is a useful alarm since an automatic index almost always means a real one is missing, and `.eqp full` shows the bytecode beside the plan.

ANALYZE;                       -- fill sqlite_stat1 before judging any plan
SELECT tbl, idx, stat FROM sqlite_stat1;
-- orders|idx_orders_status|100000 50000   <- half the table per lookup
-- orders|idx_orders_user  |100000 25

-- Stop the planner from using the useless status index
SELECT * FROM orders WHERE +status = 'paid' AND user_id = 42;

-- Force one index; the query now fails loudly if that index is dropped
SELECT * FROM orders INDEXED BY idx_orders_user WHERE user_id = 42;

-- Freeze join order: users is the outer loop, no reordering allowed
SELECT * FROM users u CROSS JOIN orders o ON o.user_id = u.id
 WHERE u.city = 'Pune';

-- Surface missing indexes instead of hiding them behind a transient one
PRAGMA automatic_index = OFF;

Answer

There are three extension points. Scalar and aggregate functions are registered from the host language with `sqlite3_create_function_v2`, surfaced as `db.create_function()` in Python and `db.function()` / `db.aggregate()` in better-sqlite3. This is how you get regular expressions at all: the `REGEXP` operator exists in the grammar but ships with no implementation, so `WHERE name REGEXP '^A'` fails with 'no such function: REGEXP' until you supply one.

Custom collations go through `sqlite3_create_collation`, which is how you implement case-insensitive or locale-aware ordering that the built-in NOCASE (ASCII only) cannot do for Indic scripts. Mark deterministic functions with the `SQLITE_DETERMINISTIC` flag, otherwise SQLite refuses to use them in index expressions, partial index predicates or generated columns. Virtual tables, registered with `sqlite3_create_module`, make arbitrary data sources look like tables.

FTS5, R-Tree, `csv`, `generate_series` and `dbstat` are all virtual tables. `SELECT name, sum(pgsize) FROM dbstat GROUP BY name` is the honest way to find what is actually consuming space in a large file, rather than guessing from row counts. Loadable extensions are shared libraries pulled in with `.load ./vec0` in the CLI or `sqlite3_load_extension` from code, and loading is disabled by default everywhere: you must call `enableLoadExtension(true)` on the driver, and many distribution builds are compiled with `SQLITE_OMIT_LOAD_EXTENSION`, so check `PRAGMA compile_options` before promising anyone this works. The extension worth knowing in 2026 is `sqlite-vec`, which adds `vec0` virtual tables and distance functions for embedding search, letting a small retrieval system live in the same file as its documents with no separate vector database and no network hop. Its honest limit is that matching is a brute-force scan, so it is excellent at tens of thousands of vectors and the wrong tool at tens of millions.

// better-sqlite3: register a deterministic scalar function
db.function('slugify', { deterministic: true }, (s) =>
  String(s).toLowerCase().replace(/[^a-z0-9]+/g, '-'));
db.prepare('SELECT slugify(title) AS slug FROM posts LIMIT 5').all();

// Loadable extensions are off by default in every driver
db.loadExtension('./vec0');
db.exec(`CREATE VIRTUAL TABLE doc_vec USING vec0(
  doc_id INTEGER PRIMARY KEY,
  embedding FLOAT[768]
)`);

-- Nearest neighbours, in the same file as the documents
SELECT d.id, d.title, v.distance
  FROM doc_vec v JOIN docs d ON d.id = v.doc_id
 WHERE v.embedding MATCH :query_vector AND k = 5
 ORDER BY v.distance;

-- Where the disk space actually went
SELECT name, sum(pgsize) AS bytes FROM dbstat
 GROUP BY name ORDER BY bytes DESC;

-- Confirm the build can even load extensions
PRAGMA compile_options;

Answer

The single-file model that makes SQLite pleasant on a laptop is what makes container orchestration hostile, and four failures recur. Network storage: a database on NFS, EFS, Azure Files or any CSI driver that emulates POSIX locking will eventually corrupt, and WAL will not even open there because the `-shm` file needs real shared-memory mmap semantics. The symptom is `disk I/O error` or `unable to open database file` with no obvious trigger.

SQLite belongs on a local disk or a block volume attached to exactly one node, which means a StatefulSet with a ReadWriteOnce PersistentVolumeClaim, never a ReadWriteMany share. Rolling deploys: the default Deployment strategy starts the new pod before terminating the old one, so for several seconds two processes hold the same file. Even with correct locking that is two writers and a SQLITE_BUSY storm, and on shared storage it is corruption.

Set `strategy: Recreate`, or a StatefulSet with `replicas: 1`, and accept a brief write outage on deploy. Horizontal autoscaling of the writer is simply not available. Ephemeral filesystems: writing to the container's writable layer instead of a mounted volume means every restart silently starts from an empty database, because opening a missing path creates it.

Mount the volume and assert on startup that an expected table exists rather than trusting the path. Backup and restore: run Litestream as a sidecar sharing the volume, plus an initContainer that runs `litestream restore -if-db-not-exists -if-replica-exists` so a rescheduled pod rebuilds state from object storage before the app starts. Add a readiness probe that really queries the database, since a pod whose database failed to open will otherwise pass a TCP check and error on every request. For an India-hosted product, keep the bucket in ap-south-1 next to the node so restores are fast and cross-region egress never appears on the bill.

# StatefulSet fragment: one writer, local block volume, Litestream sidecar
spec:
  replicas: 1
  template:
    spec:
      initContainers:
        - name: restore
          image: litestream/litestream
          args: ['restore', '-if-db-not-exists', '-if-replica-exists',
                 '/data/app.db']
          volumeMounts: [{ name: data, mountPath: /data }]
      containers:
        - name: app
          volumeMounts: [{ name: data, mountPath: /data }]
          readinessProbe:
            exec:
              command: ['sqlite3', '/data/app.db', 'SELECT 1 FROM users LIMIT 1;']
        - name: litestream
          image: litestream/litestream
          args: ['replicate']
          volumeMounts: [{ name: data, mountPath: /data }]
  volumeClaimTemplates:
    - metadata: { name: data }
      spec:
        accessModes: ['ReadWriteOnce']   # never ReadWriteMany