SQL CLI - Powerful CSV/JSON Query Tool with Interactive TUI & CLI Modes

A vim-inspired SQL query tool for CSV and JSON files. Features both an interactive terminal UI for data exploration and a non-interactive CLI mode for scripting and automation.

🚀 Why SQL CLI?

Think less for CSV files, but with SQL superpowers:

• 🎯 Two Modes: Interactive TUI for exploration, non-interactive for scripting & automation
• 📁 Point & Query: Drop any CSV/JSON file and immediately start querying
• ⚡ Lightning Fast: In-memory engine handles 100K+ rows with sub-second response
• 🎮 Vim-Inspired: Modal editing, hjkl navigation, powerful keyboard shortcuts
• 🧠 Smart Completion: Context-aware SQL completion with fuzzy matching
• 🔍 Advanced Filtering: Regex, fuzzy search, complex WHERE clauses
• 📊 Rich SQL Features: Date functions, string manipulation, mathematical operations
• 📤 Multiple Outputs: CSV, JSON, TSV, or pretty tables - perfect for pipelines

⚡ Quick Start

# Install from Cargo  
cargo install sql-cli

# Point at any CSV or JSON file
sql-cli data.csv

# Immediately start querying with full SQL support
SELECT * FROM data WHERE amount > 1000 ORDER BY date DESC LIMIT 10

🎯 Two Powerful Modes

🖥️ Interactive TUI Mode (Default)

Launch the full vim-inspired terminal interface for data exploration:

# Interactive mode - explore your data with vim keybindings
sql-cli data.csv
sql-cli trades.json

# Navigate with hjkl, search with /, execute queries interactively

🚀 Non-Interactive Query Mode (New!)

Execute SQL queries directly from the command line - perfect for scripting and automation:

# Run a query and get CSV output
sql-cli data.csv -q "SELECT * FROM data WHERE price > 100"

# Output as JSON
sql-cli data.csv -q "SELECT id, name, value FROM data" -o json

# Pretty table format
sql-cli data.csv -q "SELECT * FROM data LIMIT 10" -o table

# Save results to file
sql-cli data.csv -q "SELECT * FROM data WHERE status='active'" -O results.csv

# Execute SQL from a file
sql-cli large_dataset.json -f complex_analysis.sql -o table

# Limit output rows
sql-cli data.csv -q "SELECT * FROM data" -o json -l 100

Non-Interactive Options:

• -q, --query <SQL> - Execute SQL query directly
• -f, --query-file <file> - Execute SQL from file
• -o, --output <format> - Output format: csv, json, table, tsv (default: csv)
• -O, --output-file <file> - Write results to file
• -l, --limit <n> - Limit output to n rows
• --case-insensitive - Case-insensitive string matching
• --auto-hide-empty - Auto-hide empty columns

Use Cases:

# Data pipeline integration
sql-cli raw_data.csv -q "SELECT * FROM raw_data WHERE valid=1" | process_further.sh

# Automated reporting
sql-cli sales.csv -f monthly_report.sql -o json > report_$(date +%Y%m).json

# Quick data analysis
sql-cli logs.csv -q "SELECT COUNT(*) as errors FROM logs WHERE level='ERROR'" -o table

# Data cleaning
sql-cli messy_data.csv -q "SELECT * FROM messy_data WHERE email.EndsWith('.com')" -O clean_data.csv

# Complex calculations
sql-cli finances.csv -q "SELECT date, amount * (1 + tax_rate) as total FROM finances" -o table

💪 Powerful SQL Engine Features

🔥 Core SQL + Modern Extensions

Your SQL CLI combines traditional SQL with modern LINQ-style methods and advanced functions:

-- Traditional SQL with modern LINQ methods
SELECT 
    customer_name.Trim() as name,
    email.EndsWith('.com') as valid_email,
    ROUND(price * quantity, 2) as total,
    DATEDIFF('day', order_date, NOW()) as days_ago
FROM orders 
WHERE customer_name.Contains('corp')
  AND price BETWEEN 100 AND 1000
  AND order_date > DATEADD('month', -6, TODAY())
ORDER BY total DESC 
LIMIT 25

📊 Advanced Functions Library

Date & Time Functions

-- Comprehensive date handling with multiple format support
SELECT 
    NOW() as current_time,                    -- 2024-08-31 15:30:45
    TODAY() as current_date,                  -- 2024-08-31  
    DATEDIFF('day', '2024-01-01', order_date) as days_since_year,
    DATEADD('month', 3, ship_date) as warranty_expires
FROM orders
WHERE DATEDIFF('year', created_date, NOW()) <= 2

Supported Date Formats:

• ISO: 2024-01-15, 2024-01-15 14:30:00
• US: 01/15/2024, 01/15/2024 2:30 PM
• EU: 15/01/2024, 15/01/2024 14:30
• Excel: 15-Jan-2024, Jan 15, 2024
• Full: January 15, 2024, 15 January 2024

