HeliosDB-Lite SQLite Conversion Guide

Complete Guide to Transparent SQLite ↔ HeliosDB-Lite Conversion

Version: 1.0.0 Last Updated: December 8, 2025

---

Overview

HeliosDB-Lite provides transparent, automatic conversion between SQLite and HeliosDB-Lite file formats. This allows you to:

• Migrate existing SQLite databases to HeliosDB-Lite with zero code changes
• Export HeliosDB data back to SQLite for compatibility
• Maintain backward compatibility with SQLite-based applications
• Preserve complete data integrity during conversion

The conversion system handles: - Schema migration with intelligent type mapping - Data preservation with verification - Index and constraint conversion - Large file streaming for memory efficiency - Automatic rollback on conversion failures

---

Table of Contents

1. How Transparent Conversion Works
2. Automatic Conversion on First Connection
3. Manual Conversion
4. Type Mapping Reference
5. File Size Expectations
6. Performance and Timing
7. Recovery from Failures
8. Verification Procedures
9. Advanced Usage
10. Troubleshooting

---

How Transparent Conversion Works

Conversion Process

When you connect to a SQLite database file using HeliosDB-Lite, the system:

1. Detects the SQLite file format by checking the magic header (SQLite format 3)
2. Validates the SQLite database integrity using PRAGMA integrity_check
3. Analyzes the schema, extracting tables, columns, indexes, and constraints
4. Maps SQLite types to equivalent HeliosDB-Lite types
5. Creates a new HeliosDB-Lite database with the converted schema
6. Transfers data using streaming for memory efficiency
7. Verifies data integrity by comparing row counts and checksums
8. Returns a connection to the new HeliosDB-Lite database

The entire process is automatic and transparent - you don't need to change your code.

Data Integrity Guarantees

The converter provides the following guarantees:

• Atomicity: Conversion is all-or-nothing (rollback on failure)
• Consistency: Schema and data constraints are preserved
• Durability: Converted data is persisted to disk with fsync
• Verification: Row counts and checksums are validated post-conversion

---

Automatic Conversion on First Connection

Python API (Recommended)

from pathlib import Path
from tools.HELIOSDB_SQLITE_CONVERTER import TransparentConverter

# Connect to SQLite database - automatically converts to HeliosDB
file_path = Path("my_database.sqlite")
success, connection, messages = TransparentConverter.connect_with_auto_conversion(file_path)

if success:
    print("Database connected (converted if needed)")
    for msg in messages:
        print(f"  {msg}")
else:
    print("Connection failed")
    for msg in messages:
        print(f"  ERROR: {msg}")

What Happens Behind the Scenes

Input:  my_database.sqlite (SQLite file)
                ↓
        [Detect SQLite Format]
                ↓
        [Validate Integrity]
                ↓
        [Extract Schema & Data]
                ↓
        [Convert to HeliosDB]
                ↓
Output: my_database.heliosdb/ (HeliosDB directory)
        my_database.sqlite (preserved for backup)

First Connection Behavior

First time connecting:

Detected SQLite database: my_database.sqlite
Converting to HeliosDB format (one-time operation)...
Progress: 25.0% - users (1000/4000 rows)
Progress: 50.0% - products (2000/4000 rows)
Progress: 75.0% - orders (3000/4000 rows)
Progress: 100.0% - order_items (4000/4000 rows)
Conversion completed: my_database.heliosdb
Original SQLite file preserved: my_database.sqlite

Subsequent connections:

Opening existing HeliosDB database: my_database.heliosdb

---

Manual Conversion

Command-Line Interface

For batch conversions or scripts, use the CLI:

# Basic conversion
python3 tools/HELIOSDB_SQLITE_CONVERTER.py \
    my_database.sqlite \
    my_database.heliosdb

# Streaming mode (recommended for large files)
python3 tools/HELIOSDB_SQLITE_CONVERTER.py \
    large_database.sqlite \
    large_database.heliosdb \
    --mode streaming

# Bulk mode (faster for small-medium databases)
python3 tools/HELIOSDB_SQLITE_CONVERTER.py \
    small_database.sqlite \
    small_database.heliosdb \
    --mode bulk

# Row-by-row mode (safest, slowest)
python3 tools/HELIOSDB_SQLITE_CONVERTER.py \
    critical_database.sqlite \
    critical_database.heliosdb \
    --mode row_by_row

# Skip verification (not recommended)
python3 tools/HELIOSDB_SQLITE_CONVERTER.py \
    my_database.sqlite \
    my_database.heliosdb \
    --no-verify

