BUG: Improve SQLite connection reliability with timeout, WAL mode, and row_factory #863
Description
Is there an existing issue for this?
- I have searched the existing issues
What happened?
What happened?
Currently, the get_db_connection() function in the database layer does not properly configure a few important SQLite connection settings that improve stability and reliability, especially under concurrent access. This can lead to subtle bugs like database locks, inconsistent transaction behavior, and harder-to-read query results.
Why this matters
PictoPy uses SQLite as the primary backend database for storing image metadata and embeddings. Without proper configuration:
-
Lack of connection timeout
SQLite may quickly fail with database is locked errors under concurrent reads/writes instead of waiting and retrying. This can cause unexpected failures in backend flows where multiple DB operations are happening.
-
WAL (Write-Ahead Logging) mode not enabled
By default, SQLite uses rollback journal mode, which limits concurrency and performance. WAL mode dramatically improves concurrent read/write performance and is considered a best practice for applications with frequent DB access.
-
Rows returned as tuples instead of named fields
Currently, results are standard tuples. This increases the risk of bugs when accessing columns by index instead of name and reduces code readability.
Proposed fix
Update get_db_connection() to:
-
Add
timeout=30insqlite3.connect()to avoid fast “database locked” errors -
Enable WAL mode via
PRAGMA journal_mode = WAL; -
Set
conn.row_factory = sqlite3.Rowso query results can be accessed by column name
Expected behavior after fix
- Better resilience under concurrent DB access
- Improved performance and fewer lock-related errors
- Cleaner and safer code when reading DB results
✅ Checklist
- Add timeout to SQLite connection
- Enable WAL mode using PRAGMA journal_mode = WAL
- Configure row_factory to return sqlite3.Row objects
- Verify WAL mode using PRAGMA journal_mode
- Confirm named-column access in query results
- Ensure no breaking changes to existing DB operations
Record
- I agree to follow this project's Code of Conduct
Checklist before Submitting
- Have you updated docs for it?
- Have you added unit tests?
- Have you made sure unit tests pass?
- Have you made sure code formatting is correct?
- Do Your changes passes all tests?