HeliosDB vs SQLite

SQLite is the most deployed database in the world for good reason -- it's simple, reliable, and requires zero configuration. HeliosDB-Lite builds on this foundation while adding the features modern applications need.

---

Quick Comparison

Feature	SQLite	HeliosDB-Lite
Deployment	Single file	Single file
Configuration	Zero-config	Zero-config
SQL Dialect	SQLite-specific	PostgreSQL-compatible
Write concurrency	Single writer	Multiple writers (MVCC)
Vector search	No	Native HNSW
Encryption at rest	Extension (SEE)	Built-in TDE
Server mode	No	Yes
Database branching	No	Yes
Time-travel queries	No	Yes
JSONB support	JSON only	Full JSONB
Row-level security	No	Yes
Window functions	Basic	Full PostgreSQL

---

What You Keep

Both databases offer:

• Single-file database -- Easy backup, deployment, and management
• Zero configuration -- Works out of the box
• ACID transactions -- Data integrity guaranteed
• Cross-platform -- Windows, macOS, Linux
• Embeddable -- Library, not a separate server
• No dependencies -- Self-contained

---

What You Gain

1. PostgreSQL SQL Compatibility

Write SQL that works everywhere:

-- SQLite-specific syntax
INSERT OR REPLACE INTO users (id, name) VALUES (1, 'Alice');
SELECT json_extract(data, '$.email') FROM users;

-- HeliosDB-Lite uses PostgreSQL syntax
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;
SELECT data->>'email' FROM users;

Your existing PostgreSQL knowledge transfers directly.

2. Concurrent Write Access

SQLite uses file locking that blocks concurrent writes:

SQLite:
Writer 1: BEGIN -> INSERT -> [Writer 2 BLOCKED] -> COMMIT
Writer 2:                                        -> BEGIN -> INSERT -> COMMIT

HeliosDB-Lite (MVCC):
Writer 1: BEGIN -> INSERT -> COMMIT
Writer 2:     BEGIN -> INSERT -> COMMIT  (runs concurrently)

3. Native Vector Search

-- HeliosDB-Lite: Built-in vector support
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding VECTOR(768)
);

SELECT * FROM documents
ORDER BY embedding <=> $query_vector
LIMIT 10;

-- SQLite: Requires external extension or manual implementation
-- with significant complexity

4. Server Mode

# Start as a network server when needed
heliosdb-lite start --port 5432

# Connect with any PostgreSQL client
psql -h localhost -p 5432 -d myapp

Share the same database across multiple applications without file locking issues.

5. Database Branching

-- Test schema changes safely
CREATE BRANCH testing FROM main;
USE BRANCH testing;

ALTER TABLE users ADD COLUMN preferences JSONB;
-- Test your changes...

-- If everything works, merge
MERGE BRANCH testing INTO main;

6. Time-Travel Queries

-- What did the data look like yesterday?
SELECT * FROM orders
AS OF TIMESTAMP NOW() - INTERVAL '1 day'
WHERE customer_id = 42;

-- Recover deleted data without backups
INSERT INTO orders
SELECT * FROM orders
AS OF TIMESTAMP '2026-01-25 10:00:00'
WHERE id = 123;

7. Built-in Encryption

# Enable with configuration
[encryption]
enabled = true
algorithm = "AES-256-GCM"

No extensions needed, FIPS 140-3 compliant.

---

Migration Guide

Schema Migration

Most SQLite schemas work with minimal changes:

-- SQLite
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT UNIQUE,
    data TEXT,  -- JSON stored as TEXT
    created_at TEXT DEFAULT (datetime('now'))
);

-- HeliosDB-Lite
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE,
    data JSONB,  -- Native JSONB
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Data Migration

# Export from SQLite
sqlite3 myapp.db ".dump" > export.sql

# Transform and import
heliosdb-lite import --from sqlite --input export.sql --database myapp.db

Code Changes

SQLite	HeliosDB-Lite
sqlite3.connect()	heliosdb.connect()
? placeholders	$1, $2, $3 placeholders
INSERT OR REPLACE	ON CONFLICT DO UPDATE
IFNULL()	COALESCE()
json_extract()	-> and ->> operators

---

When to Stay with SQLite

SQLite might be better if you:

• Need the absolute smallest binary size
• Are building for extremely constrained environments (< 1MB RAM)
• Require compatibility with existing SQLite tooling
• Have no need for PostgreSQL compatibility

---

Performance Comparison

Write Performance

Operation	SQLite	HeliosDB-Lite
Single INSERT	~50K/s	~45K/s
Bulk INSERT	~100K/s	~700K/s
Concurrent writes	Blocked	~80K/s per writer

Read Performance

Operation	SQLite	HeliosDB-Lite
Point lookup	~500K/s	~450K/s
Range scan	~1M/s	~1.2M/s (columnar)
Aggregation	~500K/s	~2M/s (SIMD)
Vector search	N/A	~3K QPS

---

Example: Note-Taking App

With SQLite

import sqlite3
import json

db = sqlite3.connect('notes.db')
db.execute('''
    CREATE TABLE IF NOT EXISTS notes (
        id INTEGER PRIMARY KEY,
        title TEXT,
        content TEXT,
        tags TEXT,  -- JSON array as text
        created_at TEXT
    )
''')

def add_note(title, content, tags):
    db.execute(
        'INSERT INTO notes (title, content, tags, created_at) VALUES (?, ?, ?, datetime("now"))',
        (title, content, json.dumps(tags))
    )

def search_notes(query):
    # Full-text search requires FTS5 extension
    return db.execute(
        'SELECT * FROM notes WHERE content LIKE ?',
        (f'%{query}%',)
    ).fetchall()

With HeliosDB-Lite

import heliosdb
from sentence_transformers import SentenceTransformer

model = SentenceTransformer('all-MiniLM-L6-v2')
db = heliosdb.connect('notes.db')

db.execute('''
    CREATE TABLE IF NOT EXISTS notes (
        id SERIAL PRIMARY KEY,
        title TEXT,
        content TEXT,
        tags TEXT[],
        embedding VECTOR(384),
        created_at TIMESTAMPTZ DEFAULT NOW()
    )
''')

def add_note(title, content, tags):
    embedding = model.encode(content)
    db.execute(
        '''INSERT INTO notes (title, content, tags, embedding)
           VALUES ($1, $2, $3, $4)''',
        (title, content, tags, embedding)
    )

def search_notes(query):
    # Semantic search using embeddings
    embedding = model.encode(query)
    return db.query(
        '''SELECT title, content, embedding <=> $1 AS relevance
           FROM notes
           ORDER BY relevance
           LIMIT 10''',
        (embedding,)
    )

def search_by_tag(tag):
    # Native array containment
    return db.query(
        'SELECT * FROM notes WHERE $1 = ANY(tags)',
        (tag,)
    )

---

Getting Started

# Install
cargo install heliosdb-lite

# Migrate existing SQLite database
heliosdb-lite migrate --from sqlite --input old.db --output new.db

# Or start fresh
heliosdb-lite repl --memory