HeliosDB Nano REPL Guide

HeliosDB Nano REPL Guide

Interactive command-line interface for HeliosDB Nano, inspired by PostgreSQL’s psql and SQLite’s shell.

Quick Start

Default Mode (Uses ./heliosdb-data)

# Automatically uses ./heliosdb-data directory
heliosdb-nano repl

In-Memory Mode (No Persistence)

heliosdb-nano repl --memory

Custom Data Directory

heliosdb-nano repl --data-dir ./mydb
# or
heliosdb-nano repl -d ./mydb

Features

Multi-Line SQL Support

SQL statements can span multiple lines. The REPL waits for a semicolon (;) before executing:

heliosdb> CREATE TABLE users (
       ->   id INT PRIMARY KEY,
       ->   name TEXT NOT NULL,
       ->   email TEXT
       -> );
Query OK (0.002s)

Command History

• Use ↑/↓ arrow keys to navigate command history
• History is persisted across sessions in .heliosdb_history
• Search history with Ctrl-R (reverse search)

Auto-Completion

Tab completion for:

• SQL keywords (SELECT, FROM, WHERE, etc.)
• Table names
• Column names (context-aware)
• Functions

Example:

heliosdb> SEL<TAB>
  → SELECT

heliosdb> SELECT * FROM us<TAB>
  → SELECT * FROM users

Meta Commands

Meta commands start with backslash (\) and provide database introspection and control.

Database Information

\d - List All Tables

heliosdb> \d

Tables:
  customers
  orders
  products

\d <table> - Describe Table Schema

heliosdb> \d users

Table: users
──────────────────────────────────────────────────
Column               Type            Nullable   Primary Key
──────────────────────────────────────────────────
id                   Int4            NO         YES
name                 Text            NO
email                Text            YES
created_at           Timestamp       NO

\dt - List Tables with Details

heliosdb> \dt

Tables:
────────────────────────────────────────────────────────────
Name                           Columns
────────────────────────────────────────────────────────────
customers                      5
orders                         7
products                       4

Control Commands

\h or \help - Show Help

heliosdb> \h

HeliosDB Nano REPL Commands
════════════════════════════════════════════════════════════

Meta Commands:
  \q, \quit, \exit  - Quit the REPL
  \h, \help, \?     - Show this help
  \d                - List all tables
  \d <table>        - Describe table schema
  \dt               - List tables with details
  \timing           - Toggle query timing
  \e, \edit         - Edit query in $EDITOR (not yet implemented)

SQL Commands:
  End SQL statements with semicolon (;)
  Multi-line statements are supported
  Press Ctrl-C to cancel current input
  Press Ctrl-D to exit

Examples:
  CREATE TABLE users (id INT, name TEXT);
  INSERT INTO users VALUES (1, 'Alice');
  SELECT * FROM users;
  \d users

\timing - Toggle Query Timing

heliosdb> \timing
Timing is on

heliosdb> SELECT * FROM users;
...
(0.015s)

heliosdb> \timing
Timing is off

\q, \quit, or \exit - Quit REPL

heliosdb> \q
Goodbye!

\dump [FILE] - Dump Database to SQL File

Export the entire database to a SQL dump file for backup or migration.

Basic Usage:

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

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

Output Includes:

• Database metadata (creation timestamp)
• CREATE TABLE statements for all tables
• Column definitions with types and constraints
• Primary key and NOT NULL constraints
• All data rows in INSERT format

Example Output:

-- HeliosDB Nano Database Dump
-- Generated: 2025-12-09 14:23:45
-- Database: heliosdb-nano

-- Table: users
CREATE TABLE IF NOT EXISTS users (
  id Int4 PRIMARY KEY NOT NULL,
  name Text NOT NULL,
  email Text
);

-- Table: orders
CREATE TABLE IF NOT EXISTS orders (
  id Int4 PRIMARY KEY NOT NULL,
  user_id Int4 NOT NULL,
  amount Numeric
);

For In-Memory Mode Persistence:

To automatically dump on exit from in-memory REPL:

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

Then restore in another session:

# Create new in-memory database
heliosdb-nano repl --memory

# In REPL, restore from dump using:
# \i persistent_backup.sql  (or source via SQL)

Keyboard Shortcuts

• Ctrl-C: Cancel current multi-line input (or show exit hint if no input)
• Ctrl-D: Exit REPL
• Ctrl-R: Reverse search through history
• Ctrl-A: Move to beginning of line
• Ctrl-E: Move to end of line
• Ctrl-K: Delete from cursor to end of line
• Ctrl-U: Delete from cursor to beginning of line
• ↑/↓: Navigate command history
• Tab: Auto-complete

SQL Examples

Creating Tables