# Verbose output for debugging
python3 tools/HELIOSDB_SQLITE_CONVERTER.py \
    my_database.sqlite \
    my_database.heliosdb \
    --verbose

Python Programmatic API

from pathlib import Path
from tools.HELIOSDB_SQLITE_CONVERTER import (
    SQLiteToHeliosDBConverter,
    ConversionMode
)

# Create converter
converter = SQLiteToHeliosDBConverter(
    sqlite_path=Path("my_database.sqlite"),
    heliosdb_path=Path("my_database.heliosdb"),
    mode=ConversionMode.STREAMING,
    verify_integrity=True
)

# Define progress callback
def on_progress(progress):
    pct = progress.progress_percentage()
    print(f"Progress: {pct:.1f}% - {progress.current_table}")

# Run conversion
success = converter.convert(progress_callback=on_progress)

if success:
    print(f"Converted {converter.progress.converted_tables} tables")
    print(f"Total rows: {converter.progress.converted_rows}")
    print(f"Time: {converter.progress.elapsed_time():.2f}s")
else:
    print("Conversion failed!")
    for error in converter.progress.errors:
        print(f"  ERROR: {error}")

---

Type Mapping Reference

SQLite → HeliosDB Type Conversions

SQLite Type	HeliosDB Type	Notes
INTEGER	INT8	64-bit signed integer
INT	INT4	32-bit signed integer
TINYINT	INT2	16-bit signed integer
SMALLINT	INT2	16-bit signed integer
BIGINT	INT8	64-bit signed integer
TEXT	TEXT	Unlimited length
VARCHAR(n)	VARCHAR(n)	Preserves length
CHAR(n)	CHAR(n)	Preserves length
REAL	FLOAT8	64-bit float
FLOAT	FLOAT4	32-bit float
DOUBLE	FLOAT8	64-bit float
NUMERIC	NUMERIC	Arbitrary precision
DECIMAL(p,s)	FLOAT8	⚠️ Precision may be lost
BOOLEAN	BOOLEAN	Native boolean
DATE	DATE	Native date type
DATETIME	TIMESTAMP	Timestamp without timezone
TIMESTAMP	TIMESTAMP	Timestamp without timezone
BLOB	BYTEA	Binary data
UUID	UUID	Native UUID type
JSON	JSONB	Binary JSON

HeliosDB → SQLite Type Conversions (Export)

HeliosDB Type	SQLite Type	Notes
BOOLEAN	INTEGER	Stored as 0/1
INT2	SMALLINT	16-bit integer
INT4	INTEGER	32-bit integer
INT8	BIGINT	64-bit integer
FLOAT4	REAL	32-bit float
FLOAT8	REAL	64-bit float
NUMERIC	NUMERIC	Arbitrary precision
VARCHAR(n)	TEXT	⚠️ Length not enforced
TEXT	TEXT	Unlimited length
CHAR(n)	TEXT	⚠️ Padding not enforced
BYTEA	BLOB	Binary data
DATE	TEXT	ℹ️ ISO8601 format
TIME	TEXT	ℹ️ ISO8601 format
TIMESTAMP	TEXT	ℹ️ ISO8601 format
TIMESTAMPTZ	TEXT	ℹ️ ISO8601 format
UUID	TEXT	String representation
JSON	TEXT	JSON string
JSONB	TEXT	JSON string
VECTOR(n)	TEXT	ℹ️ Stored as JSON array
ARRAY	TEXT	ℹ️ Stored as JSON array

Conversion Warnings

The converter generates warnings for lossy conversions:

⚠️ DECIMAL → FLOAT8: Arbitrary precision is lost. Use NUMERIC in HeliosDB if precision is critical.

ℹ️ DATE/TIME → TEXT: SQLite doesn't have native date/time types. Values are stored as ISO8601 strings.

ℹ️ VECTOR/ARRAY → TEXT: Complex types are serialized as JSON for SQLite compatibility.

---

File Size Expectations

Storage Overhead

HeliosDB-Lite uses RocksDB (LSM-tree) for storage, which has different characteristics than SQLite (B-tree):

Database Size	SQLite File	HeliosDB Directory	Overhead
1 MB	1 MB	1.2-1.5 MB	+20-50%
100 MB	100 MB	120-150 MB	+20-50%
1 GB	1 GB	1.2-1.5 GB	+20-50%
10 GB	10 GB	12-15 GB	+20-50%

Why the overhead?

