Back to interview questions

100+ SQLite Interview Questions and Answers

100+ SQLite Interview Questions and Answers

Explore 120 SQLite interview questions and answers on types, STRICT tables, WAL, transactions, indexing, JSON, FTS5, migrations, backups and embedded databases.

Explore 120 SQLite interview questions and answers on types, STRICT tables, WAL, transactions, indexing, JSON, FTS5, migrations, backups and embedded databases.

By

Team Sherlock.sh

Add Sherlock as a preferred source on Google

SQLite interviews should reveal whether a candidate can build reliable software around an embedded database, not just write a familiar SELECT statement. Recruiters hiring mobile, desktop, edge or backend engineers need to assess storage types, connection ownership, transaction boundaries and the operational life of a database file. Those decisions explain why an import accepts unexpected text, a background job blocks local edits or a copied file misses committed changes.

These 100+ SQLite Interview Questions and Answers cover 120 questions across beginner, intermediate and experienced levels. The scope includes affinity and STRICT tables, rowid design, foreign keys, WAL concurrency, query planning, JSON, FTS5, migrations, backups and secure file handling. Current official documentation identifies SQLite 3.54.1; executable examples use the available SQLite 3.50.4 runtime, with newer behavior and untested fixes identified where relevant.

How can recruiters tell whether a candidate understands SQLite’s behavior beyond generic SQL syntax?

Ask the candidate to explain a small fixture, then change one condition: open a second connection, keep a read transaction alive, insert a numeric-looking string or restore a backup into a fresh process. Strong answers predict the consequence, identify the engine or driver assumption and propose a check that could disprove their explanation. Experienced candidates also explain what a successful local test cannot establish about production concurrency or durability.

Remote technical interviews can involve potential AI cheating, deepfakes, impersonation and unauthorized assistance. Sherlock AI can support interview-integrity workflows alongside original SQLite exercises, structured follow-up questions and human review. State permitted tools clearly and interpret integrity signals in context rather than treating one signal as conclusive proof of misconduct.

SQLite Interview Questions

Select questions for the role’s actual use of SQLite, including the host language, deployment model and database lifecycle. Ask candidates to state version and connection assumptions when they affect the answer.

SQLite Interview Questions for Beginners

Establish the embedded architecture, value types, schema rules and connection lifecycle.

  1. What does it mean that SQLite is an embedded database engine?

  2. When is SQLite a good architectural choice?

  3. How does a SQLite database file differ from a database connection?

  4. How can an application identify the SQLite engine it actually uses?

  5. What are SQLite’s five storage classes?

  6. What is type affinity in SQLite?

  7. Does declaring VARCHAR(20) enforce a twenty-character limit?

  8. Does DECIMAL(10,2) provide fixed-point decimal arithmetic in SQLite?

  9. What does a STRICT table change?

  10. Why is ANY useful in a STRICT table?

  11. How are Boolean values represented in SQLite?

  12. How should dates and timestamps be stored in SQLite?

  13. What is a rowid table?

  14. Why does INTEGER PRIMARY KEY differ from INT PRIMARY KEY?

  15. When is AUTOINCREMENT necessary?

  16. Can a SQLite primary key unexpectedly contain NULL?

  17. How are foreign keys enabled reliably?

  18. How does a CHECK constraint treat NULL in SQLite?

  19. How can an application inspect the schema?

  20. How do prepared statements and bound values fit SQLite’s API?

  21. Why should application code finish cursors promptly?

  22. What is an in-memory SQLite database?

  23. How can SQLite open a database read-only?

  24. What is a PRAGMA, and why should settings be read back?

  25. What does BEGIN DEFERRED do?

  26. How is BEGIN IMMEDIATE different from BEGIN EXCLUSIVE?

  27. What happens when COMMIT or ROLLBACK ends a transaction?

  28. Can BEGIN transactions be nested?

  29. Why does SQLite allow only one writer at a time?

  30. What changes when WAL mode is enabled?

  31. What is a WAL checkpoint?

  32. What does a busy timeout accomplish?

  33. How should an application distinguish SQLITE_BUSY from SQLITE_LOCKED?

  34. What does EXPLAIN QUERY PLAN show?

  35. Why is an index not automatically beneficial?

  36. What is SQLite UPSERT intended to handle?

  37. Why is INSERT OR REPLACE not the same as an update?

  38. What does RETURNING provide in SQLite?

  39. Does SQLite store JSON in a special SQL column type?

  40. When would FTS5 be preferable to LIKE for search?

SQLite Interview Questions for Intermediate

Explore SQLite-specific query behavior, constraints, indexes, concurrency, JSON and text search.

  1. How can affinity make a comparison behave differently from a literal-only expression?

  2. What is the difference between CAST and column affinity?

  3. How can integer division surprise a SQLite query?

  4. Why can built-in NOCASE fail to implement international case-insensitive identity?

  5. How do LIKE and GLOB differ in SQLite?

  6. How does SQLite handle a scalar subquery that returns several rows?

  7. Why are bare columns in aggregate queries risky?

  8. When can WITHOUT ROWID improve a table design?

  9. How do VIRTUAL and STORED generated columns differ?

  10. What makes a parent key valid for a SQLite foreign key?

  11. Why should child foreign-key columns often be indexed?

  12. When are deferred foreign keys useful?

  13. How should foreign_key_check and integrity_check be used together?

  14. What is a covering index in SQLite?

  15. How should a composite index match filtering and ordering?

  16. What makes an expression index usable by SQLite?

  17. How does a partial index reduce maintenance for a selective subset?

  18. What does an automatic index in a plan indicate?

  19. What does USE TEMP B-TREE in EQP suggest?

  20. How do ANALYZE and PRAGMA optimize help planning?

  21. What does SQLITE_BUSY_SNAPSHOT mean in WAL mode?

  22. Why can a long-running reader cause a WAL file to grow?

  23. How do checkpoint modes differ operationally?

  24. How does synchronous affect WAL durability?

  25. Why is a network share a poor shortcut for multi-machine SQLite access?

  26. How should bulk inserts be batched?

  27. Why must Python transaction behavior be configured explicitly?

  28. Why is last_insert_rowid connection-sensitive?

  29. What are important memory and composability limits of RETURNING?

  30. How do ABORT, FAIL and IGNORE change constraint-error behavior?

  31. How do json_extract and the JSON arrow operators differ?

  32. How can JSON null be distinguished from a missing path?

  33. Why can json_object turn an apparent array into a quoted string?

  34. How do json_each and json_tree differ?

  35. What is SQLite JSONB, and what does it not promise?

  36. How can a JSON field be indexed without indexing an entire document?

  37. How should user input be passed to an FTS5 MATCH query?

  38. What does changing an FTS5 tokenizer affect?

  39. How should FTS5 ranking and snippets be interpreted?

  40. Why must an external-content FTS5 index be maintained explicitly?

SQLite Interview Questions for Experienced

Examine durability, migrations, security, resource control and deployment trade-offs.

  1. How would you design a safe read-modify-write workflow for SQLite?

  2. How should a retry policy handle contention without duplicating effects?

  3. When does a single application writer queue help?

  4. What is the significance of SQLite’s WAL-reset fix for deployment?

  5. How would you prove an application really gets repeatable WAL reads?

  6. Why can copying only the main database file produce an incomplete backup?

  7. What makes the online backup API preferable for a live application?

  8. When is VACUUM INTO useful compared with an ordinary backup?

  9. Why might deleting rows not shrink a SQLite file?

  10. How should a migration change a table when direct ALTER support is insufficient?

  11. How has recent ALTER COLUMN support changed migration planning?

  12. How can user_version support application migrations?

  13. What compatibility checks matter when distributing a database file?

  14. What does ATTACH imply for cross-database atomicity?

  15. How should an application respond to disk-full or I/O errors?

  16. What should happen first when corruption is suspected?

  17. What security boundary does SQLite provide around a local file?

  18. Is encryption included automatically when using SQLite?

  19. Why should untrusted database files be treated differently from trusted application data?

  20. What can an authorizer callback enforce, and what can it not replace?

  21. Why should extension loading be tightly controlled?

  22. How should application-defined SQL functions be declared and tested?

  23. What risks arise when a custom collation changes behavior?

  24. How can a progress handler support cancellation of expensive queries?

  25. Why are resource limits useful even for read-only SQL?

  26. Does serialized threading mode make shared connections logically safe?

  27. Why should open SQLite connections not be carried across fork boundaries?

  28. What should a performance investigation measure beyond query elapsed time?

  29. How should query-plan changes after an engine upgrade be evaluated?

  30. When should INDEXED BY be used cautiously?

  31. How can SQLite’s join strategy affect large local joins?

  32. How should large BLOBs be handled when full materialization is costly?

  33. What does changing page size require in a WAL database?

  34. How do cache_size and mmap_size differ conceptually?

  35. What makes immutable=1 dangerous on a changing database?

  36. How should an offline synchronization protocol use SQLite transactions?

  37. When is one database per tenant a reasonable design?

  38. How can dbstat help investigate SQLite storage overhead?

  39. How should SQLite MATERIALIZED and NOT MATERIALIZED CTE hints be interpreted?

  40. How would you diagnose a report that passes tests on SQLite but fails on PostgreSQL?

SQLite Interview Questions and Answers

The answers connect engine behavior to application decisions and small reproducible fixtures. SQL blocks are self-contained examples for fresh databases; Python blocks create isolated fixtures and close their connections. These checks establish their stated outcomes, not production performance or power-loss guarantees.

SQLite Interview Questions and Answers for Beginners

Look for precise distinctions between a file, a connection, a transaction and a stored value.

1. What does it mean that SQLite is an embedded database engine?

SQLite runs as a library inside the application process rather than as a separate database server. SQL execution, transaction management and file access happen through that library, so an application can use a relational database without deploying a server service.

For a desktop catalog, this makes installation and offline operation straightforward. It also means the application owns connection lifecycle, file permissions and upgrade compatibility. “Embedded” does not mean the data must fit in memory or that transactions are absent; it describes where the engine runs and how the application reaches it.

2. When is SQLite a good architectural choice?

SQLite is a strong candidate for application-local data, device storage, caches and workloads where a local database file and serialized writes fit the design. The useful question is whether its execution and concurrency model matches the workload, not whether the product is small enough to sound simple.

An offline field application can keep its working data locally and synchronize through an application protocol. A service needing many simultaneous independent writers or direct remote database access may fit a client-server engine better. Measure actual write duration, data growth and operational requirements before treating a popularity claim as an architecture decision.

3. How does a SQLite database file differ from a database connection?

The file holds persistent database state; a connection is an application handle used to execute statements against it. Several connections can open the same database, but each has its own transaction state and many connection-specific settings.

For example, enabling foreign-key enforcement on one connection does not configure every future connection. A connection factory should establish and verify required settings consistently. Closing a handle releases its resources, while deleting the file destroys persistent storage; those are different lifecycle operations and should never be confused in cleanup code.

4. How can an application identify the SQLite engine it actually uses?

Query sqlite_version() on the application’s connection and inspect relevant capabilities or compile options. The version of a command-line executable elsewhere on the machine does not necessarily identify the library linked into Python, a mobile framework or another driver.

For this article, executable fixtures use Python’s SQLite 3.50.4 runtime; current official documentation lists 3.54.1. Record both the runtime and required features during diagnosis. A version number alone may not establish optional module availability, so a small capability probe is useful for features such as FTS5.

5. What are SQLite’s five storage classes?

SQLite values have the storage classes NULL, INTEGER, REAL, TEXT and BLOB. A column declaration influences conversion and validation, but in ordinary non-STRICT tables it does not assign one immutable storage class to every value in that column.

Use typeof(value) when investigating unexpected input. A sensor column with numeric affinity may contain an integer reading in one row and unconvertible text in another. That flexibility can be intentional, but applications needing homogeneous data should use suitable constraints or STRICT tables rather than assume the declared type already guarantees it.

