HeliosDB Nano SQLite Feature Fallback Guide

Version: 3.0.1 Last Updated: 2025-12-08 Author: HeliosDB Team

Overview

HeliosDB Nano is designed as a PostgreSQL-compatible embedded database with advanced features like vector search, time-travel queries, and MVCC transactions. While it supports most PostgreSQL features, some database capabilities require fallback strategies or application-level implementation.

This guide provides comprehensive information about:

Supported vs. unsupported features
Fallback strategies for each limitation
Performance implications
How to enable/disable fallbacks
Migration path to full HeliosDB features

Feature Support Matrix

Feature	Support Level	Fallback Strategy	Performance Impact
DECIMAL/NUMERIC	Fallback Available	Convert to FLOAT8	Minimal
TRIGGER	Not Supported	Application-level	Depends on implementation
CHECK Constraint	Fallback Available	Application validation	Minimal
AUTOINCREMENT	Fallback Available	UUID or manual sequence	Minimal
SERIAL	Partial Support	INT4/INT8 with explicit values	None
FOREIGN KEY	Fallback Available	Application enforcement	Depends on validation approach
JSONB Operators	✅ Fully Supported	N/A - Native support	Optimized with GIN index
Vector Search	✅ Fully Supported	N/A - Native HNSW	Optimized
Time Travel Queries	✅ Fully Supported	N/A - Native MVCC	Minimal overhead
CTE (WITH clause)	✅ Fully Supported	N/A - Native support	Optimized
Materialized Views	✅ Fully Supported	N/A - Native support	Optimized refresh
Branching	✅ Fully Supported	N/A - Native support	Copy-on-write

Detailed Feature Fallbacks

1. DECIMAL/NUMERIC Type Conversion

Problem

DECIMAL and NUMERIC types provide arbitrary precision arithmetic, but HeliosDB Nano currently uses FLOAT8 (64-bit floating point) which has limited precision.

Fallback Strategy

Automatic conversion to FLOAT8 with precision warning.

-- Original SQL
CREATE TABLE accounts (
    id INT4,
    balance DECIMAL(10, 2)
);

-- Processed SQL (automatic fallback)
CREATE TABLE accounts (
    id INT4,
    balance FLOAT8  -- Warning: Precision loss possible
);

Precision Loss Examples

Safe Range: ±1.7E±308 (FLOAT8 range)
Precision: ~15-17 decimal digits
Issue: Binary representation can’t exactly represent decimal fractions

# Example precision loss
0.1 + 0.2 == 0.3  # False in binary floating point!

Alternative: String Storage for Exact Decimals

For extreme precision requirements (scientific calculations):

CREATE TABLE measurements (
    id INT4,
    value TEXT,  -- Store as string "123.456789012345"
    precision INT2
);

Performance Impact

FLOAT8: Native CPU operations, fastest
INT8 (cents): Native operations, equally fast
TEXT: Requires parsing, slower but exact

2. TRIGGER - Application-Level Implementation

Problem

Database triggers (BEFORE/AFTER INSERT/UPDATE/DELETE) are not supported in HeliosDB Nano v3.0.

Fallback Strategy

Implement trigger logic in application layer using ORM hooks, middleware, or event listeners.

Examples

Django ORM Signals

from django.db.models.signals import pre_save, post_save
from django.dispatch import receiver

@receiver(pre_save, sender=Account)
def validate_balance(sender, instance, **kwargs):
    """Equivalent to BEFORE INSERT/UPDATE trigger"""
    if instance.balance < 0:
        raise ValueError("Balance cannot be negative")

@receiver(post_save, sender=Account)
def log_balance_change(sender, instance, created, **kwargs):
    """Equivalent to AFTER INSERT/UPDATE trigger"""
    if not created:  # UPDATE
        AuditLog.objects.create(
            table='accounts',
            operation='UPDATE',
            record_id=instance.id
        )

SQLAlchemy Events

from sqlalchemy import event
from sqlalchemy.orm import Session

@event.listens_for(Account, 'before_insert')
def validate_before_insert(mapper, connection, target):
    """BEFORE INSERT trigger equivalent"""
    if target.balance < 0:
        raise ValueError("Balance cannot be negative")

