Post A4wpLXs8uLNO1cBczw by tek@freeradical.zone
(DIR) More posts by tek@freeradical.zone
(DIR) Post #A4wkiN7QYVBPYzOvMu by tek@freeradical.zone
2021-03-06T22:08:45Z
2 likes, 1 repeats
SQLite is such a joy to work with. If I'm ever writing a program that needs to persist data that's not a perfect fit for JSON, it goes in SQLite. Needs concurrency and may have multiple programs writing simultaneously? SQLite. Chance it may need ad-hoc queries and I don't want to write an entire reporting API. Yep, SQLite. It's just wonderful.
(DIR) Post #A4wkwFi0tzRgdV8HnE by seachaint@hackers.town
2021-03-06T22:11:07Z
0 likes, 0 repeats
@tek I thought "Multiple distinct processes writing to same DB" was exactly the use case SQLite was _not_ good for?Asking in earnest as an SQLite noob
(DIR) Post #A4wm2c3Upam7dNmHey by ColinTheMathmo@mathstodon.xyz
2021-03-06T22:23:28Z
0 likes, 0 repeats
@tek I've just today been thinking that I need to transition a project to using a "proper" database, so this is timely.What resource would you recommend for getting started? There's a gazillion tutorials out there, and I'm pretty sure most of them are ... (significant pause) ... quite poor.
(DIR) Post #A4wmO7LfgAMIllX7YW by tek@freeradical.zone
2021-03-06T22:27:29Z
0 likes, 0 repeats
@seachaint It depends on how many processes we're talking about. I wouldn't want it with several huge processes inserting a million rows at once. But imagine you have two processes intermittently inserting a row or two. Doing it yourself with non-SQLite might look like "try to acquire a lock, then open and the backing store JSON file, update it, write it back out, and release the lock". Contrast with using SQLite: both processes write "insert into foo..." and the DB handles it. Nirvana.
(DIR) Post #A4wmSM9iZI4DqqxmwC by tek@freeradical.zone
2021-03-06T22:28:15Z
0 likes, 0 repeats
@ColinTheMathmo What language are you working with?
(DIR) Post #A4wmgjMd4qoNsurBGS by ColinTheMathmo@mathstodon.xyz
2021-03-06T22:28:27Z
0 likes, 0 repeats
@tek Python
(DIR) Post #A4wmmBKsVSFE5MdYSe by tek@freeradical.zone
2021-03-06T22:29:13Z
0 likes, 0 repeats
@ColinTheMathmo This is 90% of what you need to get started: https://docs.python.org/3/library/sqlite3.html#sqlite3.Row(I was just looking at that as a reference for something I hadn't done in a while.)
(DIR) Post #A4wmyk4rADITJG8G8m by fikran@islamicate.space
2021-03-06T22:34:08.208546Z
0 likes, 0 repeats
@tek SQLite is pretty awesome!
(DIR) Post #A4wogH90CJLiGrOSUy by seachaint@hackers.town
2021-03-06T22:48:44Z
0 likes, 0 repeats
@tek Is this relying on _negligible odds_ of a clobbering event, or does SQLite somehow handle locking between competing processes for itself? e.g. is it a design assumption of SQLite that you can, sometimes, have two non-communicating processes doing writes? I would use SQLite much more if this were so! I was under the impression it was an explicit no-no
(DIR) Post #A4woqj47F4TFlM5hqa by tek@freeradical.zone
2021-03-06T22:53:38Z
0 likes, 0 repeats
@seachaint It handles locking itself: https://sqlite.org/lockingv3.htmlThere's also a FAQ on it: https://www.sqlite.org/faq.html#q5Short version: yep, it's great for this! Again, the problem is when you have multiple heavy writers at once, and then all but one starves. You'd still have data integrity even then.
(DIR) Post #A4wpC9TuDuAtluN3Q0 by x51@cybre.space
2021-03-06T22:56:56Z
0 likes, 0 repeats
@tek Low-key solid piece of technology.
(DIR) Post #A4wpLXs8uLNO1cBczw by tek@freeradical.zone
2021-03-06T22:58:04Z
0 likes, 0 repeats
@x51 It's an amazing little project.
(DIR) Post #A4wrBrAGWZqOs6AffU by seachaint@hackers.town
2021-03-06T23:21:15Z
0 likes, 0 repeats
@tek That's really great to know, thank you. I had read "unsuitable for multiple writes" as meaning "not locked or semaphored, expect meltdowns", rather than "not super performant but otherwise safe". Definitely excited to level up my SQLite and remove a lot of BS from my next project. :)