---
title: "HeliosDB Lite REPL Guide"
---

# HeliosDB Lite REPL Guide

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

## Quick Start

### Default Mode (Uses./heliosdb-data)
```bash
# Automatically uses ./heliosdb-data directory
heliosdb-lite repl
```

### In-Memory Mode (No Persistence)
```bash
heliosdb-lite repl --memory
```

### Custom Data Directory
```bash
heliosdb-lite repl --data-dir ./mydb
# or
heliosdb-lite repl -d ./mydb
```

## Features

### Multi-Line SQL Support
SQL statements can span multiple lines. The REPL waits for a semicolon (`;`) before executing:

```sql
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
```sql
heliosdb> \d

Tables:
  customers
  orders
  products
```

#### `\d <table>` - Describe Table Schema
```sql
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
```sql
heliosdb> \dt

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

### Control Commands

#### `\h` or `\help` - Show Help
```sql
heliosdb> \h

HeliosDB Lite 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
```sql
heliosdb> \timing
Timing is on

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

heliosdb> \timing
Timing is off
```

#### `\q`, `\quit`, or `\exit` - Quit REPL
```sql
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:**
```sql
-- 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:**
```sql
-- HeliosDB Lite Database Dump
-- Generated: 2025-12-09 14:23:45
-- Database: heliosdb-lite

-- 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:
```bash
heliosdb-lite repl --memory --dump-on-shutdown --dump-file persistent_backup.sql
```

Then restore in another session:
```bash
# Create new in-memory database
heliosdb-lite 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
```sql
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
```sql
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
```sql
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
```sql
heliosdb> UPDATE products SET price = 19.99 WHERE id = 2;
Query OK, 1 row affected (0.001s)
```

### Deleting Data
```sql
heliosdb> DELETE FROM products WHERE NOT in_stock;
Query OK, 0 rows affected (0.001s)
```

### Complex Queries
```sql
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:
```bash
export HELIOSDB_NO_HISTORY=1
heliosdb-lite 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:

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

```sql
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:
   ```sql
   BEGIN;
   INSERT INTO users VALUES (1, 'Alice');
   INSERT INTO orders VALUES (1, 1, 100.00);
   COMMIT;
   ```

## Comparison with Other REPLs

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

## Troubleshooting

### REPL Won't Start
```bash
# Check if binary exists
which heliosdb-lite

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

### History Not Saving
```bash
# Check permissions
ls -la .heliosdb_history

# Set explicit history path
export HELIOSDB_HISTORY_PATH=/tmp/heliosdb_history
heliosdb-lite 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
```bash
# Execute SQL from a file
cat schema.sql | heliosdb-lite repl -d mydb.db

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

### Environment Variables
```bash
# Disable colors
export NO_COLOR=1

# Set history size
export HELIOSDB_HISTORY_SIZE=10000

# Disable history
export HELIOSDB_NO_HISTORY=1
```

## See Also

- [SQL Reference](/docs/lite/user-guide/sql-reference/) - Complete SQL syntax guide