heliosdb> CREATE TABLE products (
       ->   id INT PRIMARY KEY,
       ->   name TEXT NOT NULL,
       ->   price FLOAT8,
       ->   in_stock BOOLEAN DEFAULT true
       -> );
Query OK (0.003s)

Inserting Data

heliosdb> INSERT INTO products (id, name, price)
       -> VALUES (1, 'Laptop', 999.99),
       ->        (2, 'Mouse', 24.99),
       ->        (3, 'Keyboard', 79.99);
Query OK, 3 rows affected (0.001s)

Querying Data

heliosdb> SELECT * FROM products WHERE price > 50;
┌────┬──────────┬────────┬──────────┐
│ id │ name     │ price  │ in_stock │
├────┼──────────┼────────┼──────────┤
│ 1  │ Laptop   │ 999.99 │ true     │
│ 3  │ Keyboard │ 79.99  │ true     │
└────┴──────────┴────────┴──────────┘
(2 rows) (0.002s)

Updating Data

heliosdb> UPDATE products SET price = 19.99 WHERE id = 2;
Query OK, 1 row affected (0.001s)

Deleting Data

heliosdb> DELETE FROM products WHERE NOT in_stock;
Query OK, 0 rows affected (0.001s)

Complex Queries

heliosdb> SELECT
       ->   COUNT(*) as total_products,
       ->   AVG(price) as avg_price,
       ->   MAX(price) as max_price
       -> FROM products
       -> WHERE in_stock = true;
┌────────────────┬───────────┬───────────┐
│ total_products │ avg_price │ max_price │
├────────────────┼───────────┼───────────┤
│ 3              │ 368.32    │ 999.99    │
└────────────────┴───────────┴───────────┘
(1 row) (0.003s)

Configuration

History File

Command history is saved to .heliosdb_history in the current directory.

To disable history:

export HELIOSDB_NO_HISTORY=1
heliosdb-nano repl --memory

Timing

Query timing is enabled by default. Toggle with \timing command.

Error Handling

SQL Errors

SQL errors are displayed in red with helpful messages:

heliosdb> SELECT * FROM nonexistent_table;
ERROR: Table 'nonexistent_table' does not exist

Canceling Input

Press Ctrl-C to cancel a multi-line statement in progress:

heliosdb> SELECT * FROM users
       -> WHERE id = 1
       -> AND name LIKE 'A%'
       -> ^C
heliosdb>

Tips & Best Practices

1. Use Auto-Completion: Press Tab liberally to speed up typing and avoid errors

2. Check Schema First: Use \d and \d <table> to understand your database structure

3. Format Multi-Line Queries: Break complex queries across lines for readability

4. Enable Timing: Keep \timing on to identify slow queries during development

5. Use History: Leverage command history (↑/↓) to avoid retyping queries

6. Transaction Support: Wrap multiple operations in transactions:

   BEGIN;
   INSERT INTO users VALUES (1, 'Alice');
   INSERT INTO orders VALUES (1, 1, 100.00);
   COMMIT;

Comparison with Other REPLs

Feature	HeliosDB Nano	PostgreSQL psql	SQLite
Multi-line SQL	✓	✓	✓
Auto-completion	✓	✓	✗
History	✓	✓	✓
Meta commands	✓	✓	✓
Pretty tables	✓	✓	✓
Timing	✓	\timing	.timer
In-memory mode	✓	✗	✓

Troubleshooting

REPL Won’t Start

# Check if binary exists
which heliosdb-nano

# Try with explicit path
./target/release/heliosdb-nano repl --memory

History Not Saving

# Check permissions
ls -la .heliosdb_history

# Set explicit history path
export HELIOSDB_HISTORY_PATH=/tmp/heliosdb_history
heliosdb-nano repl --memory

Completion Not Working

Ensure you’re using a compatible terminal:

• Linux: Most terminals work
• macOS: Terminal.app, iTerm2
• Windows: Windows Terminal, WSL

Advanced Usage

Scripting with REPL

# Execute SQL from a file
cat schema.sql | heliosdb-nano repl -d mydb.db

# One-liner
echo "SELECT COUNT(*) FROM users;" | heliosdb-nano repl --memory

Environment Variables

# Disable colors
export NO_COLOR=1

# Set history size
export HELIOSDB_HISTORY_SIZE=10000

# Disable history
export HELIOSDB_NO_HISTORY=1

Future Enhancements

Planned features (not yet implemented):

• \e - Edit query in $EDITOR
• \i <file> - Execute SQL from file
• \o <file> - Send output to file
• \copy - Import/export data
• \watch - Repeat query periodically
• Syntax highlighting
• Multi-database connections

See Also

• SQL Reference - Complete SQL syntax guide
• Performance Guide - Query optimization tips
• Transaction Guide - ACID transactions