SQLite in Production: Lessons from Running a Store on a Single File
1–10 of 135 posts
Re: SQLite in Production: Lessons from Running a Store on a Single File
#2> 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.
More simply:
sqlite> select typeof('{a:1}'->>'a') ;
╭──────────────────────╮
│ typeof('{a:1}'->>... │
╞══════════════════════╡
│ integer │
╰──────────────────────╯
sqlite> select typeof('{a:1}'->'a') ;
╭──────────────────────╮
│ typeof('{a:1}'->'a') │
╞══════════════════════╡
│ text │
╰──────────────────────╯Re: SQLite in Production: Lessons from Running a Store on a Single File
#3> The technical fix was embarrassingly simple: stop pushing to main every ten minutes.
Wait, you push straight to main?
> 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):
> Avoid rapid-fire pushes to main — 11 pushes in 2h caused overlapping Kamal deploys with concurrent SQLite access.
Wait, you let _Claude_ push your e-commerce code straight to main which immediately results in a production deploy?
Re: SQLite in Production: Lessons from Running a Store on a Single File
#4> Backups are cp production.sqlite3 backup.sqlite3
I use gobackup[0] as another container in compose.yml file which can backup to multiple locations.
Re: SQLite in Production: Lessons from Running a Store on a Single File
#5> The technical fix was embarrassingly simple: stop pushing to main every ten minutes. Wait, you push straight to main? > 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): > Avoid rapid-fire pushes to main — 11 pushes in 2h caused overlapping Kamal deploys with concurrent SQLite access. Wait, you let _Claude_ push your e-commer…
Patient: doctor, my app loses data when I deploy twice during a 10 minute interval!
Doctor: simply do not do that
Re: SQLite in Production: Lessons from Running a Store on a Single File
#6> The technical fix was embarrassingly simple: stop pushing to main every ten minutes. Wait, you push straight to main? > 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): > Avoid rapid-fire pushes to main — 11 pushes in 2h caused overlapping Kamal deploys with concurrent SQLite access. Wait, you let _Claude_ push your e-commer…
Patient: doctor, my app loses data when I deploy twice during a 10 minute interval! Doctor: simply do not do that
Doctor: solution is simple, stop letting that stupid clown Pagliacci define how you do your work!
Patient: but doctor,
Re: SQLite in Production: Lessons from Running a Store on a Single File
#7I'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.
how hard and complex is it to roll out postgres?
Re: SQLite in Production: Lessons from Running a Store on a Single File
#8The 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.
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.
Thanks for the vibes!
Re: SQLite in Production: Lessons from Running a Store on a Single File
#9> embarrassingly simple
This is becoming the new overused LLM goto expression for describing basic concepts.
Re: SQLite in Production: Lessons from Running a Store on a Single File
#10llm generated article.
please consider writing it yourself. quirks in human writing is infinitely more interesting than a next-token-predicted 500 word piece