HeliosDB-Lite SQLite Compatibility Troubleshooting Guide¶

This guide helps you quickly resolve common issues when using HeliosDB-Lite as a SQLite drop-in replacement.

Table of Contents¶

Installation Issues
Import and Module Errors
Compatibility Issues
Performance Problems
Data Migration Issues
Mode Switching Issues
Connection and Threading Issues
Testing and Debugging Tips

Installation Issues¶

Problem: pip install fails¶

Symptoms:

$ pip install heliosdb-sqlite
ERROR: Could not find a version that satisfies the requirement heliosdb-sqlite

Solutions:

Update pip and setuptools:

python -m pip install --upgrade pip setuptools wheel
pip install heliosdb-sqlite

Check Python version:

python --version  # Must be Python 3.8 or higher

Try specific version:
```
pip install heliosdb-sqlite==1.0.0
```

Install from source (if pre-built wheels unavailable):

git clone https://github.com/heliosdb/heliosdb-lite.git
cd heliosdb-lite/python
pip install .

Problem: Import works in terminal but not in IDE¶

Symptoms:

# Works in terminal
$ python -c "import heliosdb_sqlite"

# Fails in PyCharm/VSCode
ModuleNotFoundError: No module named 'heliosdb_sqlite'

Solutions:

Verify IDE is using correct Python interpreter:
PyCharm: Settings > Project > Python Interpreter
VSCode: Select interpreter from command palette (Ctrl+Shift+P)

Install in IDE's environment:

# Find IDE's Python path
which python

# Install there explicitly
/path/to/ide/python -m pip install heliosdb-sqlite

Restart IDE after installation

Import and Module Errors¶

Problem: "No module named 'heliosdb_sqlite'"¶

Symptoms:

import heliosdb_sqlite as sqlite3
# ModuleNotFoundError: No module named 'heliosdb_sqlite'

Solutions:

Verify installation:

pip list | grep heliosdb
python -c "import heliosdb_sqlite; print(heliosdb_sqlite.__version__)"

Check virtual environment:

# Activate correct environment
source venv/bin/activate  # Linux/Mac
venv\Scripts\activate     # Windows

# Verify
which python
pip list

Reinstall package:

pip uninstall heliosdb-sqlite
pip install heliosdb-sqlite

Problem: AttributeError with HeliosDB-Lite methods¶

Symptoms:

conn = sqlite3.connect('app.db')
conn.some_method()
# AttributeError: 'Connection' object has no attribute 'some_method'

Solutions:

Check if method is standard SQLite:

import sqlite3 as std_sqlite

# Check if method exists in standard SQLite
print(dir(std_sqlite.connect(':memory:')))

Use HeliosDB-Lite specific features correctly:

# Wrong: advanced features need to be enabled
conn = sqlite3.connect('app.db')
conn.create_branch('test')  # May fail

# Right: enable advanced features
conn = sqlite3.connect('app.db', heliosdb_mode='advanced')
branch = conn.create_branch('test')  # Works!

Check API documentation:

# Get help on connection object
help(conn)

# List all available methods
print([m for m in dir(conn) if not m.startswith('_')])

Compatibility Issues¶

Problem: Existing code breaks with HeliosDB-Lite¶

Symptoms:

# Code worked with SQLite but fails with HeliosDB-Lite
cursor.execute('SELECT * FROM users')
# Some error occurs

Solutions:

Enable compatibility mode:

# Maximum SQLite compatibility
conn = sqlite3.connect('app.db', heliosdb_mode='sqlite_compat')

Check for unsupported features:

# Test basic operations one by one
import heliosdb_sqlite as sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# Test CREATE
cursor.execute('CREATE TABLE test (id INTEGER, name TEXT)')
print("CREATE: OK")

# Test INSERT
cursor.execute('INSERT INTO test VALUES (1, "test")')
conn.commit()
print("INSERT: OK")

