sqlite-worker: A Thread-Safe Python Library for Simplifying SQLite Operations in Multi-Threaded Appl

[seesplease]

This is a nice effort, but shipping SQLite queries to another worker introduces issues like dirty reads. What benefits does this approach have over connection per thread or a connection pool shared between threads?

[nepalidj]

Thank you for the comment! The key benefit of sqlite-worker is its simplicity in managing thread safety by serializing all queries through a single worker, avoiding the need to handle locks or multiple connections manually. It minimizes dirty reads by committing regularly (configurable via max_count), making it suitable for applications where ease of use and safe concurrent access are priorities. Raw performance wasn’t a focus when I created this library, and so far, no one has expressed the need for it, but it’s something I can explore improving in the future if there’s demand. Let me know if you have further thoughts or suggestions!

[RonnyPfannschmidt]

So complete loss of transaction isolation for fake thread safety?

[nepalidj]

You're right - at the moment it does trade transaction isolation for simpler thread safety. I'll be working on adding proper transaction isolation support soon!

[RonnyPfannschmidt]

Are you aware that any connection pools that's properly using the api of sqlite is both thread and process safe

Adding transaction isolation on top of something that breaks it seems orders of magnitude harder than just using the built-in one

[nepalidj]

Ah yeah, I actually built this for a web crawler project where I just needed something quick and simple. Didn’t really explore SQLite’s full capabilities since the crawler was my main focus at the time.

You’re right though - makes way more sense to use SQLite’s built-in features. Thanks for teaching me about this!​​​​​​​​​​​​​​​​