HeliosDB Nano SQLite Drop-In Replacement Guide

HeliosDB Nano SQLite Drop-In Replacement Guide

What is a Drop-In Replacement?

A drop-in replacement means you can replace SQLite with HeliosDB Nano in your existing Python applications with zero or minimal code changes. Your application continues to work exactly as before, but now you have access to:

• Better concurrency - Multiple writers without database locks
• Advanced features - Vector search, time-travel queries, database branching
• Three flexible modes - REPL, daemon server, or hybrid
• Production-ready - Built in Rust for performance and safety

The beauty of HeliosDB Nano is that you don’t have to use advanced features right away. Start with drop-in compatibility, then explore advanced capabilities when you’re ready.

---

Table of Contents

1. Installation
2. 5-Minute Quick Start
3. Common Migration Patterns
4. Connection String Changes
5. Before/After Code Examples
6. Testing Your Migration
7. Troubleshooting Common Issues
8. Performance Expectations vs. SQLite
9. Enabling Advanced Features (Quick Reference)
   • Vector Search & RAG
   • Database Branching
   • Time-Travel Queries
   • Transparent Data Encryption (TDE)
   • Server Mode
   • Hybrid Mode
   • Performance Tuning
10. What’s Next?

---

Installation

Option 1: Direct Replacement (Recommended for Quick Start)

# Install HeliosDB Nano with SQLite compatibility
pip install heliosdb-sqlite

That’s it! HeliosDB Nano automatically registers itself as a SQLite-compatible database driver.

Option 2: Side-by-Side Testing

# Install alongside existing SQLite
pip install heliosdb-sqlite

# Your code can use both during transition
import sqlite3  # Original SQLite
import heliosdb_sqlite as heliosdb  # HeliosDB Nano

Option 3: Virtual Environment (Recommended for Production)

# Create fresh environment
python -m venv heliosdb_env
source heliosdb_env/bin/activate  # On Windows: heliosdb_env\Scripts\activate

# Install dependencies
pip install heliosdb-sqlite
pip install -r requirements.txt

---

5-Minute Quick Start

Step 1: Minimal Code Change

Before (SQLite):

import sqlite3

conn = sqlite3.connect('myapp.db')
cursor = conn.cursor()
cursor.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
cursor.execute('INSERT INTO users VALUES (1, "Alice")')
conn.commit()

After (HeliosDB Nano):

import heliosdb_sqlite as sqlite3  # Only change needed!

conn = sqlite3.connect('myapp.db')
cursor = conn.cursor()
cursor.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
cursor.execute('INSERT INTO users VALUES (1, "Alice")')
conn.commit()

That’s the only change required for basic usage!

Step 2: Verify It Works

import heliosdb_sqlite as sqlite3

# Connect to database
conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# Create table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        price REAL
    )
