On: Ask HN: Have you used SQLite as a primary database...

[pieterhg]

Yes for all my sites: Nomad List, Remote OK, Hoodmaps, Rebase etc. No real issues at all.

[WA → pieterhg]

I read in one of your Tweets that you use one database file per (unrelated) table to avoid corruption. Why did you move to this model? Are multiple tables per file really more easy to corrupt?

[pieterhg → WA]

Yes! It kinda happened because I had no idea how SQLite worked so I thought this was normal.

Then I thought this is great and makes it easier to move the db file if it just has 1 table. So I can download it easily for local dev for ex.

And yes in case there's corruption which never rly happens, only one file thus table would be affected.

PS: one thing that really helped reduce issues was setting PRAGMA MODE to WAL

[barryhennessy → WA]

I'd be interested to know what kind of corruption you were facing.

[Glench → pieterhg]

Here's a Twitter thread with some numbers from pieterhg's use of SQLite in production: https://twitter.com/levelsio/status/1308406118314635266

[el_dev_hell → pieterhg]

Awesome to hear!

How do you handle this? Do you store the SQLite file somewhere like s3 or just in memory?

How does this work for such high traffic sites?

[pieterhg → el_dev_hell]

No in the filesystem on a VPS. All my sites just run on a VPS. Nothing fancy!

[levelSEOfan → pieterhg]

glad to see pieter here, I am keeping an eye on rebase ;)