7 ms·

The tech stack behind this is pretty novel, janet, htmx, and per user sqlite files, check the FAQ for more details, it’s pretty interesting

by swlkr 4y ago

harryvederci 4y ago
Thanks! I think the tech stack actually deserves an HN item of its own, as I don't think many people know this is possible and may resort to something like rqlite without really needing it. I don't expect everyone to read the full FAQ, so TL/DR: Every user gets a separate DB file, and a cron job syncs their records with a read-only[0] DB file with the same schema. That way, writes to individual DB files won't block reads from the "global" one. It's not a fit for every use case, but so far it seems to be working fine here! [0]: read-only for incoming requests, not for me.
- jhgb 4y ago
  > Every user gets a separate DB file, and a cron job syncs their records Call it SQLitus Notes or something like that?
  - harryvederci 4y ago
    Haha, I might rename it to that. Currently it's something along the lines of "import-user-db-into-global-db".
- anyfactor 4y ago
  Holy heck. What a coincidence. For the last few days, I was thinking about databases and was wondering if per user database can be possible. To be honest I was thinking about duckdb and sqlite3, with a mothership kinda postgreSQL database. I was thinking having a "your very own database" could be a justifiable reason for a price bump from pro to enterprise version. Then I was thinking of the idea of SQLite being something like a web-deliverable database instead of JSON responses through an REST API. Edit: I would really love to hear about this database per user approach. The more I think about this the more I am fascinated. Like for what amount of data-size justifies to have an SQLite3 db or something more bulkier? A CV has very small amount of data, so why not just use a JSON file? I wonder in that case if accessing a JSON file from a cloud storage could be more performant compared to SQLite3. You don't need to have the full set of utilities of SQL if you are just showing the entire data.
  - harryvederci 4y ago
    Haha awesome! Good to see lots of people are starting to see SQLite as an option, I think it used to be misunderstood as a toy database. It'll take some time before I can really recommend this workflow, I'll make sure to add a blog to the withoutdistractions.com platform soon. I didn't expect this to hit the front page as it's my first "Show HN", otherwise I would have created one up front with an RSS feed. For me I went from Postgres to one central SQLite file to the current approach. I don't what the best approach would be, but I just put a user id column in every table and instead of having an integer primary key, I have a primary key of user id + table id. Then I have some Janet logic in place to get all table + column names except for a few private ones from the user's DB file, and put those in the "global" DB file. If anyone knows a better way to do this I'm all ears! I think SQLite has some kind of native way now to merge DB files with the same schema as well, but I think there was some limitation on the amount of tables you could apply this to. Not 100% sure, though.
- mappu 4y ago
  If you turn on WAL mode in SQLite, then a single writer does not block concurrent readers. There are good reasons to shard SQLite databases if you want multiple concurrent writers, but i think this particular use case is fine without sharding,
  - harryvederci 4y ago
    I haven't looked into WAL mode too much, thanks for mentioning it. 2 justifications for (probably) keeping my current approach are: - I can do per-user migrations, with zero downtime for other users. - I only sync data with the global read-only DB file that is intended to be public. The name, picture, email address, etc of the user is never part of the global db file, so it's less likely to leak in case something would go wrong.
jothac 4y ago
https://twitter.com/kelseyhightower/status/1516293351384834048 https://twitter.com/kelseyhightower/status/15162933513848340... - not far off! Would be super interesting to find how this scales!

The tech stack behind this is pretty novel, janet, htmx, and per user sqlite files, check the FAQ for more details, it’s pretty interesting

by swlkr 4y ago

The tech stack behind this is pretty novel, janet, htmx, and per user sqlite files, check the FAQ for more details, it’s pretty interesting

harryvederci 4y ago

Thanks! I think the tech stack actually deserves an HN item of its own, as I don't think many people know this is possible and may resort to something like rqlite without really needing it. I don't expect everyone to read the full FAQ, so TL/DR: Every user gets a separate DB file, and a cron job syncs their records with a read-only[0] DB file with the same schema. That way, writes to individual DB files won't block reads from the "global" one. It's not a fit for every use case, but so far it seems to be working fine here! [0]: read-only for incoming requests, not for me.

jhgb 4y ago
> Every user gets a separate DB file, and a cron job syncs their records Call it SQLitus Notes or something like that?
- harryvederci 4y ago
  Haha, I might rename it to that. Currently it's something along the lines of "import-user-db-into-global-db".
anyfactor 4y ago
Holy heck. What a coincidence. For the last few days, I was thinking about databases and was wondering if per user database can be possible. To be honest I was thinking about duckdb and sqlite3, with a mothership kinda postgreSQL database. I was thinking having a "your very own database" could be a justifiable reason for a price bump from pro to enterprise version. Then I was thinking of the idea of SQLite being something like a web-deliverable database instead of JSON responses through an REST API. Edit: I would really love to hear about this database per user approach. The more I think about this the more I am fascinated. Like for what amount of data-size justifies to have an SQLite3 db or something more bulkier? A CV has very small amount of data, so why not just use a JSON file? I wonder in that case if accessing a JSON file from a cloud storage could be more performant compared to SQLite3. You don't need to have the full set of utilities of SQL if you are just showing the entire data.
- harryvederci 4y ago
  Haha awesome! Good to see lots of people are starting to see SQLite as an option, I think it used to be misunderstood as a toy database. It'll take some time before I can really recommend this workflow, I'll make sure to add a blog to the withoutdistractions.com platform soon. I didn't expect this to hit the front page as it's my first "Show HN", otherwise I would have created one up front with an RSS feed. For me I went from Postgres to one central SQLite file to the current approach. I don't what the best approach would be, but I just put a user id column in every table and instead of having an integer primary key, I have a primary key of user id + table id. Then I have some Janet logic in place to get all table + column names except for a few private ones from the user's DB file, and put those in the "global" DB file. If anyone knows a better way to do this I'm all ears! I think SQLite has some kind of native way now to merge DB files with the same schema as well, but I think there was some limitation on the amount of tables you could apply this to. Not 100% sure, though.
mappu 4y ago
If you turn on WAL mode in SQLite, then a single writer does not block concurrent readers. There are good reasons to shard SQLite databases if you want multiple concurrent writers, but i think this particular use case is fine without sharding,
- harryvederci 4y ago
  I haven't looked into WAL mode too much, thanks for mentioning it. 2 justifications for (probably) keeping my current approach are: - I can do per-user migrations, with zero downtime for other users. - I only sync data with the global read-only DB file that is intended to be public. The name, picture, email address, etc of the user is never part of the global db file, so it's less likely to leak in case something would go wrong.

jothac 4y ago

https://twitter.com/kelseyhightower/status/1516293351384834048 https://twitter.com/kelseyhightower/status/15162933513848340... - not far off! Would be super interesting to find how this scales!