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?
"Kamal runs blue-green deploys — it starts a new container, health-checks it, then stops the old one. During the switchover, both containers are running. Both mount ultrathink_storage. Both have the SQLite files open."
WAL mode requires shared access to System V IPC mapped memory. This is unlikely to work across containers.
In case anybody needs a refresher:
https://en.wikipedia.org/wiki/Shared_memory
https://en.wikipedia.org/wiki/CB_UNIX
https://www.ibm.com/docs/en/aix/7.1.0?topic=operations-syste...
I think you're exactly right about the WAL shared memory not crossing the container boundary. EDIT: It looks like WAL works fine across Docker boundaries, see https://news.ycombinator.com/item?id=47637353#47677163
I don't know much about Kamal but I'd look into ways of "pausing" traffic during a deploy - the trick where a proxy pretends that a request is taking another second to finish when it's actually held in the proxy while the two containers switch over.
From https://kamal-deploy.org/docs/upgrading/proxy-changes/ it looks like Kamal 2's new proxy doesn't have this yet, they list "Pausing requests" as "coming soon".
The easiest approach is to kill sqlite, then start the new one. I’d use a unix lockfile as a last-resort mechanism (assuming the container environment doesn’t somehow break those).
I don't, fwiw (so long as all containers are bind mounting the same underlying fs).
Could the two containers in the OP have been running on separate filesystems, perhaps?
Although my tests were slamming the db with reads and write I didn't induce a bad read or write using WAL.
But I wouldn't use experimental results to override what the sqlite people are saying. I (and you) probably just didn't happen to hit the right access pattern.
The containers would need to use a path on a shared FS to setup the SHM handle, and, even then, this sounds like the sort of thing you could probably break via arcane misconfiguration.
I agree shm should work in principle though.
That would eliminate the need for shared memory.
See more: https://sqlite.org/wal.html#concurrency
Why not?
Doctor: simply do not do that
Patient: but doctor,
"The constraint is real: one server, and careful deploy pacing."
Another strong LLM smell, "The <X> is real", nicely bookends an obviously generated blog-post.
https://sqlite.org/cli.html#special_commands_to_sqlite3_dot_...
Oh.
Guess I know what I'm fixing before lunch. Thank you :)
Does your OS have a single-user mode?
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.
The reason you don't usually choose SQLite for the web is future-proofing. If you're totally sure you'll always stay single-server forever, then sure, go for it. But if there's even a tiny chance 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.
If the only argument for a piece of tech in comparison to another one is "future-proofing", that's pretty much acknowledging the other one is simpler to setup and maintain.
For web servers specifically, no, SQLite is not generally part of that spectrum. That makes as much sense as saying that in a kitchen, you want a spectrum of knives from Swiss Army Knives to chef's knives. No -- Swiss Army Knives are not part of the spectrum. For web servers, you do have a wide spectrum of database options from single servers to clusters to multi-region clusters, along with many other choices. But SQLite is not generally part of that spectrum, because it's not client-server.
> since you'll still need to migrate your local Postgres to a central Postres
No you don't. You leave your DB in-place and turn off the web server part. Or even if you do want to migrate to something beefier when needed, it's basically as easy as copying over a directory. It's nothing compared to migrating from SQLite to Postgres.
> since it's still using the SQL standard.
No, every variant of SQL is different. You'll generally need to review every single query to check what needs rewriting. Features in one database work differently from in another. Most of the basic concepts are the same, and the basic syntax is the same, but the intermediate and advanced concepts can have both different features and different syntax. Not to mention sometimes wildly different performance that needs to be re-analyzed.
> that's pretty much acknowledging the other one is simpler to setup and maintain.
No it's not. What logic led you there...? They're basically equally simple to set up and maintain, but one also scales while the other doesn't. That's the point.
The main advantage of SQLite has nothing to do with setup and maintenance, but rather the fact that it is file-based and can be integrated into the binary of other applications, which makes it amazing for locally embedded databases used by user-installed applications. But these aren't advantages when you're running a server. And it becomes a problem when you need to scale to multiple webservers.
SQLite is not a terrible choice here.
Not sure how? All of them can be backed up with a single command. But if you want live backups (replication) as opposed to daily or hourly, SQLite is the only one that doesn't support that.
Locally running database servers are massively underrated as a working technology for smaller sites. You can even easily replicate it to another server for resiliency while keeping the local performance.
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!
I do not understand the level of carelessness and lack of thinking displayed in the OP.
Our AI future is a lot less grand than I expected.
More simply:
sqlite> select typeof('{a:1}'->>'a') ;
╭──────────────────────╮
│ typeof('{a:1}'->>... │
╞══════════════════════╡
│ integer │
╰──────────────────────╯
sqlite> select typeof('{a:1}'->'a') ;
╭──────────────────────╮
│ typeof('{a:1}'->'a') │
╞══════════════════════╡
│ text │
╰──────────────────────╯please consider writing it yourself. quirks in human writing is infinitely more interesting than a next-token-predicted 500 word piece
This is becoming the new overused LLM goto expression for describing basic concepts.
It's not just a repost. The thread includes a comment I made at the time which now from "1 hour ago".
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.
Anyone have some docs on how to cutover gracefully with sqlite on other providers?
Or you use some distributed SQLite tool like rqlite, etc
how hard and complex is it to roll out postgres?
I use gobackup[0] as another container in compose.yml file which can backup to multiple locations.