SQLite for Prototypes and Small Apps: Stronger Than You Think

July 6, 2026 · 6 min read · Tools & Stack

Key Takeaways

SQLite is a real database: ACID, legendary test coverage, most-deployed on earth.
Zero ops means your whole runbook is one line: there is a file.
Backup is copying the file; rehearse the restore and it takes four minutes.
The only real wall is concurrent writes; serialize them and the wall moves.
Migrating to Postgres is a day, not a month, if you kept your SQL boring.

Let us talk about that guilt, because it is misplaced. Using SQLite for prototypes and small apps is not cutting a corner. In a wide range of cases it is the correct engineering decision, and the people giving you that look are carrying a database server they do not need like a piece of rolling luggage through an airport.

SQLite is a real database: fully ACID, tested harder than almost any software on earth, and by deployment count the most used database on the planet, sitting inside every phone, every browser, and a healthy share of things with wings. For a prototype or small app with one deploy target and reads vastly outnumbering writes, it is not a compromise. It is the right call, and the file is a feature.

The Database Guilt Trip

The guilt has a source: somewhere along the way, the industry decided that real means client-server. Job posts list Postgres the way resumes list communication skills. Architecture diagrams in conference talks always show a database box separate from the app box, and nobody wants to be the person whose diagram has one box.

But a database server is not free, even when the software is. Someone patches it. Someone rotates credentials. Someone gets paged when connections run out at 5 p.m. on a Friday. For a prototype, that someone is you, and you have a demo on Thursday. The prototype database choice is not a maturity test. It is a bet about where your limited hours go.

The Short Answer: SQLite Is a Real Database

The feature list reads like a checklist you would write yourself: full ACID transactions, indexes, foreign keys, views, triggers, JSON functions, even full-text search. The test suite is the stuff of legend, millions of test cases against a codebase that runs in airliners and medical devices. Your startup's Kanban board is not going to scare it.

Where it differs is operational, not qualitative. SQLite is a library, not a server. Your app links it, opens a file, and speaks SQL. There is no daemon, no port, no max connections, no 2 a.m. alert about a process you forgot you were running.

What Zero Ops Actually Buys You

Zero ops means the entire infrastructure section of your runbook fits in one line: there is a file. No credentials in a secrets manager, no connection pool to tune, no mysterious latency because the database lives in a different availability zone from your app. On a five-dollar VPS, your whole system is a binary and a file, and you can scp the entire state of the business to your laptop to debug with real data.

Backups become almost comically simple: copy the file. Put it in a cron job. If you want to be fancy, stream changes continuously to object storage, litestream-style, and now you have point-in-time recovery for the price of a sandwich. Then do the restore drill. Say you delete the file at 10 a.m. Restore is copying last night's backup back into place, and the whole exercise takes four minutes, including the part where you remember where the backups live. When did you last rehearse a restore on that managed Postgres instance you are paying for?

How Far SQLite Actually Goes

The illustrative numbers surprise people. On a cheap VPS, SQLite serves thousands of reads per second without breaking a sweat. A hundred thousand rows is nothing. Ten million rows is perfectly fine for analytics-shaped reads with a proper index. Turn on WAL mode and readers do not even block during a write, which covers most small apps entirely, because most small apps are read-heavy: dashboards, listings, status pages, reports.

Here is the real wall: concurrent writers. SQLite allows one writer at a time, and when fifty users all submit forms in the same second, writes queue up. That is the honest limit, and you should know it before the internet tells you about it in a less friendly tone.

The Write-Queue Trick

The trick that moves the wall: serialize your writes through one process or a small queue. Your app already does this if it is a single process, which a prototype usually is. Form submissions and status changes funnel through one writer, transactions stay short, and suddenly the single-writer limit stops mattering, because your app never actually had fifty concurrent writes. It had fifty concurrent users, which is a completely different animal. Read-heavy tools like Streamlit dashboards reading straight from the file may never touch the wall at all.

When to Actually Leave SQLite

Honest exit criteria exist, and you should write them down while you are still calm. Leave when you run multiple app servers that all need to write, because one file on one disk does not stretch across a fleet. Leave when a growing team needs direct database access with roles and permissions, because SQLite in production means file permissions, and file permissions are a blunt instrument. Leave when the convenience of managed Postgres is worth more to you than the simplicity of a file, which is a legitimate trade and not a moral failure.

The SQLite vs Postgres comparison at that point is not a fight. Postgres wins on concurrency, access control, and ecosystem, full stop. The good news: if you kept your SQL boring, meaning no exotic pragmas and no clever file-format hacks, migrating from SQLite to Postgres is a day of work, not a month. Export, adjust a few types, import, point the app at the new connection string, done. Teams that graduate from Airtable to a real schema often land on SQLite first for exactly this reason: it is the shortest distance to boring, correct SQL.

Choosing SQLite for Prototypes and Small Apps: The Decision

Run the checklist:

Fewer than about fifty users: SQLite.
One deploy target, meaning one VPS or one container: SQLite.
Reads vastly outnumber writes: SQLite, happily.
Multiple app servers or genuinely heavy concurrent writes: Postgres, and do not feel clever about it.
A team of analysts demanding direct access with permissions: Postgres again.

Notice what is not on the list: embarrassment. The rest of a solid FDE stack follows the same rule, by the way. Pick the boring thing that fits the actual load, and spend the saved hours on the demo.

So the next time someone on the call asks where the database server is, skip the mumble. It is a file, it has backups, the restore drill takes four minutes, and it will outlast this startup's first two pivots. Ask them when they last rehearsed theirs.

Frequently Asked Questions

Is SQLite good enough for production?

Yes, for the right shape of production: one server, reads vastly outnumbering writes, and a team small enough that file permissions suffice. Plenty of high-traffic sites and serious appliances run on it. The mismatch case is a fleet of app servers all writing at once.

How many users can SQLite handle?

Concurrent readers: hundreds, comfortably, on modest hardware. The constraint is writers, since SQLite allows one at a time. Apps with a few dozen users and occasional writes never notice; apps with constant simultaneous writes will.

When should you move from SQLite to Postgres?

When you run multiple app servers that write, when a growing team needs direct access with roles and permissions, or when managed-Postgres convenience beats file simplicity. If your SQL stayed boring, the migration itself is roughly a day of export, adjust, import, and repoint.

What breaks first when SQLite hits its limits?

Write contention. Concurrent write transactions start queuing, latency creeps up on form submissions, and 'database is locked' errors appear under burst load. Reads keep scaling fine, which is why the fix is often a write queue before it's a new database.

Want the full picture? Read the complete guide to forward deployment engineers.

Read the Guide