skip to content

SQLite

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

on this pageshow

explore

questions

page 1 of 2

What does it mean that SQLite is an in-process, serverless database, and how does an application reach its data?

level: juniorimportance: must knowfreq 72%

answer

  1. a library, not a service
  2. no daemon, no port, no wire protocol
  3. SQL calls are function calls
  4. the process reads the file itself

basics

~20 s

SQLite is a C library linked into your program: there is no server process, no port and no network protocol. Each SQL call is a function call that reads and writes the database file directly from the application's own process.

solid answer

~50 s

With a client-server engine such as PostgreSQL, the application sends SQL over a socket to a separate server process that owns the data files. SQLite has no intermediary: the engine is compiled or linked into the application, `sqlite3_open()` opens an ordinary file, `sqlite3_prepare_v2()` compiles SQL into bytecode and `sqlite3_step()` runs that bytecode in the caller's own thread, reading pages straight from disk. That is why SQLite calls itself zero-configuration: nothing to install, start, log into or keep running, and any program that can read the file can use the database. The price comes from the same design. No long-running process coordinates access, so locking is coarse and only one writer at a time can change a file, and a stray pointer in the application can damage SQLite's own memory, which a separate server process would be protected from.

go deeper

for a junior

Recall the three absences: no server process, no port, no network protocol. Then say what replaces them: a library inside the application that reads and writes one file directly.

for a middle

Walk through open, prepare and step, and say that the bytecode runs in the calling thread. Name what the missing server would have provided, such as fine-grained locking and protection from client bugs.

for a senior

Show the consequences in production: the file must live on local storage, a crash or memory bug in the application is also the database's problem, and concurrency is limited to one writer per file.

for a principal

Frame in-process as a trade, not a feature: no operations burden and no network round-trip, paid for with coarse write concurrency and data tied to one machine. Say which workloads accept that.

## Two ways to reach a database Most SQL engines are **client-server**: a database server runs as its own process (or set of processes), listens on a TCP port or local socket, and owns the data files. Applications connect, authenticate, send SQL text over a wire protocol and receive rows back. **SQLite** works differently. It is a library, distributed as a single C source file (`sqlite3.c`, the "amalgamation"), that is compiled or linked into the application. SQLite's documentation calls this design **serverless**: "the process that wants to access the database reads and writes directly from the database files on disk. There is no intermediary server process." | | Client-server engine | SQLite | |---|---|---| | Where the engine runs | A separate server process | Inside the application's process | | How SQL travels | Over a socket, via a wire protocol | As a function call (`sqlite3_prepare_v2()`, `sqlite3_step()`) | | Who opens the data files | Only the server | Every process that uses the database | | Setup before first use | Install, initialise, start, create logins | None: open a file path | | Who coordinates concurrent access | The server, with fine-grained locks | Operating-system locks on the file | ## What happens when your code runs a query 1. `sqlite3_open()` (or `sqlite3_open_v2()`) opens a database file, creating it if it does not exist when the read-write and create flags are set, which is what `sqlite3_open()` does. 2. `sqlite3_prepare_v2()` runs the tokenizer, parser and code generator, which turn SQL text into a **prepared statement**: a small bytecode program. 3. `sqlite3_step()` hands that program to SQLite's virtual machine, in the **calling thread**, until it produces a row, finishes or fails. 4. Below the virtual machine, the B-tree and pager modules ask for fixed-size pages, and the **OS interface (VFS)** turns those requests into ordinary file reads and writes. No message ever leaves the process. Language bindings and mobile platform APIs wrap these same C calls, so the model is identical whether the caller is a desktop program, a command-line tool or a phone app. ## "Serverless" is not the cloud meaning SQLite's documentation distinguishes two meanings. **Classic serverless**, SQLite's meaning, is an engine running in the same process, thread and address space as the application, with no message passing or network activity. **Neo-serverless** is a hosted database whose servers exist but are run by a provider, so developers never manage them. A hosted service that markets itself as serverless still has servers; SQLite really has none. ## What the design gives you - **Zero configuration**: no server to install, configure, patch or monitor, and no administrator needed before a program can create a database. - **Low latency**: a query is a function call, with no network round-trip and no serialisation of results onto a wire. - **One portable file**: the database is a single ordinary file that can be copied, emailed or opened on a machine with a different CPU architecture. - **Several processes, no coordinator**: unusually for an embedded engine, many processes can open the same file at once; they coordinate through file locks rather than through a server. ## What it costs you - **Coarser concurrency**: without a long-running process to arbitrate, SQLite locks at the level of the whole database file, so any number of readers can work at once but only one writer can change a file at any instant. - **No isolation from application bugs**: a server process is protected from a client's stray pointers; SQLite shares the application's address space, so memory corruption in the application can reach the engine and the data it writes. - **The data must sit next to the code**: the engine reads the file directly, so the file belongs on storage local to the process that issues the SQL, not on a remote machine. - **Access control is the file's**: the core library has no login step, so whoever can open the file can read it; operating-system file permissions decide who that is. ## Where this subject stops How the lock states progress during a write and what `SQLITE_BUSY` means belong to the material on SQLite's locking and write concurrency; the layout of pages and journal files inside the file belongs to the material on SQLite's file format. What matters here is the shape: a library inside your process, one file on local storage, and the operating system as the only shared referee.

  • If SQLite has no server, how can two separate programs use the same database file at the same time?
    Each program runs its own copy of the SQLite library against the same file, and the copies coordinate through operating-system file locks: POSIX advisory locks on Unix, `LockFile()` and `LockFileEx()` on Windows. Any number of processes can read at once but only one can write at a time, and in the default rollback-journal mode readers also wait while a writer commits. Those locks only work if the filesystem implements them correctly.
  • Can a networked service still be built on SQLite, given that clients cannot connect to it?
    Yes. The service's own server process is the application that links SQLite and sits on the same machine as the file. Clients send high-level requests to that service over the network, and the service turns them into SQL against its local file. SQLite's documentation describes this pattern, where the network hop is between client and service, never between the engine and the disk.
  • What does SQLite's documentation mean by "SQLite competes with fopen()"?
    That SQLite is not trying to be a shared enterprise data server. Its niche is local storage for one application or device, replacing ad hoc data files that a program would otherwise read and write itself, while adding SQL queries, transactions and a stable, documented file format in place of a home-grown one.