Mathematical Functions

-- Rich mathematical operations
SELECT 
    ROUND(price * 1.08, 2) as taxed_price,
    SQRT(POWER(width, 2) + POWER(height, 2)) as diagonal,
    MOD(id, 100) as batch_number,
    ABS(actual - target) as variance,
    POWER(growth_rate, years) as compound_growth
FROM products
WHERE SQRT(area) BETWEEN 10 AND 50

Available Math Functions: ROUND, ABS, FLOOR, CEILING, MOD, QUOTIENT, POWER, SQRT, EXP, LN, LOG, LOG10

🧮 Scientific Calculator Mode with DUAL Table

-- Use DUAL table for calculations (Oracle-compatible)
SELECT PI() * POWER(5, 2) as circle_area FROM DUAL;
SELECT DEGREES(PI()/2) as right_angle FROM DUAL;

-- Scientific notation support
SELECT 1e-10 * 3.14e5 as tiny_times_huge FROM DUAL;
SELECT 6.022e23 / 1000 as molecules_per_liter FROM DUAL;

-- Physics constants for scientific computing
SELECT 
    C() as speed_of_light,        -- 299792458 m/s
    ME() as electron_mass,        -- 9.109e-31 kg
    PLANCK() as planck_constant,  -- 6.626e-34 J⋅s
    NA() as avogadro_number       -- 6.022e23 mol⁻¹
FROM DUAL;

-- Complex physics calculations
SELECT PLANCK() * C() / 500e-9 as photon_energy_500nm FROM DUAL;
SELECT MP() / ME() as proton_electron_mass_ratio FROM DUAL;

-- No FROM clause needed for simple calculations
SELECT 2 + 2;
SELECT SQRT(2) * PI();

Scientific Constants Available:

• Math: PI(), EULER(), TAU(), PHI(), SQRT2(), LN2(), LN10()
• Physics - Fundamental: C(), G(), PLANCK(), HBAR(), BOLTZMANN(), AVOGADRO(), R()
• Physics - Electromagnetic: E0(), MU0(), QE()
• Physics - Particles: ME(), MP(), MN(), AMU()
• Physics - Other: ALPHA(), RYDBERG(), SIGMA()
• Conversions: DEGREES(radians), RADIANS(degrees)

String & Text Functions

-- Advanced text manipulation
SELECT 
    TEXTJOIN(' | ', 1, first_name, last_name, department) as employee_info,
    name.Trim().Length() as clean_name_length,
    email.IndexOf('@') as at_position,
    description.StartsWith('Premium') as is_premium
FROM employees
WHERE name.Contains('manager') 
  AND email.EndsWith('.com')
  AND department.Trim() != ''

LINQ-Style String Methods:

• column.Contains('text') - Case-insensitive substring search
• column.StartsWith('prefix') - Case-insensitive prefix check
• column.EndsWith('suffix') - Case-insensitive suffix check
• column.Length() - Character count
• column.IndexOf('substring') - Find position (0-based, -1 if not found)
• column.Trim() - Remove leading/trailing spaces
• column.TrimStart() - Remove leading spaces only
• column.TrimEnd() - Remove trailing spaces only

🎯 Advanced Query Capabilities

Complex WHERE Clauses

-- Sophisticated filtering with nested logic
SELECT * FROM financial_data
WHERE (category.StartsWith('equity') OR category.Contains('bond'))
  AND price BETWEEN 50 AND 500
  AND quantity NOT IN (0, 1)  
  AND trader_name.Length() > 3
  AND DATEDIFF('day', trade_date, settlement_date) <= 3
  AND commission NOT BETWEEN 0 AND 10

Computed Columns & Expressions

-- Complex calculations in SELECT
SELECT 
    -- Computed columns with aliases
    price * quantity * (1 - discount/100) as net_amount,
    ROUND((selling_price - cost_basis) / cost_basis * 100, 2) as profit_margin_pct,
    
    -- Nested function calls
    ROUND(SQRT(POWER(leg1, 2) + POWER(leg2, 2)), 3) as hypotenuse,
    
    -- Conditional logic with functions  
    CASE 
        WHEN price.Contains('.') THEN 'Decimal'
        WHEN MOD(ROUND(price, 0), 2) = 0 THEN 'Even'
        ELSE 'Odd'
    END as price_type
FROM trade_data

Flexible ORDER BY

-- Order by computed expressions and functions
SELECT *, price * quantity as total_value
FROM orders
ORDER BY 
    customer_name.Trim(),                    -- LINQ method in ORDER BY
    ROUND(price * quantity, 2) DESC,         -- Mathematical expression
    DATEDIFF('day', order_date, NOW()) ASC,  -- Date function
    total_value DESC                         -- Computed column alias