@event.listens_for(Account, 'after_update')
def log_after_update(mapper, connection, target):
    """AFTER UPDATE trigger equivalent"""
    AuditLog.__table__.insert().values(
        table='accounts',
        operation='UPDATE',
        record_id=target.id
    ).execute(connection)

Rust Application (Native HeliosDB)

use heliosdb_nano::EmbeddedDatabase;

struct AccountTrigger;

impl AccountTrigger {
    fn before_insert(&self, account: &Account) -> Result<(), Error> {
        // Validation logic
        if account.balance < 0.0 {
            return Err(Error::validation("Balance cannot be negative"));
        }
        Ok(())
    }

    fn after_update(&self, account: &Account) -> Result<(), Error> {
        // Audit logging
        db.execute(
            "INSERT INTO audit_log (table, operation, record_id) VALUES ($1, $2, $3)",
            &["accounts", "UPDATE", &account.id.to_string()]
        )?;
        Ok(())
    }
}

Best Practices

Consistency: Ensure all entry points (API, CLI, batch jobs) apply same logic
Testing: Write comprehensive tests for trigger logic
Documentation: Document trigger behavior in application code
Transactions: Use database transactions to maintain atomicity
Error Handling: Provide clear error messages when validation fails

Performance Impact

Application-level triggers add latency per operation
Batch operations can optimize by validating once
Consider async processing for audit logging

3. CHECK Constraints - Application Validation

Problem

CHECK constraints enforce data integrity at the database level, but require application-level implementation in HeliosDB Nano.

Fallback Strategy

Implement validation in application layer before INSERT/UPDATE operations.

Examples

Pydantic Models (Python)

from pydantic import BaseModel, validator, Field

class Account(BaseModel):
    id: int
    balance: float
    status: str = Field(..., regex="^(active|inactive|suspended)$")

    @validator('balance')
    def validate_balance(cls, v):
        if v < 0:
            raise ValueError('Balance must be non-negative')
        return v

    @validator('status')
    def validate_status(cls, v):
        allowed = {'active', 'inactive', 'suspended'}
        if v not in allowed:
            raise ValueError(f'Status must be one of {allowed}')
        return v

# Usage
try:
    account = Account(id=1, balance=-100, status='active')
except ValidationError as e:
    print(e.json())  # Clear error message

Django Model Validators

from django.db import models
from django.core.validators import MinValueValidator, RegexValidator

class Account(models.Model):
    balance = models.FloatField(
        validators=[MinValueValidator(0.0, message="Balance cannot be negative")]
    )
    status = models.CharField(
        max_length=20,
        validators=[
            RegexValidator(
                regex=r'^(active|inactive|suspended)$',
                message='Invalid status'
            )
        ]
    )

    def clean(self):
        """Custom validation (CHECK constraint equivalent)"""
        if self.balance < 0:
            raise ValidationError('Balance cannot be negative')
        if self.status not in ('active', 'inactive', 'suspended'):
            raise ValidationError('Invalid status')

Database Documentation

-- Document CHECK constraints in table comments
CREATE TABLE accounts (
    id INT4 PRIMARY KEY,
    balance FLOAT8,  -- CHECK: balance >= 0
    status TEXT  -- CHECK: status IN ('active', 'inactive', 'suspended')
);

COMMENT ON COLUMN accounts.balance IS 'Balance in dollars. Validation: balance >= 0';
COMMENT ON COLUMN accounts.status IS 'Account status. Validation: status IN (active, inactive, suspended)';

Best Practices

Validate Early: Check constraints at API entry points
Centralize Validation: Use shared validation functions/classes
Document Constraints: Keep validation logic documented in code
Test Thoroughly: Write tests for boundary conditions
Fail Fast: Return clear error messages immediately

Performance Impact

Minimal if validation is efficient
Consider caching validation results for repeated operations
Batch validation can optimize throughput

4. AUTOINCREMENT - UUID Alternative

Problem

AUTOINCREMENT (SQLite-style) requires sequence coordination, which can be challenging in distributed systems.

Fallback Strategies

Strategy 1: UUID (Recommended)

-- Use UUID for primary keys
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT NOT NULL
);

-- Insert with automatic UUID generation
INSERT INTO users (name) VALUES ('Alice');  -- id generated automatically

UUID Versions:

UUID4: Random, cryptographically secure (recommended for most use cases)
UUID7: Time-ordered, better for index performance and sorting

