Database / SQLite Interview questions

SQLite is a lightweight, serverless relational database engine that stores an entire database — tables, indexes, and data — in a single ordinary file on disk. Unlike most database systems, there's no separate database server process to install, configure, or connect to over a network; the SQLite library is linked directly into the application that uses it.

#include <sqlite3.h>

sqlite3 *db;
sqlite3_open("mydata.db", &db);

It's one of the most widely deployed database engines in existence, embedded inside web browsers, mobile operating systems, and countless desktop applications, precisely because it needs no setup and no administration — the application just opens a file and starts reading/writing SQL against it.

SQLite's design centers on being small, self-contained, and zero-configuration, while still providing a genuinely full-featured SQL engine.

• Serverless — no separate database process; the engine runs in-process with the application.
• Zero configuration — no setup, accounts, or connection strings; just open a file.
• Single-file storage — the entire database (schema, data, indexes) lives in one file.
• Cross-platform — the same database file works unmodified across operating systems and architectures.
• ACID-compliant transactions — even without a server, SQLite guarantees atomicity, consistency, isolation, and durability.
• Small footprint — the whole library compiles to a few hundred KB, small enough for embedded devices.

These properties make it the default choice for local application storage rather than a shared, multi-user backend database.

MySQL and PostgreSQL run as standalone server processes that clients connect to over a network (or a local socket), meaning multiple applications on different machines can share the same database concurrently through that server. SQLite has no server at all — the database engine is a library linked directly into the application process, reading and writing straight to a local file.

This makes SQLite the natural fit for a mobile app's local storage or a desktop app's data file, while MySQL/PostgreSQL fit a shared backend serving many client applications or users simultaneously.

A SQLite database is stored as a single, ordinary binary file on disk, using a well-documented, stable file format that SQLite guarantees to maintain backward compatibility with indefinitely — a database file created by SQLite version 3 many years ago can still be opened by the latest version today.

$ file mydata.db
mydata.db: SQLite 3.x database

Internally, the file is organized into fixed-size pages (commonly 4096 bytes), storing the schema, table data (organized as B-trees), and indexes all within that same file. Because it's just a normal file, it can be copied, moved, emailed, or backed up with ordinary file operations — there's no special export/import step required the way there typically is with a server-based database's internal storage.

SQLite uses a small set of storage classes rather than the rigid, fixed-width types found in most other database systems — each value stored is tagged with one of these classes based on the value itself, regardless of the column's declared type.

CREATE TABLE example (
    id INTEGER,
    name TEXT,
    score REAL,
    data BLOB
);

This is fundamentally different from a fixed-type system: a column declared INTEGER can still technically store a text value in SQLite (subject to the column's type affinity), since the storage class is ultimately determined by the value, not strictly enforced by the declared column type the way it would be in most other SQL databases.

SQLite uses type affinity rather than strict column typing: a column's declared type is a preference for how to store a value, not a hard constraint that rejects mismatched data the way most databases enforce. Each column is assigned one of five affinities (TEXT, NUMERIC, INTEGER, REAL, BLOB) based on keywords in its declared type, and SQLite tries to convert an inserted value to match that affinity, but will still store the value as-is if conversion isn't sensible.

CREATE TABLE t (a INTEGER);
INSERT INTO t VALUES ('hello');   -- succeeds! 'hello' has no numeric form, so it's stored as TEXT

This flexibility is a deliberate design choice for SQLite's typical embedded use cases, but it's also a common surprise for developers coming from strictly-typed databases, who expect a type mismatch to raise an error rather than being silently accepted and stored in its original form.

Tables are created with standard SQL CREATE TABLE syntax, specifying column names and their declared types (which determine type affinity), along with any constraints.

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT UNIQUE
);

Running this via the sqlite3 command-line shell, or through any SQLite client library, creates the table's schema entry and B-tree structure inside the database file immediately. Unlike some databases, you don't need to specify a storage engine or tablespace — there's exactly one storage mechanism, and it always lives in the single database file you're connected to.

