SQLite in Production: Lessons from Running a Store on a Single File

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. More simply: sqlite> select typeof('{a:1}'->>'a') ; ╭──────────────────────╮ │ typeof('{a:1}'->>... │ ╞══════════════════════╡ │ integer │ ╰──────────────────────╯ vs: sqlite> select typeof('{a:1}'->'a') ; ╭──────────────────────╮ │ typeof('{a:1}'->'a') │ ╞══════════════════════╡ │ text │ ╰──────────────────────╯

yokuze

> 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?

leosanchez

> Backups are cp production.sqlite3 backup.sqlite3 I use gobackup[0] as another container in compose.yml file which can backup to multiple locations. [0]: https://gobackup.github.io/

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. how hard and complex is it to roll out postgres?

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. 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!

politelemon

> embarrassingly simple This is becoming the new overused LLM goto expression for describing basic concepts.

Natfan

llm generated article. please consider writing it yourself. quirks in human writing is infinitely more interesting than a next-token-predicted 500 word piece

jmull

Redis, four dbs, container orchestration for a site of this modest scope… generated blog posts. Our AI future is a lot less grand than I expected.

jszymborski

The LLM prose are grating read. I promise, you'd do a better job yourself.

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. https://sqlite.org/cli.html#special_commands_to_sqlite3_dot_...