# Test SELECT
cursor.execute('SELECT * FROM test')
print(f"SELECT: {cursor.fetchall()}")

# Test UPDATE
cursor.execute('UPDATE test SET name = "updated" WHERE id = 1')
conn.commit()
print("UPDATE: OK")

# Test DELETE
cursor.execute('DELETE FROM test WHERE id = 1')
conn.commit()
print("DELETE: OK")

Use fallback pattern:

# Conditional import for safety
try:
    import heliosdb_sqlite as sqlite3
    db_engine = 'heliosdb'
except Exception as e:
    print(f"HeliosDB-Lite failed ({e}), falling back to SQLite")
    import sqlite3
    db_engine = 'sqlite'

print(f"Using: {db_engine}")

Problem: SQL syntax not recognized¶

Symptoms:

cursor.execute('SELECT * FROM users LIMIT 10 OFFSET 5')
# Syntax error or unexpected behavior

Solutions:

Verify SQL is standard SQLite syntax:

# Test query in standard SQLite first
import sqlite3 as std_sqlite
test_conn = std_sqlite.connect(':memory:')
test_conn.execute('CREATE TABLE users (id INTEGER, name TEXT)')
test_conn.execute('INSERT INTO users VALUES (1, "test")')
result = test_conn.execute('SELECT * FROM users LIMIT 10 OFFSET 5')
print(result.fetchall())

Check for HeliosDB-Lite specific syntax:

# Some advanced features require enabling
conn = sqlite3.connect('app.db', enable_time_travel=True)

# Now time-travel syntax works
cursor.execute('''
    SELECT * FROM users
    AS OF TIMESTAMP '2024-01-01 10:00:00'
''')

Use parameterized queries:

# Instead of string formatting
# cursor.execute(f'SELECT * FROM users WHERE id = {user_id}')  # Bad

# Use parameters
cursor.execute('SELECT * FROM users WHERE id = ?', (user_id,))  # Good

Performance Problems¶

Problem: Queries are slower than SQLite¶

Symptoms:

# Query takes longer than expected
start = time.time()
cursor.execute('SELECT * FROM large_table')
results = cursor.fetchall()
print(f"Time: {time.time() - start:.2f}s")  # Slower than SQLite

Solutions:

Create appropriate indexes:

# Check if indexes exist
cursor.execute("SELECT name FROM sqlite_master WHERE type='index'")
print("Existing indexes:", cursor.fetchall())

# Create missing indexes
cursor.execute('CREATE INDEX IF NOT EXISTS idx_user_email ON users(email)')
cursor.execute('CREATE INDEX IF NOT EXISTS idx_post_date ON posts(created_at)')
conn.commit()

Enable query caching:

# Enable result caching for read-heavy workloads
conn = sqlite3.connect('app.db', enable_cache=True, cache_size_mb=100)

Use batch operations:

# Slow: individual inserts
for user in users:
    cursor.execute('INSERT INTO users VALUES (?, ?)', user)
    conn.commit()  # Don't commit each insert!

# Fast: batch with single commit
for user in users:
    cursor.execute('INSERT INTO users VALUES (?, ?)', user)
conn.commit()  # Single commit at end

# Fastest: executemany
cursor.executemany('INSERT INTO users VALUES (?, ?)', users)
conn.commit()

Analyze query plans:

# See how query is executed
cursor.execute('EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = ?', ('test@example.com',))
plan = cursor.fetchall()
print("Query plan:", plan)

# Look for "SCAN" (bad) vs "SEARCH" (good)
# If you see SCAN, you probably need an index

Adjust connection settings:

# Tune for performance
conn = sqlite3.connect(
    'app.db',
    isolation_level='DEFERRED',  # Less locking
    check_same_thread=False,     # Multi-threaded
    cached_statements=100,       # Cache prepared statements
    timeout=30.0                 # Longer timeout
)