Advantages:

✅ Guaranteed uniqueness across distributed systems
✅ No coordination or locking required
✅ Secure (UUID4 is cryptographically random)
✅ Works offline (no database round-trip)

Disadvantages:

❌ Larger storage (16 bytes vs 4/8 bytes for INT)
❌ Slightly slower index performance (mitigated with UUID7)
❌ Not human-readable

Strategy 2: SERIAL (Partial Support)

-- HeliosDB Nano supports SERIAL as INT4
CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- Converted to INT4
    name TEXT NOT NULL
);

Note: SERIAL in HeliosDB Nano requires explicit value insertion or application-level sequence management.

Strategy 3: Manual Sequence (Advanced)

def get_next_id(table_name: str) -> int:
    """Generate next ID with transaction safety"""
    with db.begin():
        result = db.query(f"SELECT MAX(id) FROM {table_name}")
        max_id = result[0].values[0] if result else 0
        next_id = (max_id or 0) + 1
        return next_id

# Usage
user_id = get_next_id('users')
db.execute(
    "INSERT INTO users (id, name) VALUES ($1, $2)",
    [Value::Int4(user_id), Value::String("Alice".to_string())]
)

Warning: This approach requires careful locking in concurrent environments.

Performance Comparison

Strategy	Insert Time	Storage Size	Index Performance	Uniqueness Guarantee
UUID4	Fast (no DB lookup)	16 bytes	Good	Global
UUID7	Fast (no DB lookup)	16 bytes	Better (time-ordered)	Global
SERIAL	Fast (with sequence)	4 bytes	Best	Per-table
Manual Sequence	Slow (requires MAX lookup)	4-8 bytes	Best	Requires locking

Recommendation

Use UUID7 for most applications:

Global uniqueness
Time-ordered for better index performance
No coordination required

5. Foreign Key Constraints - Application Enforcement

Problem

Foreign key constraints enforce referential integrity at the database level, but require application-level implementation in HeliosDB Nano.

Fallback Strategy

Validate foreign key references in application code before INSERT/UPDATE operations.

Examples

Python Validation Function

def validate_foreign_key(
    db: EmbeddedDatabase,
    parent_table: str,
    parent_column: str,
    child_value: Any
) -> bool:
    """Validate that foreign key reference exists"""
    sql = f"SELECT COUNT(*) FROM {parent_table} WHERE {parent_column} = $1"
    result = db.query_params(sql, [child_value])

    count = result[0].values[0]
    return count > 0

# Usage in INSERT
user_id = 123
if not validate_foreign_key(db, 'users', 'id', user_id):
    raise ValueError(f"User {user_id} does not exist")

db.execute(
    "INSERT INTO orders (user_id, amount) VALUES ($1, $2)",
    [Value::Int4(user_id), Value::Float8(99.99)]
)

Transactional Safety

def insert_order_with_validation(db, user_id, amount):
    """Insert order with foreign key validation and transaction safety"""
    with db.begin():
        # Validate foreign key within transaction
        user_exists = db.query_params(
            "SELECT 1 FROM users WHERE id = $1",
            [Value::Int4(user_id)]
        )

        if not user_exists:
            raise ValueError(f"User {user_id} not found")

        # Insert order
        db.execute(
            "INSERT INTO orders (user_id, amount) VALUES ($1, $2)",
            [Value::Int4(user_id), Value::Float8(amount)]
        )

        db.commit()  # Atomically commit both operations

CASCADE DELETE Implementation

def delete_user_with_cascade(db, user_id):
    """Delete user and cascade to dependent tables"""
    with db.begin():
        # Delete dependent records first
        db.execute("DELETE FROM orders WHERE user_id = $1", [user_id])
        db.execute("DELETE FROM sessions WHERE user_id = $1", [user_id])

        # Then delete parent record
        db.execute("DELETE FROM users WHERE id = $1", [user_id])

        db.commit()

Best Practices

Always Use Transactions: Ensure atomicity of validation + insert/update
Add Indexes: Index foreign key columns for fast validation queries
Batch Validation: For bulk inserts, validate all references once
Document Relationships: Comment foreign key relationships in schema
Test Edge Cases: Test orphaned records, circular references, etc.

Performance Optimization

