---
title: "HeliosDB-Lite REPL Commands Reference"
---

# HeliosDB-Lite REPL Commands Reference

This page lists the REPL (Read-Eval-Print Loop) meta-commands available in HeliosDB-Lite.

## 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

```sql
-- 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

```sql
-- 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

```sql
-- Create
CREATE MATERIALIZED VIEW user_stats AS
SELECT user_id, COUNT(*) as orders
FROM orders GROUP BY user_id;

-- Refresh (automatic background refresh is planned)
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

```sql
-- 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

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

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

-- After exiting REPL, you can restore with:
-- heliosdb-lite 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:
```bash
heliosdb-lite 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>` | Prints EXPLAIN hints for the table; automatic recommendations are planned | `\optimize users` |
| `\indexes <table>` | Placeholder; automatic index recommendations are planned | `\indexes orders` |

### SQL Settings

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

---

## Server & Security

| Command | Description | Example |
|---------|-------------|---------|
| `\server` | Server state | `\server` |
| `\Server state` | Detailed Server state | `\Server state` |
| `\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:

```sql
-- 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](https://www.heliosdb.com/docs/lite/user-guide/configuration/)
- [SQL Reference](/docs/lite/user-guide/sql-reference/)
- [Vector Search Guide](/docs/lite/features/vector_search/)