• Write-Ahead Log (WAL): For durability
• Memtable snapshots: For MVCC transactions
• LSM-tree levels: Compaction creates temporary files
• Metadata: Indexes, statistics, branch data

Disk Space Requirements

During conversion: - SQLite file (original): 100% - HeliosDB directory (new): 120-150% - Temporary files: 10-20%

Total required: ~230-270% of original database size

After conversion: - Original SQLite file can be archived or deleted - HeliosDB directory will compact over time (background process)

Compaction

HeliosDB automatically compacts the database in the background:

# Manual compaction (if needed)
heliosdb-lite repl --data-dir my_database.heliosdb
> VACUUM;

---

Performance and Timing

Conversion Speed

Database Size	Mode	Rows/Second	Est. Time
1 MB (10K rows)	Bulk	50,000	<1 second
100 MB (1M rows)	Streaming	25,000	~40 seconds
1 GB (10M rows)	Streaming	20,000	~8 minutes
10 GB (100M rows)	Streaming	15,000	~110 minutes

Factors affecting speed: - Disk I/O performance (SSD vs HDD) - Number of indexes to rebuild - Data types (BLOB/TEXT slower than INTEGER) - CPU cores (parallel index building)

Conversion Modes

Streaming (Recommended for most cases): - Memory usage: ~50-100 MB constant - Speed: Fast - Safety: High - Best for: Files >100 MB

Bulk (Good for small-medium databases): - Memory usage: ~5-10x database size - Speed: Fastest - Safety: High - Best for: Files <100 MB

Row-by-row (Safest for critical data): - Memory usage: ~10-20 MB constant - Speed: Slowest - Safety: Highest (transaction per row) - Best for: Critical data, corrupted databases

Progress Reporting

The converter provides real-time progress updates:

Progress: 25.0% - users (1000/4000 rows), ETA: 45.2s
Progress: 50.0% - products (2000/4000 rows), ETA: 30.1s
Progress: 75.0% - orders (3000/4000 rows), ETA: 15.0s
Progress: 100.0% - order_items (4000/4000 rows)

Conversion completed successfully!
  Converted: 4 tables
  Total rows: 4000
  Time: 60.3s

---

Recovery from Conversion Failures

Automatic Rollback

If conversion fails, the system automatically:

1. Logs the error with detailed context
2. Rolls back partial changes
3. Deletes the incomplete HeliosDB directory
4. Preserves the original SQLite file (untouched)

Common Failure Scenarios

Disk space exhausted:

ERROR: No space left on device
Rolling back conversion...
Removed HeliosDB directory: my_database.heliosdb

Schema conversion error:

ERROR: Unsupported constraint: FOREIGN KEY ON DELETE CASCADE
Rolling back conversion...

Data integrity check failed:

ERROR: Row count mismatch for table 'users': SQLite=1000, HeliosDB=999
Rolling back conversion...

Manual Recovery

If automatic rollback fails:

# 1. Remove incomplete HeliosDB directory
rm -rf my_database.heliosdb

# 2. Verify SQLite file integrity
sqlite3 my_database.sqlite "PRAGMA integrity_check;"

# 3. Retry conversion with verbose logging
python3 tools/HELIOSDB_SQLITE_CONVERTER.py \
    my_database.sqlite \
    my_database.heliosdb \
    --verbose

---

Verification Procedures

Automatic Verification

The converter automatically verifies:

1. Row counts for each table
2. Schema integrity (columns, types, constraints)
3. Index presence and uniqueness constraints
4. Data checksums (SHA-256 hash of table contents)

Manual Verification

After conversion, you can manually verify:

# 1. Connect to HeliosDB
heliosdb-lite repl --data-dir my_database.heliosdb

# 2. Check table row counts
> SELECT
    table_name,
    (SELECT COUNT(*) FROM table_name) AS row_count
  FROM information_schema.tables
  WHERE table_schema = 'public';

# 3. Verify specific table data
> SELECT COUNT(*) FROM users;
> SELECT * FROM users LIMIT 10;

# 4. Check indexes
> SELECT * FROM pg_indexes WHERE schemaname = 'public';

Checksum Verification

from tools.HELIOSDB_SQLITE_CONVERTER import DataIntegrityVerifier
import sqlite3

# Compare checksums
sqlite_conn = sqlite3.connect("my_database.sqlite")
checksum_sqlite = DataIntegrityVerifier.calculate_table_checksum(
    sqlite_conn, "users"
)