''')

# Insert data
cursor.execute('INSERT INTO products VALUES (1, "Widget", 9.99)')
cursor.execute('INSERT INTO products VALUES (2, "Gadget", 19.99)')
conn.commit()

# Query data
cursor.execute('SELECT * FROM products')
print(cursor.fetchall())
# Output: [(1, 'Widget', 9.99), (2, 'Gadget', 19.99)]

conn.close()

Step 3: Test Your Existing Application

# Run your test suite with HeliosDB Nano
python -m pytest tests/

# Or run your application
python app.py

Expected Result: Everything should work exactly as before!

---

Common Migration Patterns

Pattern 1: Import Alias (Zero Changes to Rest of Code)

# Change only this line at the top of your file
import heliosdb_sqlite as sqlite3

# Everything else stays the same
conn = sqlite3.connect('database.db')
# ... rest of your code unchanged

Pattern 2: Conditional Import (Support Both)

import os

# Use environment variable to choose
if os.getenv('USE_HELIOSDB', 'false').lower() == 'true':
    import heliosdb_sqlite as sqlite3
    print("Using HeliosDB Nano")
else:
    import sqlite3
    print("Using SQLite")

# Rest of code is identical
conn = sqlite3.connect('app.db')

Pattern 3: Gradual Migration with Feature Detection

try:
    import heliosdb_sqlite as sqlite3
    HELIOSDB_AVAILABLE = True
    print("HeliosDB Nano detected - advanced features available")
except ImportError:
    import sqlite3
    HELIOSDB_AVAILABLE = False
    print("Using standard SQLite")

# Use advanced features conditionally
conn = sqlite3.connect('app.db')
if HELIOSDB_AVAILABLE:
    # Enable vector search, branching, etc.
    conn.enable_vector_search()

---

Connection String Changes

Basic Connection (No Changes Required)

# These all work identically
conn = sqlite3.connect('database.db')          # File database
conn = sqlite3.connect(':memory:')             # In-memory database
conn = sqlite3.connect('')                     # Temporary database

Advanced Connection Options (HeliosDB Nano Specific)

# Enable HeliosDB Nano advanced features
conn = sqlite3.connect('database.db', heliosdb_mode='advanced')

# Connect to daemon server mode
conn = sqlite3.connect('database.db', heliosdb_mode='server', port=5432)

# Enable encryption at rest
conn = sqlite3.connect('database.db', encryption_key='your-secret-key')

# Hybrid mode (best of both worlds)
conn = sqlite3.connect('database.db', heliosdb_mode='hybrid')

---

Before/After Code Examples

Example 1: Simple Application

Before (SQLite):

import sqlite3

def get_user(user_id):
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users WHERE id = ?', (user_id,))
    user = cursor.fetchone()
    conn.close()
    return user

def create_user(name, email):
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
    cursor.execute('INSERT INTO users (name, email) VALUES (?, ?)', (name, email))
    conn.commit()
    user_id = cursor.lastrowid
    conn.close()
    return user_id

After (HeliosDB Nano):

import heliosdb_sqlite as sqlite3  # <- Only change!

def get_user(user_id):
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users WHERE id = ?', (user_id,))
    user = cursor.fetchone()
    conn.close()
    return user

def create_user(name, email):
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()
    cursor.execute('INSERT INTO users (name, email) VALUES (?, ?)', (name, email))
    conn.commit()
    user_id = cursor.lastrowid
    conn.close()
    return user_id

Example 2: Context Manager Pattern

Before (SQLite):

import sqlite3
from contextlib import closing

def update_inventory(product_id, quantity):
    with closing(sqlite3.connect('inventory.db')) as conn:
        with conn:
            cursor = conn.cursor()
            cursor.execute('''
                UPDATE products
                SET quantity = quantity + ?
                WHERE id = ?
            ''', (quantity, product_id))
            return cursor.rowcount

After (HeliosDB Nano):

import heliosdb_sqlite as sqlite3  # <- Only change!
from contextlib import closing

def update_inventory(product_id, quantity):
    with closing(sqlite3.connect('inventory.db')) as conn:
        with conn:
            cursor = conn.cursor()
            cursor.execute('''
                UPDATE products
                SET quantity = quantity + ?
                WHERE id = ?
            ''', (quantity, product_id))
            return cursor.rowcount

Example 3: Concurrent Access (Where HeliosDB Nano Shines)

Before (SQLite - Potential Lock Issues):

import sqlite3
import threading

def concurrent_writes():
    def writer(thread_id):
        conn = sqlite3.connect('test.db', timeout=10)
        cursor = conn.cursor()
        try:
            cursor.execute('INSERT INTO logs VALUES (?, ?)', (thread_id, 'message'))
            conn.commit()
        except sqlite3.OperationalError as e:
            print(f"Thread {thread_id} got locked: {e}")
        conn.close()

    threads = [threading.Thread(target=writer, args=(i,)) for i in range(10)]
    for t in threads:
        t.start()
    for t in threads:
        t.join()

After (HeliosDB Nano - No Locks!):

import heliosdb_sqlite as sqlite3  # <- Only change!
import threading

def concurrent_writes():
    def writer(thread_id):
        conn = sqlite3.connect('test.db')  # No timeout needed!
        cursor = conn.cursor()
        cursor.execute('INSERT INTO logs VALUES (?, ?)', (thread_id, 'message'))
        conn.commit()  # No locks, all writes succeed!
        conn.close()

    threads = [threading.Thread(target=writer, args=(i,)) for i in range(10)]
    for t in threads:
        t.start()
    for t in threads:
        t.join()

    print("All 10 concurrent writes completed without locks!")

---

Testing Your Migration

Step 1: Create a Test Script

test_migration.py

import heliosdb_sqlite as sqlite3

def test_basic_operations():
    """Test that basic SQLite operations work"""
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()

    # CREATE
    cursor.execute('CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)')

    # INSERT
    cursor.execute('INSERT INTO test VALUES (1, "hello")')
    cursor.execute('INSERT INTO test VALUES (2, "world")')
    conn.commit()

    # SELECT
    cursor.execute('SELECT * FROM test')
    results = cursor.fetchall()
    assert len(results) == 2
    assert results[0] == (1, 'hello')

    # UPDATE
    cursor.execute('UPDATE test SET value = "HELLO" WHERE id = 1')
    conn.commit()

    # DELETE
    cursor.execute('DELETE FROM test WHERE id = 2')
    conn.commit()

    # Verify
    cursor.execute('SELECT COUNT(*) FROM test')
    count = cursor.fetchone()[0]
    assert count == 1

    conn.close()
    print("✓ All basic operations passed!")

def test_transactions():
    """Test transaction support"""
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()

    cursor.execute('CREATE TABLE accounts (id INTEGER, balance REAL)')
    cursor.execute('INSERT INTO accounts VALUES (1, 100)')
    conn.commit()

    # Test rollback
    cursor.execute('UPDATE accounts SET balance = 50 WHERE id = 1')
    conn.rollback()

    cursor.execute('SELECT balance FROM accounts WHERE id = 1')
    balance = cursor.fetchone()[0]
    assert balance == 100

    conn.close()
    print("✓ Transaction tests passed!")

if __name__ == '__main__':
    test_basic_operations()
    test_transactions()
    print("\n✓ All tests passed! HeliosDB Nano is working correctly.")

Step 2: Run Your Test Suite

# Run the migration test
python test_migration.py

# Run your existing test suite
python -m pytest tests/ -v

# Run with coverage
python -m pytest tests/ --cov=your_app

Step 3: Compare Performance

benchmark.py

import time
import sqlite3 as original_sqlite
import heliosdb_sqlite as heliosdb

def benchmark(db_module, name):
    start = time.time()
    conn = db_module.connect(':memory:')
    cursor = conn.cursor()

    cursor.execute('CREATE TABLE bench (id INTEGER, value TEXT)')

    for i in range(10000):
        cursor.execute('INSERT INTO bench VALUES (?, ?)', (i, f'value_{i}'))

    conn.commit()
    conn.close()

    elapsed = time.time() - start
    print(f"{name}: {elapsed:.2f}s ({10000/elapsed:.0f} ops/sec)")

print("Comparing performance (10,000 inserts):")
benchmark(original_sqlite, "SQLite")
benchmark(heliosdb, "HeliosDB Nano")

---

Troubleshooting Common Issues

Issue 1: “Module not found” Error

Problem:

ImportError: No module named 'heliosdb_sqlite'

Solution:

# Ensure you're in the correct virtual environment
which python  # Should show your venv path

# Reinstall
pip install --upgrade heliosdb-sqlite

# Verify installation
python -c "import heliosdb_sqlite; print(heliosdb_sqlite.__version__)"

Issue 2: “Incompatible API” Error

Problem:

AttributeError: 'Connection' object has no attribute 'some_method'

Solution: Check if you’re using SQLite extensions that aren’t standard. HeliosDB Nano supports all standard SQLite APIs. For extensions, see HELIOSDB_SQLITE_ADVANCED_FEATURES.md.

# Check available methods
import heliosdb_sqlite as sqlite3
conn = sqlite3.connect(':memory:')
print(dir(conn))  # List all available methods

Issue 3: Existing Database Migration

Problem: “How do I migrate my existing SQLite database?”

Solution: Your existing .db files work directly with HeliosDB Nano! No migration needed.

import heliosdb_sqlite as sqlite3

# Your existing database.db file works as-is
conn = sqlite3.connect('existing_database.db')
cursor = conn.cursor()
cursor.execute('SELECT COUNT(*) FROM your_table')
print(f"Found {cursor.fetchone()[0]} rows")

Issue 4: Performance Not as Expected

Problem: “HeliosDB Nano seems slower than SQLite for my use case”

Solution: HeliosDB Nano optimizes for concurrency and advanced features. For single-threaded, simple operations, SQLite might be faster. Use the conditional import pattern:

import os
import threading

# Use HeliosDB Nano for concurrent scenarios
if threading.active_count() > 1 or os.getenv('ENABLE_ADVANCED_FEATURES'):
    import heliosdb_sqlite as sqlite3
else:
    import sqlite3

---

Performance Expectations vs. SQLite

Operation	SQLite	HeliosDB Nano	Notes
Single-threaded reads	⚡ Excellent	⚡ Excellent	Comparable performance
Single-threaded writes	⚡ Excellent	⚡ Good	Slight overhead for advanced features
Concurrent reads	✓ Good	⚡ Excellent	HeliosDB Nano shines here
Concurrent writes	⚠️ Locked	⚡ Excellent	No write locks in HeliosDB Nano
Large transactions	⚡ Excellent	⚡ Excellent	Similar performance
Complex queries	⚡ Excellent	⚡ Excellent	Same query planner
Vector search	❌ Not available	⚡ Excellent	HeliosDB Nano exclusive
Time-travel queries	❌ Not available	⚡ Excellent	HeliosDB Nano exclusive
Database branching	❌ Not available	⚡ Excellent	HeliosDB Nano exclusive

Key Takeaway: Use HeliosDB Nano when you need:

• Concurrent write access
• Advanced features (vectors, time-travel, branching)
• Future-proof architecture

Stick with SQLite for:

• Ultra-simple, single-threaded scripts
• Embedded systems with tight constraints
• Projects that will never need advanced features

---

Enabling Advanced Features (Quick Reference)

Once you’re using HeliosDB Nano as a drop-in replacement, you can incrementally enable powerful features. Here’s how to configure each one:

Vector Search & RAG Applications

Enable semantic search, embeddings, and AI/RAG applications:

import heliosdb_sqlite as sqlite3

# Enable vector search on connection
conn = sqlite3.connect('app.db', enable_vector_search=True)
cursor = conn.cursor()

# Create table with vector column
cursor.execute('''
    CREATE TABLE documents (
        id INTEGER PRIMARY KEY,
        content TEXT,
        embedding VECTOR(384)  -- Dimension matches your embedding model
    )