LIMIT 100

🧠 Smart Type Handling

• Automatic Coercion: String methods work on numbers (quantity.Contains('5'))
• Flexible Parsing: Multiple date formats automatically recognized
• NULL Handling: Graceful handling of missing/empty values
• Error Recovery: Helpful suggestions for column name typos

⚡ Performance Features

• In-Memory Processing: 100K+ rows with sub-second response times
• Smart Caching: Query results cached for instant re-filtering
• Optimized Evaluation: Efficient column operations and expression parsing
• Streaming Support: Large dataset handling without memory bloat

🖥️ Vim-Inspired Terminal UI

Lightning-Fast Navigation

• Help: Press F1 for comprehensive help and keybindings
• Vim Keybindings: hjkl movement, g/G for top/bottom, / and ? for search
• Column Operations: Sort (s), Pin (p), Hide (H) columns with single keystrokes
• Smart Search: Column search, data search, fuzzy matching with n/N navigation
• Virtual Scrolling: Handle datasets with 1000+ rows and 190+ columns efficiently
• Mode Switching: Insert (i), Append (a/A), Command mode (Esc)

Power User Features

• Key History: See your last 10 keystrokes with 2s fade
• Query Caching: Results cached for instant re-filtering
• Export: Ctrl+S to save current view as CSV
• Debug View: Press F5 to see internal state and diagnostics

🚀 Why Choose SQL CLI?

🔥 Unique Advantages

Feature	SQL CLI	csvlens	csvkit	Other Tools
LINQ Methods	✅ .Contains(), .StartsWith()	❌	❌	❌
Date Functions	✅ DATEDIFF, DATEADD, NOW()	❌	Limited	❌
Math Functions	✅ ROUND, SQRT, POWER, etc.	❌	Basic	❌
Vim Navigation	✅ Full vim-style	Basic	❌	❌
Computed Columns	✅ price * qty as total	❌	❌	Limited
Smart Completion	✅ Context-aware SQL	❌	❌	❌
Multiple Date Formats	✅ Auto-detection	❌	❌	❌

🎯 Perfect For

• Data Analysts: Complex calculations with LINQ-style methods
• Developers: Vim navigation + SQL power for log analysis
• Financial Teams: Advanced date arithmetic and mathematical functions
• Anyone: Who wants less for CSV files but with SQL superpowers

🔗 Real-World Examples

-- Financial Analysis
SELECT 
    trader.Trim() as trader_name,
    ROUND(SUM(price * quantity), 2) as total_volume,
    COUNT(*) as trade_count,
    ROUND(AVG(price), 4) as avg_price,
    DATEDIFF('day', MIN(trade_date), MAX(trade_date)) as trading_span
FROM trades
WHERE settlement_date > DATEADD('month', -3, TODAY())
  AND counterparty.Contains('BANK')
  AND commission BETWEEN 5 AND 100
  AND NOT status.StartsWith('CANCEL')
GROUP BY trader.Trim()
ORDER BY total_volume DESC
LIMIT 20;

-- Log Analysis  
SELECT 
    log_level,
    message.IndexOf('ERROR') as error_position,
    TEXTJOIN(' | ', 1, timestamp, service, user_id) as context,
    ROUND(response_time_ms / 1000.0, 3) as response_seconds
FROM application_logs
WHERE timestamp > DATEADD('hour', -24, NOW())
  AND (message.Contains('timeout') OR message.Contains('exception'))
  AND response_time_ms BETWEEN 1000 AND 30000
ORDER BY timestamp DESC;

📦 Installation

Install with Cargo

# Install directly from git
cargo install --git https://github.com/YOUR_USERNAME/sql-cli.git

# Or install from crates.io (when published)
cargo install sql-cli

Build from Source

git clone https://github.com/YOUR_USERNAME/sql-cli.git
cd sql-cli
cargo build --release
./target/release/sql-cli

🎮 Usage

Basic Usage

# Load CSV file
sql-cli data.csv

# Load JSON file  
sql-cli sales.json

# With enhanced mode
sql-cli --enhanced large_dataset.csv

Key Bindings

• Navigation: hjkl (vim-style), g/G (top/bottom)
• Search: / (column search), ? (data search), n/N (next/prev)
• Columns: s (sort), p (pin), H (hide)
• Modes: i (insert), a/A (append), Esc (normal)
• Export: Ctrl+S (save current view as CSV)

Advanced SQL Examples

-- Date functions and complex filtering
SELECT * FROM data 
WHERE created_date > DATEADD(MONTH, -3, NOW()) 
  AND status.Contains('active')
ORDER BY updated_date DESC

-- Aggregations and grouping
SELECT category, COUNT(*) as count, AVG(amount) as avg_amount
FROM sales 
GROUP BY category 
HAVING count > 10