# Batch validation for bulk inserts
def validate_foreign_keys_batch(db, table, column, values):
    """Validate multiple foreign key values in one query"""
    placeholders = ', '.join(['$' + str(i+1) for i in range(len(values))])
    sql = f"SELECT {column} FROM {table} WHERE {column} IN ({placeholders})"

    existing = db.query_params(sql, values)
    existing_set = {row.values[0] for row in existing}

    # Find missing references
    missing = [v for v in values if v not in existing_set]
    if missing:
        raise ValueError(f"Foreign key references not found: {missing}")

Enabling/Disabling Fallbacks

Python API Usage

from HELIOSDB_SQLITE_FEATURE_HANDLER import HeliosDBFeatureHandler

# Enable fallbacks with warnings (default)
handler = HeliosDBFeatureHandler(
    enable_fallbacks=True,
    warn_on_fallback=True,
    strict_mode=False
)

# Process SQL with automatic fallbacks
processed_sql = handler.process_sql(
    "CREATE TABLE accounts (balance DECIMAL(10,2))"
)
# Result: "CREATE TABLE accounts (balance FLOAT8)"

# Strict mode - reject unsupported features
strict_handler = HeliosDBFeatureHandler(strict_mode=True)
try:
    strict_handler.process_sql("CREATE TRIGGER ...")
except ValueError as e:
    print(e)  # "TRIGGER not supported in strict mode"

# Silent mode - no warnings
silent_handler = HeliosDBFeatureHandler(
    enable_fallbacks=True,
    warn_on_fallback=False
)

Configuration File

fallback:
  enabled: true
  warn_on_fallback: true
  strict_mode: false

  features:
    DECIMAL:
      strategy: "type_conversion"
      target_type: "FLOAT8"
    TRIGGER:
      strategy: "application_level"
    CHECK_CONSTRAINT:
      strategy: "application_level"
    FOREIGN_KEY:
      strategy: "application_level"

Migration Path to Full Features

Phase 1: Current (v3.0.1)

✅ Core SQL functionality
✅ Vector search
✅ Time-travel queries
✅ JSONB support
⚠️ DECIMAL → FLOAT8 fallback
⚠️ Application-level constraints

Phase 2: Planned (v3.1)

🚧 Native DECIMAL/NUMERIC support (arbitrary precision)
🚧 CHECK constraint enforcement
🚧 Foreign key enforcement (optional)

Phase 3: Future (v3.2+)

🔮 Database-level triggers (optional)
🔮 Stored procedures
🔮 Full PostgreSQL compatibility (98%+)

Performance Implications

Feature	Fallback Strategy	Performance Impact	Mitigation
DECIMAL → FLOAT8	Type conversion	None - Native CPU operations	Use INT8 for exact decimals
TRIGGER	Application-level	Low to Medium - Depends on implementation	Batch operations, async logging
CHECK Constraint	Application validation	Minimal - Fast validation	Cache validation results
AUTOINCREMENT → UUID	UUID generation	Minimal - No DB lookup	Use UUID7 for better index performance
Foreign Key	Application validation	Low to Medium - Extra queries	Add indexes, batch validation

Optimization Tips

Batch Operations: Validate multiple records in single query
Index Foreign Keys: Always index columns used in foreign key validation
Cache Validation: Cache valid reference IDs for frequently accessed data
Async Logging: Use async queues for audit logging (trigger replacement)
Transaction Batching: Group multiple inserts in single transaction

Security Considerations

Critical: Application-Level Enforcement

When using fallback strategies, ensure all entry points enforce constraints:

Web API endpoints
CLI tools
Batch import scripts
Database migrations
Admin interfaces

Checklist

All entry points validate data before INSERT/UPDATE
Foreign key references are validated within transactions
CHECK constraint logic is tested for boundary conditions
Trigger-equivalent logic is applied consistently
Validation errors provide clear, actionable messages
Security-sensitive operations are audited
Concurrent access is properly synchronized

Example: Secure Foreign Key Validation

def secure_insert_order(db, user_id, amount):
    """Secure order insertion with proper validation"""
    # 1. Validate input
    if amount <= 0:
        raise ValueError("Amount must be positive")

    # 2. Use transaction for atomicity
    with db.begin():
        # 3. Validate foreign key with SELECT FOR UPDATE (if supported)
        user = db.query_params(
            "SELECT id FROM users WHERE id = $1",  # Add FOR UPDATE in production
            [user_id]
        )

        if not user:
            raise ValueError(f"User {user_id} not found")

        # 4. Insert with validated data
        db.execute(
            "INSERT INTO orders (user_id, amount, created_at) VALUES ($1, $2, NOW())",
            [user_id, amount]
        )

        # 5. Commit atomically
        db.commit()