Storage class

Typical purpose

Important distinction

NULL

Missing value

Not an empty string or zero.

INTEGER

Counts or explicitly scaled units

Signed integer representation, not unlimited precision.

REAL

Approximate measurements

Binary floating-point, not fixed-point decimal.

TEXT

Labels and normalized text timestamps

Representation and collation affect meaning.

BLOB

Binary content or SQLite JSONB

Bytes require an application-defined interpretation.

6. What is type affinity in SQLite?

Affinity is a column’s preference for converting values during storage and some comparisons. Ordinary columns use TEXT, NUMERIC, INTEGER, REAL or BLOB affinity, derived from their declared type name. Affinity is a conversion rule, not a complete domain definition.

For example, inserting the text '42' into a NUMERIC-affinity column can store an integer, while 'unknown' remains text. This matters when importing identifiers with leading zeros: a textual code should usually have TEXT affinity. Inspect stored types as well as displayed values when validating a migration.

SQLite SQL — self-contained fixture

CREATE TABLE affinity_demo(value NUMERIC);
INSERT INTO affinity_demo VALUES ('42'), ('unknown');
SELECT value, typeof(value) FROM affinity_demo ORDER BY

CREATE TABLE affinity_demo(value NUMERIC);
INSERT INTO affinity_demo VALUES ('42'), ('unknown');
SELECT value, typeof(value) FROM affinity_demo ORDER BY

CREATE TABLE affinity_demo(value NUMERIC);
INSERT INTO affinity_demo VALUES ('42'), ('unknown');
SELECT value, typeof(value) FROM affinity_demo ORDER BY

The two rows retain different storage classes: integer 42 and text unknown.

7. Does declaring VARCHAR(20) enforce a twenty-character limit?

No. In an ordinary SQLite table, VARCHAR(20) gives TEXT affinity; the parenthesized length is not a built-in maximum-length constraint. A schema copied from another engine can therefore accept values that its author expected to reject.

Use an explicit constraint such as CHECK(length(label) <= 20) when that matches the domain, and add NOT NULL if missing labels are prohibited. Character-count rules are not necessarily byte limits or user-perceived grapheme limits, so clarify what a UI or external protocol actually requires before choosing the check.

8. Does DECIMAL(10,2) provide fixed-point decimal arithmetic in SQLite?

No. That declaration normally produces NUMERIC affinity rather than a fixed-precision decimal storage engine. Values can become integers or binary floating-point reals, and the precision and scale annotation does not enforce the contract familiar from some server databases.

For exact quantities with a fixed unit, an application might store integer minor units and validate range and currency separately. More general decimal arithmetic needs an explicitly chosen implementation. Do not assume formatting a REAL to two places repairs prior approximation or defines a business rounding policy.

9. What does a STRICT table change?

A STRICT table requires supported declared types and rejects values that cannot be losslessly converted to the specified type. It is a per-table option introduced in SQLite 3.37.0, so ordinary and STRICT tables can coexist in the same database.

A count column declared INTEGER in a STRICT table can accept a convertible numeric string but reject unrelated text. STRICT is not a substitute for domain rules: a negative integer is still an integer, and nullable columns still permit NULL. Add CHECK, NOT NULL and relationship constraints for those separate requirements.

SQLite SQL — self-contained fixture

CREATE TABLE strict_demo(n INTEGER NOT NULL CHECK(n >= 0)) STRICT;
INSERT INTO strict_demo VALUES ('12');
SELECT n, typeof(n) FROM

CREATE TABLE strict_demo(n INTEGER NOT NULL CHECK(n >= 0)) STRICT;
INSERT INTO strict_demo VALUES ('12');
SELECT n, typeof(n) FROM

CREATE TABLE strict_demo(n INTEGER NOT NULL CHECK(n >= 0)) STRICT;
INSERT INTO strict_demo VALUES ('12');
SELECT n, typeof(n) FROM

This valid insert becomes integer 12. The accompanying fixture tests also verify rejection of text that cannot convert and a negative count.

10. Why is ANY useful in a STRICT table?

ANY preserves the inserted value and its storage class without the normal numeric-looking-text conversion used by an ordinary non-STRICT ANY column. It is useful when a deliberately heterogeneous payload must retain distinctions such as the string '0007' versus the integer 7.

For a local import staging area, that preservation can keep the original representation available for validation. It also moves interpretation responsibility to the application. Use a typed column for a known quantity rather than making every field ANY merely to avoid handling malformed input.

SQLite SQL — self-contained fixture

CREATE TABLE preserved(value ANY) STRICT;
CREATE TABLE converted(value ANY);
INSERT INTO preserved VALUES ('0007');
INSERT INTO converted VALUES ('0007');
SELECT 'strict', typeof(value), quote(value) FROM preserved
UNION ALL
SELECT 'ordinary', typeof(value), quote(value) FROM

CREATE TABLE preserved(value ANY) STRICT;
CREATE TABLE converted(value ANY);
INSERT INTO preserved VALUES ('0007');
INSERT INTO converted VALUES ('0007');
SELECT 'strict', typeof(value), quote(value) FROM preserved
UNION ALL
SELECT 'ordinary', typeof(value), quote(value) FROM

CREATE TABLE preserved(value ANY) STRICT;
CREATE TABLE converted(value ANY);
INSERT INTO preserved VALUES ('0007');
INSERT INTO converted VALUES ('0007');
SELECT 'strict', typeof(value), quote(value) FROM preserved
UNION ALL
SELECT 'ordinary', typeof(value), quote(value) FROM

The STRICT ANY value remains text with leading zeros, while ordinary ANY stores integer 7.

11. How are Boolean values represented in SQLite?

SQLite has no separate Boolean storage class; applications commonly store false and true as integers 0 and 1. The TRUE and FALSE keywords are supported aliases in modern releases, but a Boolean-looking declaration alone does not restrict stored values to that pair.

For a required flag, use an INTEGER column with NOT NULL and a check accepting only 0 or 1. This keeps a value such as 5 from becoming an accidental third state. If unknown is meaningful, decide whether NULL represents it and make application behavior explicit instead of silently treating every nonzero value as valid input.

12. How should dates and timestamps be stored in SQLite?

Choose a consistent representation such as normalized text, Unix timestamps or Julian day values, and define its units and timezone contract. SQLite does not have a dedicated timestamp storage class that automatically supplies all those semantics.

A device log might use integer UTC milliseconds, while a calendar-only field uses YYYY-MM-DD text. Do not mix seconds and milliseconds in the same column. Built-in date functions accept specific representations and modifiers; named-timezone rules or ambiguous local times may need application-level handling with an appropriate timezone library.

13. What is a rowid table?

Most ordinary SQLite tables have a unique signed 64-bit integer rowid that forms the table’s underlying key. The special names rowid, _rowid_ and oid can expose it unless a declared column shadows the relevant name.

A declared non-integer primary key in an ordinary table usually creates a separate uniqueness structure rather than replacing this underlying key. If an identifier must be a stable application contract, declare it intentionally instead of discovering and exporting an anonymous rowid. WITHOUT ROWID tables use a different storage arrangement.

14. Why does INTEGER PRIMARY KEY differ from INT PRIMARY KEY?

In an ordinary rowid table, a normal INTEGER PRIMARY KEY declaration aliases the rowid; INT PRIMARY KEY does not. Exact type spelling matters for this special behavior even though both names otherwise suggest integer affinity.

For a local notes table, an INTEGER PRIMARY KEY can provide the persistent identifier and automatic integer assignment when the inserted value is NULL. Do not generalize the alias rule to every declaration containing “INT”; the historical inline INTEGER PRIMARY KEY DESC exception is another reason to keep the intended declaration simple and test schema behavior.

SQLite SQL — self-contained fixture

