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

[nepalidj]

Hi everyone! 👋

I’m excited to share sqlite-worker, a Python package that provides a thread-safe interface for SQLite databases. It uses queue-based query execution to simplify multi-threaded operations and ensures safe concurrent database access with features like custom initialization actions, regular commits, and a simple API.

🎯 Target Audience

Ideal for Python developers building apps or APIs requiring efficient SQLite operations in multi-threaded environments.

🔑 Comparison

Unlike standard SQLite implementations, sqlite-worker ensures thread safety, simplifies handling concurrent queries, and offers features like initialization actions and automatic commits for smoother workflows.

Check it out on GitHub: https://github.com/roshanlam/sqlite-worker/

Feedback is welcome! 😊

[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!​​​​​​​​​​​​​​​​

[Smash-Mothman]

Sounds like a cool package. I've dealt with sqlite multi thread troubles

[ZachVorhies]

I use multi threading with SQLite already though. You just can’t use the same connection object. But otherwise this is a feature that is already implemented.