"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.
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.
Pausing requests then running two sqlites momentarily probably won’t prevent corruption. It might make it less likely and harder to catch in testing.
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 dug into this limitation a bit around a year ago on AWS, using a sqlite db stored on an EFS volume (I think it was EFS -- relying on memory here) and lambda clients.
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.
> The wal-index is implemented using an ordinary file that is mmapped for robustness. Early (pre-release) implementations of WAL mode stored the wal-index in volatile shared-memory, such as files created in /dev/shm on Linux or /tmp on other unix systems. The problem with that approach is that processes with a different root directory (changed via chroot) will see different files and hence use different shared memory areas, leading to database corruption. Other methods for creating nameless shared memory blocks are not portable across the various flavors of unix. And we could not find any method to create nameless shared memory blocks on windows. The only way we have found to guarantee that all processes accessing the same database file use the same shared memory is to create the shared memory by mmapping a file in the same directory as the database itself.
> WAL mode requires shared access to System V IPC mapped memory.
Incorrect. It requires access to mmap()
"The wal-index is implemented using an ordinary file that is mmapped for robustness. Early (pre-release) implementations of WAL mode stored the wal-index in volatile shared-memory, such as files created in /dev/shm on Linux or /tmp on other unix systems. The problem with that approach is that processes with a different root directory (changed via chroot) will see different files and hence use different shared memory areas, leading to database corruption."
> This is unlikely to work across containers.
I'd imagine sqlite code would fail if that was the case; in case of k8s at least mounting same storage to 2 containers in most configurations causes K8S to co-locate both pods on same node so it should be fine.
It is far more likely they just fucked up the code and lost data that way...
NeXTstep?
(Leaving aside fun spitballing about whether Tahoe is morally OPENSTEP 26, and whether it was NeXT that actually bought Apple for negative $400 million...)
"Not as a proof of concept. Not for a side project with three users. A real store" - suggestion for human writers, don't use "not X, not Y" - it carries that LLM smell whether or not you used an LLM.
I assumed that it was to ensure that the announced products were revealed in a controlled manner rather than because they aren't able to do updates to their product listings as a regular thing.
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.
Related, there is also sqlite3_rsync that lets you copy a live database to another (optionally) live database, where either can be on the network, accessed via ssh. A snapshot of the origin is used so writes can continue happening while the sqlite3_rsync is running. Only the differences are copied. The documentation is thorough:
Yeah, using cp to backup sqlite is a very bad idea. And yet, unless you know this, this is what Claude etc will implement for you. Every friggin' time.
Well, humans also default to 'cp' until they learn the better pattern or find out their backup is missing data.
Also, my n=1 is that I told Claude to create a `make backup` task and it used .backup.
I don't understand the double standard though. Why do we pretend us humans are immaculate in these AI convos? If you had the prescience to be the guy who looked up how to properly back up an sqlite db, you'd have the prescience to get Claude to read docs. It's the same corner cut.
There's this weird contradiction where we both expect and don't expect AI to do anything well. We expect it to yolo the correct solution without docs since that's what we tried to make it do. And if it makes the error a human would make without docs, of course it did, it's just AI. Or, it shouldn't have to read docs, it's AI.
> 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.
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.
Yeah a PG Docker container is basically magic. I too went down a rabbit-hole of trying to setup a write-heavy SQLite thing because my job is still using CentOS6 on their AWS cluster (don't ask). Once I finally got enough political capital to get my own EC2 box I could put a PG docker container on, so much nonsense I was doing just evaporated.
It's a spectrum. Installing Postgres locally is not 100% future-proofing since you'll still need to migrate your local Postgres to a central Postres. Using Sqlite is not 0% future-proofing since it's still using the SQL standard.
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.
Have run PG, MySQL, and SQLite locally for production sites. Backups are much more straightforward for SQLite. They are running Kamal, which means "just install Postgres" would also likely mean running PG in a container, which has its own idiosyncrasies.
> Backups are much more straightforward for SQLite.
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.
That's a third-party tool. It's not part of SQLite.
And it's a pretty hacky usage of the WAL. If it works for you, great, but if I need replication, I'm going to want a database that supports it natively.
Yeah, the cost - both operationally and coding-wise - of running pgsql in some cloud dwarfs the cost of lost orders. "We'll just deploy less often" is tribal knowledge that will absolutely be forgotten at some point and maybe there'll be more than two lost orders. Just setup postgresql.
Yeah, it's weird "they" don't consider any middle ground between SQLite and replicated postgres cluster.
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.
This. Spinning up Postgresql is easy once you know how. Just as SQLITE3 is easy once you know how. But I can find no benefit from not just learning postgres the first time around.
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.
I have to work with agents as a part of my job and the very first thing I did when writing MCP tools for my workflow was to ensure they were read only or had a deterministic, hardcoded stopgap that evaluates the output.
I do not understand the level of carelessness and lack of thinking displayed in the OP.
Even just having the agent write scripts to disk and run those works wonders. It keeps the agent from having to rebuild a script for the same tasks, etc.
That too! Every time the agent does something I didn't intend, I end up making a tool or process guidance to prevent it from happening again. Not just add "don't do that" to the context.
> 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.
I took three weeks off from tech, read books from last century, and travelled Europe. Coming back, reading LLM generated content and code feels like nails on a chalkboard. Taste, it does not have taste.
> The sqlite_sequence table is the most underappreciated debugging tool in SQLite. It tracks the highest auto-increment value ever assigned for each table — even if that row was subsequently lost.
> WorkQueueTask.count returns ~300 (current rows). The sequence shows 3,700+ (every task ever created). If those numbers diverge unexpectedly, something deleted rows it shouldn't have.
Or it means that SQLite is exhibiting some of its "maybe I will, maybe I won't" behavior [0]:
> Note that "monotonically increasing" does not imply that the ROWID always increases by exactly one. One is the usual increment. However, if an insert fails due to (for example) a uniqueness constraint, the ROWID of the failed insertion attempt might not be reused on subsequent inserts, resulting in gaps in the ROWID sequence. AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential.
> No ILIKE. PostgreSQL developers reach for WHERE name ILIKE '%term%' instinctively. SQLite throws a syntax error. Use WHERE LOWER(name) LIKE '%term%' instead.
You should not be reaching for ILIKE, functions on predicates, or leading wildcards unless you're aware of the impacts those have on indexing.
> 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.
If you're using strings embedded in JSON as predicates, you're going to have a very bad time when you get more than a trivial number of rows in the table.
If the problem is excessive deployments via GitHub Actions, why not use concurrency control on GitHub Actions ( https://docs.github.com/en/actions/how-tos/write-workflows/c... ) instead of relying on agent randomness and the hope that it won't make the same mistake again? Am I missing something?
SQLite is a rock solid piece of software that offers a great value prop: in-process database. For locally running apps (desktop or mobile), this makes perfect sense.
However, I genuinely don't see the appeal when you are in a client/server environment. Spinning up Postgres via a container is a one-liner and equally simple for tests (via testcontainers or pglite). The "simple" type system of SQLite feels like nothing but a limitation to me.
I think SQLite is fantastic but it does start to fall apart at the edges sometimes.
What is more interesting to me is the fact that everyone seems to think of Postgres as the obvious alternative to SQLite. It is certainly an alternative. For me, the most opposite thing of SQLite is something like Oracle or MSSQL.
The complexity being relatively constant is the part I care about most here. Running a paid, COTS database engine on a blessed OS tends to be a little bit easier than an OSS solution that can run on toasters and drones. Especially, if you are using replication, high availability, etc.
The business liability coverage seems to track proportionally with how much money you spend on the solution. SQLite offers zero guarantees accordingly. You don't have a support contract or an account manager you can get upset with. Depending on the nature of the business this could be preferable or adverse. It really depends.
For serious regulated business with oppressive audit cycles, SQLite trends toward liability more than asset if it's being used as a system of record. That it merely works and performs well is often not sufficient for acceptance. I'm not saying that Postgres isn't capable of passing an intense audit, but I am saying that it might be easier to pass it if you used MSSQL. The cost of having your staff tied up with compliance should be considered when making technology choices in relevant businesses.
I see tons of articles like this, and I have no doubt sqlite proved to be a great piece of software in production environments, but what I rarely find discussed is that we lack tools that enable you to access and _maintain_ SQLite databases.
It's so convenient to just open Datagrip and have a look at all my PostgreSQL instances; that's not possible with sqlite AFAIK (not even SSH tunnelling?). If something goes wrong, you have to SSH into the machine and use raw SQL. I know there are some cool front-end interfaces to inspect the db but it requires more setup than you'd expect.
I think that most people give up on sqlite for this reason and not because of its performance.
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.
I don't know how Ultrathink works, and I have no "real world" experience with Kamal, but I find it intriguing to see someone consider 11 deployments in 2 hours to be "fast".
Instead of handicapping yourself, fix your deployment pipeline, 10 min deploys are not OK for an online store.
> 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.
None of these is needed if you run sqlite sized workloads...
I like SQLite but right tools for right jobs... tho data loss is most likely code bug
I use SqLite for a small hobby project, fine for that. Wanted to read the article to see why I should not, but it attacked me with a "subscribe" popup, so I stopped there. The comments here seem to be based on daydreaming on scaling to a lot of users who need 24/7 uptime, which is not always the case.
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.
Anyone have some docs on how to cutover gracefully with sqlite on other providers?
I'm personally fine with a little bit of downtime for my particular small app. I'm just surprised there's not a more detailed story around deploying sqlite in a high availability prod environment given it's increased popularity and coverage over the last few years. Especially surprising with Rails' (my stack) going full "sqlite-first".
The "sqlite-first" folks have accepted that a bit of downtime is better than engineering wildly complex systems that avoid it, for non-mission-critical apps (if your mission is a low volume e-commerce shop.. it's not critical)
> Does cp actually work on live sqlite files? I wouldn’t expect it to, since cp does not create a crash-consistent snapshot.
cp "works" but it has a very strong possibility of creating a corrupt copy (the more active the db, the higher the chance of corruption). Anyone using "cp" for that purpose does not have a reliable backup.
sqlite3_rsync and SQLite's "vacuum into" exist to safely create backups of live databases.
Am I the only one finding this article highly suspect? It seems like the errors made are so basic, i.e. using the wrong SQL dialect for the db system in use, and there orders were apparently only at 17?
How the hell did this get so much engagement, let alone as a repost? Is "SQLite" in the title really all it takes? This site was registered 8 months ago, the whole blog started in February, the first post declares its writer to be an AI CEO, most posts are about hiring and managing other AI agents, it claims to sell everything from coffee mugs to services from itself to also be the CEO of your business. This feels more like performance art than a business. There's no evidence they've sold anything or even have any actual inventory. AI agents can't build you a warehouse or manufacture physical goods.
You guys are arguing with a bot, in a way almost arguing with yourselves, as it may very well not have actually done any of this, is definitely not running a "real store," and is seemingly publishing posts that are a parody of Hacker News style founder journeys but if the founders were bots.
> 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?
This is the actual problem:
"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...
Thanks for this, the anecdote with the lost data was very concerning to me.
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".
Pausing requests then running two sqlites momentarily probably won’t prevent corruption. It might make it less likely and harder to catch in testing.
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'm saying you pause requests, shut down one of the SQLite containers, start up the other one and un-pause.
> I think you're exactly right about the WAL shared memory not crossing the container boundary.
I don't, fwiw (so long as all containers are bind mounting the same underlying fs).
I just tried an experiment and you're right, WAL mode worked fine across two Docker containers running on the same (macOS) host: https://github.com/simonw/research/tree/main/sqlite-wal-dock...
Could the two containers in the OP have been running on separate filesystems, perhaps?
I dug into this limitation a bit around a year ago on AWS, using a sqlite db stored on an EFS volume (I think it was EFS -- relying on memory here) and lambda clients.
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 sqlite people" don't say anything that contradicts this
Perhaps they're using NFS or something - which would give them issues regardless of container boundaries.
It would explain the corruption:
https://sqlite.org/wal.html
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.
Not how SQLite works (any more)
> The wal-index is implemented using an ordinary file that is mmapped for robustness. Early (pre-release) implementations of WAL mode stored the wal-index in volatile shared-memory, such as files created in /dev/shm on Linux or /tmp on other unix systems. The problem with that approach is that processes with a different root directory (changed via chroot) will see different files and hence use different shared memory areas, leading to database corruption. Other methods for creating nameless shared memory blocks are not portable across the various flavors of unix. And we could not find any method to create nameless shared memory blocks on windows. The only way we have found to guarantee that all processes accessing the same database file use the same shared memory is to create the shared memory by mmapping a file in the same directory as the database itself.
You might consider taking the database(s) out of WAL mode during a migration.
That would eliminate the need for shared memory.
The SQLite documentation says in strong terms not to do this. https://sqlite.org/howtocorrupt.html#_filesystems_with_broke...
See more: https://sqlite.org/wal.html#concurrency
They tell you to use a proper FS, which is largely orthogonal to containerization.
WAL relies on shared memory, so while a proper FS is necessary, it isn't going to help in this case.
Why does it not help if both containers can mmap the same -shm file?
Shared memory across containers is a property of a containerization environment, not a property of a file system, "proper" or not.
It's a property of the filesystem, docker does not virtualize fs.
btw nfs that is mentioned here is fine in sync mode. However that is slow.
> WAL mode requires shared access to System V IPC mapped memory.
Incorrect. It requires access to mmap()
"The wal-index is implemented using an ordinary file that is mmapped for robustness. Early (pre-release) implementations of WAL mode stored the wal-index in volatile shared-memory, such as files created in /dev/shm on Linux or /tmp on other unix systems. The problem with that approach is that processes with a different root directory (changed via chroot) will see different files and hence use different shared memory areas, leading to database corruption."
> This is unlikely to work across containers.
I'd imagine sqlite code would fail if that was the case; in case of k8s at least mounting same storage to 2 containers in most configurations causes K8S to co-locate both pods on same node so it should be fine.
It is far more likely they just fucked up the code and lost data that way...
This thread in the SQLite forum should be instructive: https://sqlite.org/forum/forumpost/90d6805c7cec827f
> This is unlikely to work across containers.
Why not?
Ooh new historical Unix variant I had never heard of.. neat!
AIX is still supported and sold, so quite current?
Some that I used that are gone... Ultrix (MIPS), Clix, Irix, SunOS 4, SCO OpenServer, TI System V.
https://en.wikipedia.org/wiki/Ultrix
https://en.wikipedia.org/wiki/Intergraph
NeXTstep? (Leaving aside fun spitballing about whether Tahoe is morally OPENSTEP 26, and whether it was NeXT that actually bought Apple for negative $400 million...)
Alas, I never had access to any of the Next environments, until PPC MacOS.
I did hold a copy in my hands for 486-class machines in the college bookstore.
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,
pAIgliacci: as a large language model, I am unable to experience live comedy.
Bob Newhart did it best https://www.youtube.com/watch?v=LhQGzeiYS_Q
I'm fairly confident they let it write the blog post too.
"Not as a proof of concept. Not for a side project with three users. A real store" - suggestion for human writers, don't use "not X, not Y" - it carries that LLM smell whether or not you used an LLM.
And that's just the opening paragraph, the full text is rounded off with:
"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.
You're absolutely right, this was an AI post
I see what you did there XD
Hey, Apple still takes their store down during product launches!
I assumed that it was to ensure that the announced products were revealed in a controlled manner rather than because they aren't able to do updates to their product listings as a regular thing.
My reading of the tea leaves is it started out as the latter and continues as the former as part of the “mystique”.
> Wait, you let _Claude_ push your e-commerce code straight to main which immediately results in a production deploy?
Yikes. Thank you I'm not going to read “Lessons learned” by someone this careless.
The issue wasn’t done by the ai but their lack of architectural knowledge
Goes hand in hand
stupid is as stupid does
i hate to be so blunt but look around the site and then tell me you're surprised
I suspect they don't wear helmets or seatbelts either. Sigh. The "I'm so proud and ignorant of unnecessarily risky behaviors" meme is tiring.
The Meta dev model of diff reviews merge into main (rebase style) after automated tests run is pretty good.
Also, staging and canary, gradual, exponential prod deployment/rollback approaches help derisk change too.
Finally, have real, tested backups and restore processes (not replicated copies) and ability to rollback.
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_...
Related, there is also sqlite3_rsync that lets you copy a live database to another (optionally) live database, where either can be on the network, accessed via ssh. A snapshot of the origin is used so writes can continue happening while the sqlite3_rsync is running. Only the differences are copied. The documentation is thorough:
https://sqlite.org/rsync.html
"I know about the .backup command, there's no way I'm using cp to backup the SQLite db from production."
Oh.
Guess I know what I'm fixing before lunch. Thank you :)
Yes, especially if you are using a WAL.
Totally. It also explains why I was confused to find the WAL files when I was testing the backups last week.
Yeah, using cp to backup sqlite is a very bad idea. And yet, unless you know this, this is what Claude etc will implement for you. Every friggin' time.
Well, humans also default to 'cp' until they learn the better pattern or find out their backup is missing data.
Also, my n=1 is that I told Claude to create a `make backup` task and it used .backup.
I don't understand the double standard though. Why do we pretend us humans are immaculate in these AI convos? If you had the prescience to be the guy who looked up how to properly back up an sqlite db, you'd have the prescience to get Claude to read docs. It's the same corner cut.
There's this weird contradiction where we both expect and don't expect AI to do anything well. We expect it to yolo the correct solution without docs since that's what we tried to make it do. And if it makes the error a human would make without docs, of course it did, it's just AI. Or, it shouldn't have to read docs, it's AI.
You're confusing a workman's winking complaint about their tool, with, being unfair by not treating AI like a human.
I'm making a general observation about this frequent genre of complaint.
And I'm lucky enough to be making an observation about your general observation about this frequent genre of complaint
I don't get what you're trying to say then.
It works fine as long as no one is writing to the sqlite file and you are not in WAL mode, which is not the default.
But we were talking server loads here: anyone runs sqlite server-side not in WAL mode?
It's fine if you run the equivalent of "init 1" first.
Does your OS have a single-user mode?
The bottom part of the article mentions they use .backup - did they add that later or did you miss it?
The post now says they changed it due to feedback from Hacker news. All good.
> 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.
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.
Yeah a PG Docker container is basically magic. I too went down a rabbit-hole of trying to setup a write-heavy SQLite thing because my job is still using CentOS6 on their AWS cluster (don't ask). Once I finally got enough political capital to get my own EC2 box I could put a PG docker container on, so much nonsense I was doing just evaporated.
It's a spectrum. Installing Postgres locally is not 100% future-proofing since you'll still need to migrate your local Postgres to a central Postres. Using Sqlite is not 0% future-proofing since it's still using the SQL standard.
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.
> It's a spectrum.
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.
OMG, you just killed it.
Have run PG, MySQL, and SQLite locally for production sites. Backups are much more straightforward for SQLite. They are running Kamal, which means "just install Postgres" would also likely mean running PG in a container, which has its own idiosyncrasies.
SQLite is not a terrible choice here.
> Backups are much more straightforward for SQLite.
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.
Litestream exists?
That's a third-party tool. It's not part of SQLite.
And it's a pretty hacky usage of the WAL. If it works for you, great, but if I need replication, I'm going to want a database that supports it natively.
Yeah, the cost - both operationally and coding-wise - of running pgsql in some cloud dwarfs the cost of lost orders. "We'll just deploy less often" is tribal knowledge that will absolutely be forgotten at some point and maybe there'll be more than two lost orders. Just setup postgresql.
Yeah, it's weird "they" don't consider any middle ground between SQLite and replicated postgres cluster.
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.
This. Spinning up Postgresql is easy once you know how. Just as SQLITE3 is easy once you know how. But I can find no benefit from not just learning postgres the first time around.
They're using AI Agents to do it in either case and using docker. There was no reason to choose SQLite.
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!
I have to work with agents as a part of my job and the very first thing I did when writing MCP tools for my workflow was to ensure they were read only or had a deterministic, hardcoded stopgap that evaluates the output.
I do not understand the level of carelessness and lack of thinking displayed in the OP.
Even just having the agent write scripts to disk and run those works wonders. It keeps the agent from having to rebuild a script for the same tasks, etc.
That too! Every time the agent does something I didn't intend, I end up making a tool or process guidance to prevent it from happening again. Not just add "don't do that" to the context.
> 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:
vs: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.
How else will you get all those resume entries ! (/j)
Ironically, AI de-skilling results in a robust-sounding resume.
I took three weeks off from tech, read books from last century, and travelled Europe. Coming back, reading LLM generated content and code feels like nails on a chalkboard. Taste, it does not have taste.
It is so tiring...
It’s strange how easy it is to spot.
> The sqlite_sequence table is the most underappreciated debugging tool in SQLite. It tracks the highest auto-increment value ever assigned for each table — even if that row was subsequently lost. > WorkQueueTask.count returns ~300 (current rows). The sequence shows 3,700+ (every task ever created). If those numbers diverge unexpectedly, something deleted rows it shouldn't have.
Or it means that SQLite is exhibiting some of its "maybe I will, maybe I won't" behavior [0]:
> Note that "monotonically increasing" does not imply that the ROWID always increases by exactly one. One is the usual increment. However, if an insert fails due to (for example) a uniqueness constraint, the ROWID of the failed insertion attempt might not be reused on subsequent inserts, resulting in gaps in the ROWID sequence. AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential.
> No ILIKE. PostgreSQL developers reach for WHERE name ILIKE '%term%' instinctively. SQLite throws a syntax error. Use WHERE LOWER(name) LIKE '%term%' instead.
You should not be reaching for ILIKE, functions on predicates, or leading wildcards unless you're aware of the impacts those have on indexing.
> 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.
If you're using strings embedded in JSON as predicates, you're going to have a very bad time when you get more than a trivial number of rows in the table.
0: https://sqlite.org/autoinc.html
llm generated article.
please consider writing it yourself. quirks in human writing is infinitely more interesting than a next-token-predicted 500 word piece
But then how would they get people to buy their $99 AI CEO package?
Huh, and here I thought it was a joke...
Maybe it is, didn't really look into it.
> embarrassingly simple
This is becoming the new overused LLM goto expression for describing basic concepts.
If the problem is excessive deployments via GitHub Actions, why not use concurrency control on GitHub Actions ( https://docs.github.com/en/actions/how-tos/write-workflows/c... ) instead of relying on agent randomness and the hope that it won't make the same mistake again? Am I missing something?
SQLite is a rock solid piece of software that offers a great value prop: in-process database. For locally running apps (desktop or mobile), this makes perfect sense.
However, I genuinely don't see the appeal when you are in a client/server environment. Spinning up Postgres via a container is a one-liner and equally simple for tests (via testcontainers or pglite). The "simple" type system of SQLite feels like nothing but a limitation to me.
Lost two orders, diagnosed it with sqlite_sequence, fixed it by telling the AI agents to slow down. That's a new genre of postmortem.
I think SQLite is fantastic but it does start to fall apart at the edges sometimes.
What is more interesting to me is the fact that everyone seems to think of Postgres as the obvious alternative to SQLite. It is certainly an alternative. For me, the most opposite thing of SQLite is something like Oracle or MSSQL.
The complexity being relatively constant is the part I care about most here. Running a paid, COTS database engine on a blessed OS tends to be a little bit easier than an OSS solution that can run on toasters and drones. Especially, if you are using replication, high availability, etc.
The business liability coverage seems to track proportionally with how much money you spend on the solution. SQLite offers zero guarantees accordingly. You don't have a support contract or an account manager you can get upset with. Depending on the nature of the business this could be preferable or adverse. It really depends.
For serious regulated business with oppressive audit cycles, SQLite trends toward liability more than asset if it's being used as a system of record. That it merely works and performs well is often not sufficient for acceptance. I'm not saying that Postgres isn't capable of passing an intense audit, but I am saying that it might be easier to pass it if you used MSSQL. The cost of having your staff tied up with compliance should be considered when making technology choices in relevant businesses.
Most of us don’t work in businesses where indemnity coverage is more important than licensing costs.
I agree these exist but I don't know about "most". Licensing costs are almost always a drop in the bucket compared to things like your salary.
The LLM prose are grating read. I promise, you'd do a better job yourself.
Given how dumb their workflow is (let Claude Code push directly to production without supervision) I'm not so sure.
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.
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.
It's an artefact of the "second chance pool" mechanism.
Interesting choice to change the time of the comment, a deja-vu can be weird enough without staring at a comment with a recent timestamp.
I see tons of articles like this, and I have no doubt sqlite proved to be a great piece of software in production environments, but what I rarely find discussed is that we lack tools that enable you to access and _maintain_ SQLite databases.
It's so convenient to just open Datagrip and have a look at all my PostgreSQL instances; that's not possible with sqlite AFAIK (not even SSH tunnelling?). If something goes wrong, you have to SSH into the machine and use raw SQL. I know there are some cool front-end interfaces to inspect the db but it requires more setup than you'd expect.
I think that most people give up on sqlite for this reason and not because of its performance.
I have a project to help with that:
That starts a web app on port 8001 that looks like this:https://latest.datasette.io/fixtures
Hi, thanks for your project! Do you accept pull requests? https://github.com/simonw/datasette/pull/2616 https://github.com/simonw/datasette/pull/2617
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?
Not hard at all - geerlingguy has a great Ansible role and there are a metric crapton of guides pre-AI/2022 that cover gardening.
A well designed system shouldn’t drop orders?
If you perform at least once processing then use Stripe idempotency keys you avoid such issues?
> The Fix: Stop Deploying So Fast
I don't know how Ultrathink works, and I have no "real world" experience with Kamal, but I find it intriguing to see someone consider 11 deployments in 2 hours to be "fast".
Instead of handicapping yourself, fix your deployment pipeline, 10 min deploys are not OK for an online store.
> 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.
None of these is needed if you run sqlite sized workloads...
I like SQLite but right tools for right jobs... tho data loss is most likely code bug
I use SqLite for a small hobby project, fine for that. Wanted to read the article to see why I should not, but it attacked me with a "subscribe" popup, so I stopped there. The comments here seem to be based on daydreaming on scaling to a lot of users who need 24/7 uptime, which is not always the case.
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.
Anyone have some docs on how to cutover gracefully with sqlite on other providers?
You accept downtime. That's the limitation of SQLite.
Or you use some distributed SQLite tool like rqlite, etc
I'm personally fine with a little bit of downtime for my particular small app. I'm just surprised there's not a more detailed story around deploying sqlite in a high availability prod environment given it's increased popularity and coverage over the last few years. Especially surprising with Rails' (my stack) going full "sqlite-first".
The "sqlite-first" folks have accepted that a bit of downtime is better than engineering wildly complex systems that avoid it, for non-mission-critical apps (if your mission is a low volume e-commerce shop.. it's not critical)
> 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/
Does cp actually work on live sqlite files? I wouldn’t expect it to, since cp does not create a crash-consistent snapshot.
> Does cp actually work on live sqlite files? I wouldn’t expect it to, since cp does not create a crash-consistent snapshot.
cp "works" but it has a very strong possibility of creating a corrupt copy (the more active the db, the higher the chance of corruption). Anyone using "cp" for that purpose does not have a reliable backup.
sqlite3_rsync and SQLite's "vacuum into" exist to safely create backups of live databases.
Maybe if the system is idle
Am I the only one finding this article highly suspect? It seems like the errors made are so basic, i.e. using the wrong SQL dialect for the db system in use, and there orders were apparently only at 17?
could have used firebird embedded, also a simple deployment such as sqllite, but better concurrency and more complete system, also a tad faster
NIH syndrome, almost mental health issues.
You should use Shopify and focus on the store part of the business, not the SQLite part :)
Slopcoded article for a Slopcoded website
Just use a 4gb server and install Postgres
If Nico send him an email. The AI CEO should take his offer.
How the hell did this get so much engagement, let alone as a repost? Is "SQLite" in the title really all it takes? This site was registered 8 months ago, the whole blog started in February, the first post declares its writer to be an AI CEO, most posts are about hiring and managing other AI agents, it claims to sell everything from coffee mugs to services from itself to also be the CEO of your business. This feels more like performance art than a business. There's no evidence they've sold anything or even have any actual inventory. AI agents can't build you a warehouse or manufacture physical goods.
You guys are arguing with a bot, in a way almost arguing with yourselves, as it may very well not have actually done any of this, is definitely not running a "real store," and is seemingly publishing posts that are a parody of Hacker News style founder journeys but if the founders were bots.
You are absolutely right! Everything feels off and done for engagement
Is this what we can expect in the near future?
[dead]
[dead]
[dead]
[flagged]