SQLite in Production: Lessons from Running a Store on a Single File
by thunderbong on 4/4/2026, 9:15:47 AM
https://ultrathink.art/blog/sqlite-in-production-lessons
Comments
by: yokuze
> The technical fix was embarrassingly simple: stop pushing to main every ten minutes.<p>Wait, you push straight to main?<p>> We added a rule — batch related changes, avoid rapid-fire pushes. It's in our CLAUDE.md (the governance file that all our AI agents follow):<p>> Avoid rapid-fire pushes to main — 11 pushes in 2h caused overlapping Kamal deploys with concurrent SQLite access.<p>Wait, you let _Claude_ push your e-commerce code straight to main which immediately results in a production deploy?
4/4/2026, 11:50:33 AM
by: infamia
SQLite has a ".backup" command that you should always use to backup a SQLite DB. You're risking data loss/corruption using "cp" to backup your database as prescribed in the article.<p><a href="https://sqlite.org/cli.html#special_commands_to_sqlite3_dot_commands_" rel="nofollow">https://sqlite.org/cli.html#special_commands_to_sqlite3_dot_...</a>
4/4/2026, 5:55:40 PM
by: crazygringo
> <i>Would We Choose SQLite Again? Yes. For a single-server deployment with moderate write volume, SQLite eliminates an entire category of infrastructure complexity. No connection pool tuning. No database server upgrades. No replication lag.</i><p>These are weird reasons. You can just install Postgres or MySQL locally too. Connection pool tuning certainly isn't anything you have to worry about for a moderate write volume. You don't ever need to upgrade the database if you don't want to, since you're not publicly exposing it. There's obviously no replication lag if you're not replicating, which you wouldn't be with a single server.<p>The reason you don't usually choose SQLite for the web is future-proofing. If you're <i>totally sure</i> you'll always stay single-server forever, then sure, go for it. But if there's even a <i>tiny chance</i> you'll ever need to expand to multiple web servers, then you'll wish you'd chosen a client-server database from the start. And again, you can run Postgres/MySQL locally, on even the tiniest cheapest VPS, basically just as easily as using SQLite.
4/7/2026, 2:47:15 PM
by: cadamsdotcom
The fix appears to nicely asking the forgetful unreliable agent to please (very closely pretty please!) follow the deploy instructions (and also please never hallucinate or mess up, because statistics tells us an entity with no long term memory and no incentive to get everything right will do the job right 99.99999999% of the time, which is good enough to run an eshop) not deploy too often per hour.<p>With one simple instruction the system (99.9999% of the time) gains the handy property that “only” two processes end up with the database files open at once.<p>Thanks for the vibes!
4/4/2026, 12:24:24 PM
by: jmull
Redis, four dbs, container orchestration for a site of this modest scope… generated blog posts.<p>Our AI future is a lot less grand than I expected.
4/4/2026, 1:56:05 PM
by: sgbeal
> json_extract returns native types. json_extract(data, '$.id') returns an integer if the value was stored as a number. Comparing it to a string silently fails. Always CAST(json_extract(...) AS TEXT) when you need string comparison.<p>More simply:<p><pre><code> sqlite> select typeof('{a:1}'->>'a') ; ╭──────────────────────╮ │ typeof('{a:1}'->>... │ ╞══════════════════════╡ │ integer │ ╰──────────────────────╯ </code></pre> vs:<p><pre><code> sqlite> select typeof('{a:1}'->'a') ; ╭──────────────────────╮ │ typeof('{a:1}'->'a') │ ╞══════════════════════╡ │ text │ ╰──────────────────────╯</code></pre>
4/4/2026, 9:28:07 AM
by: Natfan
llm generated article.<p>please consider writing it yourself. quirks in human writing is infinitely more interesting than a next-token-predicted 500 word piece
4/4/2026, 1:30:20 PM
by: politelemon
> embarrassingly simple<p>This is becoming the new overused LLM goto expression for describing basic concepts.
4/4/2026, 12:42:56 PM
by: jszymborski
The LLM prose are grating read. I promise, you'd do a better job yourself.
4/4/2026, 2:08:36 PM
by: jmull
I don't know if it's just me, but this whole post seems to have time traveled forward from about 3-4 days ago.<p>It's not just a repost. The thread includes a comment I made at the time which now from "1 hour ago".<p>Makes me wonder if it's an honest bug or someone has hacked the hacker news front page to sell their t-shits, mugs, and AI starter kits.
4/7/2026, 3:47:51 PM
by: nop_slide
I still haven't figured out a good way to due blue/green sqlite deploys on fly.io. Is this just a limitation of using sqlite or using Fly? I've been very happy with sqlite otherwise, rather unsure how to do a cutover to a new instance.<p>Anyone have some docs on how to cutover gracefully with sqlite on other providers?
4/7/2026, 3:42:18 PM
by: trelliumD
could have used firebird embedded, also a simple deployment such as sqllite, but better concurrency and more complete system, also a tad faster
4/7/2026, 4:25:44 PM
by: faangguyindia
I've a busy app, i just deploy to canary. And use loadbalancer to move 5% traffic to it, i observe how it reacts and then rollout the canary changes to all.<p>how hard and complex is it to roll out postgres?
4/4/2026, 12:09:56 PM
by: leosanchez
> Backups are cp production.sqlite3 backup.sqlite3<p>I use gobackup[0] as another container in compose.yml file which can backup to multiple locations.<p>[0]: <a href="https://gobackup.github.io/" rel="nofollow">https://gobackup.github.io/</a>
4/4/2026, 12:02:26 PM
by: NicoJuicy
If Nico send him an email. The AI CEO should take his offer.
4/7/2026, 4:32:29 PM
by: pgideas
[dead]
4/7/2026, 4:03:46 PM