A client-server database is a restaurant kitchen you send orders to; SQLite is a cookbook on your own counter. You cook every dish yourself with no waiter and no queue, but a spill in your kitchen lands on the cookbook too.

  • SQLite runs a small background server that the application connects to.
  • SQLite listens on a local port, so a driver needs a host name and port.
  • Serverless means SQLite's data is kept by a cloud provider.
  • Only one process can ever open a SQLite database file.
  • SQLite needs a user and password before an application can open a database.
open as a page

In SQLite, what are the -wal and -shm files that appear beside a database file, and why must you never delete them by hand?

level: juniorimportance: must knowfreq 62%

basics

~20 s

In WAL mode the -wal file holds committed changes not yet copied into the main database file, and the -shm file is a shared-memory index over it. Deleting the -wal by hand can lose committed transactions or corrupt the database.

open as a page

In SQLite, what does switching a database to WAL mode change about readers and writers running at the same time?

level: juniorimportance: must knowfreq 70%

basics

~20 s

In WAL mode SQLite readers no longer block the writer and the writer no longer blocks readers: each reader sees a fixed snapshot while one writer appends to the log. Writers still take turns, one at a time.

open as a page

In SQLite, what are the five storage classes, and how are booleans and dates stored without dedicated types?

level: juniorimportance: must knowfreq 70%

basics

~20 s

SQLite stores every value as NULL, INTEGER, REAL, TEXT or BLOB, and the class belongs to the value, not the column. Booleans are the integers 0 and 1; dates are ISO-8601 text, Julian-day reals or Unix-time integers.

open as a page

An app backs up its SQLite database by copying the file while running; why can that copy be unusable, and what should it do instead?

level: middleimportance: must knowfreq 60%

basics

~20 s

A file copy taken mid-transaction can mix old and new pages, and in WAL mode it misses commits still in the -wal file. Let SQLite make the copy: VACUUM INTO, the online backup API or sqlite3_rsync give a consistent snapshot of a live database.

open as a page

In SQLite, how do BEGIN DEFERRED, BEGIN IMMEDIATE and BEGIN EXCLUSIVE differ, and which one does a plain BEGIN use?

level: middleimportance: must knowfreq 75%

basics

~20 s

A plain BEGIN is DEFERRED: no lock until the first read or write. BEGIN IMMEDIATE takes the write lock at once; BEGIN EXCLUSIVE also shuts out readers in rollback-journal mode, and in WAL mode behaves like IMMEDIATE.

open as a page

In SQLite, what does SQLITE_BUSY mean, and how does setting busy_timeout change what a connection does when it meets one?

level: middleimportance: must knowfreq 72%

basics

~20 s

SQLITE_BUSY, reported as 'database is locked', means another connection holds a lock this one needs. With no busy handler, the default, it returns at once; PRAGMA busy_timeout installs a handler that sleeps and retries for up to that many milliseconds.

