API Documentation

SQL API

Execute standard SQL operations against your databases using the execute endpoint.

Execute SQL Query

CREATE TABLE

Create a new table with columns and constraints.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(200) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'active'
);

cURL Example

curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/execute" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "CREATE TABLE users (id INTEGER PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(200) UNIQUE)"
  }'

INSERT

Insert single or multiple rows into a table.

-- Single row insert
INSERT INTO users (id, name, email)
VALUES (1, 'John Doe', 'john@example.com');

-- Multiple rows insert
INSERT INTO users (id, name, email) VALUES
    (2, 'Jane Smith', 'jane@example.com'),
    (3, 'Bob Wilson', 'bob@example.com'),
    (4, 'Alice Brown', 'alice@example.com');

cURL Example

curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/execute" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sql": "INSERT INTO users (id, name, email) VALUES (1, '\''John Doe'\'', '\''john@example.com'\'')"}'

SELECT

Query data with filtering, sorting, and pagination.

-- Basic select
SELECT * FROM users;

-- With WHERE clause
SELECT id, name, email
FROM users
WHERE status = 'active' AND created_at > '2026-01-01';

-- With ORDER BY and LIMIT
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

-- Aggregations with GROUP BY
SELECT status, COUNT(*) AS count
FROM users
GROUP BY status
HAVING COUNT(*) > 5;

Response Example

{
  "success": true,
  "data": {
    "columns": ["id", "name", "email", "created_at"],
    "rows": [
      [1, "John Doe", "john@example.com", "2026-01-01T00:00:00Z"],
      [2, "Jane Smith", "jane@example.com", "2026-01-02T00:00:00Z"]
    ],
    "row_count": 2,
    "execution_time_ms": 5
  }
}

UPDATE

Modify existing rows in a table.

UPDATE users
SET email = 'newemail@example.com',
    status = 'updated'
WHERE id = 1;

DELETE

Remove rows from a table.

DELETE FROM users
WHERE status = 'inactive'
  AND created_at < '2025-01-01';

JOINs

Combine data from multiple tables. Supports INNER JOIN, LEFT JOIN, RIGHT JOIN, and CROSS JOIN with table aliases and qualified column names.

-- INNER JOIN with table aliases
SELECT u.name, o.product, o.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

-- LEFT JOIN
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Multi-table JOIN
SELECT p.name AS product, c.name AS category
FROM products p
JOIN product_categories pc ON p.id = pc.product_id
JOIN categories c ON pc.category_id = c.id
ORDER BY p.name;

-- CROSS JOIN (cartesian product)
SELECT * FROM colors CROSS JOIN sizes;

cURL Example - JOIN Query

curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/query" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT p.name, c.name FROM products p JOIN product_categories pc ON p.id = pc.product_id JOIN categories c ON pc.category_id = c.id"}'

Subqueries

SELECT * FROM users
WHERE id IN (
    SELECT DISTINCT user_id
    FROM orders
    WHERE price > 100
);

Parameterized Queries

curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/query" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT * FROM users WHERE email = $1 AND status = $2",
    "params": ["john@example.com", "active"]
  }'

Time-Travel Queries

HeliosDB-Lite maintains a complete history of all changes, allowing you to query data as it existed at any point in time.

AS OF TIMESTAMP

Query data as it existed at a specific timestamp.

-- Query users table as of a specific timestamp
SELECT * FROM users
AS OF TIMESTAMP '2026-01-01 12:00:00';

-- Query with timezone
SELECT * FROM users
AS OF TIMESTAMP '2026-01-01 12:00:00 UTC';

-- Query using interval (1 hour ago)
SELECT * FROM users
AS OF TIMESTAMP (CURRENT_TIMESTAMP - INTERVAL '1 hour');

cURL Example

curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/execute" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT * FROM users AS OF TIMESTAMP '\''2026-01-01 12:00:00'\''"
  }'

AS OF TRANSACTION

Query data as it existed after a specific transaction completed.

-- Query data after transaction ID 12345
SELECT * FROM orders
AS OF TRANSACTION 12345;

-- Useful for auditing specific changes
SELECT id, amount, status
FROM payments
AS OF TRANSACTION 98765
WHERE status = 'processed';

cURL Example

curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/execute" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT * FROM orders AS OF TRANSACTION 12345"
  }'

AS OF SCN (System Change Number)

Query data at a specific system change number for precise point-in-time recovery.

-- Query at a specific SCN
SELECT * FROM inventory
AS OF SCN 1000000;

-- Get current SCN
SELECT CURRENT_SCN();

cURL Example

curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/execute" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT * FROM inventory AS OF SCN 1000000"
  }'

# Get current SCN
curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/execute" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT CURRENT_SCN()"}'

VERSIONS BETWEEN

Query all versions of rows that existed between two points in time.

-- Get all versions of a product between two timestamps
SELECT * FROM products
VERSIONS BETWEEN TIMESTAMP '2026-01-01 00:00:00'
    AND TIMESTAMP '2026-01-02 00:00:00'
WHERE id = 1;

