Use SQLite if you know it's a one-to-one relationship (or SQLite with Litestream if you need to run the app in multiple places, you can go far off a single instance).
Wow, this is nuts (re: primary keys being NULL and not patching it) but like I understand the decision here. Low-key a test to see if people read the documentation of the things they use too. https://sqlite.org/lang_createtable.html#the_primary_key
Yeah so it looks like I ran into rusqlite/rusqlite!433 on GitHub. Very annoying and I need to find a way to either add a test for this or add an update trigger to fix up these values. I understand the former more, so I'll do that.
Interesting notes regarding SQLite performance. https://phiresky.github.io/blog/2020/sqlite-performance-tuning/
I'm always curious about ways of using SQLite in a distributed fashion but not like how we currently do distributed databases. I'm thinking more like "file sharing" distributed. I've also abstracted a lot of the I/O for my site — my long term hope is to be able to "pack" the data for it in a 'portable' (torrenting) way, so I can modify it from any device that's familiar with the filesystem layout and the database schema. This would effectively put the data in the hands of clients that are permitted access to it. This is how something like inkandswitch's Local First Software could be used for personal sites.
I'm glad that SQLite nudges away from concrete types and uses storage types. Because the need to test values is pushed up into the application layer, and I don't think I like doing a lot of strict typing in the database (though I've been bitten with and without it).