open as a page

In SQLite, how does a column's declared type name decide its type affinity, and what does that affinity do to inserted text?

level: middleimportance: must knowfreq 72%

basics

~20 s

SQLite picks a column's affinity from substrings of its declared type, in order: INT, then CHAR/CLOB/TEXT, then BLOB or none, then REAL/FLOA/DOUB, else NUMERIC. Affinity converts a value only when that loses nothing; otherwise the value is stored unchanged.

open as a page

Why does SQLite have no CREATE USER, GRANT or REVOKE, and what decides who can read a SQLite database file?

level: juniorimportance: should knowfreq 45%

basics

~20 s

SQLite runs inside the application and reads an ordinary file, so there is no server login and no GRANT or REVOKE. Anyone the operating system lets open the file can read every table in it.

open as a page

In SQLite, how do :memory: and temporary databases differ from a file database, and what goes wrong when tests rely on them?

level: middleimportance: should knowfreq 45%

basics

~20 s

Opening :memory: creates a RAM-only database private to that connection and deleted when it closes; an empty filename creates a private temporary database that may spill to a temp file. Tests break when a second connection expects the same data.

open as a page

How do several processes share one SQLite database file without a server, and why does a network filesystem break that arrangement?

level: middleimportance: should knowfreq 55%

basics

~20 s

Each process runs its own copy of SQLite and coordinates through operating-system locks on the database file. On a network filesystem those locks and fsync() are often unreliable, so two machines can write at once and corrupt the file.

open as a page

In SQLite, what do ANALYZE and the sqlite_stat1 table give the query planner, and how should an app run PRAGMA optimize?

level: middleimportance: should knowfreq 42%

basics

~20 s

ANALYZE scans each index and stores row counts and average duplicates per key prefix in sqlite_stat1, which the planner uses to choose indexes and join order. Statistics never refresh by themselves, so apps run PRAGMA optimize, which re-analyzes only tables that need it.

open as a page

In SQLite's EXPLAIN QUERY PLAN, what do USING COVERING INDEX and USE TEMP B-TREE FOR ORDER BY mean, and how do you avoid the sort?

level: middleimportance: should knowfreq 48%

basics

~20 s

USING COVERING INDEX means SQLite read only the index, never the table rows. USE TEMP B-TREE FOR ORDER BY means it sorted the result in a temporary B-tree. An index on the equality-filter columns followed by the ORDER BY columns removes that sort.

open as a page

Which SQLite size limits can a phone app storing chat history actually hit, and what error does each one raise?

level: middleimportance: should knowfreq 35%

basics

~20 s

SQLite caps a string, a BLOB and a whole row at 1,000,000,000 bytes (SQLITE_TOOBIG), a table at 2000 columns, a statement at 32766 host parameters since 3.32.0, and the file at page size times max_page_count (SQLITE_FULL).

open as a page

In SQLite, how do you store and query a JSON settings value, and what changes when you store it as JSONB instead of text?

level: middleimportance: should knowfreq 40%

basics

~20 s

SQLite has no JSON type: it stores JSON as text and queries it with built-in functions and the -> and ->> operators. Since 3.45.0 it can also store JSONB, a binary BLOB those functions read without re-parsing.

open as a page

How do SQLite's rollback-journal modes DELETE, TRUNCATE, PERSIST, MEMORY and OFF differ, and what does each one put at risk?

level: middleimportance: should knowfreq 38%

basics

~20 s

SQLite's rollback journal saves original pages before overwriting the database. DELETE (default), TRUNCATE and PERSIST differ only in how commit retires the -journal: delete, truncate or zero its header. MEMORY keeps it in RAM and OFF has none, so a crash can corrupt.

open as a page

After deleting most rows from a SQLite database, why does the file stay the same size, and how do VACUUM and auto_vacuum reclaim the space?

level: middleimportance: should knowfreq 45%

basics

~20 s

By default, freed pages go onto SQLite's freelist and are reused by later inserts, so the file does not shrink. VACUUM rebuilds it compactly; auto_vacuum FULL or INCREMENTAL truncates freelist pages, but must be set before tables exist or applied by VACUUM.

open as a page

In SQLite's rollback-journal mode, what do the SHARED, RESERVED, PENDING and EXCLUSIVE locks mean, and when does a writer take each?

level: middleimportance: should knowfreq 62%

basics

~20 s

In SQLite rollback-journal mode a reader holds SHARED. A writer takes RESERVED (one at a time, readers continue), then PENDING to turn away new readers, then EXCLUSIVE once existing readers leave, only while writing the file and committing.

