HeliosDB Nano REPL Commands Reference

HeliosDB Nano REPL Commands Reference

This document provides a comprehensive reference for all REPL (Read-Eval-Print Loop) meta-commands available in HeliosDB Nano.

Getting Help

Command	Description
\h, \help, \?	Show main help with category list
\h <category>	Show help for specific category

Available categories: basics, schema, branching, time-travel, vectors, documents, agents, ai, settings, examples, sql

---

Basic Commands

Command	Description	Example
\q, \quit, \exit	Quit the REPL	\q
\timing	Toggle query timing display	\timing
\show lsn	Toggle LSN/transaction display	\show lsn
\show branch	Show current database branch	\show branch

---

Schema Exploration

Command	Description	Example
\d	List all tables	\d
\d <table>	Describe table schema	\d users
\dt	List tables with column counts	\dt
\dS	List system views	\dS
\dS <view>	Describe system view	\dS pg_database_branches
\compression	Show compression statistics	\compression
\compression <table>	Show compression for table	\compression orders

---

Database Branching

Command	Description	Example
\branches	List all database branches	\branches
\use <branch>	Switch to a branch	\use dev
\snapshots	List time-travel snapshots	\snapshots

SQL Branching Operations

-- Create a branch
CREATE DATABASE BRANCH dev FROM main AS OF NOW;

-- Merge branches
MERGE DATABASE BRANCH dev INTO main;

-- Drop a branch
DROP DATABASE BRANCH dev;

---

Time-Travel Queries

Query data as it existed at a specific point in time.

SQL Syntax

-- Query at specific timestamp
SELECT * FROM users AS OF TIMESTAMP '2025-11-15 14:30:00';

-- Query at specific transaction
SELECT * FROM orders AS OF TRANSACTION 12345;

-- Compare current vs historical data
SELECT
    current.*,
    historical.status as old_status
FROM orders current
JOIN orders AS OF TIMESTAMP '2025-11-01' historical
    ON current.id = historical.id
WHERE current.status != historical.status;

---

Materialized Views

Command	Description	Example
\dmv	List materialized views	\dmv
\dmv <view>	Describe materialized view	\dmv user_stats

SQL Operations

-- Create with auto-refresh
CREATE MATERIALIZED VIEW user_stats AS
SELECT user_id, COUNT(*) as orders
FROM orders GROUP BY user_id
WITH (auto_refresh = true, max_cpu_percent = 15);

-- Manual refresh
REFRESH MATERIALIZED VIEW user_stats;

-- Check staleness
SELECT * FROM pg_mv_staleness();

---

Vector Store Management

Command	Description	Example
\vectors	List all vector stores	\vectors
\vector <name>	Show vector store details	\vector embeddings
\vector create <name> <dims> [metric]	Create vector store	\vector create mystore 384 cosine
\vector delete <name>	Delete vector store	\vector delete mystore
\vector stats <name>	Show statistics	\vector stats embeddings

Metrics

• cosine - Cosine similarity (default)
• l2 - Euclidean distance
• inner_product - Dot product

SQL Vector Operations

-- Create table with vector column
CREATE TABLE embeddings (
    id SERIAL PRIMARY KEY,
    text TEXT NOT NULL,
    embedding VECTOR(384)
);

-- Create HNSW index
CREATE INDEX idx_embedding
ON embeddings USING hnsw(embedding)
WITH (m=16, ef_construction=200);

-- K-NN search
SELECT id, text, embedding <-> query_vec AS distance
FROM embeddings
ORDER BY embedding <-> '[0.1, 0.2, ...]'::VECTOR
LIMIT 10;

---

Document Collections (RAG)

Command	Description	Example
\collections	List all collections	\collections
\collection <name>	Show collection details	\collection rag-docs
\docs <collection>	List documents	\docs my-collection
\doc <collection> <id>	Get document details	\doc rag-docs doc123
\search-docs <query>	Search across collections	\search-docs machine learning

---

Agent Sessions

Command	Description	Example
\sessions	List all agent sessions	\sessions
\session-new <name>	Create new session	\session-new my-chat
\session <id>	Show session details	\session abc123
\session-delete <id>	Delete session	\session-delete abc123
\chat <id>	Interactive chat mode	\chat abc123
\session-clear <id>	Clear session messages	\session-clear abc123

