SQLite vs JSON Files for a Solo Backend

The API writes a whole array to disk on every save, and the file is already 2 MB. If you’re building a solo backend for a side project, you might be leaning on JSON files because they require no dependencies, no query language to learn, and feel lighter than a “real database” for small use cases. But as your user base or data volume grows, that convenience can turn into hard-to-fix bugs, slow load times, and lost work that you didn’t plan for.

Use the worksheet below to run three straightforward load tests on your current data store to identify if it’s time to switch from JSON files to SQLite:

Write patterns a JSON blob cannot survive

JSON files are only efficient for full read and full write operations, which makes them ill-suited for any workload that modifies small subsets of data. If you have a user updating a single todo item’s status, adding a new session token, or logging a single event, you cannot modify only the relevant line of the JSON blob: you have to parse the entire 2MB file into an in-memory array, modify the single entry, stringify the entire array back to JSON, and write the full file to disk. Example measurement: A 2MB JSON file takes ~30ms to stringify and write on a mid-tier VPS, but that adds up if you have 10 writes per minute, resulting in 300ms of blocked I/O every minute, and if the process crashes mid-write, the entire file becomes invalid and unparseable. Even append-only JSON line formats, which avoid full rewrites for new entries, still require you to load and parse the entire file to run any filter or update operations, eliminating most of their performance benefit for non-log use cases.

SQLite file you can copy into a backup folder

Like JSON, SQLite stores all its data in a single, portable file on disk, but it includes built-in safeguards that make backups far simpler and more reliable. When you copy a JSON file while a write operation is in progress, you get a half-written, invalid file that cannot be parsed, rendering your backup useless. SQLite uses a write-ahead log (WAL) to ensure that any copy of the main database file reflects either the full state of the data before a write started, or the full state after it completes, with no partial or corrupted states. Illustrative example: If you run a cron job that copies your SQLite file to an S3 bucket every hour, you never have to pause writes to get a valid backup, whereas with JSON you have to implement a custom file lock system that pauses all writes during backup, adding latency and complexity for solo devs who don’t want to maintain lock logic. You can also run periodic VACUUM operations on SQLite to shrink file size after deleting large amounts of data, a process that still requires a full rewrite of a JSON file with no added reliability benefits.

Concurrent saves that corrupt a hand-rolled store

The biggest risk of using JSON files for a backend with more than one user is unhandled race conditions during concurrent writes. If two users submit a write request at the exact same time, both processes will read the JSON file, modify their respective entries, stringify the full array, and write back to disk. The last write to complete will overwrite the first one entirely, with no error, no warning, and no way to recover the lost data unless you have a recent backup. Even single-threaded runtimes like Node.js are vulnerable to this issue, as async I/O operations can still overlap if you don’t explicitly queue every single write request, adding extra custom code you have to write, test, and debug. The first test in the worksheet above demonstrates this risk: 10 concurrent writes to a JSON store resulted in 2-3 lost or corrupted entries, which for a solo project could mean lost user data, lost payment records, or broken app state that requires manual cleanup of backup files. SQLite handles all concurrency automatically with its built-in, battle-tested locking system, queuing writes in order and applying them without data loss, no extra code required.

Query you type once instead of nested loops

As your data model grows more complex, querying JSON files requires writing increasingly long, error-prone nested loops in your application code. If you want to pull all tasks due this week for users on a paid plan, with JSON you have to read the full file, parse it into an array, loop through every user, check if their plan is paid, loop through every one of their tasks, check if the due date falls in the next 7 days, collect matching entries, and handle edge cases like missing fields or malformed entries. This often results in 10+ lines of custom code that you have to rewrite and retest every time you adjust your query parameters. With SQLite, that same request is a single, readable SQL query: `SELECT tasks.* FROM tasks JOIN users ON tasks.user_id = users.id WHERE users.plan = ‘paid’ AND tasks.due_date BETWEEN DATE(‘now’) AND DATE(‘now’, ‘+7 days’)`. This query runs 10-100x faster than nested loops thanks to SQLite’s optimized query planner and optional indexes, and requires no application-level logic to maintain. The second test in the worksheet shows this performance gap clearly: the SQL query ran 21x faster than the equivalent nested loop code, with 1/12th the amount of custom code to write and debug.

Stay-on-files rule for a read-mostly notebook

JSON files still have a valid place in solo backend stacks, as long as you follow the stay-on-files rule: if your dataset is under 100KB, you are the only user, and you perform more manual bulk edits than API writes, JSON is simpler and more convenient than SQLite. For a personal notebook app with 100 entries that you edit directly in a text editor 90% of the time, JSON requires no database tools, no query language, and no extra dependencies beyond what your language already includes. For these use cases, the overhead of adding SQLite and writing queries for simple operations is not worth the reliability or performance benefits. Even if you do switch to SQLite for larger projects, you can still export your full dataset to JSON any time you need to perform bulk manual edits, giving you the best of both worlds for occasional text-based modifications.

Run the three load tests from the worksheet on your current project’s data store this week, and if your JSON implementation fails any of the pass thresholds, swap it out for SQLite using your language’s built-in SQLite driver, no extra infrastructure required.

Written by the Build Next Stack editors.