heliosdb_conn = # ... connect to HeliosDB
checksum_helios = DataIntegrityVerifier.calculate_table_checksum(
    heliosdb_conn, "users"
)

assert checksum_sqlite == checksum_helios, "Checksum mismatch!"

---

Advanced Usage

Custom Type Mappings

Register custom type conversions:

from tools.HELIOSDB_SQLITE_TYPE_MAPPER import custom_type_registry

# Define custom converter
def convert_custom_point(value, from_type, to_type):
    """Convert custom POINT type to HeliosDB VECTOR."""
    if isinstance(value, str):
        # Parse "POINT(x, y)" format
        coords = value.replace("POINT(", "").replace(")", "").split(",")
        return [float(x.strip()) for x in coords]
    return value

# Register custom mapping
custom_type_registry.register_custom_type(
    sqlite_type="POINT",
    heliosdb_type="VECTOR(2)",
    converter=convert_custom_point
)

Batch Conversion

Convert multiple databases:

from pathlib import Path
from tools.HELIOSDB_SQLITE_CONVERTER import SQLiteToHeliosDBConverter, ConversionMode

databases = [
    "database1.sqlite",
    "database2.sqlite",
    "database3.sqlite"
]

for db_file in databases:
    sqlite_path = Path(db_file)
    heliosdb_path = Path(f"{sqlite_path.stem}.heliosdb")

    converter = SQLiteToHeliosDBConverter(
        sqlite_path=sqlite_path,
        heliosdb_path=heliosdb_path,
        mode=ConversionMode.STREAMING
    )

    print(f"Converting {db_file}...")
    success = converter.convert()

    if success:
        print(f"  ✓ Completed in {converter.progress.elapsed_time():.2f}s")
    else:
        print(f"  ✗ Failed: {converter.progress.errors}")

Export HeliosDB to SQLite

Reverse conversion (HeliosDB → SQLite):

# Coming in future version
from tools.HELIOSDB_SQLITE_CONVERTER import HeliosDBToSQLiteConverter

exporter = HeliosDBToSQLiteConverter(
    heliosdb_path=Path("my_database.heliosdb"),
    sqlite_path=Path("exported.sqlite")
)

success = exporter.export()

---

Troubleshooting

Issue: "Not a valid SQLite file"

Cause: File is corrupted or not a SQLite database

Solution:

# Check file type
file my_database.sqlite

# Try to open with SQLite CLI
sqlite3 my_database.sqlite ".schema"

# Run integrity check
sqlite3 my_database.sqlite "PRAGMA integrity_check;"

Issue: "Conversion is very slow"

Cause: Large database, many indexes, or slow disk

Solution:

# Use streaming mode
python3 tools/HELIOSDB_SQLITE_CONVERTER.py \
    large.sqlite large.heliosdb --mode streaming

# Disable verification for initial conversion
python3 tools/HELIOSDB_SQLITE_CONVERTER.py \
    large.sqlite large.heliosdb --no-verify

# Convert to SSD if possible

Issue: "Row count mismatch after conversion"

Cause: Data corruption or conversion bug

Solution:

# Try row-by-row mode (slowest but safest)
python3 tools/HELIOSDB_SQLITE_CONVERTER.py \
    my.sqlite my.heliosdb --mode row_by_row --verbose

# Check for corruption in SQLite
sqlite3 my.sqlite "PRAGMA integrity_check;"

Issue: "Out of memory during conversion"

Cause: Using bulk mode on large database

Solution:

# Switch to streaming mode
python3 tools/HELIOSDB_SQLITE_CONVERTER.py \
    large.sqlite large.heliosdb --mode streaming

# Or increase system memory/swap

Getting Help

If you encounter issues:

1. Check logs: Converter writes detailed logs to stderr
2. Run with --verbose: Get debug-level output
3. File an issue: https://github.com/heliosdb/heliosdb-lite/issues
4. Contact support: support@heliosdb.com

---

Summary

The HeliosDB-Lite SQLite converter provides:

✅ Transparent automatic conversion on first connection ✅ Complete data integrity with verification ✅ Intelligent type mapping with warnings ✅ Memory-efficient streaming for large files ✅ Automatic rollback on failures ✅ Progress reporting and ETA estimation ✅ Backward compatibility with SQLite export

Next Steps:

• See Quick Start Guide for database usage
• Read Type System Documentation for type details
• Explore API Reference for programmatic usage

---

Document Version: 1.0.0 Last Updated: December 8, 2025 Maintainer: HeliosDB Team