SQLite: A Database in a Single File
SQLite is a self-contained SQL database engine that lives in a single file. It's ideal for mobile apps needing fast, reliable local data storage without a server. The footgun is using it for high-concurrency write scenarios, where it becomes a bottleneck.
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.
Interview question
Why is SQLite generally not recommended for a backend service with many concurrent write operations?
- a.It requires too much memory and CPU overhead for server environments.
- b.File-level locking creates a bottleneck by forcing concurrent write operations to wait.Correct
- c.Data integrity cannot be guaranteed when multiple clients attempt to modify the database simultaneously.
- d.Its in-process nature prevents efficient handling of network requests.
Why? this is the answer
The card explicitly states that SQLite uses file-level locking, which causes writers to block each other, creating a performance bottleneck for high-volume, simultaneous writes. While integrity is maintained, the performance hit makes it unsuitable.
Just read this? Test yourself on what you have been reading.
Read the original → sqlite.org
Put your scrolling time to good use
Learn one idea, try a quiz and save useful cards for revision. Tezvyn makes it easy to learn and stay current in your tech field, a few minutes at a time.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on database — each one lists the topics its interview covers.
See open roles