open as a page

SQLite documents its transactions as SERIALIZABLE; how does it provide that between connections, and where does that isolation stop applying?

level: middleimportance: should knowfreq 45%

basics

~20 s

SQLite gets serializable isolation by actually serializing writes: one writer at a time, and readers see only committed data. The exceptions are statements on the same connection, which see each other's uncommitted changes, and shared-cache connections with PRAGMA read_uncommitted on.

open as a page

In SQLite, when does a primary key column become an alias for the rowid, and when is AUTOINCREMENT worth adding?

level: middleimportance: should knowfreq 58%

basics

~10 s

A single-column primary key declared exactly INTEGER becomes the table's 64-bit rowid; INT or BIGINT do not. Ids are auto-assigned either way; AUTOINCREMENT only stops ids of deleted rows being reused, at extra cost.

open as a page

In SQLite, what does declaring a table STRICT change about typing, and what does it cost you on older SQLite versions?

level: middleimportance: should knowfreq 48%

basics

~20 s

A STRICT table (SQLite 3.37.0 and later) allows only INT, INTEGER, REAL, TEXT, BLOB or ANY columns, converts a value only when lossless, and otherwise fails with SQLITE_CONSTRAINT_DATATYPE. Older libraries report an error on such a schema.

open as a page

In SQLite, how does INSERT ... ON CONFLICT DO UPDATE resolve a conflict, and what does a RETURNING clause report for it?

level: middleimportance: should knowfreq 42%

basics

~20 s

SQLite's UPSERT (3.24.0) turns an INSERT that would break a UNIQUE or PRIMARY KEY constraint into DO NOTHING or an UPDATE of that row, with excluded.col holding the attempted value. RETURNING (3.35.0) reports each inserted or updated row, in arbitrary order.

open as a page

In SQLite, a partial index on live contacts and an expression index on lower(surname) are both ignored; what matching rules must the query satisfy?

level: seniorimportance: should knowfreq 36%

basics

~20 s

SQLite uses a partial index only when a query WHERE term exactly matches an index WHERE term, or implies its IS NOT NULL. An expression index needs the indexed expression, as written, in the WHERE or ORDER BY. SQLite does no algebra.

open as a page

A phone app's crash reports show SQLite returning 'database disk image is malformed' for some users; what corrupts a SQLite file, and how do you confirm and recover it?

level: seniorimportance: should knowfreq 45%

basics

~20 s

SQLite files corrupt when something outside SQLite breaks its rules: a file copied or restored without its -wal or journal, broken locks, two library copies in one process, or disabled syncs. Confirm with PRAGMA integrity_check; salvage with the CLI's .recover.

open as a page

A service's SQLite database runs in WAL mode and its -wal file has grown to several gigabytes; what stops checkpoints from resetting it, and how do you fix it?

level: seniorimportance: should knowfreq 40%

basics

~20 s

A WAL is reused, not shrunk, and resets only after a checkpoint copies everything back while no reader still uses it. Overlapping readers, disabled autocheckpoints or one huge transaction keep it growing; leave reader gaps, run wal_checkpoint(TRUNCATE) and set journal_size_limit.

open as a page

A multi-threaded service logging requests to one SQLite file sets busy_timeout to 5000, yet transactions that read and then write still fail with SQLITE_BUSY instantly; why, and what fixes it?

level: seniorimportance: should knowfreq 52%

basics

~20 s

A deferred transaction that has already read cannot wait to become a writer: SQLite skips the busy handler and returns SQLITE_BUSY, or SQLITE_BUSY_SNAPSHOT in WAL mode once another connection has committed. Start such transactions with BEGIN IMMEDIATE.

open as a page

A SQLite migration must change a column's type and add a UNIQUE constraint; what can ALTER TABLE do directly, and how do you do the rest safely?

level: seniorimportance: should knowfreq 45%

basics

~20 s

SQLite's ALTER TABLE can rename tables and columns, add and drop columns and, since 3.53.0, add or drop NOT NULL and CHECK constraints. Changing a type or adding UNIQUE needs the rebuild: create the new table, copy, drop the old, rename.

open as a page

When is SQLite, an embedded file database, enough for a desktop app and for a small internal web service, and when is it not?

level: principalimportance: should knowfreq 50%

basics

~20 s

SQLite fits when the code issuing SQL runs on the machine holding the file, writers can take turns, and the data fits comfortably in one file. Many hosts, remote clients writing directly, or sustained concurrent writes point to a client-server database.

open as a page

showing 1–30 of 35