SQL Query Engine 🚀

A high-performance, zero-dependency SQL query engine built in TypeScript for querying CSV, JSON, TSV, and JSONL files directly from your terminal.

Features

• Multi-Format Support: Query .csv, .json, .tsv, and .jsonl files.
• Advanced SQL Syntax:
  • SELECT DISTINCT, AS aliases, and column prefixes.
  • WHERE with AND, OR, NOT, LIKE, IN, and IS NULL.
  • Aggregations: COUNT, SUM, AVG, MIN, MAX.
  • Grouping: GROUP BY and HAVING.
  • Joins: INNER JOIN and LEFT JOIN with ON conditions.
  • Sorting & Pagination: ORDER BY (ASC/DESC), LIMIT, and OFFSET.
  • Arithmetic: Use +, -, *, /, % in selection and filters.
• CLI Power:
  • --format [table|json|csv] output options.
  • --output results.csv to save directly to disk.
  • --explain to visualize the query execution plan.
• Zero Runtime Dependencies: Lightweight and fast.

Installation

npm install
npm run build

Usage

Run queries using ts-node (for development) or node after building.

Basic Selection

npx ts-node src/index.ts query "SELECT * FROM data/users.csv"

Filtering and Aliasing

npx ts-node src/index.ts query "SELECT name AS full_name, age FROM data/users.csv WHERE age > 25 AND city LIKE 'N%'"

Aggregation and Grouping

npx ts-node src/index.ts query "SELECT city, COUNT(*) as total, AVG(salary) FROM data/users.csv GROUP BY city HAVING COUNT(*) > 1"

Joins

npx ts-node src/index.ts query "SELECT u.name, o.product FROM data/users.csv u LEFT JOIN data/orders.csv o ON u.id = o.user_id"

CLI Flags

# Export to JSON
npx ts-node src/index.ts query "SELECT * FROM data/users.csv" --format json

# Explain query plan
npx ts-node src/index.ts query "SELECT city, SUM(salary) FROM data/users.csv GROUP BY city" --explain

# Save to file
npx ts-node src/index.ts query "SELECT * FROM data/users.csv" --output backup.csv

Supported Syntax

Clause	Support
SELECT	*, col, expr AS alias, DISTINCT, AGG(col)
FROM	file.csv, file.json, file.tsv, file.jsonl, (subquery)
JOIN	INNER JOIN, LEFT JOIN
WHERE	=, !=, <>, <, >, <=, >=, AND, OR, NOT, LIKE, IN, IS NULL
GROUP BY	Single or multiple columns
HAVING	Filter aggregated results
ORDER BY	ASC, DESC (Multiple columns supported)
LIMIT	Row count limit
OFFSET	Row skip count
Operators	+, -, *, /, %

Testing

The project uses Node's native test runner (no external test dependencies like Jest).

npm test

Architecture

1. Tokenizer: Scans SQL string into tokens.
2. Parser: Recursive descent parser that builds an AST (Abstract Syntax Tree).
3. Executor: Walks the AST and applies the data pipeline:
   • Load -> Join -> Filter -> Group -> Aggregate -> Project -> Distinct -> Sort -> Slice.
4. Formatter: Renders the result as a ASCII table, JSON, or CSV.

License

MIT