-- Returns all versions of the row including:
-- - Original inserted values
-- - Each update that occurred in the time range

cURL Example - VERSIONS BETWEEN

curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/query" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT * FROM products VERSIONS BETWEEN TIMESTAMP '\''2026-01-01 00:00:00'\'' AND TIMESTAMP '\''2026-01-02 00:00:00'\'' WHERE id = 1"}'

Use Cases

Restoring Data

Use time-travel queries with INSERT to restore deleted data:

-- Restore accidentally deleted user
INSERT INTO users (id, name, email, created_at)
SELECT id, name, email, created_at
FROM users AS OF TIMESTAMP '2026-01-01 11:59:00'
WHERE id = 42;

cURL Example

curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/execute" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "INSERT INTO users (id, name, email) SELECT id, name, email FROM users AS OF TIMESTAMP '\''2026-01-01 11:59:00'\'' WHERE id = 42"
  }'

Database Branching

Create isolated copies of your database for development, testing, or experimentation without duplicating storage.

CREATE DATABASE BRANCH

Create a new branch from the current database at a specific point in time.

-- Create a branch at current state
CREATE DATABASE BRANCH dev_branch
AS OF NOW;

-- Create a branch at a specific timestamp
CREATE DATABASE BRANCH testing_branch
AS OF TIMESTAMP '2026-01-01 00:00:00';

-- Create a branch at a specific SCN
CREATE DATABASE BRANCH debug_branch
AS OF SCN 50000;

-- Create a branch at a specific transaction
CREATE DATABASE BRANCH feature_xyz
AS OF TRANSACTION 12345;

cURL Example

curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/execute" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "CREATE DATABASE BRANCH dev_branch AS OF NOW"
  }'

List Branches

-- List branches via REST API
GET /api/v1/organizations/{org_id}/databases

cURL Example

# List all databases including branches
curl -X GET "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases" \
  -H "Authorization: Bearer YOUR_TOKEN"

Switch to Branch

After creating a branch, it appears as a separate database that you can query independently.

-- Query the branch database
SELECT * FROM dev_branch.users;

-- Or connect to the branch database directly via the API
-- using the branch database ID

MERGE DATABASE BRANCH

Merge changes from a branch back into the main database.

-- Merge branch into main (fast-forward if possible)
MERGE DATABASE BRANCH dev_branch
INTO main;

-- Merge feature branch into main
MERGE DATABASE BRANCH feature_branch
INTO main;

cURL Example

# Merge branch into main
curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/execute" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "MERGE DATABASE BRANCH dev_branch INTO main"
  }'

Delete Branch

-- Delete a branch (frees copy-on-write storage)
DROP DATABASE BRANCH dev_branch;

-- Force delete (even if branch has uncommitted changes)
DROP DATABASE BRANCH dev_branch FORCE;

cURL Example

curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/execute" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "DROP DATABASE BRANCH dev_branch"
  }'

Use Cases

Example Workflow

-- 1. Create a feature branch at current state
CREATE DATABASE BRANCH feature_new_schema
AS OF NOW;

-- 2. Make changes on the branch
INSERT INTO users (id, name) VALUES (100, 'Test User');

-- 3. Test the changes
SELECT * FROM users LIMIT 10;

-- 4. If tests pass, merge back into main
MERGE DATABASE BRANCH feature_new_schema
INTO main;

-- 5. Clean up
DROP DATABASE BRANCH feature_new_schema;

Vector Operations

Native support for vector embeddings with built-in similarity search for AI/ML applications.

VECTOR Type

Store high-dimensional vectors for embeddings, feature vectors, and more.

-- Create a table with a vector column
CREATE TABLE documents (
    id INTEGER PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    embedding VECTOR(1536)  -- OpenAI ada-002 dimension
);

-- Create table for image embeddings
CREATE TABLE images (
    id INTEGER PRIMARY KEY,
    filename VARCHAR(255),
    embedding VECTOR(512)  -- CLIP embedding dimension
);

cURL Example

curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/execute" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "CREATE TABLE documents (id INTEGER PRIMARY KEY, title VARCHAR(200), content TEXT, embedding VECTOR(1536))"
  }'

Insert Vectors

-- Insert a vector as an array
INSERT INTO documents (id, title, embedding)
VALUES (
    1,
    'Introduction to AI',
    '[0.1, 0.2, 0.3, ...]'::VECTOR(1536)
);

-- Insert using array syntax
INSERT INTO documents (id, title, embedding)
VALUES (
    2,
    'Machine Learning Basics',
    ARRAY[0.15, 0.25, 0.35, ...]
);

cURL Example

curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/execute" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "INSERT INTO documents (id, title, embedding) VALUES (1, '\''Test Doc'\'', '\''[0.1, 0.2, 0.3, 0.4, 0.5]'\''::VECTOR(5))"
  }'

Similarity Search

Find similar vectors using built-in distance functions.

-- Cosine similarity search (higher is more similar)
SELECT
    id,
    title,
    COSINE_SIMILARITY(embedding, '[0.1, 0.2, ...]') AS similarity