The standard INSERT INTO statement adds a new row, specifying which columns you're providing values for (columns left out get their default value, or NULL if none is defined).

INSERT INTO person (name, age, email)
VALUES ('Ada', 34, 'ada@example.com');

Multiple rows can be inserted in a single statement by providing several comma-separated value lists, which is generally more efficient than issuing many separate single-row INSERT statements, especially when wrapped in an explicit transaction, since each individual insert outside a transaction incurs its own disk sync overhead by default.

INSERT INTO person (name, age) VALUES
    ('Grace', 41),
    ('Alan', 29);

Standard SELECT syntax retrieves rows, optionally filtering with WHERE, sorting with ORDER BY, and limiting results with LIMIT — the same core SQL syntax used across most relational databases.

SELECT name, age FROM person
WHERE age > 30
ORDER BY age DESC
LIMIT 10;

SQLite supports the full range of standard SQL query features: joins, subqueries, aggregate functions (COUNT, SUM, AVG), GROUP BY/HAVING, and common table expressions (WITH) — despite its small footprint, it's a genuinely capable SQL engine, not a stripped-down subset.

The UPDATE statement modifies existing rows matching a WHERE condition — omitting the WHERE clause updates every row in the table, which is a common and dangerous mistake if done accidentally.

UPDATE person
SET age = 35, email = 'ada.new@example.com'
WHERE id = 1;

It's good practice to first run the equivalent SELECT ... WHERE ... to confirm exactly which rows would be affected before running the actual UPDATE, especially for a condition you're not fully confident about — since an UPDATE without a safety net can silently modify far more rows than intended.

The DELETE FROM statement removes rows matching a WHERE condition — like UPDATE, omitting the condition deletes every row in the table.

DELETE FROM person WHERE id = 1;

Deleting rows doesn't automatically shrink the database file's size on disk — the freed space inside the file is marked available for reuse by future inserts, but the file itself doesn't get smaller until you explicitly run VACUUM, which rebuilds the database file to reclaim that unused space.

A PRIMARY KEY uniquely identifies each row in a table, and SQLite enforces that no two rows can share the same primary key value (unless declared otherwise for special cases like WITHOUT ROWID tables).

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    name TEXT
);

A notable SQLite-specific detail: declaring a column as INTEGER PRIMARY KEY (exactly that type, singular) makes that column an alias for the table's internal rowid — the fastest possible way to look up a row, since it directly maps to SQLite's internal B-tree key rather than requiring a separate index lookup.

AUTOINCREMENT is an optional modifier on an INTEGER PRIMARY KEY column that guarantees newly generated key values are always strictly larger than any value ever used before in that table, even after rows with high key values have been deleted — preventing key value reuse.

CREATE TABLE person (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

Without AUTOINCREMENT, a plain INTEGER PRIMARY KEY already auto-generates sequential-ish values by default (typically one more than the current maximum), but it can reuse a previously deleted row's key value under certain conditions. AUTOINCREMENT adds a small amount of extra bookkeeping overhead (an internal tracking table) specifically to guarantee that never happens, which matters if your application logic depends on IDs never repeating even after deletions.

A UNIQUE constraint ensures no two rows in a table share the same value in that column (or combination of columns, for a multi-column unique constraint), raising a constraint violation error if you try to insert or update a row that would create a duplicate.

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE
);

INSERT INTO person (email) VALUES ('ada@example.com');
INSERT INTO person (email) VALUES ('ada@example.com');   -- fails: UNIQUE constraint violation

Unlike PRIMARY KEY, a table can have multiple UNIQUE constraints across different columns, and unlike a primary key, a UNIQUE column can typically hold a NULL value (and SQLite treats multiple NULLs as not conflicting with each other under a unique constraint, since NULL is never considered equal to another NULL).

A FOREIGN KEY constraint links a column in one table to a primary/unique key in another, ensuring referential integrity — you can't insert a row referencing a value that doesn't exist in the referenced table.