Troubleshooting

Issue: Precision Loss with DECIMAL

Solution: Use INT8 for monetary values (store cents/pennies)

-- Instead of: balance DECIMAL(10, 2)
-- Use: balance_cents INT8 (stores $123.45 as 12345 cents)

Issue: Trigger Logic Not Applied

Solution: Ensure all entry points call trigger-equivalent functions

# Centralize trigger logic
def before_insert_account(account):
    if account.balance < 0:
        raise ValueError("Invalid balance")

# Call from all entry points
@app.post("/accounts")
def create_account(account: Account):
    before_insert_account(account)  # Trigger equivalent
    db.insert(account)

Issue: Foreign Key Violations

Solution: Validate within transaction, add indexes

# Add index for fast validation
db.execute("CREATE INDEX idx_users_id ON users(id)")

# Validate within transaction
with db.begin():
    if not validate_fk(db, 'users', 'id', user_id):
        raise ValueError("Invalid user_id")
    db.insert(order)

Additional Resources

Documentation: https://heliosdb.dev/docs/fallback-strategies
API Reference: https://heliosdb.dev/api/feature-handler
Examples: https://github.com/dimensigon/HDB-HeliosDB-Nano/tree/main/examples
Community: https://discord.gg/heliosdb

Summary

HeliosDB Nano provides a PostgreSQL-compatible embedded database with intelligent fallback mechanisms for features not yet supported. By following the guidance in this document, you can:

Automatically handle type conversions (DECIMAL → FLOAT8)
Implement application-level constraints (CHECK, FOREIGN KEY)
Replace triggers with ORM hooks (consistent validation)
Use UUID for primary keys (better than AUTOINCREMENT)
Maintain data integrity with proper validation and transactions

The fallback strategies are designed to be transparent, well-documented, and production-ready. As HeliosDB Nano evolves, more features will gain native support, reducing the need for fallbacks.

Next Steps:

Review your schema for unsupported features
Enable fallback handler in your application
Implement application-level validation for constraints
Add comprehensive tests for edge cases
Monitor fallback usage with logging and metrics

For questions or issues, please open an issue on GitHub or join our Discord community.

HeliosDB Nano SQLite Feature Fallback Guide

HeliosDB Nano SQLite Feature Fallback Guide

Overview

Feature Support Matrix

Detailed Feature Fallbacks

1. DECIMAL/NUMERIC Type Conversion

Problem

Fallback Strategy

Precision Loss Examples

Recommended Solution

Alternative: String Storage for Exact Decimals

Performance Impact

2. TRIGGER - Application-Level Implementation

Problem

Fallback Strategy

Examples

Django ORM Signals

SQLAlchemy Events

Rust Application (Native HeliosDB)

Best Practices

Performance Impact

3. CHECK Constraints - Application Validation

Problem

Fallback Strategy

Examples

Pydantic Models (Python)

Django Model Validators

Database Documentation

Best Practices

Performance Impact

4. AUTOINCREMENT - UUID Alternative

Problem

Fallback Strategies

Strategy 1: UUID (Recommended)

Strategy 2: SERIAL (Partial Support)

Strategy 3: Manual Sequence (Advanced)

Performance Comparison

Recommendation

5. Foreign Key Constraints - Application Enforcement

Problem

Fallback Strategy

Examples

Python Validation Function

Transactional Safety

CASCADE DELETE Implementation

Best Practices

Performance Optimization

Enabling/Disabling Fallbacks

Python API Usage

Configuration File

Migration Path to Full Features

Phase 1: Current (v3.0.1)

Phase 2: Planned (v3.1)

Phase 3: Future (v3.2+)

Performance Implications

Optimization Tips

Security Considerations

Critical: Application-Level Enforcement

Checklist

Example: Secure Foreign Key Validation

Troubleshooting

Issue: Precision Loss with DECIMAL

Issue: Trigger Logic Not Applied

Issue: Foreign Key Violations

Additional Resources

Summary