When is SQLite, an embedded file database, enough for a desktop app and for a small internal web service, and when is it not?
answer
- is the network between app and data
- writers that cannot take turns
- one host or several
- terabytes and single-file limits
- who runs backups and failover
basics
~20 sSQLite 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.
solid answer
~50 sI apply SQLite's own checklist, then the operational one. Is the data separated from the application by a network? Then client-server, because SQLite would push the heavy engine-to-disk traffic across it. Must many writers change the data at the same instant without queuing? SQLite allows one writer per file. Will the data reach terabytes? One file is the wrong container. The **desktop app** usually passes everything: one user, local disk, a portable file that doubles as the document format; the risk is users putting it on a shared drive. The **internal web service** passes while it runs on one host, because the application server is the "application" next to the file; SQLite's guidance calls fewer than 100K hits a day a conservative figure. It fails when the service needs several hosts, heavy writes, or automatic failover, which a serverless library has no server to provide.
go deeper
Recall SQLite's three warning signs: data across a network from the code, many writers at once, and very large data.
Explain why an application server next to its file still counts as local, and why one writer at a time rarely hurts when transactions are short.
Turn the checklist into evidence: measure write contention and transaction length, check where the file lives, and test the backup and restore path before trusting the choice.
Own the trade and its exit: record the triggers for moving to a client-server engine, the operational work SQLite leaves with the team, and the portability cost of waiting.
## Start from what SQLite is trying to be SQLite's documentation is explicit that it does not compete with client-server databases: those aim at a **shared repository of enterprise data**, emphasising scalability, concurrency, centralisation and control, while SQLite aims at **local data storage for individual applications and devices**, emphasising economy, simplicity and independence. Its own summary is "SQLite competes with `fopen()`". The decision is therefore less "is SQLite good enough" than "is this workload a local store or a shared server". ## SQLite's own checklist SQLite's guidance reduces the choice to three questions: 1. **Is the data separated from the application by a network?** Choose client-server. An engine filters a lot of disk traffic down to a little result traffic; SQLite lives inside the application, so a remote file forces the heavy link across the network. Here "application" means the code issuing SQL, so an application server next to its file still qualifies. 2. **Many concurrent writers that cannot queue and take turns?** Choose client-server. SQLite allows one writer per database file at a time; most write transactions take milliseconds, so taking turns covers more than people expect, but a server coordinating access handles far more write concurrency. 3. **Big data?** The hard ceiling is about 281 TB, but a single file on one filesystem is the real constraint; once data heads for terabytes, consider a client-server engine. Otherwise, the guidance says, choose SQLite: for device-local storage with low writer concurrency and under a terabyte, it is "almost always a better solution". ## The desktop application | Question | Typical desktop answer | |---|---| | Network between code and data? | No: the file is on the user's disk | | Concurrent writers? | One user, perhaps a background thread | | Size? | Megabytes to gigabytes | | Operations staff? | None, which is the point | SQLite is a strong fit and doubles as the **application file format**: File > Open opens the database, edits are saved as they happen, and Save As can be built on the backup API. The format is cross-platform and stable; every SQLite 3 release reads files written by 3.0.0, so documents outlive the app version that made them. Risks a lead should plan for: - **Users will put the file on a network share.** Locks and sync are unreliable there; detect it or document it as unsupported for shared use. - **The library version you get.** An operating system's bundled SQLite is usually older than the current release; ship your own build if you depend on newer features. - **Schema changes ship with the app**, so every installed copy must migrate its own file. ## The small internal web service | Question | When SQLite still fits | When it stops fitting | |---|---|---| | Hosts | One server holds the file and runs the service | Load or availability needs two or more servers | | Writes | Short transactions that can queue | Write-heavy traffic that cannot queue | | Traffic | SQLite calls under 100K hits a day conservative, and has seen ten times that | Sustained high traffic needing several servers | | Availability | A restart and a restore are acceptable | Automatic failover is required | The browser being on the network does not disqualify SQLite: the clients talk to the service, and the service talks to its local file. What disqualifies it is a **second host** that must write the same data. With no server process, there is no built-in replica or automatic failover to switch to, so standby copies, failover and point-in-time recovery become machinery the team builds and owns. Per-tenant database files can ease the one-writer limit, at the price of many files to back up and migrate. ## Operating cost, the part that decides it - **What SQLite removes**: no server to install, patch, size or monitor, no server connection limit to size, no credentials to rotate. - **What it leaves with you**: backups of a live file, schema migrations, and capacity, all tied to one machine's disk. - **What it lacks**: user accounts and network access control; the file's permissions are the security model. - **Exit cost**: moving to a client-server engine later means revisiting types and dialect, so keep SQL portable if growth is likely. ## A decision you can defend Write the triggers down with the choice: a second application host, sustained write contention, data heading for terabytes, or a recovery-time target a single file cannot meet. Until one fires, SQLite is often the cheaper and more reliable option; when one fires, migrate deliberately rather than patching around the limit.
- The internal web service now needs a second application server behind a load balancer. Can both use the same SQLite file?Not safely by opening it directly: the file would sit on network storage, where locks and sync are unreliable, and WAL mode requires every process on one host. Options are one host owning the file with the other forwarding writes to it, splitting data so each host owns separate files, or moving to a client-server database. For a service that has outgrown one host, the last is usually the honest answer.
- What would make you reject SQLite for the desktop app after all?A requirement that several users on different machines edit the same data at the same time. A shared network file is exactly the case SQLite's guidance warns against, so that requirement means a server, either a client-server database or a service in front of one SQLite file, with the desktop app becoming its client.
- SQLite cannot be used for any website, because a website has many users.
- Users reaching a service over a network rules out SQLite on its server.
- A network share lets several hosts use one SQLite file safely in WAL mode.
- SQLite cannot hold more than a few gigabytes, so size rules it out early.
- Choosing SQLite removes all database operations work, including backups.