CREATE TABLE order_item (
    id INTEGER PRIMARY KEY,
    person_id INTEGER,
    FOREIGN KEY (person_id) REFERENCES person(id)
);

Notably, SQLite parses foreign key constraints but doesn't actually enforce them by default, for historical backward-compatibility reasons — you must explicitly turn enforcement on per connection with:

PRAGMA foreign_keys = ON;

This is a common surprise for developers coming from other databases where foreign keys are always enforced: forgetting this pragma means orphaned references can silently be inserted with no error at all.

sqlite3 is SQLite's official interactive command-line tool for opening a database file, running SQL directly, and inspecting schema — the most common way to explore or debug a SQLite database without writing any application code.

$ sqlite3 mydata.db
sqlite> .tables
sqlite> .schema person
sqlite> SELECT * FROM person;

Beyond plain SQL, it supports "dot commands" (starting with .) for shell-specific operations like .tables (list tables), .schema (show a table's CREATE TABLE statement), .mode (change output formatting, e.g. to CSV), and .backup (create a backup file) — these dot commands are specific to the shell tool itself, not part of standard SQL.

CREATE INDEX builds a separate B-tree structure over one or more columns, letting SQLite look up matching rows directly instead of scanning the whole table — the same fundamental purpose an index serves in any relational database.

CREATE INDEX idx_person_email ON person(email);

CREATE UNIQUE INDEX idx_person_email_unique ON person(email);   -- also enforces uniqueness

Indexes speed up lookups and WHERE/ORDER BY clauses that reference the indexed column(s), but they add overhead on every insert/update/delete, since the index itself has to be kept in sync with the table's actual data — making indexes worth adding for columns frequently searched or sorted on, but not something to add reflexively to every column regardless of query patterns.

NULL represents a missing or unknown value — it's distinct from an empty string ('') or zero (0), and it follows SQL's three-valued logic: comparing anything to NULL using = yields NULL (neither true nor false), not TRUE or FALSE.

SELECT * FROM person WHERE age = NULL;    -- returns nothing; use IS NULL instead
SELECT * FROM person WHERE age IS NULL;   -- correct way to check for NULL

This is a common source of bugs for developers unfamiliar with SQL's NULL semantics: a straightforward = NULL comparison never matches anything, since NULL isn't considered equal to anything, including another NULL — the correct check is always IS NULL or IS NOT NULL.

Functionally, all three are treated identically in SQLite — whatever length or width you declare (CHAR(10), VARCHAR(255)) is completely ignored for storage purposes; SQLite stores the string exactly as given, taking only as much space as the actual text requires, since it uses dynamic typing rather than fixed-width storage.

CREATE TABLE t (
    a CHAR(10),
    b VARCHAR(255),
    c TEXT
);
INSERT INTO t VALUES ('hi', 'this text is way longer than expected', 'anything');
-- all three succeed with no truncation or padding; SQLite doesn't enforce the declared length

All three simply map to the TEXT storage class and affinity underneath. The length/width number in CHAR(10) or VARCHAR(255) is accepted syntactically (for compatibility with SQL written for other databases) but has zero effect on how SQLite actually stores or validates the value.

Every ordinary SQLite table (unless explicitly declared WITHOUT ROWID) has a hidden rowid column — a 64-bit signed integer that uniquely identifies each row and serves as the key of the table's underlying B-tree, regardless of whether you've defined your own primary key.

SELECT rowid, name FROM person;   -- rowid is accessible even if not explicitly declared

If a table declares an INTEGER PRIMARY KEY column, that column becomes an alias for rowid itself (same underlying value, just referenceable by your chosen column name), which is why looking a row up by that column is exceptionally fast — it's a direct B-tree key lookup, not a secondary index traversal. Tables declared WITHOUT ROWID skip this hidden column entirely, storing rows keyed directly by their declared primary key instead.

Instead of writing to a file on disk, SQLite can create a database that exists purely in RAM for the lifetime of the connection, using the special filename :memory: — useful for temporary data, testing, or scenarios where disk persistence isn't needed at all.

sqlite3_open(":memory:", &db);

An in-memory database is significantly faster for read/write-heavy workloads since there's no disk I/O involved at all, but its data disappears entirely the moment the connection closes — there's no way to recover it afterward unless you explicitly export/backup it to a file first. This makes it a common choice for unit tests that need a real SQL database without leaving files behind, or as a fast, throwaway scratch space within a single application run.

Because a SQLite database is just an ordinary file, the simplest backup is a straightforward file copy — but that's only safe when you're certain no write is in progress at the same moment, since copying a file mid-write can capture an inconsistent snapshot.

# simple file copy (only safe if no writes are happening concurrently)
cp mydata.db mydata_backup.db

# safer: SQLite's built-in backup command, safe even with concurrent activity
sqlite3 mydata.db ".backup mydata_backup.db"

The .backup command (or the equivalent sqlite3_backup_* C API) uses SQLite's dedicated online backup mechanism, which correctly handles a database that's actively being read from or written to during the backup, producing a consistent snapshot without requiring you to lock out other activity first — the recommended approach over a raw file copy for any database that might be in active use.

PRAGMA is SQLite's mechanism for querying or modifying internal engine settings and behavior — configuration knobs that aren't part of standard SQL but control how SQLite itself operates for the current connection or database.

PRAGMA foreign_keys = ON;      -- enable foreign key enforcement
PRAGMA journal_mode = WAL;     -- switch to write-ahead logging mode
PRAGMA table_info(person);     -- inspect a table's column definitions
PRAGMA integrity_check;        -- verify database file consistency

Some pragmas are per-connection settings that reset each time you reconnect (like foreign_keys), while others persist as part of the database file itself (like journal_mode = WAL). Because there's no separate configuration file or server settings panel the way client-server databases have, pragmas are how essentially all of SQLite's tunable behavior is accessed.

A VIEW is a saved, named query that behaves like a virtual, read-only table — querying the view runs the underlying query fresh each time, rather than storing its own separate copy of data.

CREATE VIEW adult_person AS
SELECT id, name, age FROM person WHERE age >= 18;

SELECT * FROM adult_person;   -- runs the underlying query each time

Views are useful for encapsulating a commonly-needed, possibly complex query behind a simple name, so application code (or other queries) can reference adult_person without repeating the full WHERE condition everywhere it's needed. Since a view has no storage of its own, updating the underlying tables automatically changes what the view returns the next time it's queried — there's no separate "refresh" step required.

A TRIGGER is a piece of SQL logic that runs automatically in response to a specific table event — INSERT, UPDATE, or DELETE — either before or after that event occurs, without the application needing to explicitly call anything.

CREATE TRIGGER update_timestamp
AFTER UPDATE ON person
BEGIN
    UPDATE person SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

Common uses: automatically maintaining an "updated at" timestamp, enforcing business rules that go beyond what a simple constraint can express, logging changes to an audit table, or cascading a change to related data. Triggers run inside the same transaction as the statement that fired them, so if the trigger's logic fails, the whole original operation is rolled back along with it.

SQLite doesn't actually have a TRUNCATE statement at all — that's a notable difference from many other SQL databases. The remaining two behave distinctly.

DELETE FROM person;   -- removes all rows, table still exists, empty
DROP TABLE person;    -- table itself is gone entirely

DELETE FROM table with no WHERE clause is the closest equivalent to what other databases call TRUNCATE, though internally it's still implemented as a row-by-row delete rather than the specialized fast-path some other engines use for true truncation.

A transaction groups multiple statements so they either all take effect together, or none of them do — the standard atomicity guarantee. In SQLite, every statement outside an explicit transaction is actually wrapped in its own implicit, single-statement transaction automatically; BEGIN lets you group several statements into one larger transaction instead.

BEGIN TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;

If something goes wrong partway through, ROLLBACK undoes every change made since BEGIN, leaving the database exactly as it was before the transaction started. Wrapping multiple related writes in an explicit transaction is also significantly faster than letting each one run as its own implicit transaction, since SQLite only needs to sync to disk once at commit, rather than once per statement.

Both are journaling strategies SQLite uses to guarantee atomicity and crash recovery, but they take different approaches to how changes are recorded before being made permanent.

PRAGMA journal_mode = WAL;

WAL mode is generally the better choice for applications with any meaningful concurrent read/write activity, while the traditional rollback journal remains SQLite's conservative, backward-compatible default.

Foreign key enforcement was added to SQLite well after the file format and basic engine were already established and widely deployed, so turning it on by default risked breaking existing applications that had databases with foreign key declarations that weren't actually being enforced (and might have had inconsistent data as a result) — enabling enforcement retroactively on those datasets could suddenly cause previously "working" operations to start failing.

PRAGMA foreign_keys = ON;   -- must be set explicitly, per connection

For new applications, though, the recommendation is almost universally to enable it — without enforcement, a foreign key declaration is purely documentation, with no actual guarantee that referenced rows exist, silently allowing orphaned references that a real foreign key constraint is specifically meant to prevent. The historical compatibility reasoning applies to SQLite's own defaults, not to what's advisable for new schema design.

Declaring a column as exactly INTEGER PRIMARY KEY makes it a direct alias for the table's internal rowid, meaning lookups by that column hit the B-tree's key directly — the fastest possible access path. A PRIMARY KEY on a non-integer column (or declared with WITHOUT ROWID), or a composite primary key across multiple columns, doesn't get this same rowid-aliasing behavior.

CREATE TABLE fast_lookup (
    id INTEGER PRIMARY KEY,   -- aliases rowid: fastest lookups
    name TEXT
);

CREATE TABLE composite_key (
    a TEXT,
    b TEXT,
    PRIMARY KEY (a, b)        -- composite key; no rowid alias
);

This distinction matters for performance-sensitive schema design: if a table's natural primary key is a single integer, declaring it as INTEGER PRIMARY KEY gets you the fastest lookup path essentially for free, whereas any other kind of primary key requires a normal (still efficient, but comparatively slower) B-tree index traversal.

SQLite allows many simultaneous readers, but only ever one writer at a time for a given database file — there's no concept of row-level or table-level locking granularity the way a full client-server database offers; the whole database file is the unit of write locking.

flowchart LR
    A[Multiple readers] -->|can proceed concurrently| B[Database file]
    C[One writer] -->|exclusive access during write| B
    D[Second writer] -->|must wait| C

In the default rollback journal mode, a writer briefly blocks new readers too during the actual commit; in WAL mode, readers can continue concurrently with the single active writer, though a second writer still has to wait its turn. This single-writer model is perfectly adequate for typical embedded, single-application use, but is the central reason SQLite isn't a good fit for applications with many independent processes all needing to write heavily and simultaneously.

A TABLE physically stores its own rows on disk; a VIEW stores no data of its own at all — it's just a saved query definition that's re-executed against the underlying tables every time you select from it.

CREATE TABLE t (id INTEGER, name TEXT);          -- stores data
CREATE VIEW v AS SELECT id, name FROM t;          -- stores only the query

Because a view has no storage of its own, it can never become "stale" relative to the tables it's built from — there's nothing to refresh, since the underlying query simply runs fresh every time.

SQLite supports the standard SQL join types — INNER JOIN, LEFT JOIN, and (more recently) RIGHT JOIN and FULL JOIN — combining rows from two or more tables based on a matching condition.

SELECT person.name, order_item.total
FROM person
INNER JOIN order_item ON person.id = order_item.person_id;

SELECT person.name, order_item.total
FROM person
LEFT JOIN order_item ON person.id = order_item.person_id;
-- includes every person, even those with no matching order_item rows

INNER JOIN only returns rows where a match exists on both sides; LEFT JOIN returns every row from the left table regardless of whether a match exists, filling in NULLs for the right side's columns when there's no match — the same standard join semantics used across virtually every relational database.

An upsert inserts a new row, or updates an existing one instead if the insert would violate a uniqueness constraint — a single statement covering both the "create" and "update" case, avoiding a separate check-then-insert-or-update sequence in application code.

INSERT INTO person (id, name, visit_count)
VALUES (1, 'Ada', 1)
ON CONFLICT(id) DO UPDATE SET visit_count = visit_count + 1;

ON CONFLICT(column) DO UPDATE SET ... specifies exactly what to do when a conflicting row already exists, and can reference the conflicting existing row's values via excluded.column for the values that were about to be inserted. There's also a simpler DO NOTHING variant when you just want to silently skip the insert on conflict rather than updating anything.

Prefixing any query with EXPLAIN QUERY PLAN shows, in human-readable form, how SQLite intends to execute it — which indexes (if any) it will use, whether it needs to scan the whole table, and how joins will be processed — without actually running the query itself.

EXPLAIN QUERY PLAN
SELECT * FROM person WHERE email = 'ada@example.com';

-- output might show:
-- SEARCH person USING INDEX idx_person_email (email=?)
-- (good: using an index)
-- vs.
-- SCAN person
-- (bad: scanning every row, no index used)

This is the primary tool for diagnosing slow queries: seeing SCAN on a large table where you expected an indexed SEARCH is usually the first clue that a needed index is missing, or that the query is written in a way that prevents SQLite from using an index that does exist.

A composite (or compound) primary key spans more than one column, with uniqueness enforced across the combination of those columns rather than any single one alone — useful for tables representing a many-to-many relationship, where the natural unique identifier really is a pair (or more) of values together.

CREATE TABLE enrollment (
    student_id INTEGER,
    course_id INTEGER,
    PRIMARY KEY (student_id, course_id)
);
-- the same student_id can appear multiple times (for different courses),
-- and the same course_id can appear multiple times (for different students),
-- but the (student_id, course_id) pair as a whole must be unique

Unlike a single-column INTEGER PRIMARY KEY, a composite primary key doesn't get the rowid-alias performance shortcut — lookups against it go through a normal B-tree index traversal on the combined key, still efficient, but not the absolute fastest single-integer lookup path.

SQLite uses a small set of lock states applied to the whole database file to coordinate access between connections: UNLOCKED, SHARED (for reading), RESERVED (a writer intends to write soon but hasn't yet), PENDING, and EXCLUSIVE (actively writing).

flowchart LR
    A[UNLOCKED] --> B[SHARED - readers]
    B --> C[RESERVED - writer intends to commit]
    C --> D[PENDING - waiting for readers to finish]
    D --> E[EXCLUSIVE - actively writing]
    E --> A

Multiple connections can hold a SHARED lock simultaneously (concurrent readers), but only one connection at a time can hold RESERVED/EXCLUSIVE, and a write can't finalize until existing readers release their SHARED locks. If a lock can't be acquired immediately, SQLite returns a SQLITE_BUSY error (or waits, if a busy timeout is configured), which application code needs to handle by retrying rather than treating it as a fatal error.

Both are maintenance commands, but they address different things: VACUUM reclaims unused disk space left behind by deletes/updates by rebuilding the database file; ANALYZE gathers statistics about table/index contents that SQLite's query planner uses to make better decisions about which index to use for a given query.

VACUUM;
ANALYZE;

Every table and index in SQLite is stored as a separate B-tree within the single database file — a balanced tree structure where each node is one fixed-size page (typically 4096 bytes), organized so that lookups, insertions, and range scans all stay efficient even as the table grows large.

flowchart TD
    A[Root page] --> B[Interior page]
    A --> C[Interior page]
    B --> D[Leaf page: rows 1-50]
    B --> E[Leaf page: rows 51-100]
    C --> F[Leaf page: rows 101-150]

Table B-trees are keyed by rowid and store the actual row data in their leaf pages; index B-trees are keyed by the indexed column's value and store a reference back to the corresponding rowid, which is then used to fetch the full row from the table's own B-tree. A lookup by primary key (rowid) is a single B-tree traversal; a lookup via a secondary index requires first traversing the index's B-tree, then a second traversal into the table's B-tree to fetch the actual row — which is why a "covering index" (one that includes every column a query needs) can skip that second traversal entirely.

In rollback journal mode, a writer modifies the actual database file directly (after saving the original data to a journal for potential rollback), which means readers have to wait during the brief window a writer is committing, since the file they'd be reading from is actively being changed. WAL mode instead has writers append new versions of changed pages to a separate write-ahead log file, leaving the main database file untouched during normal operation.

sequenceDiagram
    participant Writer
    participant WALfile as WAL file
    participant DBfile as Main DB file
    participant Reader
    Writer->>WALfile: append new page versions
    Reader->>DBfile: reads original pages (unaffected by writer)
    Reader->>WALfile: also checks WAL for newer versions
    Note over WALfile,DBfile: Periodic checkpoint merges WAL into main DB file

Because readers can see a consistent snapshot by combining the main database file with whatever's in the WAL at the moment they started reading, they never have to wait for a writer to finish — the writer and readers operate on effectively separate views that get reconciled later. This is the core mechanism behind WAL mode's readers-never-block-on-writers concurrency improvement.

Calling BEGIN starts an explicit transaction, and every statement afterward operates within it until either COMMIT makes the changes permanent or ROLLBACK discards them.

flowchart TD
    A[BEGIN] --> B[Statements execute, journal/WAL records original or new page state]
    B --> C{COMMIT or ROLLBACK?}
    C -->|COMMIT| D[Changes finalized; journal deleted or WAL checkpointed]
    C -->|ROLLBACK| E[Journal used to restore original data; changes discarded]

Under the hood, in rollback journal mode, SQLite copies each page's original content into the journal file before modifying it in the main database, so a ROLLBACK simply restores those original pages. On COMMIT, the journal file is deleted (its job done) and the modified pages remain in place. In WAL mode, the mechanics differ (new pages go to the WAL rather than the journal), but the guarantee is the same: until COMMIT actually completes, an interrupted transaction (a crash, a ROLLBACK) leaves the database exactly as it was beforehand.

A LIKE '%word%' query has to scan every row and check the pattern against each one, since a leading wildcard prevents SQLite from using a normal B-tree index at all — on a large table, this means a full table scan for every search, which gets slower as the table grows and offers none of the relevance-ranking or linguistic features (stemming, tokenization) a real search feature typically needs.

-- slow: full table scan, no relevance ranking
SELECT * FROM articles WHERE body LIKE '%database%';

-- FTS5: indexed, ranked full-text search
CREATE VIRTUAL TABLE articles_fts USING fts5(title, body);
SELECT * FROM articles_fts WHERE articles_fts MATCH 'database';

FTS5 is SQLite's built-in full-text search extension: it builds a specialized inverted index over the text (mapping words to the rows containing them), supporting fast MATCH queries, relevance ranking, phrase search, and prefix matching — capabilities a plain LIKE scan simply can't provide efficiently or at all, regardless of table size.

Optimization generally follows a consistent sequence: understand what the query planner is actually doing, then address the specific gap that's causing it to do more work than necessary.

1. Run EXPLAIN QUERY PLAN to see whether the query is using an index (SEARCH) or scanning the whole table (SCAN).
2. Add an index on columns used in WHERE, JOIN conditions, or ORDER BY, if a full scan shows up where a targeted lookup would be expected.
3. Consider a covering index that includes every column the query needs, letting SQLite skip the second B-tree traversal to fetch the full row.
4. Run ANALYZE if the planner seems to be choosing a poor index despite one existing — stale or missing statistics can lead it astray.
5. Avoid functions on indexed columns in WHERE clauses (like WHERE lower(name) = 'ada'), since that typically prevents the index on name from being used at all, unless an expression index is created specifically for that expression.

SQLite's design choices that make it excellent for embedded, single-application use become real constraints once an application needs many independent processes hammering the same database with heavy concurrent writes.

• Single-writer limitation — only one write transaction can proceed at a time per database file, regardless of how many CPU cores are available.
• No built-in network access — there's no client-server protocol, so remote clients can't connect to it directly the way they would to MySQL/PostgreSQL over a network.
• No user/role-based access control — file-system permissions are the only access control SQLite itself provides.
• Limited horizontal scaling — there's no built-in replication or sharding across multiple database files/servers.

These aren't flaws so much as deliberate tradeoffs for its target use case — a genuinely multi-user, high-write-concurrency backend is exactly the scenario a client-server database like PostgreSQL was built for, and pushing SQLite into that role usually means fighting against its core design rather than working with it.

ACID guarantees are typically associated with a server process coordinating access, but SQLite achieves the same guarantees entirely within the library linked into the application, using file-level locking and journaling (rollback journal or WAL) as its coordination mechanism instead of a server.

• Atomicity — the journal/WAL ensures a transaction's changes are either fully applied or fully rolled back, even across a crash.
• Consistency — constraints (primary key, unique, foreign key when enabled, check constraints) are validated before a transaction is allowed to commit.
• Isolation — the locking states (SHARED/RESERVED/EXCLUSIVE, or WAL's snapshot mechanism) prevent one transaction from seeing another's uncommitted changes.
• Durability — committed data is flushed (fsynced) to disk so it survives a subsequent crash or power loss.

The key insight is that ACID compliance is a property of how data is written and coordinated, not something that inherently requires a separate server — SQLite just implements all of that coordination logic directly inside the linked-in library instead of a remote process.

In WAL mode, committed transactions accumulate as appended entries in the WAL file rather than being written into the main database file immediately. A checkpoint is the process that periodically copies those accumulated WAL entries into the main database file, after which the WAL file can be reset (or truncated) since its contents are now reflected in the main file.

flowchart LR
    A[WAL file accumulates committed pages] --> B{Checkpoint triggered}
    B --> C[Copy WAL pages into main DB file, in order]
    C --> D[WAL file reset/truncated]
    D --> A

Checkpoints happen automatically by default once the WAL file grows past a threshold (roughly 1000 pages), but can also be triggered manually via PRAGMA wal_checkpoint. A checkpoint has to wait for any readers still using older WAL content to finish before it can safely truncate the WAL file, which is why a long-running read transaction can cause the WAL file to grow larger than usual — checkpointing is effectively paused (or partial) until that reader completes.

SQLite effectively provides serializable isolation for its transactions — the strictest standard isolation level — rather than offering a configurable choice of levels (read committed, repeatable read, serializable) the way many client-server databases do.

flowchart LR
    A[Transaction begins] --> B[Sees a consistent snapshot]
    B --> C[No other transaction's uncommitted changes visible]
    C --> D[Commit only succeeds if no conflicting write occurred]

In practice this is achieved through SQLite's locking model (or WAL's snapshot mechanism): a reader never sees another transaction's uncommitted writes, and a writer can't commit if it would conflict with changes made since its transaction began. There's no equivalent to loosening this to a weaker isolation level for performance, the way you might deliberately choose READ COMMITTED in PostgreSQL — SQLite's transactions are serializable by default and don't offer that particular tradeoff knob.

Adding STRICT to a CREATE TABLE statement opts that specific table out of SQLite's usual flexible type affinity behavior, instead enforcing that inserted values must actually match the column's declared type — much closer to how most other SQL databases behave by default.

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    age INTEGER
) STRICT;

INSERT INTO person (age) VALUES ('not a number');   -- fails: STRICT enforces type matching

Without STRICT, that same insert would silently succeed, storing the text value in the INTEGER-affinity column as discussed with type affinity. STRICT tables were added specifically to give developers who want more predictable, conventional type enforcement an explicit opt-in, without changing SQLite's default (flexible, dynamically-typed) behavior for tables that don't request it.