openskills.info
Course catalog
SQLite logoCourse Preview

SQLite

SQLite is a relational database engine that runs inside an application instead of as a separate database server. It stores tables, indexes, and other database content in a portable file, which suits local application data and devices that need transactional storage without a database service.

itDatabases and data storage

Don't Panic — SQLite

The first thing to know about SQLite is that it is not a database server. It is a library. Your application links it, calls it in the same process, and the database is a file the operating system sees. There is no daemon, no network port, no login prompt. The entire cast is the application, the library, and a file on disk.

That single-file arrangement is the feature, not a limitation. Moving a database means moving a file. Backing it up means copying one. Starting over means deleting it. The tradeoff is that SQLite leaves things like access control, locking, and crash recovery to the host application and the operating system rather than managing them inside a service.

Everything flows through a pager that reads and writes fixed-size pages, coordinates locks, and manages journals. SQL text passes through a parser and code generator into virtual-machine instructions, then through B-trees and the pager to the file. This layered design means SQL semantics stay above platform-specific file operations, which is why copying an active database file without the proper backup method can quietly corrupt it.

WAL mode changes how readers and writers interact. Instead of writing changes directly into the main database file, changes append to a separate write-ahead log. Readers keep using a stable snapshot while one writer appends. A checkpoint later moves committed pages back into the main file. Readers and one writer can overlap. Two writers cannot.

Here is the part that surprises most people: SQLite is the most widely deployed database engine in the world. Every smartphone, most browsers, and countless applications carry one. The reason is the same simplicity that makes it feel small — it is a library, so it goes where the application goes.

Two things to get right from the start. Foreign keys are off by default. Each connection must enable them explicitly with PRAGMA foreign_keys = ON before any transaction. Skip that and referential integrity is a polite suggestion, not enforced. And WAL does not create multiple concurrent writers. A long-lived reader can prevent checkpoints from completing, letting the WAL grow without bound. Monitor the WAL file size and keep write transactions short.

For the full architecture, read the Intro. The Cheatsheet covers the operation modes and index decisions. Field Notes has what teams actually get wrong.

Where this skill leads

Relevant careers

See how this topic contributes to broader role-level skill maps.

Sources