-- String manipulation
SELECT UPPER(name) as name_upper, 
       LEFT(description, 50) as desc_preview
FROM products
WHERE name.StartsWith('A')

📊 Terminal Charts (NEW!)

SQL CLI now includes a powerful standalone charting tool (sql-cli-chart) that creates terminal-based visualizations of your SQL query results. Perfect for time series analysis, trend visualization, and data exploration.

Chart Tool Usage

# Basic time series chart
sql-cli-chart data.csv -q "SELECT time, value FROM data" -x time -y value -t "My Chart"

# Filter data with SQL WHERE clause
sql-cli-chart trades.csv \
  -q "SELECT timestamp, price FROM trades WHERE symbol = 'AAPL'" \
  -x timestamp -y price -t "AAPL Price Chart"

Real-World Example: VWAP Trading Analysis

Visualize algorithmic trading data with SQL filtering to focus on specific patterns:

# Chart fill volume progression for CLIENT orders only
sql-cli-chart data/production_vwap_final.csv \
  -q "SELECT snapshot_time, filled_quantity FROM production_vwap_final WHERE order_type LIKE '%CLIENT%'" \
  -x snapshot_time -y filled_quantity \
  -t "CLIENT Order Fill Progression"

# Compare with ALL orders (shows chaotic "Christmas tree" pattern)
sql-cli-chart data/production_vwap_final.csv \
  -q "SELECT snapshot_time, filled_quantity FROM production_vwap_final" \
  -x snapshot_time -y filled_quantity \
  -t "All Orders - Mixed Pattern"

The Power of SQL Filtering: The first query filters to show only CLIENT orders (991 rows), displaying a clean upward progression. The second shows all 3320 rows including ALGO and SLICE orders, creating a noisy pattern. This demonstrates how SQL queries let you focus on exactly the data patterns you want to visualize.

Interactive Chart Controls

Once the chart opens, use these vim-like controls:

• hjkl - Pan left/down/up/right
• +/- - Zoom in/out
• r - Reset view to auto-fit
• q/Esc - Quit

Example Scripts

Ready-to-use chart examples are in the scripts/ directory:

# VWAP average price over time
./scripts/chart-vwap-price.sh

# Fill volume progression
./scripts/chart-vwap-volume.sh

# Compare different order types
./scripts/chart-vwap-algo-comparison.sh

Chart Features

• SQL Query Integration: Use full SQL power to filter and transform data before charting
• Smart Auto-Scaling: Automatically adapts Y-axis range for optimal visibility
• Time Series Support: Automatic timestamp parsing and time-based X-axis
• Interactive Navigation: Pan and zoom to explore your data
• Terminal Native: Pure terminal graphics, no GUI dependencies

⚠️ SQL Features Not Yet Supported

While SQL CLI provides extensive SQL functionality, some standard SQL features are not yet implemented:

Aggregate Functions

• COUNT(*), COUNT(column) - Row counting
• SUM(column) - Sum of values
• AVG(column) - Average calculation
• MIN(column), MAX(column) - Min/max values
• STDDEV(), VARIANCE() - Statistical functions

Grouping & Aggregation

• GROUP BY clause - Grouping rows
• HAVING clause - Filtering groups
• Aggregate expressions in SELECT

Joins & Subqueries

• JOIN, LEFT JOIN, RIGHT JOIN - Table joins
• UNION, INTERSECT, EXCEPT - Set operations
• Subqueries and correlated queries
• Common Table Expressions (CTEs)

Data Modification

• INSERT, UPDATE, DELETE - Data modification
• CREATE TABLE, ALTER TABLE - DDL operations

Other Features

• DISTINCT keyword - Unique values only
• Window functions (ROW_NUMBER(), RANK(), etc.)
• EXISTS, ALL, ANY operators

Note: SQL CLI is designed for read-only data analysis and exploration. For full SQL database functionality, consider using a traditional database system.

🔧 Development

Running Tests

# Run all tests
cargo test

# Run specific test suite
cargo test --test data_view_trades_test

Build Commands

# Format code (required before commits)
cargo fmt

# Build release
cargo build --release

# Run with file
cargo run data.csv

🎯 Performance

• 10K-100K rows: Interactive queries (50-200ms)
• Complex queries on 100K rows: ~100-200ms
• Memory usage: ~50MB for 100K rows
• Navigation: Zero-latency keyboard response

📚 Documentation

Comprehensive documentation available in the docs/ folder covering:

• Architecture and design decisions
• SQL parser implementation
• TUI component system
• Performance optimization techniques

🤝 Contributing

1. Fork the repository
2. Create a feature branch
3. Run cargo fmt before committing (required)
4. Submit a pull request

📄 License

MIT License - see the LICENSE file for details.

---

Built with Rust 🦀 | Powered by ratatui + crossterm | Inspired by vim