docs/code-assistant/database-connections.md

SQLite Connection Management

Overview

The application uses a robust SQLite connection management strategy designed to handle high concurrency and prevent "database is locked" errors.

Journal Mode Selection

The application uses different journal modes depending on database characteristics:

WAL Mode (Write-Ahead Logging)

Used for databases with high concurrency and frequent reads:

• metadata.db - Main file metadata database
• sessions.db - User session data

Benefits: Allows concurrent reads during writes, better performance for read-heavy workloads.

Drawback: WAL files can become corrupted under rapid concurrent access during initialization.

DELETE Mode

Used for simple databases with infrequent writes:

• locks.db - File locking database

Benefits: Simpler, no WAL file corruption issues, sufficient for low-concurrency use cases.

When to use DELETE mode:

• Small databases with infrequent writes
• Short-lived data (like locks)
• Databases that don't benefit from WAL's read concurrency
• When rapid concurrent access during tests causes WAL corruption

See fastapi_app/lib/core/locking.py for an example of DELETE mode implementation.

Key Components

1. DatabaseManager (fastapi_app/lib/core/database.py)

The core class for database interaction. It implements:

• Connection Pooling: Uses queue.Queue to reuse connections, reducing the overhead of opening/closing files and avoiding file descriptor exhaustion.
• WAL Mode Initialization: Ensures WAL mode is enabled safely using a raw connection and file locking during startup (_ensure_db_exists).
• Transaction Management: Provides a transaction() context manager that explicitly handles BEGIN, COMMIT, and ROLLBACK.
• Autocommit Mode: Connections are opened with isolation_level=None (autocommit) to allow manual transaction control and prevent implicit transactions from locking the database unexpectedly.

2. Singleton Pattern (fastapi_app/lib/core/dependencies.py)

• _DatabaseManagerSingleton ensures only one DatabaseManager instance exists per database file.
• This allows the connection pool to be shared across the application, preventing multiple pools from competing for the same database file.

3. Locking (fastapi_app/lib/core/sqlite_utils.py)

• with_db_lock(db_path): Uses a reentrant lock (threading.RLock) to serialize schema initialization and WAL mode setup per database file.

4. Busy Timeout

All connections set PRAGMA busy_timeout = 30000 (30 seconds) to wait for locks instead of failing immediately with "database is locked" errors.

Connection Lifecycle

1. Acquisition: get_connection() attempts to retrieve a connection from the pool. If empty, it creates a new sqlite3.Connection with timeout=60.0 and isolation_level=None.
2. Usage: The connection is yielded to the caller.
3. Release:
   • conn.rollback() is called to ensure no uncommitted state leaks to the next user.
   • The connection is put back into the pool.

Best Practices for Code Assistants

• Always use DatabaseManager: Do not create raw sqlite3.connect() calls in business logic.
• Use transaction() for writes: Ensure atomicity for INSERT/UPDATE/DELETE operations.
• Pass DatabaseManager instances: When classes need database access (e.g., StorageReferenceManager), pass the initialized manager instance, not the file path, to utilize the pool.
• Choose the right journal mode: Use WAL for high-concurrency databases, DELETE for simple low-write databases.
• Always set busy_timeout: Use conn.execute("PRAGMA busy_timeout = 30000") to prevent immediate failures on lock contention.