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.

-- INNER JOIN
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;

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 New

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';

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();

VERSIONS BETWEEN

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

-- Get all versions of a user record between two timestamps
SELECT
    id,
    name,
    email,
    VERSIONS_STARTTIME AS version_start,
    VERSIONS_ENDTIME AS version_end,
    VERSIONS_OPERATION AS operation
FROM users
VERSIONS BETWEEN TIMESTAMP
    '2026-01-01 00:00:00' AND '2026-01-02 00:00:00'
WHERE id = 1;

-- Get all changes between two SCNs
SELECT * FROM orders
VERSIONS BETWEEN SCN 1000 AND 2000;

Version Metadata Columns

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;

Database Branching New

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

CREATE DATABASE BRANCH

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

-- Create a branch at current state
CREATE DATABASE BRANCH dev_branch
FROM production_db;

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

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

-- Create a branch with a description
CREATE DATABASE BRANCH feature_xyz
FROM production_db
WITH (description = 'Testing feature XYZ implementation');

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 FROM production_db"
  }'

List Branches

-- List all branches of a database
SELECT * FROM DATABASE_BRANCHES('production_db');

-- Get branch metadata
SELECT
    branch_name,
    parent_database,
    created_at,
    branch_point_scn,
    storage_size_bytes
FROM DATABASE_BRANCHES('production_db');

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 parent database.

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

-- Merge with conflict resolution strategy
MERGE DATABASE BRANCH feature_branch
INTO production_db
WITH (
    on_conflict = 'source_wins'  -- or 'target_wins', 'abort'
);

-- Preview merge (dry run)
MERGE DATABASE BRANCH dev_branch
INTO production_db
WITH (dry_run = TRUE);

Merge Conflict Resolution Options

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;

Use Cases

Example Workflow

-- 1. Create a feature branch
CREATE DATABASE BRANCH feature_new_schema
FROM production_db;

-- 2. Apply schema changes on branch
ALTER TABLE feature_new_schema.users
ADD COLUMN phone VARCHAR(20);

-- 3. Run tests against branch
SELECT * FROM feature_new_schema.users LIMIT 10;

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

-- 5. Clean up
DROP DATABASE BRANCH feature_new_schema;

Vector Operations New

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
);

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 (most common for text embeddings)
SELECT
    id,
    title,
    COSINE_SIMILARITY(embedding, '[0.1, 0.2, ...]'::VECTOR) AS similarity
FROM documents
ORDER BY similarity DESC
LIMIT 10;

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

-- Inner product (dot product)
SELECT
    id,
    title,
    INNER_PRODUCT(embedding, '[0.1, 0.2, ...]'::VECTOR) AS score
FROM documents
ORDER BY score DESC
LIMIT 10;

Distance Functions

Vector Indexes

Create indexes for faster similarity search on large datasets.

-- Create an HNSW index for fast approximate nearest neighbor search
CREATE INDEX documents_embedding_idx
ON documents
USING HNSW(embedding)
WITH (
    m = 16,           -- Number of connections per layer
    ef_construction = 64  -- Size of dynamic candidate list
);

-- Create an IVFFlat index (faster to build, slightly less accurate)
CREATE INDEX documents_embedding_ivf
ON documents
USING IVFFLAT(embedding)
WITH (lists = 100);

Vector Operators

-- Add vectors
SELECT embedding + '[0.1, 0.1, ...]'::VECTOR FROM documents;

-- Subtract vectors
SELECT embedding - '[0.1, 0.1, ...]'::VECTOR FROM documents;

-- Scalar multiplication
SELECT embedding * 2.0 FROM documents;

-- Get vector dimension
SELECT VECTOR_DIMS(embedding) FROM documents;

-- Normalize vector
SELECT VECTOR_NORM(embedding) FROM documents;

Use Cases

Example: Semantic Search Pipeline

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

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

-- 3. Search for relevant documents
SELECT
    id,
    content,
    COSINE_SIMILARITY(embedding, $1) AS relevance
FROM knowledge_base
WHERE COSINE_SIMILARITY(embedding, $1) > 0.7
ORDER BY relevance DESC
LIMIT 5;

Encryption Coming Soon

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

Planned Features

• Transparent Data Encryption (TDE) - Encrypt data automatically with zero code changes
• 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

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