Hacker News Reader

Comment by harryvederci | Hacker News Reader

Something I'm doing in my CV application is using 1 SQLite DB file per user.

When a user logs in, a check is done: "Does this user's DB file have the latest migration?" If not, the migrations are applied. That way, you only get a slight delay as a user when a migration is needed. None of the other users their DB files are affected. More technical details are in the FAQ: https://withoutdistractions.com/cv/faq

In terms of the article: I'm only changing 1 tire at a time at 100mph, not all of them.

PS: I recently did a "Show HN" about the app, it got some interesting feedback: https://news.ycombinator.com/item?id=31246696

View on HN

> Something I'm doing in my CV application is using 1 SQLite DB file per user.

That's the "easy" case where most (all?) data is easily separable/shardeable, in this case by user. Once you start having relationships between users (messages, likes, groups...) everything starts getting warty and you need approaches like that of TFA.

Correct! I didn't' mean to say it's a good fit for every use case.

For me, it works. For someone else, it may be a terrible idea. Always good to think things through, and there's no shame in going for something more commonly used. Boring isn't bad, it's often a good choice.

This seems great for application where you (as a dev) has absolutely no need to ever access any of your user's data. However, if you have a process that needs to grab some users data, you'll have no choice but to make sure all users have similar db schema (or centralize within the same database).

Wait , are you running this site on a raspberry pi?

The biggest issue with SQLite is what happens if your server serving stuff goes down mid write? Power surge maybe. You lose data from that day till previous cronjob?

> The biggest issue with SQLite is what happens if your server serving stuff goes down mid write?

Why would that matter? SQLite is still fully ACID compliant like other DBs.

I imagine that GP means a hardware failure severe enough that it permanently borks the disk drive of the machine. In that case vanilla SQLite would indeed lose data written since the time of the last offsite backup. MySQL/Postgres/etc typically have streaming replication which means you can promote one of the replicas and not lose any (or at least much less) data. There are some new-ish projects like Litestream (https://litestream.io/) that provide a similar functionality for SQLite by streaming the WAL file changes to S3.

The "boots up our application within a second on a Raspberry Pi" comment in the FAQ[0] is actually about my development setup, as I don't own a fancy computer myself. It was mainly meant to illustrate the fast startup time of the Janet[1] language. In fact, that startup time is so fast, you can run your app as a CGI script. That has some more possible advantages, see the FAQ for more details.

[0] FAQ: https://withoutdistractions.com/cv/faq#technology

[1] Janet: https://janet-lang.org/

They do gain some data security as they've effectively sharded their database by user. So at the point of a power failure only the active users' data would be at risk of corruption at the file level.

Of course there's always the risk of damage to the media in general, but then you can never fully protect against hardware failures.

Reply on news.ycombinator.com