FROM documents
ORDER BY similarity DESC
LIMIT 10;

-- Euclidean distance search (lower is more similar)
SELECT
    id,
    title,
    L2_DISTANCE(embedding, '[0.1, 0.2, ...]') AS distance
FROM documents
ORDER BY distance ASC
LIMIT 10;

-- Using vector operators
SELECT id, title, embedding <-> '[0.1, 0.2, ...]' AS l2_dist FROM documents;  -- L2 distance
SELECT id, title, embedding <=> '[0.1, 0.2, ...]' AS cos_dist FROM documents;  -- Cosine distance

cURL Example - Similarity Search

# Search for similar documents using cosine similarity
curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/execute" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT id, title, COSINE_SIMILARITY(embedding, '\''[0.1, 0.2, 0.3, 0.4, 0.5]'\'') AS similarity FROM documents ORDER BY similarity DESC LIMIT 10"
  }'

Distance Functions

Vector Operators

Vector Indexes

Create indexes for faster similarity search on large datasets.

-- HNSW index (SQLite style)
CREATE INDEX documents_embedding_idx
ON documents(embedding) USING hnsw;

-- HNSW index (PostgreSQL/pgvector style)
CREATE INDEX documents_embedding_idx
ON documents USING hnsw(embedding vector_l2_ops);

-- IVFFLAT index
CREATE INDEX documents_embedding_ivf
ON documents USING ivfflat(embedding vector_cosine_ops);

cURL Example - Create Vector Index

curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/execute" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "CREATE INDEX documents_embedding_idx ON documents(embedding) USING hnsw"
  }'

Use Cases

Example: Semantic Search Pipeline

-- 1. Create documents table with embeddings
CREATE TABLE knowledge_base (
    id INTEGER PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536)
);

-- 2. Create HNSW index for fast similarity search
CREATE INDEX kb_embedding_idx ON knowledge_base(embedding) USING hnsw;

-- 3. Insert documents with embeddings
INSERT INTO knowledge_base (id, content, embedding)
VALUES (
    1,
    'HeliosDB supports time-travel queries',
    '[0.1, 0.2, 0.3, ...]'
);

-- 4. Search for similar documents
SELECT
    id,
    content,
    COSINE_SIMILARITY(embedding, '[0.1, 0.2, ...]') AS relevance
FROM knowledge_base
ORDER BY relevance DESC
LIMIT 5;

Complete RAG Pipeline cURL Example

# 1. Create knowledge base table
curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/execute" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sql": "CREATE TABLE knowledge_base (id INTEGER PRIMARY KEY, content TEXT, embedding VECTOR(5))"}'

# 2. Create HNSW index for fast search
curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/execute" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sql": "CREATE INDEX kb_embedding_idx ON knowledge_base(embedding) USING hnsw"}'

# 3. Insert document with embedding (embedding from your ML model)
curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/execute" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "INSERT INTO knowledge_base (id, content, embedding) VALUES (1, '\''HeliosDB supports time-travel queries'\'', '\''[0.1, 0.2, 0.3, 0.4, 0.5]'\'')"
  }'

# 4. Search for relevant documents
curl -X POST "https://cloud.heliosdb.com/api/v1/organizations/{org_id}/databases/{db_id}/execute" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT id, content, COSINE_SIMILARITY(embedding, '\''[0.1, 0.2, 0.3, 0.4, 0.5]'\'') AS relevance FROM knowledge_base ORDER BY relevance DESC LIMIT 5"
  }'

Encryption

HeliosDB-Lite provides multiple encryption modes to protect your data at rest and in transit.

Encryption Modes

Planned SQL Syntax

-- Create an encrypted database
CREATE DATABASE secure_db
WITH (
    encryption = 'AES-256',
    key_management = 'managed'  -- or 'customer-managed'
);

-- Create a table with encrypted columns
CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(200),
    ssn VARCHAR(11) ENCRYPTED,
    credit_card VARCHAR(20) ENCRYPTED
);

-- Enable encryption on existing table
ALTER TABLE users
SET (encryption = TRUE);

-- Encrypt specific column
ALTER TABLE users
ALTER COLUMN ssn SET ENCRYPTED;

Key Management

Available Features

• Transparent Data Encryption (TDE) - Available - Encrypt data automatically with zero code changes using AES-256-GCM
• Password-Based Key Derivation - Available - Derive encryption keys from passwords using Argon2

Planned Features

• Key Rotation - Automatic key rotation with configurable schedules
• Audit Logging - Track all encryption key access and operations
• Searchable Encryption - Query encrypted data without decrypting (limited operations)
• Envelope Encryption - Data keys encrypted by master keys for enhanced security
• Zero-Knowledge Encryption - Client-side encryption where the server never sees plaintext

Compliance

Encryption features are designed to help meet compliance requirements for:

• GDPR (General Data Protection Regulation)
• HIPAA (Health Insurance Portability and Accountability Act)
• PCI DSS (Payment Card Industry Data Security Standard)
• SOC 2 Type II
• ISO 27001