{"id":352,"date":"2026-10-25T16:00:00","date_gmt":"2026-10-25T16:00:00","guid":{"rendered":"https:\/\/backendside.com\/blog\/?p=352"},"modified":"2026-11-21T11:23:08","modified_gmt":"2026-11-21T11:23:08","slug":"when-to-use-sqlite-and-when-not","status":"publish","type":"post","link":"https:\/\/backendside.com\/blog\/2026\/10\/25\/when-to-use-sqlite-and-when-not\/","title":{"rendered":"SQLite Is Everywhere: When It&#8217;s the Right Database (and When It Isn&#8217;t)"},"content":{"rendered":"<p class=\"lead\">SQLite runs inside your phone, your browser and a long list of desktop apps &mdash; we covered what it is and how the single-file model works in <a href=\"https:\/\/backendside.com\/blog\/2026\/07\/01\/sqlite-explained-worlds-most-deployed-database\/\">SQLite Explained: The World&rsquo;s Most-Deployed Database<\/a>. This post answers the harder, more practical question: <strong>should <em>your<\/em> next project use it?<\/strong> Instead of a list of pros and cons, here is a decision guide &mdash; 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.<\/p>\n<h2>The one question that settles most cases<\/h2>\n<p>Forget database size and &ldquo;is it production-grade&rdquo; for a moment. Ask this instead:<\/p>\n<p><strong>Do all the processes that write to this data run on the same machine as the file?<\/strong><\/p>\n<p>If yes, SQLite is very likely a good fit, and probably the simplest one. If no &mdash; if laptops in an office, several web servers, or a fleet of workers all need to write to the same data &mdash; you want a client-server database, because SQLite has no network protocol: every writer must open the file directly. Almost every situation where SQLite &ldquo;fell over&rdquo; turns out to be a violation of this rule, usually by putting the file on a network share.<\/p>\n<p>A useful second question: <strong>how many writes per second, and how big is each transaction?<\/strong> 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.<\/p>\n<h2>A scenario checklist<\/h2>\n<p>Here is how that reasoning plays out for common projects:<\/p>\n<table>\n<thead>\n<tr>\n<th>Scenario<\/th>\n<th>SQLite?<\/th>\n<th>Why<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>Desktop or mobile app storing its own data<\/td>\n<td>Yes<\/td>\n<td>One machine, one app &mdash; the case SQLite was built for.<\/td>\n<\/tr>\n<tr>\n<td>Local cache of remote data<\/td>\n<td>Yes<\/td>\n<td>Fast reads, real queries, easy to throw away and rebuild.<\/td>\n<\/tr>\n<tr>\n<td>Small to medium website on a single server<\/td>\n<td>Usually yes<\/td>\n<td>Read-heavy traffic is handled well; enable WAL mode and keep writes short.<\/td>\n<\/tr>\n<tr>\n<td>Data analysis of a dataset that fits on one disk<\/td>\n<td>Yes<\/td>\n<td>Full SQL, indexes and window functions with no server to install.<\/td>\n<\/tr>\n<tr>\n<td>File format for documents or project files<\/td>\n<td>Yes<\/td>\n<td>Atomic saves and partial updates instead of rewriting a whole file.<\/td>\n<\/tr>\n<tr>\n<td>Sensor or device logging on the device itself<\/td>\n<td>Yes<\/td>\n<td>Small footprint, crash-safe transactions.<\/td>\n<\/tr>\n<tr>\n<td>Shared database on a file server for several PCs<\/td>\n<td>No<\/td>\n<td>Network file locking is unreliable; corruption risk.<\/td>\n<\/tr>\n<tr>\n<td>Web app behind a load balancer with several servers<\/td>\n<td>No<\/td>\n<td>Writers on different machines need a server database.<\/td>\n<\/tr>\n<tr>\n<td>High-volume multi-tenant SaaS with heavy concurrent writes<\/td>\n<td>No<\/td>\n<td>The single-writer design becomes the bottleneck.<\/td>\n<\/tr>\n<tr>\n<td>Fine-grained per-user permissions on the data<\/td>\n<td>No<\/td>\n<td>SQLite has no users or roles; anyone who can read the file can read the data.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">SQLiteDesk &mdash; See What Is Really in the File<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">The fastest way to judge whether SQLite fits is to look at a real database. <strong>SQLiteDesk<\/strong> opens or creates a database in one click, shows tables, views, indexes and triggers in a searchable schema tree, and draws an <strong>ER diagram of an existing database<\/strong> from the live schema &mdash; 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.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/sqlitedesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore SQLiteDesk &rarr;<\/a>\n<\/div>\n<h2>The limits that actually matter<\/h2>\n<p>SQLite&rsquo;s theoretical limits are rarely the problem &mdash; 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:<\/p>\n<ul>\n<li><strong>One writer at a time.<\/strong> 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.<\/li>\n<li><strong>Commit cost, not row cost.<\/strong> 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 &ldquo;SQLite is slow&rdquo; complaints are really this.<\/li>\n<li><strong>Same host only.<\/strong> SQLite relies on the operating system&rsquo;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.<\/li>\n<li><strong>No built-in access control.<\/strong> Security is whatever the file system gives you, plus encryption such as SQLCipher if you need the file unreadable at rest.<\/li>\n<li><strong>Loose typing by default.<\/strong> Columns accept values of other types unless you use <code>STRICT<\/code> tables or <code>CHECK<\/code> constraints.<\/li>\n<\/ul>\n<p>The batching point is worth seeing in code, because it is the single biggest performance lever you have:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>-- Slow: each INSERT is its own transaction and its own disk sync\nINSERT INTO readings (sensor, value) VALUES (1, 20.5);\nINSERT INTO readings (sensor, value) VALUES (1, 20.7);\n-- ...thousands more...\n\n-- Fast: one transaction, one sync at COMMIT\nBEGIN;\nINSERT INTO readings (sensor, value) VALUES (1, 20.5);\nINSERT INTO readings (sensor, value) VALUES (1, 20.7);\n-- ...thousands more...\nCOMMIT;\n\n-- For a single-server web app, also consider:\nPRAGMA journal_mode = WAL;     -- readers no longer block the writer\nPRAGMA busy_timeout = 5000;    -- wait up to 5 s for a lock instead of failing<\/code><\/pre>\n<h2>SQLite vs a client-server database<\/h2>\n<p>PostgreSQL, MySQL, MariaDB and SQL Server are not &ldquo;better SQLite&rdquo;; they solve a different problem. Compare them on the things that drive the decision:<\/p>\n<table>\n<thead>\n<tr>\n<th><\/th>\n<th>SQLite<\/th>\n<th>Client-server database<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>Setup<\/td>\n<td>None &mdash; a library and a file<\/td>\n<td>Install, configure, secure and update a server<\/td>\n<\/tr>\n<tr>\n<td>Concurrent writers<\/td>\n<td>One at a time<\/td>\n<td>Many, with row-level locking<\/td>\n<\/tr>\n<tr>\n<td>Access from other machines<\/td>\n<td>Not supported<\/td>\n<td>Built in, over the network<\/td>\n<\/tr>\n<tr>\n<td>Users and permissions<\/td>\n<td>None<\/td>\n<td>Roles, grants, auditing<\/td>\n<\/tr>\n<tr>\n<td>Latency per query<\/td>\n<td>Very low (in-process)<\/td>\n<td>Network round trip on every query<\/td>\n<\/tr>\n<tr>\n<td>Backup<\/td>\n<td>Online backup API or <code>VACUUM INTO<\/code><\/td>\n<td>Dump tools, replication, point-in-time recovery<\/td>\n<\/tr>\n<tr>\n<td>Deploying with your app<\/td>\n<td>Ship the file<\/td>\n<td>Ship and run a service<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Note the latency row: because SQLite runs inside your process, issuing many small queries is cheap. Patterns that would be an &ldquo;N+1 query&rdquo; disaster over a network are often perfectly fine against SQLite.<\/p>\n<h2>SQLite vs JSON, CSV and other flat files<\/h2>\n<p>The more common real-world competitor is not PostgreSQL &mdash; 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:<\/p>\n<table>\n<thead>\n<tr>\n<th><\/th>\n<th>SQLite<\/th>\n<th>JSON \/ CSV file<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>Updating one record<\/td>\n<td>Writes only the changed pages<\/td>\n<td>Rewrite the entire file<\/td>\n<\/tr>\n<tr>\n<td>Crash in the middle of a save<\/td>\n<td>Transaction rolls back cleanly<\/td>\n<td>Possibly a truncated or half-written file<\/td>\n<\/tr>\n<tr>\n<td>Finding records<\/td>\n<td>Indexed SQL queries<\/td>\n<td>Load everything, filter in code<\/td>\n<\/tr>\n<tr>\n<td>Memory use<\/td>\n<td>Reads only what it needs<\/td>\n<td>Usually whole file in memory<\/td>\n<\/tr>\n<tr>\n<td>Human-readable in a text editor<\/td>\n<td>No (needs a viewer)<\/td>\n<td>Yes<\/td>\n<\/tr>\n<tr>\n<td>Diffable in version control<\/td>\n<td>Poorly<\/td>\n<td>Well<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>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 &mdash; use SQLite.<\/p>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">SQLiteDesk &mdash; Move Off JSON and CSV Without Losing Data<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">Outgrown a pile of JSON or CSV files? <strong>SQLiteDesk<\/strong> 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 &mdash; ZIP codes, phone and account numbers &mdash; stay text, so leading zeros survive. And when you need to back up a database that is in use, its <strong>online backup is safe on a live database while writes are happening<\/strong> &mdash; no risky file copies.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/sqlitedesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore SQLiteDesk &rarr;<\/a>\n<\/div>\n<h2>Anti-patterns that give SQLite a bad name<\/h2>\n<p>Most SQLite failures are not SQLite&rsquo;s fault. These are the patterns to avoid:<\/p>\n<ol>\n<li><strong>The shared network drive.<\/strong> Putting one <code>.db<\/code> 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.<\/li>\n<li><strong>Copying the file while it is open.<\/strong> A plain file copy taken mid-write can capture an inconsistent state &mdash; and in WAL mode, recent commits may still live in the <code>-wal<\/code> file you did not copy. Use the online backup API, the CLI&rsquo;s <code>.backup<\/code> command, or <code>VACUUM INTO 'backup.db'<\/code>.<\/li>\n<li><strong>Autocommit loops.<\/strong> Thousands of single-row transactions in a loop, as shown above. Wrap them in one transaction.<\/li>\n<li><strong>Long-running write transactions.<\/strong> 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.<\/li>\n<li><strong>No busy timeout.<\/strong> Without <code>busy_timeout<\/code>, a second connection that finds the database locked fails immediately with &ldquo;database is locked&rdquo; instead of waiting a moment.<\/li>\n<li><strong>Everything in one JSON column.<\/strong> 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.<\/li>\n<li><strong>Trusting declared types.<\/strong> A column declared <code>INTEGER<\/code> will happily store the text <code>'abc'<\/code> unless the table is <code>STRICT<\/code>. Validate, or let the database do it.<\/li>\n<\/ol>\n<h2>Three quick worked decisions<\/h2>\n<p><strong>A point-of-sale app for a single shop till.<\/strong> One machine, modest writes, must survive power cuts. SQLite, with WAL mode and regular online backups to another drive. Easy call.<\/p>\n<p><strong>The same shop wants three tills sharing stock levels.<\/strong> 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.<\/p>\n<p><strong>An internal reporting site on one server with a few hundred users.<\/strong> Mostly reads, occasional writes from a nightly import. SQLite in WAL mode is a sensible, low-maintenance choice &mdash; and if it ever outgrows one server, migrating the schema to a server database is straightforward.<\/p>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">SQLiteDesk &mdash; A SQLite Manager for Windows<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">Decided SQLite is the right tool? <strong>SQLiteDesk<\/strong> 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 &mdash; no subscription, no ads.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/sqlitedesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore SQLiteDesk &rarr;<\/a>\n<\/div>\n<h2>Key takeaways<\/h2>\n<ul>\n<li>The deciding question is <strong>where the writers run<\/strong>: if every writer is on the same machine as the file, SQLite is usually the simplest correct choice.<\/li>\n<li>The practical limits are <strong>one writer at a time<\/strong>, <strong>same host only<\/strong> and <strong>no built-in access control<\/strong> &mdash; not database size.<\/li>\n<li>Most performance problems come from <strong>committing too often<\/strong>; batch writes into transactions and use WAL mode plus a busy timeout for concurrent apps.<\/li>\n<li>Against JSON or CSV files, SQLite wins as soon as you need partial updates, crash-safe saves or queries.<\/li>\n<li>Avoid the anti-patterns: <strong>no network shares, no copying open files, no long write transactions<\/strong>.<\/li>\n<li><strong>SQLiteDesk<\/strong> lets you inspect, design, import into and back up SQLite databases on Windows from one window.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>A practical decision guide for SQLite: the one question that settles most cases, real workload limits, side-by-side comparisons with client-server databases and with JSON or CSV files, and the anti-patterns that turn a good choice into a bad one.<\/p>\n","protected":false},"author":1,"featured_media":405,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,8],"tags":[34,32,33],"class_list":["post-352","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqlite","category-tutorials","tag-database-design","tag-sqlite","tag-sqlitedesk"],"_links":{"self":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/352","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/comments?post=352"}],"version-history":[{"count":1,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/352\/revisions"}],"predecessor-version":[{"id":406,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/352\/revisions\/406"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/media\/405"}],"wp:attachment":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/media?parent=352"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/categories?post=352"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/tags?post=352"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}