''')

# Insert with embedding
from sentence_transformers import SentenceTransformer
model = SentenceTransformer('all-MiniLM-L6-v2')

embedding = model.encode("Your document text").tolist()
cursor.execute('INSERT INTO documents (content, embedding) VALUES (?, ?)',
               ('Your document text', embedding))

# Semantic search
query_embedding = model.encode("search query").tolist()
cursor.execute('''
    SELECT content, vector_distance(embedding, ?, 'cosine') as similarity
    FROM documents
    ORDER BY similarity ASC
    LIMIT 10
''', (query_embedding,))

Distance metrics: cosine, euclidean, manhattan

→ Full guide: HELIOSDB_SQLITE_ADVANCED_FEATURES.md#vector-search-from-python

---

Database Branching

Create isolated branches for testing, A/B experiments, or safe migrations:

import heliosdb_sqlite as sqlite3

conn = sqlite3.connect('production.db')

# Create a branch
branch = conn.create_branch('experiment')

# Work on branch (doesn't affect main)
branch_conn = sqlite3.connect('production.db', branch='experiment')
cursor = branch_conn.cursor()
cursor.execute('ALTER TABLE users ADD COLUMN new_field TEXT')

# If satisfied, merge back
conn.merge_branch('experiment')

# Or discard
conn.delete_branch('experiment')

# List all branches
branches = conn.list_branches()
for b in branches:
    print(f"{b.name} - created: {b.created_at}")

Use cases: Schema migrations, A/B testing, feature development, rollback points

→ Full guide: HELIOSDB_SQLITE_ADVANCED_FEATURES.md#database-branching

---

Time-Travel Queries

Query historical data states for audit, compliance, or recovery:

import heliosdb_sqlite as sqlite3
from datetime import datetime, timedelta

# Enable time-travel
conn = sqlite3.connect('app.db', enable_time_travel=True)
cursor = conn.cursor()

# Query current state
cursor.execute('SELECT * FROM users WHERE id = 123')

# Query state from 1 hour ago
one_hour_ago = datetime.now() - timedelta(hours=1)
cursor.execute(f'''
    SELECT * FROM users
    WHERE id = 123
    AS OF TIMESTAMP '{one_hour_ago.isoformat()}'