# Set pragmas
conn.execute('PRAGMA journal_mode=WAL')
conn.execute('PRAGMA synchronous=NORMAL')
conn.execute('PRAGMA cache_size=10000')
conn.execute('PRAGMA temp_store=MEMORY')

Problem: High memory usage¶

Symptoms:

# Memory keeps growing
conn = sqlite3.connect('app.db')
# Memory usage increases over time

Solutions:

Close connections properly:

# Use context manager
with sqlite3.connect('app.db') as conn:
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users')
    results = cursor.fetchall()
# Connection automatically closed

# Or explicit close
conn = sqlite3.connect('app.db')
try:
    # ... do work ...
finally:
    conn.close()

Fetch data in chunks:

# Don't load all data at once
cursor.execute('SELECT * FROM large_table')

# Fetch in batches
batch_size = 1000
while True:
    rows = cursor.fetchmany(batch_size)
    if not rows:
        break
    process_rows(rows)

Limit cache size:

conn = sqlite3.connect('app.db', cache_size_mb=50)  # Limit cache to 50MB

Vacuum database:

# Reclaim unused space
conn.execute('VACUUM')

Data Migration Issues¶

Problem: Can't open existing SQLite database¶

Symptoms:

conn = sqlite3.connect('existing.db')
# Error: database disk image is malformed

Solutions:

Check database integrity:

# Use SQLite CLI to check
sqlite3 existing.db "PRAGMA integrity_check;"

Repair database:

# Export and reimport
sqlite3 existing.db ".dump" > backup.sql
sqlite3 new.db < backup.sql

# Then use new.db with HeliosDB-Lite

Check file permissions:

ls -la existing.db  # Should be readable/writable
chmod 644 existing.db

Problem: Data looks corrupted after switching to HeliosDB-Lite¶

Symptoms:

# Data appears incorrect or missing
cursor.execute('SELECT * FROM users')
results = cursor.fetchall()
# Results don't match expectations

Solutions:

Verify with SQLite first:

import sqlite3 as std_sqlite

# Check with standard SQLite
std_conn = std_sqlite.connect('app.db')
std_cursor = std_conn.cursor()
std_cursor.execute('SELECT COUNT(*) FROM users')
print(f"SQLite sees: {std_cursor.fetchone()[0]} users")

# Check with HeliosDB-Lite
import heliosdb_sqlite
hdb_conn = heliosdb_sqlite.connect('app.db')
hdb_cursor = hdb_conn.cursor()
hdb_cursor.execute('SELECT COUNT(*) FROM users')
print(f"HeliosDB-Lite sees: {hdb_cursor.fetchone()[0]} users")

Check transaction isolation:

# Ensure proper transaction handling
conn = sqlite3.connect('app.db', isolation_level='DEFERRED')
conn.execute('BEGIN')
cursor = conn.cursor()
cursor.execute('SELECT * FROM users')
results = cursor.fetchall()
conn.commit()

Flush caches:

# Force data to disk
conn.commit()
conn.execute('PRAGMA wal_checkpoint(FULL)')

Mode Switching Issues¶

Problem: Can't start server mode¶

Symptoms:

server = sqlite3.start_server('app.db', port=5432)
# Error: Address already in use

Solutions:

Check if port is in use:

# Linux/Mac
lsof -i :5432
netstat -tuln | grep 5432

# Windows
netstat -ano | findstr :5432

# Kill existing process or use different port

Use different port:

server = sqlite3.start_server('app.db', port=5433)  # Different port

Check permissions:

# Ensure you can bind to port (< 1024 needs root)
sudo python your_script.py  # If port < 1024

# Or use port > 1024
server = sqlite3.start_server('app.db', port=8432)

Problem: Hybrid mode not working¶

Symptoms:

conn = sqlite3.connect('app.db', heliosdb_mode='hybrid')
# Can't connect remotely even though server should be running

Solutions:

Check server info:

conn = sqlite3.connect('app.db', heliosdb_mode='hybrid')
info = conn.get_server_info()
print(f"Server running: {info.is_running}")
print(f"Port: {info.port}")
print(f"Host: {info.host}")

Check firewall:

# Linux
sudo ufw status
sudo ufw allow 5432/tcp

# Check if server is listening
netstat -tuln | grep 5432

Test connection:

# Try connecting with psql
psql -h localhost -p 5432 -U helios app

# Or with Python
import psycopg2
conn = psycopg2.connect(host='localhost', port=5432, database='app')

Connection and Threading Issues¶

Problem: "SQLite objects created in a thread can only be used in that same thread"¶

Symptoms:

conn = sqlite3.connect('app.db')
# Use connection from different thread
# Error: SQLite objects created in a thread can only be used in that same thread

Solutions:

Disable thread checking:

conn = sqlite3.connect('app.db', check_same_thread=False)

Use connection per thread:

import threading

thread_local = threading.local()

def get_connection():
    if not hasattr(thread_local, 'conn'):
        thread_local.conn = sqlite3.connect('app.db')
    return thread_local.conn

# Each thread gets its own connection
def worker():
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users')

Use connection pooling:

from heliosdb_sqlite import connection_pool

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

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

Problem: Concurrent write deadlocks¶

Symptoms:

# Multiple threads trying to write
# Some threads hang or timeout

Solutions:

HeliosDB-Lite should handle this, but if it happens:

# Increase timeout
conn = sqlite3.connect('app.db', timeout=30.0)

# Use retry logic
import time

def write_with_retry(query, params, max_retries=3):
    for attempt in range(max_retries):
        try:
            cursor.execute(query, params)
            conn.commit()
            return
        except sqlite3.OperationalError as e:
            if 'locked' in str(e) and attempt < max_retries - 1:
                time.sleep(0.1 * (2 ** attempt))  # Exponential backoff
            else:
                raise

Check if you're actually using HeliosDB-Lite:

import heliosdb_sqlite as sqlite3
print(f"Using: {sqlite3.__name__}")  # Should say 'heliosdb_sqlite'

Testing and Debugging Tips¶

Enable debug logging¶

import heliosdb_sqlite as sqlite3
import logging

# Enable debug logging
logging.basicConfig(level=logging.DEBUG)
sqlite3.enable_debug_logging()

# Now all database operations are logged
conn = sqlite3.connect('app.db')
cursor = conn.cursor()
cursor.execute('SELECT * FROM users')

Compare behavior with SQLite¶

import sqlite3 as std_sqlite
import heliosdb_sqlite as hdb_sqlite

def test_query(db_module, name):
    try:
        conn = db_module.connect(':memory:')
        cursor = conn.cursor()

        cursor.execute('CREATE TABLE test (id INTEGER, value TEXT)')
        cursor.execute('INSERT INTO test VALUES (1, "hello")')
        conn.commit()

        cursor.execute('SELECT * FROM test')
        result = cursor.fetchall()

        print(f"{name}: {result}")
        conn.close()
        return True
    except Exception as e:
        print(f"{name} failed: {e}")
        return False

# Test both
test_query(std_sqlite, "SQLite")
test_query(hdb_sqlite, "HeliosDB-Lite")

Isolate the problem¶

# Minimal reproducible example
import heliosdb_sqlite as sqlite3

# Test with in-memory database (simpler)
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# Test your exact failing query
try:
    cursor.execute('YOUR FAILING QUERY HERE')
    print("Query succeeded!")
except Exception as e:
    print(f"Query failed: {e}")
    import traceback
    traceback.print_exc()

Check HeliosDB-Lite version and capabilities¶

import heliosdb_sqlite as sqlite3

print(f"HeliosDB-Lite version: {sqlite3.__version__}")
print(f"SQLite version: {sqlite3.sqlite_version}")

