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¶
- Installation
- 5-Minute Quick Start
- Common Migration Patterns
- Connection String Changes
- Before/After Code Examples
- Testing Your Migration
- Troubleshooting Common Issues
- Performance Expectations vs. SQLite
- Enabling Advanced Features (Quick Reference)
- Vector Search & RAG
- Database Branching
- Time-Travel Queries
- Transparent Data Encryption (TDE)
- Server Mode
- Hybrid Mode
- Performance Tuning
- What's Next?
Installation¶
Option 1: Direct Replacement (Recommended for Quick Start)¶
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:
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:
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:
→ 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:
- Vector Search - Add semantic search to your application
-
See:
HELIOSDB_SQLITE_ADVANCED_FEATURES.md -
Database Branching - Create experimental branches
-
Example:
conn.create_branch('experiment') -
Time-Travel Queries - Query historical data
-
Example:
SELECT * FROM users AS OF TIMESTAMP '2024-01-01' -
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