SQLite: A Database in a Single File

WHY IT EXISTS Applications, especially on mobile or embedded devices, need a way to reliably store and query structured data locally. Setting up a traditional client-server database like PostgreSQL is overkill; it requires a separate process, complex configuration, and network communication. SQLite was created to provide the power of a full-featured, transactional SQL database without the operational overhead, embedding it directly into the application as a library.

THE MENTAL MODEL Think of SQLite not as a database server you connect to, but as a single file (e.g., my_app_data.sqlite) in your application's directory that you can query with standard SQL. It's an in-process C library that reads and writes directly to this file. It's like having a powerful, private spreadsheet that your code can manipulate with high-level commands, all without a network connection or a separate running program.

HOW IT WORKS When your application links against the SQLite library, it gains a complete SQL engine. SQL queries are not sent over a network; they are function calls to the library. The engine parses, optimizes, and executes these queries against the database file. To ensure transactional integrity (ACID properties), SQLite uses file-level locking. This is a simple and robust mechanism for single-system concurrency, but it's also the source of its main limitation: it's not designed for high-volume, simultaneous writes from many different processes or machines.

WHEN TO USE IT SQLite is the go-to choice for local data storage in mobile apps (iOS and Android), desktop applications, and embedded systems. It excels at caching data from a remote server for offline access, storing user-generated content, managing application state, or serving as a portable file format for complex data. If your application needs a local, structured, and transactional data store, SQLite is almost always the right answer.

WHEN NOT TO USE IT Avoid SQLite for services that require high write concurrency from multiple clients, like a busy website backend. Its file-level locking will cause writers to block each other, creating a performance bottleneck. It's also not intended for huge, multi-terabyte datasets or situations requiring complex user permissions and network-based access control. For these cases, a client/server database like PostgreSQL or MySQL is more appropriate.

ONE CANONICAL EXAMPLE A mobile email client needs to store thousands of emails, their read status, and folder structure for offline viewing. Using SQLite, the app can create tables for messages, folders, and accounts. When the user launches the app, it instantly queries the local SQLite database to populate the UI, providing a fast user experience while syncing with the remote email server in the background.