v1.1.0

[cwt]

NeoSQLite v1.1.0 Release Notes

Overview

This release introduces MongoDB-compatible ObjectId support to NeoSQLite, providing full 12-byte ObjectId generation, storage, and interchangeability with PyMongo. The release also includes performance optimizations with JSONB support for the new _id column and maintains complete backward compatibility with existing data and applications.

Highlights

Enhanced ObjectId Implementation

• MongoDB-compatible ObjectId: Full 12-byte ObjectId implementation following MongoDB specification with automatic generation when no _id is provided
• Hex String Interchangeability: Complete hex string compatibility with PyMongo ObjectIds for seamless data exchange
• Dedicated _id Column: New _id JSONB column with unique indexing for faster lookups and proper ObjectId storage
• Backward Compatibility: Full support for existing collections with automatic _id column addition via ALTER TABLE
• Performance Optimization: _id column uses JSONB when available (same as data column) for enhanced performance

Performance Improvements

• JSONB Optimization: Both data and _id columns now use JSONB type when available in SQLite for better performance
• Index Usage: Efficient unique indexing on _id column for fast ObjectId lookups
• Query Plan Optimization: EXPLAIN query plan verification confirms index usage for ObjectId lookups
• Memory Efficiency: Optimized storage and retrieval of ObjectId values using JSONB

New Features

ObjectId Implementation

• neosqlite.objectid.ObjectId: Complete implementation following MongoDB specification
• Automatic Generation: ObjectIds automatically generated when no _id provided during insertion
• Manual Assignment: Support for user-provided ObjectIds during document insertion
• Dedicated Storage: New _id column with unique indexing for efficient storage and lookup
• JSON Serialization: Proper JSON encoding/decoding support with custom encoder
• Thread Safety: Proper locking for safe multi-threaded generation

Enhanced Collection Schema

• New Schema: Tables now use (id INTEGER PRIMARY KEY AUTOINCREMENT, _id JSONB, data JSONB) when JSONB support available
• Backward Compatibility: Existing tables get _id column added via ALTER TABLE when accessed
• Unique Indexing: Automatic unique index creation on _id column for performance
• SQL Translation: Enhanced SQL translator to handle _id field queries properly

Query Engine Updates

• _id Query Support: Full support for queries against _id field with SQL optimization
• Mixed Type Queries: Support for queries that combine integer IDs and ObjectIds
• Index Optimization: Query engine now optimizes queries using the unique _id index
• Backward Compatibility: Existing integer-based queries continue to work unchanged

ObjectId Features

• 12-Byte Structure: Follows MongoDB specification (4 bytes timestamp, 3 bytes random, 2 bytes PID, 3 bytes counter)
• Automatic Generation: New ObjectIds automatically generated when documents don't have _id field
• Manual Assignment: Support for user-provided ObjectIds during document insertion
• Timestamp Extraction: generation_time() method to extract creation timestamp
• Validation: is_valid() method for ObjectId validation
• Serialization: Proper JSON serialization/deserialization support with custom encoder
• Thread Safety: Proper locking mechanisms for safe multi-threaded ObjectId generation

Technical Benefits

• MongoDB Compatibility: Full compatibility with MongoDB ObjectId format and behavior
• Performance Optimization: JSONB type and unique indexing provide enhanced performance
• Backward Compatibility: Full support for existing data and code with automatic schema migration
• Thread Safety: Proper locking mechanisms ensure safe concurrent ObjectId generation
• Memory Efficiency: Optimized storage using JSONB format when available
• Interchangeability: Ready for integration with MongoDB systems using hex interchangeability

Breaking Changes

There are no intentional breaking changes in this release that would break existing application logic. All existing APIs and functionality remain fully compatible with previous versions. However, there are important behavioral changes to be aware of:

• _id Field Type Change: For new documents, the _id field now contains a MongoDB-compatible ObjectId instead of the integer ID
• Backward Compatibility: Existing documents continue to work as before, with the integer ID accessible as the _id field until the document is updated
• Dual ID System: The SQLite integer ID is still available in the id field for all documents

Migration Notes

For existing databases, this release automatically adds the _id column to existing collections when they are first accessed. This process is transparent and maintains full backward compatibility. New collections will be created with the optimized schema using JSONB types when available.

Important Behavioral Changes to Note:

1. New Documents: When inserting new documents without specifying an _id, the _id field will contain an auto-generated ObjectId (not the integer id)

2. Existing Documents: Documents created before this release will continue to have their integer ID as the _id value until they are updated or replaced

3. Accessing Integer ID: The integer ID is always available in the id field for all documents (both old and new)

4. Querying: You can query using either the ObjectId (for new documents) or integer ID (for old documents) in the _id field, with the system handling the appropriate lookup

Code Migration Example:

# Old behavior (still works for existing documents)
old_doc = collection.find_one({"_id": 123})  # Finds by integer ID for old docs

# New behavior (for new documents)
new_doc = collection.insert_one({"name": "new"})
# new_doc now has an ObjectId in _id field, integer ID still accessible via 'id'

# To access the integer ID for any document:
integer_id = new_doc.get("id")  # This is the SQLite auto-increment ID
object_id = new_doc.get("_id")  # This is the MongoDB-compatible ObjectId

Installation

# Standard installation
pip install neosqlite

# For enhanced JSON/JSONB support
pip install neosqlite[jsonb]

# For memory-constrained processing of large result sets
pip install neosqlite[memory-constrained]

# Install multiple extras
pip install neosqlite[jsonb,memory-constrained]

Notable Features

ObjectId Example

from neosqlite import Connection
from neosqlite.objectid import ObjectId

# Connect to database
with Connection(":memory:") as conn:
    collection = conn.my_collection
    
    # Insert document - ObjectId automatically generated
    result = collection.insert_one({"name": "test", "value": 123})
    print(f"Inserted with ObjectId: {result.inserted_id}")
    
    # Find by ObjectId
    doc = collection.find_one({"_id": result.inserted_id})  # This will be the integer ID
    print(f"Document: {doc}")
    print(f"ObjectId in _id field: {doc['_id']}")  # This will be the actual ObjectId
    
    # Insert with manual ObjectId
    manual_oid = ObjectId()
    collection.insert_one({"_id": manual_oid, "name": "manual", "value": 456})
    
    # Query using ObjectId
    found = collection.find_one({"_id": manual_oid})
    print(f"Found with ObjectId: {found}")
    
    # Query using hex string (interchangeable with PyMongo)
    hex_result = collection.find_one({"_id": str(manual_oid)})
    print(f"Found with hex string: {hex_result}")

Enhanced Performance with JSONB

# New collections automatically use JSONB for both data and _id columns when available
# This provides better performance than TEXT/JSON columns
with Connection(":memory:") as conn:
    collection = conn.my_collection
    
    # Both _id and data columns use JSONB (when support is available)
    # Providing faster access and smaller storage footprint
    for i in range(1000):
        collection.insert_one({"name": f"doc_{i}", "value": i})
    
    # Queries against _id field use the unique index for performance
    result = collection.find_one({"_id": some_objectid})

This release represents a major milestone for NeoSQLite, adding full MongoDB-compatible ObjectId support while maintaining backward compatibility and enhancing performance with JSONB optimization. The implementation provides seamless integration with MongoDB workflows through hex string interchangeability while maintaining the efficiency and reliability of SQLite storage.

---

This discussion was created from the release v1.1.0.