CREATE TABLE notes(id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes(body) VALUES ('offline draft');
SELECT id, rowid, body FROM

CREATE TABLE notes(id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes(body) VALUES ('offline draft');
SELECT id, rowid, body FROM

CREATE TABLE notes(id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes(body) VALUES ('offline draft');
SELECT id, rowid, body FROM

The first generated ID and rowid are both 1 in this fresh fixture.

15. When is AUTOINCREMENT necessary?

AUTOINCREMENT changes automatic rowid selection so automatically assigned values do not reuse previously committed rowids from that table. It adds bookkeeping and overhead; ordinary INTEGER PRIMARY KEY already generates identifiers without it.

Use it only when non-reuse is part of the application’s identity contract. It does not guarantee consecutive numbers or remove gaps after failed operations. If IDs must be unique across devices before synchronization, a local increasing integer may be insufficient regardless of AUTOINCREMENT; choose a broader identity scheme deliberately.

16. Can a SQLite primary key unexpectedly contain NULL?

Yes, ordinary rowid tables retain a historical exception that can allow NULL in many declared primary-key columns. INTEGER PRIMARY KEY, WITHOUT ROWID tables and STRICT tables have stronger relevant behavior; an explicit NOT NULL also closes the ordinary nullable-key gap.

For a text-key settings table, declare the intended non-null rule or use an appropriate STRICT or WITHOUT ROWID design. Do not validate SQLite schemas solely against the textbook statement that every primary key always implies NOT NULL. Multiple missing identifiers can undermine an application’s lookup assumptions even when the schema appears to name a key.

17. How are foreign keys enabled reliably?

Enable PRAGMA foreign_keys=ON for each connection before starting its transaction, then read the setting back. Do not rely on the default, which can depend on the build and is commonly off.

Changing this setting inside an active transaction has no effect. A connection pool or background worker therefore needs the same initialization contract as the main application. Declaring REFERENCES expresses the relationship in the schema, but enforcement must actually be active on every writer that is expected to preserve it.

Python — self-contained SQLite fixture

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:", isolation_level=None)) as db:
    db.execute("PRAGMA foreign_keys=ON")
    assert db.execute("PRAGMA foreign_keys").fetchone() == (1,)
    db.execute("CREATE TABLE device(id INTEGER PRIMARY KEY)")
    db.execute("CREATE TABLE sample(device_id INTEGER REFERENCES device(id))")
    try:
        db.execute("INSERT INTO sample VALUES (99)")
    except sqlite3.IntegrityError:
        pass  # Expected: no parent device 99 exists.
    else:
        raise AssertionError("Foreign key was not enforced")
import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:", isolation_level=None)) as db:
    db.execute("PRAGMA foreign_keys=ON")
    assert db.execute("PRAGMA foreign_keys").fetchone() == (1,)
    db.execute("CREATE TABLE device(id INTEGER PRIMARY KEY)")
    db.execute("CREATE TABLE sample(device_id INTEGER REFERENCES device(id))")
    try:
        db.execute("INSERT INTO sample VALUES (99)")
    except sqlite3.IntegrityError:
        pass  # Expected: no parent device 99 exists.
    else:
        raise AssertionError("Foreign key was not enforced")
import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:", isolation_level=None)) as db:
    db.execute("PRAGMA foreign_keys=ON")
    assert db.execute("PRAGMA foreign_keys").fetchone() == (1,)
    db.execute("CREATE TABLE device(id INTEGER PRIMARY KEY)")
    db.execute("CREATE TABLE sample(device_id INTEGER REFERENCES device(id))")
    try:
        db.execute("INSERT INTO sample VALUES (99)")
    except sqlite3.IntegrityError:
        pass  # Expected: no parent device 99 exists.
    else:
        raise AssertionError("Foreign key was not enforced")

The fixture verifies enforcement rather than assuming the PRAGMA succeeded.

18. How does a CHECK constraint treat NULL in SQLite?

A CHECK fails when its expression evaluates to numeric zero; a NULL result does not reject the row. Thus CHECK(quantity >= 0) alone permits a missing quantity, just as it permits a valid nonnegative value.

Combine it with NOT NULL when absence is invalid. In a flexible-typing table, also consider whether unexpected storage classes could change comparison behavior; a STRICT INTEGER column makes a numeric quantity contract clearer. Constraints should encode the real domain instead of depending on a UI to submit only well-formed values.

19. How can an application inspect the schema?

Query sqlite_schema for object definitions and use appropriate PRAGMAs such as table_xinfo for column metadata. The schema table contains SQL definitions, while introspection pragmas expose useful structured details.

When comparing a migrated table, inspect constraints, indexes, triggers and views rather than just column names. table_info omits some generated or hidden columns, so it is not always a complete inventory. Treat schema inspection as read-only diagnosis; directly editing sqlite_schema is not an ordinary migration technique.

20. How do prepared statements and bound values fit SQLite’s API?

Prepare fixed SQL, bind application values through the driver, execute it and finish or release the statement. Binding preserves the distinction between SQL syntax and data, so a note containing an apostrophe remains a value.

In Python’s sqlite3 driver, a question-mark placeholder can be paired with a parameter tuple. Do not add quotes around the placeholder or substitute it with string formatting. Bindings represent values, not arbitrary table names or sort keywords; dynamic structural choices need a controlled mapping to approved SQL fragments.

Python — self-contained SQLite fixture

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as db:
    db.execute("CREATE TABLE notes(body TEXT)")
    body = "Reader's note; DROP TABLE notes;"
    db.execute("INSERT INTO notes(body) VALUES (?)", (body,))
    assert db.execute("SELECT body FROM notes").fetchone() == (body,)
    db.commit()
import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as db:
    db.execute("CREATE TABLE notes(body TEXT)")
    body = "Reader's note; DROP TABLE notes;"
    db.execute("INSERT INTO notes(body) VALUES (?)", (body,))
    assert db.execute("SELECT body FROM notes").fetchone() == (body,)
    db.commit()
import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as db:
    db.execute("CREATE TABLE notes(body TEXT)")
    body = "Reader's note; DROP TABLE notes;"
    db.execute("INSERT INTO notes(body) VALUES (?)", (body,))
    assert db.execute("SELECT body FROM notes").fetchone() == (body,)
    db.commit()

The SQL-looking note is stored as one value and the table remains intact.

21. Why should application code finish cursors promptly?

An active statement can retain resources and keep an implicit transaction open. Fetching only the first row of a query and leaving its cursor alive can therefore have effects beyond memory use, particularly when a read snapshot is held.

For a local search screen, consume the needed result and explicitly close the cursor when abandoning it. Avoid retaining a database cursor across user think time or slow network work. Closing a cursor is distinct from committing an explicit transaction, so the surrounding transaction boundary still needs deliberate handling.

22. What is an in-memory SQLite database?

Opening :memory: creates a database held in memory for that connection’s lifetime. Separate ordinary connections to that name normally create separate databases, not shared access to one implicit global store.

This makes it useful for small isolated fixtures, but it cannot reproduce file-locking, WAL sidecars, disk-full behavior or persistence after process exit. Named shared-memory URI arrangements have separate rules. Use file-backed temporary fixtures when the behavior being tested depends on storage or multiple connections.

23. How can SQLite open a database read-only?

A supported URI filename can use mode=ro to request read-only access, with URI handling enabled in the host API. This prevents accidental creation of a new writable file at a misspelled path and establishes a useful access boundary.

Read-only access is not the same as declaring the file immutable. A live WAL database may require sidecar access or suitable preexisting files, and other processes may still change its contents. Choose the opening mode from the deployment contract rather than adding URI flags until an error disappears.

24. What is a PRAGMA, and why should settings be read back?

PRAGMAs are SQLite-specific commands for configuration and inspection. Their scope and timing vary: some affect a connection, some persist in a database, and some cannot be changed meaningfully in a transaction.

Unknown PRAGMA names can be silently ignored, so a typo may look successful. After setting foreign keys or requesting a journal mode, inspect the reported value and handle an unsupported result. Maintain a small explicit initialization policy instead of assuming every PRAGMA is a permanent database-wide switch.

25. What does BEGIN DEFERRED do?

BEGIN DEFERRED starts an explicit transaction boundary without immediately acquiring the write transaction. The first database access determines whether it begins as a read or write transaction, and a later attempt to upgrade a reader to a writer can fail under contention.

This suits work that may remain read-only, but it does not reserve future writing capacity. If a workflow reads a value and then must update based on it, consider its conflict policy and whether BEGIN IMMEDIATE better expresses the intent. A transaction keyword alone does not remove the need to handle a failed upgrade.

26. How is BEGIN IMMEDIATE different from BEGIN EXCLUSIVE?

BEGIN IMMEDIATE attempts to start the write transaction immediately. BEGIN EXCLUSIVE does likewise, but outside WAL mode it also prevents other connections from reading while the transaction is active; in WAL mode the two have the same relevant locking behavior.

For a short read-modify-write operation, IMMEDIATE can surface writer contention before application decisions are made from a snapshot. It still can fail with SQLITE_BUSY and should not surround slow user interaction. EXCLUSIVE is not a general speed setting and can unnecessarily reduce reader availability in rollback-journal mode.

BEGIN mode

When writing is reserved

Reader consequence

DEFERRED

When a write transaction is actually started or a read upgrade succeeds.

Depends on later access and journal mode.

IMMEDIATE

Attempted at BEGIN.

WAL readers can generally continue; rollback-mode commit still needs appropriate locks.

EXCLUSIVE

Attempted at BEGIN.

Blocks other readers in rollback mode; same relevant behavior as IMMEDIATE in WAL.

27. What happens when COMMIT or ROLLBACK ends a transaction?

COMMIT accepts the transaction’s changes, while ROLLBACK abandons its uncommitted changes. A successful commit is meaningful within the configured durability and storage guarantees; receiving rows from a write statement is not by itself proof that the enclosing transaction committed.

An application should use explicit error handling so a failed multi-step operation does not accidentally commit partial intended work. Some errors leave the transaction active while others can roll it back, so inspect the driver’s transaction state. Do not assume every exception has already performed the cleanup your code needs.

28. Can BEGIN transactions be nested?

No. SQLite does not nest BEGIN transactions; use savepoints for nested units of work inside a transaction. Rolling back to a savepoint undoes later changes while leaving the outer transaction active.

A library that participates in a caller’s transaction can use a carefully named savepoint rather than trying to issue a second BEGIN. Releasing an inner savepoint does not make its changes durable independently: an outer rollback can still undo them. Coordinate transaction ownership so helper code does not unexpectedly commit its caller’s work.

SQLite SQL — self-contained fixture

CREATE TABLE steps(n INTEGER);
BEGIN;
INSERT INTO steps VALUES (1);
SAVEPOINT optional_step;
INSERT INTO steps VALUES (2);
ROLLBACK TO optional_step;
RELEASE optional_step;
COMMIT;
SELECT n FROM

CREATE TABLE steps(n INTEGER);
BEGIN;
INSERT INTO steps VALUES (1);
SAVEPOINT optional_step;
INSERT INTO steps VALUES (2);
ROLLBACK TO optional_step;
RELEASE optional_step;
COMMIT;
SELECT n FROM

CREATE TABLE steps(n INTEGER);
BEGIN;
INSERT INTO steps VALUES (1);
SAVEPOINT optional_step;
INSERT INTO steps VALUES (2);
ROLLBACK TO optional_step;
RELEASE optional_step;
COMMIT;
SELECT n FROM

Only the first row remains after committing the outer transaction.

29. Why does SQLite allow only one writer at a time?

SQLite serializes write transactions for a database to coordinate changes to its shared persistent state. Multiple connections can exist and many can read, but WAL does not turn one database into a system with simultaneous independent writers.

A burst of short writes may work well because each transaction finishes quickly. A long import transaction can delay every other writer even if it changes different tables. Evaluate writer occupancy and batching policy rather than assuming that splitting writes across more connections automatically increases throughput.

30. What changes when WAL mode is enabled?

In WAL mode, committed changes are appended to a write-ahead log and later copied into the main database by checkpointing. Readers can generally continue against their snapshots while a writer appends changes, improving reader-writer concurrency.

The trade-offs include sidecar files, checkpoint management and same-host shared-memory requirements. There is still one writer, and SQLITE_BUSY remains possible. A deployment should select a currently patched engine and test its actual workload rather than treating WAL as a universal fix for contention.

31. What is a WAL checkpoint?

A checkpoint transfers committed WAL content back into the main database file. It is separate from committing a transaction, so a successful commit does not require that all its pages have already reached the main file.

Readers can constrain how far a checkpoint progresses because their snapshots may still need older WAL content. Automatic checkpointing is available, but applications with long-lived reads or heavy writes should observe WAL growth and checkpoint outcomes. Deleting the WAL to make it smaller can destroy committed data and is not a checkpoint strategy.

32. What does a busy timeout accomplish?

A busy timeout lets SQLite wait and retry certain lock conflicts for a bounded period instead of immediately returning SQLITE_BUSY. It can smooth short contention, but it does not increase the number of allowed writers or guarantee every conflict can be resolved by waiting.

Choose a timeout consistent with the application’s latency budget and inspect errors after it expires. A stale WAL snapshot that cannot be upgraded needs a new transaction, not simply a longer sleep. Also distinguish database-level waiting from a host framework’s own request timeout.

33. How should an application distinguish SQLITE_BUSY from SQLITE_LOCKED?

SQLITE_BUSY usually indicates a conflict with another connection, whereas SQLITE_LOCKED commonly indicates a conflict within the same connection or a shared-cache situation. Extended error codes provide more specific context and should be preserved in diagnostics.

For example, an unfinished statement can interfere with a schema operation on its own connection. Blindly retrying that operation may not help until the statement is finished. Log the operation, transaction state and exact code so contention handling addresses the actual resource owner rather than classifying every locking error as temporary load.

34. What does EXPLAIN QUERY PLAN show?

It shows a high-level description of SQLite’s chosen access strategy, including scans, indexed searches and temporary sorting structures. It is diagnostic plan information, not a timed execution report equivalent to another engine’s EXPLAIN ANALYZE.

For a local catalog lookup, inspect whether the expected predicate can use an index and whether a sort needs a temporary B-tree. Then measure execution separately with representative data. SQLite warns that EQP’s output format can change, so application correctness should not depend on parsing its human-oriented detail strings.

35. Why is an index not automatically beneficial?

An index can reduce lookup or sorting work, but it takes space and must be maintained by writes. SQLite may correctly prefer a scan when much of a table is needed or when an index’s ordering does not match the query.

For a small settings table, a new index might add maintenance without measurable benefit; a large local event table may benefit greatly from a well-chosen composite index. Use query shape and realistic data to justify the index. Its existence does not establish result order without ORDER BY.

36. What is SQLite UPSERT intended to handle?

UPSERT resolves a uniqueness conflict during an INSERT by updating the conflicting row or doing nothing. The excluded qualifier refers to the proposed inserted values, while the update can also use existing values.

For a local per-device counter, a unique device key can support an insert-or-increment operation. UPSERT does not catch every validation error: NOT NULL, CHECK and foreign-key failures are separate. Define whether repeated requests should replace, accumulate or be ignored before choosing the conflict action.

SQLite SQL — self-contained fixture

CREATE TABLE counters(device TEXT PRIMARY KEY NOT NULL, n INTEGER NOT NULL);
INSERT INTO counters VALUES ('sensor-a', 2);
INSERT INTO counters VALUES ('sensor-a', 3)
ON CONFLICT(device) DO UPDATE SET n = counters.n + excluded.n
RETURNING device,

CREATE TABLE counters(device TEXT PRIMARY KEY NOT NULL, n INTEGER NOT NULL);
INSERT INTO counters VALUES ('sensor-a', 2);
INSERT INTO counters VALUES ('sensor-a', 3)
ON CONFLICT(device) DO UPDATE SET n = counters.n + excluded.n
RETURNING device,

CREATE TABLE counters(device TEXT PRIMARY KEY NOT NULL, n INTEGER NOT NULL);
INSERT INTO counters VALUES ('sensor-a', 2);
INSERT INTO counters VALUES ('sensor-a', 3)
ON CONFLICT(device) DO UPDATE SET n = counters.n + excluded.n
RETURNING device,

The existing count becomes 5. This intentionally accumulates the proposed count; it is not an idempotent request-deduplication design.

37. Why is INSERT OR REPLACE not the same as an update?

On a relevant uniqueness conflict, REPLACE deletes the conflicting row and then inserts a replacement. That can change identity or trigger relationship consequences that an ordinary UPDATE would not have.

A settings row referenced by other records should not be replaced casually just to avoid a duplicate-key error. Omitted columns can take insertion defaults instead of preserving previous values, and cascading relationships may matter. Use an intentional UPSERT update when the desired behavior is to retain the existing entity and change selected attributes.

SQLite SQL — self-contained fixture

PRAGMA foreign_keys=ON;
CREATE TABLE parent(id INTEGER PRIMARY KEY, label TEXT);
CREATE TABLE child(parent_id INTEGER REFERENCES parent(id) ON DELETE CASCADE);
INSERT INTO parent VALUES (1, 'old');
INSERT INTO child VALUES (1);
INSERT OR REPLACE INTO parent VALUES (1, 'new');
SELECT (SELECT label FROM parent), (SELECT count(*) FROM child)

PRAGMA foreign_keys=ON;
CREATE TABLE parent(id INTEGER PRIMARY KEY, label TEXT);
CREATE TABLE child(parent_id INTEGER REFERENCES parent(id) ON DELETE CASCADE);
INSERT INTO parent VALUES (1, 'old');
INSERT INTO child VALUES (1);
INSERT OR REPLACE INTO parent VALUES (1, 'new');
SELECT (SELECT label FROM parent), (SELECT count(*) FROM child)

PRAGMA foreign_keys=ON;
CREATE TABLE parent(id INTEGER PRIMARY KEY, label TEXT);
CREATE TABLE child(parent_id INTEGER REFERENCES parent(id) ON DELETE CASCADE);
INSERT INTO parent VALUES (1, 'old');
INSERT INTO child VALUES (1);
INSERT OR REPLACE INTO parent VALUES (1, 'new');
SELECT (SELECT label FROM parent), (SELECT count(*) FROM child)

The child count becomes zero because REPLACE deleted the old parent and the declared cascade ran. The same parent ID being inserted again does not restore the child.

38. What does RETURNING provide in SQLite?

RETURNING, available since 3.35.0, sends values from rows directly modified by an INSERT, UPDATE or DELETE back to the application. It can retrieve an assigned ID without a separate lookup and reports both insertion and update outcomes from an UPSERT.

It does not include every indirect trigger or foreign-key change, and returned row order is not guaranteed. Consume or finish the statement properly and distinguish its result from transaction commit. SQLite’s RETURNING cannot be treated as a PostgreSQL-style data-modifying CTE input.

39. Does SQLite store JSON in a special SQL column type?

JSON text is stored using ordinary SQLite values, commonly TEXT, and modern releases also support SQLite’s JSONB representation as a BLOB. Declaring a column JSON in an ordinary table does not by itself validate documents or create a distinct storage class.

For a configuration payload, combine an appropriate storage choice with JSON validation and explicit requirements for important fields. JSON functions can expose values for querying, but deeply nested flexible payloads do not replace every relational constraint. Check the deployed build and feature version rather than assuming all JSON APIs are universally available.

40. When would FTS5 be preferable to LIKE for search?

FTS5 provides token-based full-text indexing and query features suited to searching document content. LIKE expresses character-pattern matching and does not automatically become a linguistic full-text search engine merely because a column has an ordinary B-tree index.

A local help library may need phrases, prefixes and ranked results, making FTS5 appropriate. A short identifier prefix filter may need only ordinary indexed lookup. Tokenization and FTS query syntax affect behavior, so test representative language, punctuation and user-input handling; module availability also depends on the build.

SQLite Interview Questions and Answers for Intermediate

Evaluate how the candidate diagnoses an observed result and selects a mechanism with explicit limits.

41. How can affinity make a comparison behave differently from a literal-only expression?

A comparison involving a column can apply that column’s affinity to the other operand. A comparison between two bare literals does not inherit a table column’s conversion rule, so values that look equivalent on screen need not compare the same way.

For example, an INTEGER-affinity column containing 42 can compare equal to the bound text '42', while the literal expression 42 = '42' is false. Diagnose the storage class and expression affinity before adding casts everywhere. A cast may solve one comparison while changing index eligibility or masking invalid imported data.

SQLite SQL — self-contained fixture

CREATE TABLE readings(n INTEGER);
INSERT INTO readings VALUES (42);
SELECT n = '42' AS column_comparison,
       42 = '42' AS literal_comparison
FROM

CREATE TABLE readings(n INTEGER);
INSERT INTO readings VALUES (42);
SELECT n = '42' AS column_comparison,
       42 = '42' AS literal_comparison
FROM

CREATE TABLE readings(n INTEGER);
INSERT INTO readings VALUES (42);
SELECT n = '42' AS column_comparison,
       42 = '42' AS literal_comparison
FROM

The result is 1 for the column comparison and 0 for the literal comparison.

42. What is the difference between CAST and column affinity?

CAST explicitly requests conversion of an expression, including conversions that can lose information. Column affinity applies its own storage or comparison rules and does not simply run the same unconditional cast on every value.

For instance, casting text that begins with digits to INTEGER can yield a numeric prefix, while inserting malformed numeric text into an ordinary NUMERIC column may preserve it as text. Use validation before conversion when truncation is unacceptable. A successful CAST is not proof that the entire original string satisfied an integer input contract.

43. How can integer division surprise a SQLite query?

When both operands are integers, division can produce an integer result with the fractional part discarded. Introducing a REAL operand changes the arithmetic, but also introduces binary floating-point representation rather than exact decimal arithmetic.

For a completion fraction, 3 / 2 produces 1 while 3.0 / 2 produces 1.5. Decide whether the desired result is a ratio, a rounded count or an exact monetary quantity before choosing the expression. Also define zero-denominator behavior instead of allowing NULL to become an unexplained missing report value.

44. Why can built-in NOCASE fail to implement international case-insensitive identity?

SQLite’s built-in NOCASE collation primarily folds ASCII letters; it is not a complete Unicode case-folding and normalization policy. Visually or linguistically related strings can therefore remain distinct under a uniqueness rule using that collation.

For international usernames, choose a documented normalization and comparison strategy, potentially using application processing or a suitable extension. Test accents, composed versus decomposed forms and language-specific cases. Changing an identity policy later may expose collisions, so do not retrofit a new collation without a data and index migration plan.

45. How do LIKE and GLOB differ in SQLite?

LIKE uses SQL percent and underscore wildcards and is normally case-insensitive for ASCII letters, while GLOB uses Unix-style patterns such as star and question mark and is case-sensitive. Their pattern languages and escaping behavior are different.

For a file browser, GLOB may express a case-sensitive suffix rule, while a product prefix filter may use LIKE. Neither should be treated as a general Unicode-aware search policy. Prefix optimization also depends on the pattern, collation and implementation, so confirm the plan for the actual query rather than assuming every wildcard search uses an index.

46. How does SQLite handle a scalar subquery that returns several rows?

SQLite takes the first row’s value for a scalar subquery instead of raising the multiple-row error that some other engines produce. Without a deterministic ordering or a uniqueness guarantee, that can hide an incorrect assumption about the data.

A lookup of one display label should usually be backed by a key that permits only one match. Adding LIMIT 1 may suppress the symptom without defining which row is correct. If choosing one record is intentional, specify the selection rule and stable tie-breaker; otherwise fix the relationship or return a multirow result.

47. Why are bare columns in aggregate queries risky?

SQLite permits selected columns that are neither grouped nor aggregated, but their values can come from an arbitrarily chosen input row. Special behavior around a single built-in MIN or MAX can associate bare values with an extremal row, yet ties and other cases still require care.

For a latest sensor reading, selecting a label beside MAX(timestamp) is not a complete deterministic record-selection strategy. Use a query that identifies the intended row and handles equal timestamps explicitly. Portability and correctness improve when the result’s row identity is specified instead of relying on SQLite’s permissive aggregate syntax.

48. When can WITHOUT ROWID improve a table design?

WITHOUT ROWID can suit tables whose natural primary key is non-integer or composite, because the primary key becomes the storage key rather than maintaining both a rowid table and a separate primary-key index. It can reduce duplicate key storage and some lookups.

A compact table keyed by locale and message code is a reasonable candidate. It is not universally faster: wide keys affect secondary indexes, and incremental BLOB I/O and rowid-specific APIs have restrictions. Compare representative size and access patterns instead of applying it to every table mechanically.

49. How do VIRTUAL and STORED generated columns differ?

A VIRTUAL generated column computes its value when read, while a STORED generated column stores the computed value when the row is written. Both derive from allowed expressions over the row and cannot be assigned directly like ordinary columns.

A normalized search key can make query intent clearer and can be indexed when eligible. Choose based on read cost, storage and migration constraints: SQLite allows adding a VIRTUAL generated column through ADD COLUMN but not a STORED one that way. Generated columns require SQLite 3.31.0 or later and do not support arbitrary subqueries or nondeterministic calculations.

SQLite SQL — self-contained fixture

CREATE TABLE lines(
  quantity INTEGER NOT NULL,
  unit_cents INTEGER NOT NULL,
  total_cents INTEGER GENERATED ALWAYS AS
    (quantity * unit_cents) VIRTUAL
);
INSERT INTO lines(quantity, unit_cents) VALUES (3, 125);
SELECT total_cents FROM

CREATE TABLE lines(
  quantity INTEGER NOT NULL,
  unit_cents INTEGER NOT NULL,
  total_cents INTEGER GENERATED ALWAYS AS
    (quantity * unit_cents) VIRTUAL
);
INSERT INTO lines(quantity, unit_cents) VALUES (3, 125);
SELECT total_cents FROM

CREATE TABLE lines(
  quantity INTEGER NOT NULL,
  unit_cents INTEGER NOT NULL,
  total_cents INTEGER GENERATED ALWAYS AS
    (quantity * unit_cents) VIRTUAL
);
INSERT INTO lines(quantity, unit_cents) VALUES (3, 125);
SELECT total_cents FROM

The generated total is 375 integer cents. Production quantities still need domain and range checks.

50. What makes a parent key valid for a SQLite foreign key?

The referenced columns must be a suitable named primary key or have an eligible UNIQUE constraint or index with the required matching collation. A rowid that is not exposed as a named key is not a foreign-key target simply because it identifies rows internally.

If a text parent key uses a custom collation, inspect how its unique index was declared before blaming child data for a foreign-key mismatch. Some schema mismatches appear when preparing or executing modifying statements rather than when creating the child table. Test a valid insertion and an invalid reference as part of schema validation.

51. Why should child foreign-key columns often be indexed?

When a parent key is deleted or changed, SQLite may need to search the child table for referencing rows. Without a suitable child-side index, that check can scan many rows and prolong the write transaction.

For a local photo library with many tags per asset, an index beginning with the asset reference can help both integrity checks and ordinary lookups. It is generally a performance choice rather than a requirement for correctness. Reuse a suitable existing composite index and account for write overhead instead of blindly adding duplicates.

52. When are deferred foreign keys useful?

A deferred foreign key can allow temporarily unresolved references within a transaction, provided the constraint is satisfied when the transaction commits. This helps coordinated changes whose intermediate statement order would otherwise violate the relationship.

For example, an import may insert mutually related records in stages within one transaction. It does not permit committing an orphan. A failed commit can leave the transaction open for repair or rollback, and RESTRICT actions have immediate behavior that should not be confused with deferred checking. Exercise the exact declaration and error path before using it in an importer.

53. How should foreign_key_check and integrity_check be used together?

They inspect different classes of problems. foreign_key_check reports relationship violations, while integrity_check examines database structural and various consistency properties; integrity_check does not replace the foreign-key check.

After a controlled migration, run the relevant checks and investigate every returned issue before accepting the new database. An “ok” structural result does not establish that business totals or application semantics are correct. Validate expected rows and constraints separately, and retain the pre-migration recovery copy until the full acceptance criteria pass.

54. What is a covering index in SQLite?

A covering index contains all values needed for a query so SQLite can answer it without additional table lookups. In an ordinary rowid table, index entries also carry the rowid, which can help satisfy a selected INTEGER PRIMARY KEY value.

For a catalog lookup by category that returns only a name, a composite index on category and name may cover the query. Adding a large description just to cover one screen can substantially increase storage and write work. Compare the full workload and inspect EQP; SQLite’s mechanism should not be explained using PostgreSQL visibility-map rules.

SQLite SQL — self-contained fixture

CREATE TABLE catalog(id INTEGER PRIMARY KEY, category TEXT, name TEXT, body TEXT);
CREATE INDEX category_name ON catalog(category, name);
INSERT INTO catalog VALUES (1, 'guide', 'Setup', 'Long body');
EXPLAIN QUERY PLAN
SELECT name FROM catalog WHERE category='guide'

CREATE TABLE catalog(id INTEGER PRIMARY KEY, category TEXT, name TEXT, body TEXT);
CREATE INDEX category_name ON catalog(category, name);
INSERT INTO catalog VALUES (1, 'guide', 'Setup', 'Long body');
EXPLAIN QUERY PLAN
SELECT name FROM catalog WHERE category='guide'

CREATE TABLE catalog(id INTEGER PRIMARY KEY, category TEXT, name TEXT, body TEXT);
CREATE INDEX category_name ON catalog(category, name);
INSERT INTO catalog VALUES (1, 'guide', 'Setup', 'Long body');
EXPLAIN QUERY PLAN
SELECT name FROM catalog WHERE category='guide'

The tested plan uses a covering index. Plan wording and choices are version- and workload-dependent; this tiny fixture is not a performance benchmark.

55. How should a composite index match filtering and ordering?

Choose its leading keys to match useful equality restrictions, followed by range or ordering needs, while accounting for the actual workload. SQLite can then potentially narrow the search and avoid sorting, but not every combination of predicates uses every indexed column equally.

For a device log filtered by device and ordered by timestamp, an index beginning with device then timestamp is a natural candidate. A timestamp-only workload may need a different choice. Missing a leading key does not justify an absolute “never usable” claim because skip-scan and other planner choices exist; inspect representative plans.

56. What makes an expression index usable by SQLite?

The query expression must match the indexed expression closely; SQLite does not generally prove algebraic equivalence to find a match. Whitespace differences are tolerated, but reversing operands can prevent an otherwise mathematically equivalent expression from using the index.

For an index on x + y, a predicate on y + x should not be assumed to use it. Keep shared expressions consistent or expose a generated column when that improves maintainability. Only permitted deterministic expressions can be indexed, so a changing clock value is not an appropriate index key expression.

SQLite SQL — self-contained fixture

CREATE TABLE coordinates(x INTEGER, y INTEGER);
CREATE INDEX coordinate_sum ON coordinates(x + y);
EXPLAIN QUERY PLAN SELECT * FROM coordinates WHERE x + y = 12;
EXPLAIN QUERY PLAN SELECT * FROM coordinates WHERE y + x = 12

CREATE TABLE coordinates(x INTEGER, y INTEGER);
CREATE INDEX coordinate_sum ON coordinates(x + y);
EXPLAIN QUERY PLAN SELECT * FROM coordinates WHERE x + y = 12;
EXPLAIN QUERY PLAN SELECT * FROM coordinates WHERE y + x = 12

CREATE TABLE coordinates(x INTEGER, y INTEGER);
CREATE INDEX coordinate_sum ON coordinates(x + y);
EXPLAIN QUERY PLAN SELECT * FROM coordinates WHERE x + y = 12;
EXPLAIN QUERY PLAN SELECT * FROM coordinates WHERE y + x = 12

On the tested engine, the matching expression uses the index and the reversed expression scans. These diagnostic results are not an API text contract.

57. How does a partial index reduce maintenance for a selective subset?

A partial index includes only rows satisfying its WHERE predicate. It can make a frequently accessed small subset cheaper to index than the entire table, provided SQLite can establish that a query’s conditions imply that predicate.

For unfinished uploads, an index restricted to state='pending' can support the worker’s lookup while omitting completed history. Use the same meaningful predicate in the query and inspect the plan; SQLite’s implication rules are limited. The subset can grow, and updates that enter or leave it still require index maintenance.

58. What does an automatic index in a plan indicate?

SQLite may create a transient index for a statement when the estimated benefit of repeated lookups outweighs the cost of building it. It is not a persistent schema index that remains available to future statements.

A repeated automatic index during a frequent join can be a clue to evaluate a durable index or a different query shape. It is not proof that adding an index is always correct: occasional queries may benefit from transient work without permanent write overhead. Measure the recurrent workload and preserve the query’s semantics before changing the schema.

59. What does USE TEMP B-TREE in EQP suggest?

It commonly indicates temporary work for ordering, grouping or duplicate elimination that the chosen access path does not already supply. The extra structure can cost memory or temporary storage, but its presence alone does not prove a performance defect.

A local report that sorts most rows may reasonably need this work. Inspect whether an appropriate index can supply the required ordering without making filtering or writes worse. Reduce unnecessary result width and volume when valid, then measure; forcing an index merely to remove the phrase from a plan is not a performance objective.

60. How do ANALYZE and PRAGMA optimize help planning?

ANALYZE gathers statistics about data and indexes for the planner. Current SQLite guidance recommends PRAGMA optimize as a practical way to perform appropriate analysis work, with behavior improved in 3.46.0 and later to bound its scope.

Run it according to connection and schema-change patterns rather than after every statement. For long-lived connections, follow the documented initialization and periodic usage recommendations. Statistics can change plan choices, so verify representative workloads after a substantial data shift; collecting statistics does not add an access path or guarantee every query becomes faster.

61. What does SQLITE_BUSY_SNAPSHOT mean in WAL mode?

It means a connection is trying to upgrade a read snapshot to a writer after another connection has committed changes that make that upgrade invalid. Letting the old snapshot write would fork database history, so waiting cannot make that particular snapshot current.

End the transaction and retry the entire read-modify-write operation from a fresh view when the application’s retry policy permits it. Do not retry only the final UPDATE using decisions derived from the stale read. BEGIN IMMEDIATE can acquire the writer role before the reads when the workflow is known to require writing.

62. Why can a long-running reader cause a WAL file to grow?

A reader retains a snapshot whose end mark can prevent checkpointing from completing and the WAL from being reused as expected. Writers may continue appending while that old reader remains active, so file growth is not necessarily evidence that checkpointing was never requested.

Find long-lived transactions or unfinished cursors, especially exports and UI result streams. Shorten or redesign those reads where the consistency contract permits it. Increasing checkpoint frequency alone cannot remove a reader’s need for its snapshot; monitor progress and reader lifetime together.

63. How do checkpoint modes differ operationally?

PASSIVE checkpoints what it can without waiting for readers or writers and never invokes the busy handler. FULL uses the busy handler while waiting for no writer and for readers to use the latest snapshot, then checkpoints all frames. RESTART additionally waits until readers no longer depend on the WAL so the next writer can restart it; TRUNCATE adds shrinking the WAL to zero bytes on successful completion.

Inspect returned status and page counts rather than assuming the request completed its strongest goal. A busy handler can stop waiting, and competition for the checkpoint lock can itself return busy without invoking that handler. Repeatedly demanding TRUNCATE during traffic can harm latency; choose the mode around workload and recovery requirements, not just a preferred file size.

64. How does synchronous affect WAL durability?

The synchronous setting controls when SQLite asks the operating system to synchronize data to durable storage. In WAL mode, FULL adds synchronization at transaction commit, while NORMAL can trade some power-loss durability of recent commits for less synchronization work while preserving consistency under its documented assumptions.

Choose according to whether recent committed data may be lost after a power failure, and include the filesystem and hardware behavior in that assessment. A process-crash test is not a power-loss durability test. Do not turn synchronization off merely because a microbenchmark looks faster without documenting what guarantee changed.

65. Why is a network share a poor shortcut for multi-machine SQLite access?

SQLite relies on filesystem locking and storage behavior, and WAL requires same-host shared-memory coordination. Sharing one live file among machines through a network filesystem is not equivalent to running a database server that mediates requests.

If remote clients need access, put an application service in front of a database owned on one host or choose a server database suited to the workload. Copying a closed snapshot for distribution is a different use case. Do not infer safe concurrent writes from a successful single-user test on a mounted share.

66. How should bulk inserts be batched?

Use prepared parameterized statements and sensible transaction batches to amortize commit overhead, while limiting writer occupancy, memory and recovery work. One transaction per row can waste synchronization work; one enormous transaction can monopolize the writer and make failures costly.

For a device import, stage and validate input, then choose batch size from measured latency and interruption requirements. An all-or-nothing file import may require a larger transaction than a resumable feed. Document the resumability and deduplication rule rather than changing transaction boundaries solely for throughput.

67. Why must Python transaction behavior be configured explicitly?

Python’s sqlite3 wrapper has transaction-control behavior in addition to SQLite’s own autocommit state, and newer Python versions expose an autocommit attribute. Implicit BEGIN behavior, explicit SQL transactions and wrapper commit methods must be understood together.

For portable examples using explicit SQL BEGIN/COMMIT, this article uses isolation_level=None on the tested Python runtime and manages the transaction through SQL. Production code should follow the documentation for its exact Python version. Do not assume that a connection context manager closes the connection or that every executescript call preserves an existing transaction boundary.

68. Why is last_insert_rowid connection-sensitive?

It reports the rowid of the most recent successful relevant INSERT on that connection, not a global last row across the database. Inserts into WITHOUT ROWID tables do not update it, and concurrent use of one connection can make assumptions about “my last insert” unsafe.

Use RETURNING when suitable or obtain the driver’s insertion result immediately within a clearly owned connection. Never query MAX(id) to discover the row just inserted: another connection can insert a larger value. Also distinguish insertion identity from the outcome of an UPSERT that updated an existing row.

69. What are important memory and composability limits of RETURNING?

SQLite accumulates RETURNING output before delivering rows, so a large modification returning wide values can consume substantial memory. It is a top-level result to the application, not a general relation that can be placed inside a data-modifying CTE.

For a batch update, return only the fields the caller needs and bound the batch where the transaction contract allows. Do not assume streaming the cursor means the engine streamed each write and released its output immediately. If another table must be updated from the result, design explicit statements within the same transaction rather than porting PostgreSQL syntax unchanged.

70. How do ABORT, FAIL and IGNORE change constraint-error behavior?

ABORT normally backs out the current statement’s changes while keeping earlier transaction work. FAIL can preserve changes made earlier in the same statement, and IGNORE skips certain offending rows without reporting the ordinary constraint error. Their applicability depends on the constraint; foreign-key errors do not simply follow every requested alternative.

For a multirow import, FAIL can leave a partial statement result that the caller must understand, while IGNORE can hide rejected records. Prefer an explicit acceptance and error-reporting policy. Do not infer transaction-wide rollback from the fact that one statement raised an exception.

71. How do json_extract and the JSON arrow operators differ?

With a single scalar path, json_extract can return an SQL scalar such as text, integer or NULL; multiple paths produce JSON text representing an array. The arrow operators separate JSON-text extraction from SQL-value extraction: -> returns a JSON representation and ->> returns an SQL value.

For a payload containing a string label, the JSON representation includes JSON quoting while the SQL text value does not. This affects comparisons and composing new documents. Check types with representative null, missing, array and scalar cases instead of assuming every extraction returns a JSON string.

SQLite SQL — self-contained fixture

SELECT json_extract('{"label":"blue"}', '$.label'),
       '{"label":"blue"}' -> '$.label',
       '{"label":"blue"}' ->> '$.label'

SELECT json_extract('{"label":"blue"}', '$.label'),
       '{"label":"blue"}' -> '$.label',
       '{"label":"blue"}' ->> '$.label'

SELECT json_extract('{"label":"blue"}', '$.label'),
       '{"label":"blue"}' -> '$.label',
       '{"label":"blue"}' ->> '$.label'

The values are SQL text blue, JSON text "blue", and SQL text blue respectively.

72. How can JSON null be distinguished from a missing path?

Use json_type for the path as well as extraction. A present JSON null has the JSON type name 'null', whereas a missing path produces SQL NULL from json_type; json_extract alone can return SQL NULL in both cases.

This distinction matters in a patch protocol where an explicit null means “clear the value” and omission means “leave it unchanged.” Validate the enclosing document first and preserve the protocol’s field-presence semantics. Treating both cases as a default value can silently change the meaning of an update.

SQLite SQL — self-contained fixture

SELECT json_type('{"x":null}', '$.x') AS explicit_null,
       json_type('{"x":null}', '$.missing') AS absent,
       json_extract('{"x":null}', '$.x') AS

SELECT json_type('{"x":null}', '$.x') AS explicit_null,
       json_type('{"x":null}', '$.missing') AS absent,
       json_extract('{"x":null}', '$.x') AS

SELECT json_type('{"x":null}', '$.x') AS explicit_null,
       json_type('{"x":null}', '$.missing') AS absent,
       json_extract('{"x":null}', '$.x') AS

The result is the text null, SQL NULL, and SQL NULL, exposing presence separately from extraction.

73. Why can json_object turn an apparent array into a quoted string?

A value argument supplied as ordinary SQL text is normally encoded as a JSON string, even when its characters look like an array. A value produced directly by an appropriate JSON function is recognized as JSON for composition.

Compare passing the text '[2,4]' with passing json('[2,4]') to json_object. The first produces a string field; the second produces an array field. Do not repair this by hand-splicing JSON punctuation, which reintroduces escaping and validation errors; use the functions with deliberate value types.

SQLite SQL — self-contained fixture

SELECT json_object('values', '[2,4]') AS string_field,
       json_object('values', json('[2,4]')) AS

SELECT json_object('values', '[2,4]') AS string_field,
       json_object('values', json('[2,4]')) AS

SELECT json_object('values', '[2,4]') AS string_field,
       json_object('values', json('[2,4]')) AS

One result contains a JSON string field; the other contains an array field.

74. How do json_each and json_tree differ?

json_each walks the immediate children of an object or array, while json_tree recursively exposes the structure. Their table-valued results include path and type information that helps distinguish containers from scalar leaves.

For a settings array, json_each can produce one row per element without application-side parsing. For nested diagnostics, json_tree can locate leaf values, but recursive expansion can multiply rows considerably. Filter deliberately and do not treat internal traversal IDs as stable external identifiers across documents or engine versions.

SQLite SQL — self-contained fixture

SELECT key, value, type
FROM json_each('[10,20,30]')
ORDER BY key

SELECT key, value, type
FROM json_each('[10,20,30]')
ORDER BY key

SELECT key, value, type
FROM json_each('[10,20,30]')
ORDER BY key

This returns three integer elements with array indexes 0, 1 and 2.

75. What is SQLite JSONB, and what does it not promise?

SQLite JSONB, introduced in 3.45.0, stores SQLite’s internal JSON representation as a BLOB to avoid repeated text parsing and potentially reduce size. It is an SQLite-specific opaque format, not PostgreSQL JSONB and not a documented interchange format for other systems.

Use JSON functions to create and inspect it, and export ordinary JSON when interoperability is needed. It does not generally promise constant-time lookup of arbitrary fields. Benchmark the actual operation and keep validation at trust boundaries rather than assuming that any BLOB labeled JSONB is valid.

SQLite SQL — self-contained fixture

SELECT typeof(jsonb('{"enabled":true}')),
       json(jsonb('{"enabled":true}'))

SELECT typeof(jsonb('{"enabled":true}')),
       json(jsonb('{"enabled":true}'))

SELECT typeof(jsonb('{"enabled":true}')),
       json(jsonb('{"enabled":true}'))

The first result is blob; the second returns the JSON text representation. JSONB requires SQLite 3.45.0 or later.

76. How can a JSON field be indexed without indexing an entire document?

Create an eligible expression index or generated column for the specific extracted value used by the query. The query must use a compatible expression and type contract so the planner can recognize the access path.

For local documents filtered by a string project key, an index on json_extract(payload,'$.project') can avoid parsing and scanning every document during each lookup. Validate that the field has the intended type; numbers and strings are not interchangeable identifiers. This is targeted relational indexing, not a general inverted index over every JSON path.

SQLite SQL — self-contained fixture

CREATE TABLE documents(payload TEXT NOT NULL CHECK(json_valid(payload)));
CREATE INDEX document_project ON documents(json_extract(payload, '$.project'));
INSERT INTO documents VALUES ('{"project":"atlas","title":"Guide"}');
SELECT json_extract(payload, '$.title')
FROM documents
WHERE json_extract(payload, '$.project')='atlas'

CREATE TABLE documents(payload TEXT NOT NULL CHECK(json_valid(payload)));
CREATE INDEX document_project ON documents(json_extract(payload, '$.project'));
INSERT INTO documents VALUES ('{"project":"atlas","title":"Guide"}');
SELECT json_extract(payload, '$.title')
FROM documents
WHERE json_extract(payload, '$.project')='atlas'

CREATE TABLE documents(payload TEXT NOT NULL CHECK(json_valid(payload)));
CREATE INDEX document_project ON documents(json_extract(payload, '$.project'));
INSERT INTO documents VALUES ('{"project":"atlas","title":"Guide"}');
SELECT json_extract(payload, '$.title')
FROM documents
WHERE json_extract(payload, '$.project')='atlas'

The query returns Guide. Add field-type requirements if project must always be a string.

77. How should user input be passed to an FTS5 MATCH query?

Bind it as a value to protect the SQL statement, then separately decide whether the product accepts FTS query syntax or treats input as literal search text. Parameterization prevents SQL injection but does not disable FTS operators, phrases or syntax errors inside the bound search expression.

A search box supporting advanced syntax can expose that contract and handle parse errors. A literal phrase search needs appropriate FTS string quoting, including doubled embedded quotation marks. Bound input is still capable of an expensive or unexpectedly broad search, so apply resource and UX limits suited to the application.

Python — self-contained SQLite fixture

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as db:
    db.execute("CREATE VIRTUAL TABLE help USING fts5(body)")
    db.executemany("INSERT INTO help(body) VALUES (?)", [
        ("reset network settings",), ("change display settings",)
    ])
    user_text = "network settings"
    phrase = '"' + user_text.replace('"', '""') + '"'
    rows = db.execute(
        "SELECT rowid FROM help WHERE help MATCH ? ORDER BY rowid",
        (phrase,),
    ).fetchall()
    assert rows == [(1,)]
import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as db:
    db.execute("CREATE VIRTUAL TABLE help USING fts5(body)")
    db.executemany("INSERT INTO help(body) VALUES (?)", [
        ("reset network settings",), ("change display settings",)
    ])
    user_text = "network settings"
    phrase = '"' + user_text.replace('"', '""') + '"'
    rows = db.execute(
        "SELECT rowid FROM help WHERE help MATCH ? ORDER BY rowid",
        (phrase,),
    ).fetchall()
    assert rows == [(1,)]
import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as db:
    db.execute("CREATE VIRTUAL TABLE help USING fts5(body)")
    db.executemany("INSERT INTO help(body) VALUES (?)", [
        ("reset network settings",), ("change display settings",)
    ])
    user_text = "network settings"
    phrase = '"' + user_text.replace('"', '""') + '"'
    rows = db.execute(
        "SELECT rowid FROM help WHERE help MATCH ? ORDER BY rowid",
        (phrase,),
    ).fetchall()
    assert rows == [(1,)]

The quoted phrase finds the first document. This requires FTS5 in the deployed build and deliberately treats the input as one literal phrase.

78. What does changing an FTS5 tokenizer affect?

The tokenizer determines how text becomes searchable terms, including case handling, punctuation and optional stemming or diacritic behavior. It therefore changes search semantics rather than merely changing performance.

A technical-document search may need identifiers such as error codes treated differently from natural-language prose. Evaluate representative queries and documents before selecting unicode61, porter, trigram or a custom tokenizer. A tokenizer change normally requires rebuilding the relevant index content; existing postings do not retroactively acquire the new tokenization policy.

79. How should FTS5 ranking and snippets be interpreted?

Ranking functions such as bm25 estimate relevance under their scoring model; in SQLite FTS5, lower bm25 values represent better matches. Snippet and highlight helpers provide presentation context, not proof that the highest-ranked document is objectively the correct answer.

For a local help search, compare relevance on realistic queries and use a deterministic tie-breaker where needed. Escape or sanitize the resulting text appropriately for its display context, especially when adding markup around matches. Stored document content can be untrusted even when the search query was safely bound.

80. Why must an external-content FTS5 index be maintained explicitly?

An external-content table separates the authoritative document content from the FTS index. The application must keep their changes synchronized, often through carefully designed triggers or another transactional maintenance path.

Creating maintenance triggers does not automatically index all rows that existed before the triggers; an initial rebuild or population step is needed. If content and postings diverge, MATCH results can disagree with ordinary content reads. Test insertion, update and deletion paths, including recovery and migration, rather than checking only the first successful search.

SQLite Interview Questions and Answers for Experienced

Strong answers connect the engine contract to recovery and operational evidence instead of treating a successful query as proof of system reliability.

81. How would you design a safe read-modify-write workflow for SQLite?

Acquire the appropriate transaction before reading the state used for a write, keep the critical section short and validate the write outcome. For a workflow that certainly writes, BEGIN IMMEDIATE can move writer contention to the start instead of discovering an invalid snapshot upgrade after calculations.

An application allocating a local sequence can read and update within that transaction, then commit before performing slow external work. This protects database decisions but does not make an external API call atomic with the commit. Define idempotency and recovery separately, and retry from fresh reads when the database transaction must be restarted.

82. How should a retry policy handle contention without duplicating effects?

Classify the exact error and transaction state, then retry only operations whose outcome and side effects are understood. Use bounded attempts and backoff for suitable transient contention; stale-snapshot errors require restarting the whole dependent transaction.

A local sync queue should attach a stable request identity so an application retry can recognize work already committed. Do not resend an external message merely because the client lost its response. Constraint failures and malformed SQL usually require correction, while an uncertain commit outcome requires reconciliation rather than an unconditional replay.

83. When does a single application writer queue help?

A writer queue can make admission, ordering and backpressure explicit when many application tasks compete for SQLite’s single writer. It can reduce chaotic connection-level retries and provide a clear place to batch compatible work.

For a desktop application, background imports and UI edits can submit bounded transactions to the same owner. The queue does not remove the underlying serialization limit and can become a latency bottleneck or failure point. Preserve priority, cancellation and shutdown behavior, and avoid turning slow computation or network calls into work performed while the database transaction is held.

84. What is the significance of SQLite’s WAL-reset fix for deployment?

The official WAL documentation describes a rare race affecting older versions when concurrent connections write or checkpoint, with fixes in 3.51.3 and later and selected backports including 3.50.7. A successful ordinary workload test does not establish that an affected build is free of the race.

Inventory the actual linked libraries and use an appropriately patched release for deployment. The local 3.50.4 fixtures in this article illustrate deterministic semantics only; they do not validate that fix or recommend that runtime for production. Avoid claiming a retry policy or checkpoint frequency is a substitute for the engine correction.

85. How would you prove an application really gets repeatable WAL reads?

Use a file-backed fixture with two distinct connections and explicit transaction boundaries. Establish a read snapshot on one connection, commit a change on the other, and verify that the first retains its old view until its transaction ends.

Then begin a new read and verify the committed value becomes visible. This tests a specific ordered interaction, not every timing race or failure mode. Ensure both connections report WAL mode and avoid confusing wrapper-created transactions with the intended SQL boundaries; an in-memory single-connection test cannot establish this behavior.

Python — self-contained SQLite fixture

import sqlite3
from contextlib import closing
from pathlib import Path
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    path = Path(directory) / "snapshot.db"
    with closing(sqlite3.connect(path, isolation_level=None)) as reader, \
         closing(sqlite3.connect(path, isolation_level=None)) as writer:
        assert reader.execute("PRAGMA journal_mode=WAL").fetchone() == ("wal",)
        reader.execute("CREATE TABLE state(n INTEGER)")
        reader.execute("INSERT INTO state VALUES (1)")
        reader.execute("BEGIN")
        assert reader.execute("SELECT n FROM state").fetchone() == (1,)
        writer.execute("UPDATE state SET n=2")
        assert reader.execute("SELECT n FROM state").fetchone() == (1,)
        reader.execute("COMMIT")
        assert reader.execute("SELECT n FROM state").fetchone() == (2,)
import sqlite3
from contextlib import closing
from pathlib import Path
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    path = Path(directory) / "snapshot.db"
    with closing(sqlite3.connect(path, isolation_level=None)) as reader, \
         closing(sqlite3.connect(path, isolation_level=None)) as writer:
        assert reader.execute("PRAGMA journal_mode=WAL").fetchone() == ("wal",)
        reader.execute("CREATE TABLE state(n INTEGER)")
        reader.execute("INSERT INTO state VALUES (1)")
        reader.execute("BEGIN")
        assert reader.execute("SELECT n FROM state").fetchone() == (1,)
        writer.execute("UPDATE state SET n=2")
        assert reader.execute("SELECT n FROM state").fetchone() == (1,)
        reader.execute("COMMIT")
        assert reader.execute("SELECT n FROM state").fetchone() == (2,)
import sqlite3
from contextlib import closing
from pathlib import Path
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    path = Path(directory) / "snapshot.db"
    with closing(sqlite3.connect(path, isolation_level=None)) as reader, \
         closing(sqlite3.connect(path, isolation_level=None)) as writer:
        assert reader.execute("PRAGMA journal_mode=WAL").fetchone() == ("wal",)
        reader.execute("CREATE TABLE state(n INTEGER)")
        reader.execute("INSERT INTO state VALUES (1)")
        reader.execute("BEGIN")
        assert reader.execute("SELECT n FROM state").fetchone() == (1,)
        writer.execute("UPDATE state SET n=2")
        assert reader.execute("SELECT n FROM state").fetchone() == (1,)
        reader.execute("COMMIT")
        assert reader.execute("SELECT n FROM state").fetchone() == (2,)

The statements are deliberately ordered on one thread; they demonstrate snapshot visibility, not simultaneous checkpoint/write race testing or crash resilience.

86. Why can copying only the main database file produce an incomplete backup?

In WAL mode, committed changes may still exist only in the WAL, so the main file alone may not represent the latest committed database. Copying files while writes occur also risks capturing pieces from different moments.

Use the online backup API or another documented consistent backup procedure rather than manually guessing which sidecars to copy. A closed, quiescent database has a different copying contract from a live one. Verify a restored copy with integrity and application checks; the existence of a copied file is not evidence that the backup is complete or usable.

87. What makes the online backup API preferable for a live application?

It creates a consistent destination database through SQLite’s own mechanisms and can work incrementally so the source is not held continuously for the full copy duration. Host wrappers such as Python expose this capability without requiring the application to implement raw page copying.

Use a separate destination under controlled ownership, handle busy/error outcomes and publish the backup only after completion. The API replaces destination contents, so the target must be intentional. Test restoring it with the application’s schema and data expectations instead of treating backup completion as the end of the recovery procedure.

Python — self-contained SQLite fixture

import sqlite3
from contextlib import closing
from pathlib import Path
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    target = Path(directory) / "backup.db"
    with closing(sqlite3.connect(":memory:")) as source, \
         closing(sqlite3.connect(target)) as backup:
        source.execute("CREATE TABLE settings(k TEXT PRIMARY KEY, v TEXT)")
        source.execute("INSERT INTO settings VALUES ('theme', 'dark')")
        source.commit()
        source.backup(backup)
    with closing(sqlite3.connect(target)) as restored:
        assert restored.execute("PRAGMA integrity_check").fetchone() == ("ok",)
        assert restored.execute("SELECT k,v FROM settings").fetchall() == [
            ("theme", "dark")
        ]
import sqlite3
from contextlib import closing
from pathlib import Path
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    target = Path(directory) / "backup.db"
    with closing(sqlite3.connect(":memory:")) as source, \
         closing(sqlite3.connect(target)) as backup:
        source.execute("CREATE TABLE settings(k TEXT PRIMARY KEY, v TEXT)")
        source.execute("INSERT INTO settings VALUES ('theme', 'dark')")
        source.commit()
        source.backup(backup)
    with closing(sqlite3.connect(target)) as restored:
        assert restored.execute("PRAGMA integrity_check").fetchone() == ("ok",)
        assert restored.execute("SELECT k,v FROM settings").fetchall() == [
            ("theme", "dark")
        ]
import sqlite3
from contextlib import closing
from pathlib import Path
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    target = Path(directory) / "backup.db"
    with closing(sqlite3.connect(":memory:")) as source, \
         closing(sqlite3.connect(target)) as backup:
        source.execute("CREATE TABLE settings(k TEXT PRIMARY KEY, v TEXT)")
        source.execute("INSERT INTO settings VALUES ('theme', 'dark')")
        source.commit()
        source.backup(backup)
    with closing(sqlite3.connect(target)) as restored:
        assert restored.execute("PRAGMA integrity_check").fetchone() == ("ok",)
        assert restored.execute("SELECT k,v FROM settings").fetchall() == [
            ("theme", "dark")
        ]

The source transaction is committed before backup, and the destination is reopened and checked. This fixture does not simulate concurrent backup traffic or storage failure.

88. When is VACUUM INTO useful compared with an ordinary backup?

VACUUM INTO creates a compact database copy without changing the source file’s logical contents, making it useful when a rebuilt snapshot is desired. The online backup API is a different mechanism and can support incremental copying.

Choose based on workload, available space and backup scheduling rather than calling one universally better. The destination must meet SQLite’s requirements, and interrupted output should not be treated as complete. Verify the resulting copy before distributing it; a compact file still needs a compatibility contract with the application that opens it.

89. Why might deleting rows not shrink a SQLite file?

Deleted pages can remain on the freelist for reuse, so file size need not fall with the live row count. This can be efficient for an application that repeatedly deletes and inserts similar amounts of data.

Inspect page and freelist information before concluding there is a leak. VACUUM rebuilds and can reclaim space but requires work and free disk capacity; auto-vacuum modes have their own layout and maintenance trade-offs. Schedule reclamation according to storage pressure and workload rather than running a full rebuild after every deletion.

90. How should a migration change a table when direct ALTER support is insufficient?

Use SQLite’s documented table-rebuild procedure: preserve dependent definitions, create the desired replacement, copy validated data, replace the old table and recreate affected indexes, triggers and views within a controlled transaction. Handle foreign-key settings outside the transaction where required and run relationship checks before accepting the migration.

Do not begin by casually renaming the old table and assume every dependency will still point where intended. Test on a realistic copy, preserve a recovery path and verify transformed values, not just row counts. Directly patching sqlite_schema bypasses important protections and is not the normal shortcut.

91. How has recent ALTER COLUMN support changed migration planning?

SQLite 3.53.0 added ALTER COLUMN support for setting or dropping NOT NULL constraints. This is narrower than arbitrary type or constraint alteration and does not make old migration recipes or older runtimes automatically compatible.

Check the minimum engine across every device or deployment before using it. The local 3.50.4 runtime cannot execute that newer syntax, so it is documented here without a runtime claim. A table rebuild remains relevant for unsupported transformations; adding NOT NULL also requires confirming that existing data satisfies the intended rule.

92. How can user_version support application migrations?

PRAGMA user_version provides an integer field the application can use to track its schema version. SQLite does not interpret that number as a migration plan, so the application must define transitions and update the marker consistently with the schema change.

An offline application can compare the stored version with supported versions and run an authorized migration transaction. Do not confuse it with schema_version, which SQLite uses internally and should not be manually repurposed. Reject unsupported newer schemas clearly instead of opening them and guessing that the columns are close enough.

93. What compatibility checks matter when distributing a database file?

Check both file-level accessibility and schema features against the oldest supported engine. A stable SQLite file format does not mean an old parser understands STRICT tables, generated columns or every newer SQL expression stored in the schema.

For a shipped read-only catalog, test opening and representative queries on the actual application runtime, including extensions and collations. Record a minimum engine requirement and validate upgrade or rollback paths. A database created by a newer release can remain compatible when it avoids newer features, but that must be established rather than inferred from its filename.

Feature

Minimum engine noted here

Deployment check

Generated columns

3.31.0

Stored schema must be understood by every reader.

RETURNING

3.35.0

Host driver must consume result-bearing writes correctly.

STRICT tables

3.37.0

Type and schema compatibility must both be tested.

SQLite JSONB

3.45.0

Keep the binary format SQLite-specific.

ALTER COLUMN SET/DROP NOT NULL

3.53.0

Not executed on the local 3.50.4 runtime.

94. What does ATTACH imply for cross-database atomicity?

ATTACH lets one connection access additional database files, but transaction guarantees across the set depend on journaling mode and configuration. In WAL mode, changes are atomic within each individual database, not necessarily across all attached files as one crash-atomic unit.

Do not split an invariant across two WAL files and assume one COMMIT provides the same recovery guarantee as a single database. If separate files are essential, design reconciliation or move the invariant into one atomic store. Also qualify schema names so queries do not accidentally resolve to an unintended attached object.

95. How should an application respond to disk-full or I/O errors?

Stop assuming the intended write succeeded, preserve the exact error and inspect transaction state before cleanup or retry. Some serious errors can roll back a transaction, and subsequent operations should not blindly continue as though earlier changes remain pending.

For a local recorder, surface an actionable storage failure and retain unsaved input through an appropriate application mechanism when feasible. Avoid repeatedly replaying a large batch into a full filesystem. Recovery tests should include controlled failure injection on disposable storage; ordinary successful fixtures do not establish crash or disk-failure resilience.

96. What should happen first when corruption is suspected?

Preserve evidence and a recoverable copy, stop unnecessary writes and investigate the storage, application and engine context. Structural checks can characterize problems, but they do not identify every cause or automatically restore correct business data.

Restore a known-good backup when that meets the recovery objective. Salvage tooling may recover partial records, yet recovered data must be validated for missing relationships and lost values before use. Do not delete journals, overwrite the source or run speculative schema edits merely to make the first error message disappear.

97. What security boundary does SQLite provide around a local file?

SQLite is not a server with built-in per-user GRANT accounts protecting the file from its owning process. Access is primarily controlled by the operating system, application design and the privileges of the process that can open the database and its sidecars.

For a multi-user desktop system, protect the directory, backups and exported copies as well as the main file. Application tenant filters are not a substitute for isolating untrusted local processes. If users need independent server-enforced privileges, place a trusted service boundary around access or choose an appropriate server database.

98. Is encryption included automatically when using SQLite?

No. Ordinary SQLite databases are not automatically encrypted at rest. Encryption requires a deliberately selected solution, such as a suitable SQLite extension or storage-layer encryption, together with key management and recovery procedures.

Evaluate what is protected: the main file, WAL, temporary files, backups and exported data may have different exposure paths. Do not describe a password in the application UI as database encryption unless the storage design actually enforces it. Test compatibility and recovery with the selected implementation rather than assuming all “SQLite encryption” products share one file format.

99. Why should untrusted database files be treated differently from trusted application data?

A database file can contain schema objects and expressions that interact with application-defined functions or consume resources when queried. Parsing a file successfully does not mean every stored schema definition is safe to execute with the application’s privileges.

Use current patched libraries, appropriate defensive configuration, limits and trusted-schema restrictions where supported. Keep dangerous application functions out of contexts where a supplied schema could invoke them. The exact protection depends on the wrapper and build, so test the intended restriction instead of advertising a generic “safe SQLite file viewer” based only on read-only opening.

100. What can an authorizer callback enforce, and what can it not replace?

An authorizer callback can allow or deny classes of SQL operations while statements are compiled on its connection. It can support a restricted query interface by rejecting writes or access to selected objects.

It is not an operating-system sandbox or a substitute for controlling filesystem permissions, extensions and application callbacks. Design and test the callback for all relevant action codes and schema paths, and ensure statements are prepared under the intended policy. A simplistic keyword filter cannot provide the same structured decision point and can easily miss alternate SQL forms.

101. Why should extension loading be tightly controlled?

Loadable extensions execute native code in the application process. Allowing arbitrary paths or untrusted extension binaries therefore grants capabilities far beyond selecting rows from a database.

Enable loading only when the application needs a known, trusted extension through an explicitly controlled path, and disable it afterward where appropriate. Parameterized SQL does not make a malicious native library harmless. Package, version and review extension dependencies as executable code, including their platform compatibility and update lifecycle.

102. How should application-defined SQL functions be declared and tested?

Register functions with clear type, NULL, error and determinism contracts. Mark a function deterministic only when equal inputs truly produce equal outputs under the relevant persistent assumptions; SQLite may use that promise in schema expressions and optimizations.

A normalization function backed by a changing external configuration should not casually be used in an expression index. If its semantics change, stored derived data or indexes may need rebuilding. Test malformed input and callback exceptions as well as ordinary values, and avoid hidden network or filesystem side effects inside a query function.

103. What risks arise when a custom collation changes behavior?

Indexes ordered under the old comparison rule may no longer agree with the new rule, which can affect search and uniqueness behavior. A collation name alone does not encode the version of the code implementing it.

For a distributed catalog, keep normalization and collation implementations consistent across writers and readers. Plan REINDEX or other appropriate migration steps when the comparison changes, and check for newly equivalent keys before enforcing uniqueness. Treating a library update as a harmless code-only change can leave persisted ordering assumptions invalid.

104. How can a progress handler support cancellation of expensive queries?

A progress handler runs periodically during relevant SQLite work and can request interruption by returning a nonzero value. It gives an application a way to enforce a cancellation flag or approximate work budget without embedding arbitrary LIMIT clauses into every query.

Keep the callback lightweight and obey its reentrancy restrictions; it should not modify the invoking connection. Interruption is an error path that needs statement and transaction cleanup. It is not an exact wall-clock scheduler, so combine it with appropriate application deadlines and test cancellation on representative expensive statements.

105. Why are resource limits useful even for read-only SQL?

A read-only query can still allocate substantial memory, expand complex expressions or perform excessive work. SQLite’s configurable limits and application result limits help contain that resource consumption when inputs or query shapes are not fully trusted.

For an analyst query panel, constrain accepted SQL complexity, returned rows and execution budget according to the product’s needs. Lower limits carefully so legitimate queries still work, and test errors at the boundary. Read-only mode protects against writes; it does not by itself prevent denial of service through computation.

106. Does serialized threading mode make shared connections logically safe?

Serialized mode protects SQLite API access with mutexes, but it does not give each application thread an independent transaction on the same connection. Operations can interleave in ways that violate the application’s intended sequence even when the library remains internally consistent.

Prefer clear connection ownership or explicit synchronization around whole logical units. Python also has its own same-thread policy that must be understood separately. Turning off a wrapper check is not a complete concurrency design, and no threading mode creates multiple simultaneous writers to the same database.

107. Why should open SQLite connections not be carried across fork boundaries?

A fork can duplicate process memory and file descriptors without creating a clean independent SQLite connection and locking lifecycle. Reusing inherited connection state in parent and child can lead to unsafe behavior rather than ordinary two-connection coordination.

Create connections in the process that owns and uses them, and close or avoid inherited handles according to the application’s process model. For worker pools, initialize database access after the worker starts. A successful small test after fork does not establish that inherited locks, caches and cleanup remain safe under real traffic.

108. What should a performance investigation measure beyond query elapsed time?

Measure transaction duration, busy waits, result materialization, application processing, WAL/checkpoint behavior and storage conditions alongside the SQL plan. A fast individual SELECT does not explain a UI stall caused by a long-held writer or slow row conversion.

Use representative database size and distributions, and separate cold-cache from warm-cache observations. Record engine version and compile options so results are reproducible. A microbenchmark over an in-memory database may help compare an expression, but it cannot predict filesystem latency or contention in a deployed application.

109. How should query-plan changes after an engine upgrade be evaluated?

Compare correctness and measured performance on representative workloads, including skewed parameters and data sizes, rather than requiring every plan string to remain identical. SQLite’s optimizer evolves, and its diagnostic text is not a stable application interface.

Keep a reversible deployment plan and capture regressions with schema, statistics and a reduced reproduction. An older plan is not automatically better because it is familiar. Query Planner Stability Guarantee settings have specific prerequisites and do not substitute for compatibility testing across engine versions or schema changes.

110. When should INDEXED BY be used cautiously?

INDEXED BY requires SQLite to use a named index for the table access or fail to prepare the statement; it is stronger than an advisory hint. The documentation positions it as a way to detect unwanted plan changes rather than the first tool for routine tuning.

First fix query semantics, indexing and statistics. If a carefully justified requirement remains, test its behavior when the index changes or disappears and include that dependency in migrations. Hard-coding an index into every query can turn a harmless schema adjustment into an application failure and prevent better future choices.

111. How can SQLite’s join strategy affect large local joins?

SQLite implements joins using nested loops, with the planner choosing loop order and access paths. Repeated inner work can dominate when an outer relation is large or a useful lookup index is absent.

Inspect EQP’s loop structure and measure with realistic cardinalities. An automatic index may reduce repeated scanning, but adding persistent indexes or reducing valid intermediate work can be more appropriate for recurrent queries. Do not import a diagnosis that assumes a PostgreSQL hash-join node exists; use the mechanisms of the actual engine.

112. How should large BLOBs be handled when full materialization is costly?

Consider incremental BLOB I/O when the schema and API support it, so the application can read or write ranges without loading the entire value. A zeroblob can reserve a fixed-size value for subsequent incremental writes.

The handle has constraints, including rowid-table requirements and fixed size, and an open BLOB handle participates in statement/transaction lifetime. Close it promptly and handle invalidation if its row changes. Compare storing content in the database with managed external files based on atomicity, backup and access requirements rather than assuming one is always faster.

113. What does changing page size require in a WAL database?

SQLite does not allow changing page size while the database remains in WAL mode. A controlled change requires following the documented journal-mode and rebuild procedure, with compatible maintenance conditions and sufficient space.

Page size affects storage and I/O trade-offs, so do not change it solely because a larger number sounds faster. Benchmark the actual mix of small lookups, scans and writes, and verify backup/restore compatibility afterward. The operation belongs in planned maintenance, not a per-connection initialization script executed during ordinary traffic.

114. How do cache_size and mmap_size differ conceptually?

The page cache holds database pages managed by SQLite, while memory-mapped I/O can map eligible database content into the process address space. They influence different parts of memory and I/O behavior and are subject to runtime, build and platform limits.

Measure resident memory and workload behavior before increasing either across many connections. A negative cache_size value expresses an approximate kibibyte target rather than a page count, which matters when page size changes. Neither setting fixes a missing index or long transaction, and aggregate process memory can exceed one connection’s apparent cache target.

115. What makes immutable=1 dangerous on a changing database?

It asserts that the database file cannot change, allowing SQLite to skip locking and change detection. If another process modifies the file despite that promise, reads can become incorrect or produce errors because the engine is relying on a false premise.

Use it only for genuinely immutable deployment artifacts under controlled ownership. It is not a workaround for permission failures on an actively updated database. A read-only connection merely refrains from writing; an immutable connection additionally assumes nobody else changes the data, which is a much stronger contract.

116. How should an offline synchronization protocol use SQLite transactions?

Use transactions to atomically update local state and record the intent to synchronize, then let a separate protocol deliver changes and reconcile responses. SQLite protects local database atomicity; it does not supply distributed conflict resolution or exactly-once delivery across devices.

For a local edit, store a stable operation ID and payload with the changed record in one transaction. After network acknowledgment, update delivery state through another controlled transaction. Handle duplicates, stale server versions and deleted records explicitly. Holding a write transaction open while waiting for the network makes local contention worse without creating distributed atomicity.

117. When is one database per tenant a reasonable design?

Separate files can provide operational isolation and separate writer domains when tenants are truly independent. They also multiply connections, migrations, backups and file-management responsibilities, and make cross-tenant queries or atomic changes harder.

For a desktop workspace application, per-workspace files may align with export and ownership boundaries. For a service with thousands of active tenants, quantify file descriptors, memory and maintenance cost before adopting the pattern. Filesystem isolation still needs enforcement; choosing separate names does not itself prevent one process from opening another tenant’s database.

118. How can dbstat help investigate SQLite storage overhead?

The dbstat virtual table reports space use for B-tree pages, including payload and unused space, when the build enables SQLITE_ENABLE_DBSTAT_VTAB. It can help distinguish a large table from a large secondary index and show where page utilization deserves investigation.

For a local document store, compare the bytes attributed to content and search indexes before deciding that every byte is a leak. dbstat omits freelist pages, pointer-map pages and the lock page, so its totals are not a complete filesystem accounting. Its observations guide a hypothesis about schema or packing; they do not by themselves prove that VACUUM or dropping an index will improve the application.

119. How should SQLite MATERIALIZED and NOT MATERIALIZED CTE hints be interpreted?

These SQLite 3.35.0-and-later hints influence how a CTE is implemented. MATERIALIZED uses an ephemeral result and acts as an optimization fence, preventing transformations such as flattening and predicate push-down across that boundary. NOT MATERIALIZED permits treatment like an ordinary view or subquery but does not forbid the planner from materializing it.

For a costly intermediate calculation, a fence may avoid an undesirable transformation but also prevent a selective outer filter from reducing work early. Compare the plan and measured workload with and without the hint, preserving query semantics. Leave the choice to the planner unless evidence justifies intervention, and do not use a hint as a general contract for the number of calls to a function with side effects.

120. How would you diagnose a report that passes tests on SQLite but fails on PostgreSQL?

Compare the dialect and runtime contracts before assuming either engine is broken. SQLite’s flexible typing, bare aggregate columns, scalar-subquery behavior, date representation and function availability can accept or interpret queries differently from PostgreSQL.

Reduce the failing case to a small fixture with explicit schema and expected rows, then identify the actual portability requirement. Tests intended to validate PostgreSQL should run against PostgreSQL; SQLite tests are valuable for SQLite behavior, not as a complete substitute. Keep engine-specific SQL behind a deliberate boundary or maintain separate implementations where semantics genuinely differ.

SQLite Documentation and Further Reading

Use these primary references with the actual SQLite library linked into the application. The executable fixtures use SQLite 3.50.4 and do not validate later engine fixes, newer ALTER COLUMN support, production load or storage-failure behavior. Feature availability can also depend on compile options and the host wrapper; deployment should use an appropriately patched supported release.

Team Sherlock.sh

Building fraud intelligence for live high-stake video interactions (financial discussions, hiring interviews, board meeting etc).

Catch candidate fraud before it costs you.

Sherlock AI helps you detect AI-assisted, proxy, and deepfake candidates, so every hiring decision is backed by evidence.

Ask AI about Sherlock.sh

Similar Posts