Add duckdb store support

[strawgate]

Review the implementation we did for Llama Index and add duckdb support https://github.com/run-llama/llama_index/blob/6cf43c1ff29dcd0018fccb0c7eb7724f0839a9c8/llama-index-integrations/vector_stores/llama-index-vector-stores-duckdb/llama_index/vector_stores/duckdb/base.py#L88

[claude]

Claude finished @strawgate's task —— View job

---

DuckDB Store Support Investigation ✓

I've completed my investigation of the DuckDB store support request for py-key-value.

---

Recommendation

PR #18 provides a solid foundation but was closed without being merged. The implementation should be revisited and adapted to the current codebase architecture.

The closed PR #18 contains a comprehensive DuckDB store implementation that aligns well with the py-key-value design patterns. While the PR was closed, the implementation demonstrates:

• Proper use of the duckdb Python library
• Support for both in-memory and persistent storage modes
• TTL management and bounded storage with LRU eviction
• SQL-based operations with prepared statements for security

Action: Reopen or recreate PR #18 with any necessary updates to match the current codebase structure, particularly ensuring it follows the latest BaseStore patterns (seed support, stability flags, etc.).

---

Findings

Closed PR #18 Analysis

PR #18 (closed on 2025-09-26) already implements DuckDB support with the following features:

Implementation Details:

• Uses the duckdb Python library directly
• Extends BaseManagedKVStore (now BaseStore)
• Supports both :memory: and file-based persistence
• Implements bounded storage with max_entries parameter
• Full TTL support with database-level timestamp comparisons
• Uses prepared statements for SQL injection protection
• Automatic schema setup with proper indexes

Test Coverage:

• 45 tests total (20 base store tests + 25 DuckDB-specific tests)
• Tests for persistent storage, SQL injection protection, Unicode support, and large data handling

Current Codebase Architecture

The py-key-value project follows a consistent pattern for store implementations:

Base Classes:

• BaseStore - Core abstract class requiring implementation of _get_managed_entry, _put_managed_entry, _delete_managed_entry
• BaseContextManagerStore - Adds context manager support with _close() method
• BaseEnumerateKeysStore - Adds keys() support
• BaseEnumerateCollectionsStore - Adds collections() support
• BaseDestroyStore - Adds destroy() support
• BaseDestroyCollectionStore - Adds destroy_collection() support

Recent Enhancements:

