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, PI()

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

• 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 Mode: F5 for internal state inspection

🚀 Why Choose SQL CLI?

🔥 Unique Advantages

🎯 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')

🔧 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