skip to content

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.