''')

# Track all changes in a date range
cursor.execute('''
    SELECT * FROM users
    WHERE id = 123
    FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-12-31'
''')

# Enable versioning on a table
cursor.execute('ALTER TABLE audit_logs ADD SYSTEM VERSIONING')

Use cases: Audit trails, compliance, accidental deletion recovery, debugging

→ Full guide: HELIOSDB_SQLITE_ADVANCED_FEATURES.md#time-travel-queries

---

Transparent Data Encryption (TDE)

Encrypt data at rest with transparent encryption:

import heliosdb_sqlite as sqlite3
import os

# Retrieve encryption key from secure storage
encryption_key = os.getenv('DB_ENCRYPTION_KEY')

# Create/open encrypted database
conn = sqlite3.connect('secure.db', encryption_key=encryption_key)
cursor = conn.cursor()

# Use normally - encryption is transparent
cursor.execute('CREATE TABLE secrets (id INTEGER, data TEXT)')
cursor.execute('INSERT INTO secrets VALUES (1, "sensitive data")')
conn.commit()

# Data is encrypted on disk, decrypted automatically on read

Column-level encryption:

cursor.execute('''
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        username TEXT,              -- Not encrypted (searchable)
        email TEXT ENCRYPTED,       -- Encrypted column
        ssn TEXT ENCRYPTED          -- Encrypted column
    )