---

AI Features

Command	Description	Example
\ai templates	List AI schema templates	\ai templates
\ai template <name>	Show template DDL	\ai template ecommerce
\ai infer [format]	Infer schema from data	\ai infer json
\ai generate <desc>	Generate schema from description	\ai generate "SaaS app"
\ai optimize <table>	AI optimization suggestions	\ai optimize users

Available Templates

• ecommerce - E-commerce with products, orders, customers
• blog - Blog with posts, comments, tags
• saas - Multi-tenant SaaS application
• rag - RAG with documents, chunks, embeddings
• agents - AI agent memory with sessions
• vector-store - Vector database schema
• analytics - Event tracking and analytics
• iot - IoT device data
• social - Social network schema
• crm - CRM with contacts and deals
• inventory - Inventory management
• chatbot - Chatbot conversations

---

Performance & Diagnostics

Command	Description	Example
\explain <query>	Show execution plan	\explain SELECT * FROM users
\profile <query>	Profile with timing	\profile SELECT COUNT(*) FROM orders
\telemetry	Database statistics	\telemetry
\stats	Quick statistics	\stats

EXPLAIN Output

\explain SELECT * FROM users WHERE id = 1;

Query Execution Plan:
──────────────────────────────────────────────────────────
Query: SELECT * FROM users WHERE id = 1

Plan:
  Index Scan on users_pkey
    Index Cond: (id = 1)
    Rows: 1 (estimated)

Profile Output

\profile SELECT * FROM users;

Query Profile:
──────────────────────────────────────────────────────────
Execution Metrics:
  Rows returned: 1000
  Total time: 5.234ms
  Time per row: 5.234µs

Resource Usage:
  Memory (est.): 100000 bytes

Sample Results (first 3 rows):
  [1] Int4(1), String("Alice"), ...
  [2] Int4(2), String("Bob"), ...
  [3] Int4(3), String("Charlie"), ...

---

Database Export & Backup

Command	Description	Example
\dump	Dump entire database to SQL file	\dump
\dump <file>	Dump database to specific file	\dump backup.sql

Usage Examples

-- Dump to default file (dump.sql)
\dump

-- Dump to custom file
\dump /backups/mydb_backup.sql

-- After exiting REPL, you can restore with:
-- heliosdb-nano restore --input backup.sql

Dump Output

The .dump command generates:

• CREATE TABLE statements for all tables
• Column definitions with types and constraints
• Primary key and NOT NULL constraints
• Row data from all tables
• Timestamp of when dump was created

Note: For in-memory mode persistence, use --dump-on-shutdown flag when starting REPL:

heliosdb-nano repl --memory --dump-on-shutdown --dump-file persistent_backup.sql

---

Configuration

Command	Description	Example
\config	Show current configuration	\config
\config reload	Reload from file	\config reload
\set	Show REPL variables	\set
\set <var> <val>	Set REPL variable	\set search_path public
\optimize <table>	Optimization recommendations	\optimize users
\indexes <table>	Index recommendations	\indexes orders

SQL Settings

SET optimizer = on;
SET statement_timeout = 30000;  -- milliseconds
SET work_mem = 8192;  -- KB
SHOW ALL;
SHOW optimizer;

---

Server & Security

Command	Description	Example
\server	Server status	\server
\server status	Detailed server status	\server status
\ssl status	SSL/TLS configuration	\ssl status
\user list	List users	\user list
\user add <name>	Add user	\user add alice
\user remove <name>	Remove user	\user remove alice
\password <user>	Change password	\password alice

---

System Views

Query system metadata using these functions:

-- Database branches
SELECT * FROM pg_database_branches();

-- Materialized view staleness
SELECT * FROM pg_mv_staleness();

-- Vector index statistics
SELECT * FROM pg_vector_index_stats();

---

Tips & Shortcuts

1. Multi-line input: SQL statements continue on the next line until you type ;
2. Cancel input: Press Ctrl-C to cancel current input
3. Exit REPL: Press Ctrl-D or type \q
4. Command history: Use up/down arrows to navigate history
5. Tab completion: Press Tab for table name completion

---

See Also

• Configuration Reference
• SQL Reference
• Vector Search Guide
• Branching & Time-Travel