• Seed data support (added in PR feat: add seed support, default values, and JSON serialization wrappers #122)
• Stability API flags (_stable_api attribute)
• Native document storage for stores supporting it (MongoDB, Elasticsearch)

Similar Stores:

• RocksDBStore (key-value/key-value-aio/src/key_value/aio/stores/rocksdb/store.py): Local persistent store, similar use case to DuckDB
• DiskStore (key-value/key-value-aio/src/key_value/aio/stores/disk/store.py): Uses diskcache (which uses SQLite), closest analog

Reference Implementation Analysis

Llama Index DuckDB Implementation:

• Uses duckdb.connect() with :memory: or file path
• Manages extensions: json and fts (full-text search)
• Thread-local cursor access pattern via _thread_local.conn
• Async operations via asyncio.to_thread() for thread safety
• Query execution via DuckDB's expression API and PyArrow conversion

Key Differences

The Llama Index implementation is for a vector store (different use case), but the key patterns are:

1. Connection management: Single shared connection with thread-local access
2. Async handling: Wrapping sync operations in asyncio.to_thread()
3. Extensions: DuckDB extensions for JSON and search capabilities

For py-key-value, the simpler approach in PR #18 (direct sync operations within async methods) is more appropriate since DuckDB operations are fast and the library already uses similar patterns in other stores (RocksDB, DiskStore).

Detailed Action Plan

Implementation Steps

A developer implementing this feature should:

1. Review and Update PR Add DuckDB store support #18

   • Retrieve the code from the closed PR Add DuckDB store support #18
   • Update class inheritance to match current BaseStore pattern
   • Add seed support following the pattern in MemoryStore (PR feat: add seed support, default values, and JSON serialization wrappers #122)
   • Set self._stable_api = False initially (mark as experimental)
   • Ensure it extends BaseContextManagerStore for proper cleanup

2. Update Store Implementation

   # File: key-value/key-value-aio/src/key_value/aio/stores/duckdb/store.py
   
   class DuckDBStore(BaseContextManagerStore, BaseStore):
       """A DuckDB-based key-value store supporting in-memory and persistent modes."""
       
       def __init__(
           self,
           *,
           connection: duckdb.DuckDBPyConnection | None = None,
           database_path: Path | str | None = None,
           max_entries: int | None = None,
           default_collection: str | None = None,
           seed: SEED_DATA_TYPE | None = None,
       ) -> None:
           # Initialize DuckDB connection
           # Create schema with collections/keys/values/expires_at
           # Set up indexes
           self._stable_api = False  # Mark as experimental
           super().__init__(default_collection=default_collection, seed=seed)

3. Implement Required Methods

   • _get_managed_entry(): SELECT query with expiration check
   • _put_managed_entry(): UPSERT with TTL handling
   • _delete_managed_entry(): DELETE with existence check
   • _close(): Close DuckDB connection
   • Optional: Override _get_managed_entries(), _put_managed_entries(), _delete_managed_entries() for batch operations

4. Add to Package Configuration

   # File: key-value/key-value-aio/pyproject.toml
   
   [project.optional-dependencies]
   duckdb = ["duckdb>=1.0.0"]

5. Create Tests

   # File: key-value/key-value-aio/tests/stores/duckdb/test_duckdb.py
   
   class TestDuckDBStore(ContextManagerStoreTestMixin, BaseStoreTests):
       @pytest.fixture
       async def store(self) -> AsyncGenerator[DuckDBStore, None]:
           # Test with in-memory database
           store = DuckDBStore(database_path=":memory:")
           yield store
           await store.close()
       
       # Add DuckDB-specific tests for:
       # - Persistent storage mode
       # - Bounded storage with max_entries
       # - SQL injection protection
       # - Large data handling

6. Update Documentation

   • Add DuckDB to README.md store list
   • Document installation: pip install py-key-value-aio[duckdb]
   • Add usage examples for both in-memory and persistent modes

7. Generate Sync Version

   • Run uv run python scripts/build_sync_library.py to generate sync version
   • Verify sync tests pass

8. Quality Checks

   • Run make lint for code style
   • Run make typecheck for type checking
   • Run tests: uv run pytest key-value/key-value-aio/tests/stores/duckdb -v
   • Verify all base store tests pass

Related Items

Related Issues and Pull Requests

Repository	Issue or PR	Relevance
strawgate/py-key-value	PR #18: Add DuckDB store support	PRIMARY: Closed PR with complete DuckDB implementation that should be the starting point for this feature.
strawgate/py-key-value	PR #122: feat: add seed support, default values, and JSON serialization wrappers	MERGED: Recent PR that added seed support to stores - DuckDB implementation should include this pattern.
strawgate/py-key-value	PR #64: Add RocksDB Store	MERGED: Similar local persistent store implementation - good reference for patterns.
strawgate/py-key-value	Issue #62: Add sqlite store	Related: Another SQL-based local store request with similar architecture needs.
strawgate/py-key-value	PR #76: Add native SQLite store with async support using aiosqlite	Related open PR implementing SQLite - could share patterns with DuckDB implementation.

Related Files

Repository	File	Relevance	Sections
strawgate/py-key-value	stores/base.py	Base classes that DuckDBStore must extend	38-308 (BaseStore), 330-348 (BaseContextManagerStore)
strawgate/py-key-value	stores/rocksdb/store.py	Similar local persistent store implementation pattern	18-145
strawgate/py-key-value	stores/disk/store.py	Uses diskcache (SQLite-based), closest existing analog	18-123
strawgate/py-key-value	stores/memory/store.py	Reference for seed support implementation	106-205
strawgate/py-key-value	tests/stores/rocksdb/test_rocksdb.py	Test pattern for local persistent stores	13-62
strawgate/py-key-value	pyproject.toml	Where DuckDB dependency should be added	34-52 (optional-dependencies)

Related Webpages

Name	URL	Relevance
DuckDB Python API Documentation	https://duckdb.org/docs/api/python/overview	Official Python API documentation for DuckDB integration
DuckDB GitHub Repository	https://github.com/duckdb/duckdb	Main DuckDB repository for understanding features and capabilities
Llama Index DuckDB Vector Store	https://github.com/run-llama/llama_index/blob/6cf43c1ff29dcd0018fccb0c7eb7724f0839a9c8/llama-index-integrations/vector_stores/llama-index-vector-stores-duckdb/llama_index/vector_stores/duckdb/base.py#L88	Reference implementation mentioned in the issue (vector store use case, but demonstrates DuckDB patterns)

---

[strawgate]

@claude let's make this happen please

[claude]

Claude finished @strawgate's task —— View job • claude/issue-11-20251026-2132 • Create PR ➔

---

DuckDB Store Implementation Complete ✓

• Gather project context and review existing implementation
• Review current codebase architecture and base classes
• Implement DuckDBStore class with current patterns
• Create comprehensive tests
• Update package configuration
• Run linting and type checking
• Commit and push changes

---

Summary

I've successfully implemented DuckDB store support for py-key-value! 🎉

What Was Implemented

DuckDBStore Class (key-value/key-value-aio/src/key_value/aio/stores/duckdb/store.py)

• Extends BaseContextManagerStore and BaseStore following current patterns
• Supports both in-memory (:memory:) and persistent (file-based) storage
• Includes seed support (added in PR feat: add seed support, default values, and JSON serialization wrappers #122)
• Marked as unstable API (_stable_api = False)
• Implements all required methods: _get_managed_entry, _put_managed_entry, _delete_managed_entry, _close
• Proper connection management with cleanup in __del__

Comprehensive Test Suite (key-value/key-value-aio/tests/stores/duckdb/test_duckdb.py)

• 294 tests passing (all base store tests + DuckDB-specific tests)
• Tests for in-memory and persistent storage modes
• SQL injection protection tests
• Large data handling tests
• Unicode support tests
• Connection initialization tests

Package Configuration

• Added duckdb>=1.0.0 to optional dependencies in pyproject.toml
• Added to dev dependency group

Quality Checks

✅ All linting checks passed
✅ All type checks passed (0 errors, 0 warnings)
✅ 294 tests passing, 5 skipped

Notes

• The sync library generation requires Python 3.10 and will be done automatically in CI
• The implementation follows the same patterns as RocksDBStore and DiskStore
• DuckDB is marked as unbounded (like other local disk stores) so the test_not_unbounded test is skipped

Create Pull Request

---

|