SQLite runs inside your phone, your browser and a long list of desktop apps — we covered what it is and how the single-file model works in SQLite Explained: The World’s Most-Deployed Database. This post answers the harder, more practical question: should your next project use it? Instead of a list of pros and cons, here is a decision guide — one question that settles most cases, the real limits you will hit, honest comparisons with the alternatives, and the anti-patterns that cause most SQLite horror stories.

The one question that settles most cases

Forget database size and “is it production-grade” for a moment. Ask this instead:

Do all the processes that write to this data run on the same machine as the file?

If yes, SQLite is very likely a good fit, and probably the simplest one. If no — if laptops in an office, several web servers, or a fleet of workers all need to write to the same data — you want a client-server database, because SQLite has no network protocol: every writer must open the file directly. Almost every situation where SQLite “fell over” turns out to be a violation of this rule, usually by putting the file on a network share.

A useful second question: how many writes per second, and how big is each transaction? SQLite allows only one writer at a time. Readers are cheap and can run in parallel, but writes queue up. For most applications this is invisible, because a well-batched write takes milliseconds. For a workload that is dominated by many independent writers hammering the database at once, it is the ceiling.

A scenario checklist

Here is how that reasoning plays out for common projects:

Scenario SQLite? Why
Desktop or mobile app storing its own data Yes One machine, one app — the case SQLite was built for.
Local cache of remote data Yes Fast reads, real queries, easy to throw away and rebuild.
Small to medium website on a single server Usually yes Read-heavy traffic is handled well; enable WAL mode and keep writes short.
Data analysis of a dataset that fits on one disk Yes Full SQL, indexes and window functions with no server to install.
File format for documents or project files Yes Atomic saves and partial updates instead of rewriting a whole file.
Sensor or device logging on the device itself Yes Small footprint, crash-safe transactions.
Shared database on a file server for several PCs No Network file locking is unreliable; corruption risk.
Web app behind a load balancer with several servers No Writers on different machines need a server database.
High-volume multi-tenant SaaS with heavy concurrent writes No The single-writer design becomes the bottleneck.
Fine-grained per-user permissions on the data No SQLite has no users or roles; anyone who can read the file can read the data.
🔧 BackendSide Tool

SQLiteDesk — See What Is Really in the File

The fastest way to judge whether SQLite fits is to look at a real database. SQLiteDesk opens or creates a database in one click, shows tables, views, indexes and triggers in a searchable schema tree, and draws an ER diagram of an existing database from the live schema — so you can see how the data fits together before you commit to a design. The SQL workspace has syntax highlighting and schema-aware autocomplete for trying out the queries your app will need.

Explore SQLiteDesk →

The limits that actually matter

SQLite’s theoretical limits are rarely the problem — the maximum database size runs into the hundreds of terabytes, and a single row can hold up to a gigabyte by default. The practical limits are different:

  • One writer at a time. A write transaction locks the database for other writers until it commits. In the default rollback-journal mode it also blocks readers briefly; in WAL mode readers carry on undisturbed.
  • Commit cost, not row cost. Every committed transaction waits for the disk to confirm the data is safe. Inserting 10,000 rows as 10,000 separate transactions is slow; inserting them in one transaction is fast. Most “SQLite is slow” complaints are really this.
  • Same host only. SQLite relies on the operating system’s file locks. On network file systems (SMB shares, NFS) those locks are often unreliable, and WAL mode needs shared memory that only works on one machine.
  • No built-in access control. Security is whatever the file system gives you, plus encryption such as SQLCipher if you need the file unreadable at rest.
  • Loose typing by default. Columns accept values of other types unless you use STRICT tables or CHECK constraints.

The batching point is worth seeing in code, because it is the single biggest performance lever you have:

-- Slow: each INSERT is its own transaction and its own disk sync
INSERT INTO readings (sensor, value) VALUES (1, 20.5);
INSERT INTO readings (sensor, value) VALUES (1, 20.7);
-- ...thousands more...

-- Fast: one transaction, one sync at COMMIT
BEGIN;
INSERT INTO readings (sensor, value) VALUES (1, 20.5);
INSERT INTO readings (sensor, value) VALUES (1, 20.7);
-- ...thousands more...
COMMIT;

-- For a single-server web app, also consider:
PRAGMA journal_mode = WAL;     -- readers no longer block the writer
PRAGMA busy_timeout = 5000;    -- wait up to 5 s for a lock instead of failing

SQLite vs a client-server database

PostgreSQL, MySQL, MariaDB and SQL Server are not “better SQLite”; they solve a different problem. Compare them on the things that drive the decision:

SQLite Client-server database
Setup None — a library and a file Install, configure, secure and update a server
Concurrent writers One at a time Many, with row-level locking
Access from other machines Not supported Built in, over the network
Users and permissions None Roles, grants, auditing
Latency per query Very low (in-process) Network round trip on every query
Backup Online backup API or VACUUM INTO Dump tools, replication, point-in-time recovery
Deploying with your app Ship the file Ship and run a service

Note the latency row: because SQLite runs inside your process, issuing many small queries is cheap. Patterns that would be an “N+1 query” disaster over a network are often perfectly fine against SQLite.

SQLite vs JSON, CSV and other flat files

The more common real-world competitor is not PostgreSQL — it is a JSON or CSV file. Flat files are fine for small, rarely changing data like a settings file. They start hurting as soon as the data grows or changes often:

SQLite JSON / CSV file
Updating one record Writes only the changed pages Rewrite the entire file
Crash in the middle of a save Transaction rolls back cleanly Possibly a truncated or half-written file
Finding records Indexed SQL queries Load everything, filter in code
Memory use Reads only what it needs Usually whole file in memory
Human-readable in a text editor No (needs a viewer) Yes
Diffable in version control Poorly Well

A good rule of thumb: if you find yourself writing code to search, sort, or partially update a JSON file, or to make its saves crash-safe, you are reinventing a database — use SQLite.

🔧 BackendSide Tool

SQLiteDesk — Move Off JSON and CSV Without Losing Data

Outgrown a pile of JSON or CSV files? SQLiteDesk imports CSV and JSON with type inference and creates the table for you. Large CSV files are streamed, and values that would lose digits as numbers — ZIP codes, phone and account numbers — stay text, so leading zeros survive. And when you need to back up a database that is in use, its online backup is safe on a live database while writes are happening — no risky file copies.

Explore SQLiteDesk →

Anti-patterns that give SQLite a bad name

Most SQLite failures are not SQLite’s fault. These are the patterns to avoid:

  1. The shared network drive. Putting one .db on a file server so several PCs can use it is the classic mistake. It may work for weeks, then corrupt under concurrent use. If several machines must write, use a server database.
  2. Copying the file while it is open. A plain file copy taken mid-write can capture an inconsistent state — and in WAL mode, recent commits may still live in the -wal file you did not copy. Use the online backup API, the CLI’s .backup command, or VACUUM INTO 'backup.db'.
  3. Autocommit loops. Thousands of single-row transactions in a loop, as shown above. Wrap them in one transaction.
  4. Long-running write transactions. Holding a write lock while the app waits for user input or a network call blocks every other writer. Do the slow work first, then open a short transaction.
  5. No busy timeout. Without busy_timeout, a second connection that finds the database locked fails immediately with “database is locked” instead of waiting a moment.
  6. Everything in one JSON column. Storing whole objects as JSON text gives up indexes and constraints. SQLite has good JSON functions, but use real columns for anything you filter or join on.
  7. Trusting declared types. A column declared INTEGER will happily store the text 'abc' unless the table is STRICT. Validate, or let the database do it.

Three quick worked decisions

A point-of-sale app for a single shop till. One machine, modest writes, must survive power cuts. SQLite, with WAL mode and regular online backups to another drive. Easy call.

The same shop wants three tills sharing stock levels. Now there are writers on three machines. Do not put the SQLite file on a shared folder. Either run a small server database, or give each till its own SQLite database and synchronise through a central service.

An internal reporting site on one server with a few hundred users. Mostly reads, occasional writes from a nightly import. SQLite in WAL mode is a sensible, low-maintenance choice — and if it ever outgrows one server, migrating the schema to a server database is straightforward.

🔧 BackendSide Tool

SQLiteDesk — A SQLite Manager for Windows

Decided SQLite is the right tool? SQLiteDesk is where you will spend your time with it: design tables visually with a live DDL preview, browse, sort and filter rows in an Excel-style grid, run SQL, export to CSV, JSON or SQL, and run VACUUM, ANALYZE, REINDEX and integrity checks from a menu. Runs 100% offline. Available on the Microsoft Store with a free trial — no subscription, no ads.

Explore SQLiteDesk →

Key takeaways

  • The deciding question is where the writers run: if every writer is on the same machine as the file, SQLite is usually the simplest correct choice.
  • The practical limits are one writer at a time, same host only and no built-in access control — not database size.
  • Most performance problems come from committing too often; batch writes into transactions and use WAL mode plus a busy timeout for concurrent apps.
  • Against JSON or CSV files, SQLite wins as soon as you need partial updates, crash-safe saves or queries.
  • Avoid the anti-patterns: no network shares, no copying open files, no long write transactions.
  • SQLiteDesk lets you inspect, design, import into and back up SQLite databases on Windows from one window.