''')

Key rotation:

# Rotate encryption key
conn.rekey(new_encryption_key)

→ Full guide: HELIOSDB_SQLITE_ADVANCED_FEATURES.md#encryption-at-rest

---

Server Mode (PostgreSQL Wire Protocol)

Run HeliosDB Nano as a network-accessible server:

import heliosdb_sqlite as sqlite3

# Start server
server = sqlite3.start_server(
    database='app.db',
    host='0.0.0.0',
    port=5432,
    max_connections=100
)

print(f"Server running on port {server.port}")
# Connect using: postgresql://localhost:5432/app

Clients connect using standard PostgreSQL drivers:

import psycopg2

conn = psycopg2.connect(
    host='localhost',
    port=5432,
    database='app',
    user='helios'
)
cursor = conn.cursor()
cursor.execute('SELECT * FROM users')

→ Full guide: HELIOSDB_SQLITE_ADVANCED_FEATURES.md#server-mode-switching

---

Hybrid Mode

Combine embedded (fast local) and server (remote access) modes:

import heliosdb_sqlite as sqlite3

# Start in hybrid mode
conn = sqlite3.connect('app.db', heliosdb_mode='hybrid')

# Local access (fast, embedded)
cursor = conn.cursor()
cursor.execute('SELECT * FROM users')

# Also accessible via network for other clients
server_info = conn.get_server_info()
print(f"Remote access: {server_info.url}")

Use cases: Web apps with admin tools, ML training with remote monitoring

→ Full guide: HELIOSDB_SQLITE_ADVANCED_FEATURES.md#hybrid-mode-usage

---

Performance Tuning

Connection pooling:

from heliosdb_sqlite import connection_pool

pool = connection_pool.create(
    database='app.db',
    pool_size=20,
    max_overflow=10
)

with pool.connection() as conn:
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users')

Query caching:

conn = sqlite3.connect('app.db', enable_cache=True, cache_size_mb=100)

# Cache statistics
stats = conn.get_cache_stats()
print(f"Hit rate: {stats.hit_rate:.2%}")

Batch operations:

users = [('user1', 'email1'), ('user2', 'email2'), ...]
cursor.batch_insert('users', ['name', 'email'], users)

→ Full guide: HELIOSDB_SQLITE_ADVANCED_FEATURES.md#performance-tuning-for-heliosdb-nano

---

Feature Summary Table

Feature	Enable With	Primary Use Case
Vector Search	enable_vector_search=True	AI/ML, RAG, semantic search
Branching	conn.create_branch('name')	Safe testing, A/B experiments
Time-Travel	enable_time_travel=True	Audit, compliance, recovery
TDE Encryption	encryption_key='...'	Data security, compliance
Server Mode	start_server(...)	Multi-client access
Hybrid Mode	heliosdb_mode='hybrid'	Local + remote access
Query Cache	enable_cache=True	Read performance

---

What’s Next?

Explore Advanced Features

Once you’re comfortable with basic drop-in usage, explore:

1. Vector Search - Add semantic search to your application

   • See: HELIOSDB_SQLITE_ADVANCED_FEATURES.md

2. Database Branching - Create experimental branches

   • Example: conn.create_branch('experiment')

3. Time-Travel Queries - Query historical data

   • Example: SELECT * FROM users AS OF TIMESTAMP '2024-01-01'

4. Server Mode - Run as PostgreSQL-compatible server

   • See: HELIOSDB_SQLITE_MIGRATION_PATTERNS.md

Learn Migration Patterns

See real-world examples for:

• Django ORM
• SQLAlchemy
• Flask/FastAPI
• Asyncio applications

Check out: HELIOSDB_SQLITE_MIGRATION_PATTERNS.md

Join the Community

• GitHub: [HeliosDB Nano Repository]
• Documentation: Full docs available in /docs
• Issues: Report bugs or request features
• Discussions: Share your use cases

---

Summary

HeliosDB Nano is designed to be a true drop-in replacement for SQLite:

✓ Change one import line ✓ Existing databases work as-is ✓ All standard SQLite APIs supported ✓ Better concurrency out of the box ✓ Optional advanced features when you need them ✓ Three flexible modes: REPL, daemon, hybrid ✓ Production-ready and actively maintained

Get started in 5 minutes:

# Install
# $ pip install heliosdb-sqlite

# Use
import heliosdb_sqlite as sqlite3
conn = sqlite3.connect('myapp.db')
# ... rest of your code unchanged

That’s it! You’re now running HeliosDB Nano with all its benefits, and you can explore advanced features whenever you’re ready.

---

Next Steps:

• Read HELIOSDB_SQLITE_MIGRATION_PATTERNS.md for framework-specific examples
• Check HELIOSDB_SQLITE_FAQ.md for common questions
• See HELIOSDB_SQLITE_TROUBLESHOOTING.md for detailed problem-solving
• Explore HELIOSDB_SQLITE_ADVANCED_FEATURES.md when ready for more power