# Check available features
conn = sqlite3.connect(':memory:')
features = conn.get_features()
print(f"Vector search: {features.vector_search}")
print(f"Time-travel: {features.time_travel}")
print(f"Branching: {features.branching}")
print(f"Encryption: {features.encryption}")

Generate diagnostic report¶

def generate_diagnostic_report():
    import heliosdb_sqlite as sqlite3
    import sys
    import platform

    print("=== HeliosDB-Lite Diagnostic Report ===\n")

    print(f"Python version: {sys.version}")
    print(f"Platform: {platform.platform()}")
    print(f"HeliosDB-Lite version: {sqlite3.__version__}")
    print(f"SQLite version: {sqlite3.sqlite_version}")

    print("\nTesting basic operations...")

    try:
        conn = sqlite3.connect(':memory:')
        cursor = conn.cursor()

        cursor.execute('CREATE TABLE test (id INTEGER)')
        cursor.execute('INSERT INTO test VALUES (1)')
        cursor.execute('SELECT * FROM test')
        result = cursor.fetchall()

        print(f"Basic operations: OK")
        print(f"Result: {result}")

        conn.close()
    except Exception as e:
        print(f"Basic operations: FAILED")
        print(f"Error: {e}")

    print("\n=== End Diagnostic Report ===")

# Run diagnostic
generate_diagnostic_report()

Getting Help¶

If you've tried these solutions and still have issues:

Check GitHub Issues: https://github.com/heliosdb/heliosdb-lite/issues
Ask on Stack Overflow: Tag with heliosdb
Join Discord Community: Real-time help from users
Email Support: support@heliosdb.io

When asking for help, include: - HeliosDB-Lite version (sqlite3.__version__) - Python version - Operating system - Minimal code that reproduces the issue - Full error message and traceback - What you've already tried

Common Error Messages¶

"database is locked"¶

Cause: Multiple processes trying to write simultaneously (shouldn't happen with HeliosDB-Lite!)

Fix:

# Verify you're actually using HeliosDB-Lite
import heliosdb_sqlite as sqlite3
print(sqlite3.__name__)  # Should be 'heliosdb_sqlite'

# If confirmed HeliosDB-Lite, increase timeout
conn = sqlite3.connect('app.db', timeout=30.0)

"no such table"¶

Cause: Table doesn't exist or transaction issues

Fix:

# List all tables
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
print("Available tables:", cursor.fetchall())

# Create table if missing
cursor.execute('CREATE TABLE IF NOT EXISTS users (id INTEGER, name TEXT)')

"disk I/O error"¶

Cause: Permissions, disk space, or hardware issues

Fix:

# Check disk space
df -h

# Check permissions
ls -la database.db

# Check disk health
# Linux: sudo smartctl -H /dev/sda
# Mac: diskutil verifyVolume /

Prevention Tips¶

Always use context managers:

with sqlite3.connect('app.db') as conn:
    # Work with database
    pass
# Automatically closed and committed

Use parameterized queries:

cursor.execute('SELECT * FROM users WHERE id = ?', (user_id,))  # Safe

Handle exceptions:

try:
    conn.execute('INSERT INTO users VALUES (?, ?)', (id, name))
    conn.commit()
except sqlite3.IntegrityError:
    print("Duplicate key")
    conn.rollback()
except Exception as e:
    print(f"Unexpected error: {e}")
    conn.rollback()
    raise

Test thoroughly:

# Test suite should cover
pytest tests/ -v --cov

# Include integration tests
# Include concurrent access tests
# Include migration tests

Monitor in production:

conn.enable_monitoring()
metrics = conn.get_metrics()
# Alert on anomalies

For more help: - HELIOSDB_SQLITE_DROP_IN_GUIDE.md - Getting started - HELIOSDB_SQLITE_FAQ.md - Common questions - HELIOSDB_SQLITE_ADVANCED_FEATURES.md - Advanced usage - GitHub Issues - Bug reports and discussions