HeliosDB-Lite SQLite Drop-In Replacement Guide

HeliosDB-Lite SQLite Drop-In Replacement Guide

What is a Drop-In Replacement?

A drop-in replacement means you can replace SQLite with HeliosDB-Lite 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-Lite 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-Lite with SQLite compatibility
pip install heliosdb-sqlite

That’s it! HeliosDB-Lite 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-Lite

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-Lite):

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-Lite
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-Lite")
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-Lite 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-Lite Specific)

# Enable HeliosDB-Lite 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-Lite):

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-Lite):

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-Lite 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-Lite - 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-Lite 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-Lite")

---

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-Lite 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-Lite! 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-Lite seems slower than SQLite for my use case”

Solution: HeliosDB-Lite 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-Lite 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-Lite	Notes
Single-threaded reads	⚡ Excellent	⚡ Excellent	Comparable performance
Single-threaded writes	⚡ Excellent	⚡ Good	Slight overhead for advanced features
Concurrent reads	✓ Good	⚡ Excellent	HeliosDB-Lite shines here
Concurrent writes	⚠️ Locked	⚡ Excellent	No write locks in HeliosDB-Lite
Large transactions	⚡ Excellent	⚡ Excellent	Similar performance
Complex queries	⚡ Excellent	⚡ Excellent	Same query planner
Vector search	❌ Not available	⚡ Excellent	HeliosDB-Lite exclusive
Time-travel queries	❌ Not available	⚡ Excellent	HeliosDB-Lite exclusive
Database branching	❌ Not available	⚡ Excellent	HeliosDB-Lite exclusive

Key Takeaway: Use HeliosDB-Lite 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-Lite 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-Lite 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-lite

---

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-Lite Repository]
• Documentation: Full docs available in /docs
• Issues: Report bugs or request features
• Discussions: Share your use cases

---

Summary

HeliosDB-Lite 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-Lite 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