Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

ultrathink.art

Post reply on HN
Print view View original

91–100 of 135 posts

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

#91

Post by sgbeal »

hedora wrote: post #44
leosanchez wrote: post #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. [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.

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

#92

Post by kristiandupont »

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.

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

#93

Post by adobrawy »

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?

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

#94

Post by warmwaffles »

qingcharles wrote: post #65
infamia wrote: post #14

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

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

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

#96

Post by mattrighetti »

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.

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

#97

Post by voidfunc »

chasil wrote: post #25
yokuze wrote: post #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-commer…

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/S…

Ooh new historical Unix variant I had never heard of.. neat!

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

#99

Post by chasil »

voidfunc wrote: post #97
chasil wrote: post #25

Earlier quoted context omitted.

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/S…

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

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

#100

Post by These335 »

xnorswap wrote: post #22
yokuze wrote: post #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-commer…

I'm fairly confident they let it write the blog post too.

You're absolutely right, this was an AI post
Post reply on HN