Hacker News Reader

Comment by whalesalad | Hacker News Reader

Wouldn't you want to use a different db file for this? I wouldn't want to put this in the same db as the rest of my app, especially considering how simple it is to just open a new file.

View on HN

If you want to be able to send to the queue in the same transaction as other state changes, it has to be the same file. That’s often a useful property.

SQLite allows transactions involving multiple databases (see https://www.sqlite.org/atomiccommit.html section 5)

Oh! TIL! Thank you for that. :)

SQLite is such a great thing. Amazing value for zero cost both shmeckels and maintenance. It's a shame most startups go for postgres blindly due to "scalability concerns" it feels like a major case of premature optimization.

It really is! I've become a SQLite fanboy. :D Although I can definitely see the value in Postgres as well. But it's usually one of those two.

My beef with SQLite is the weaker typing + not even enforcing foreign keys unless you open the DB in a certain mode.

IMO it should become more opinionated and stricter. I'd honestly pay to buy a license for SQLite with the legacy baggage removed and rewritten to be as strict as PostgreSQL or more. Well... basically embedded PostgreSQL I guess.

I also don’t like those parts, but for an otherwise great project, I’d happily use the parts I do like. Strict tables go a long way for me, and you can make foreign key checks a compile-time enforcement.

Agreed, plus check my other sibling comment: https://news.ycombinator.com/item?id=39681241

Same. I’ve been using it in a project, and the entire time I feel like I’m needing to be twice as careful not to introduce problems.

Yep, I am even considering writing a Rust library that does opinionated opening of SQLite databases with FK enforcing turned on, strict tables and other such goodies, and making sure dates / times and such are properly (de)serialized so we can have some convention inside SQLite itself (f.ex. fully ISO formatted datetimes with or without timezones).

Ha, I’ve started the same thing in Go, but didn’t get that far. https://github.com/maragudk/sqlite

Reply on news.ycombinator.com