TinySQL

Live demo →

A from-scratch SQL engine in TypeScript with a visible query planner. You write a query, and the right-hand panel shows the plan the engine actually ran — which table it scanned, where it used an index, how many rows each step estimated, and how many it really produced. Everything runs in the browser; the same engine code runs under Node for the tests and the CLI.

This is not SQLite. There is no sql.js, no SQLite.wasm, no DuckDB, no third-party SQL parser. The lexer, parser, planner, executor and hash index are all in src/engine, in about 2,300 lines. It implements a small dialect, on purpose, so that the interesting part — deciding between a sequential scan and an index lookup — stays readable.

Quick start

npm install
npm run dev

Then open the printed URL. Three datasets load on their own, and on a first visit the sample query runs too, so the first screen already has a plan to read.

npm test          # 173 tests
npm run build     # static site in dist/

The CLI runs the same engine over CSV files:

npm run cli -- --file public/datasets/employees.csv --file public/datasets/departments.csv --query "SELECT e.name AS employee, d.name AS department FROM employees e JOIN departments d ON e.dept_id = d.id WHERE e.dept_id = 3"

employee           department
-----------------  -----------
Ada Lovelace       Engineering
Grace Hopper       Engineering
...

7 rows · 4.3 ms · 108 rows touched · no index used

Add --explain to see the plan instead of the rows. npm run cli -- --help lists every flag.

The package is not published to npm, so there is no npx tinysql. npm run cli -- is the invocation that works in a fresh clone.

Claude Code plugin

plugin/ packages the same engine as a Claude Code plugin, so Claude can run SQL over CSV files on your machine and explain the plan it got back. It adds two MCP tools, run_sql and describe_csv, and a /tinysql:explain skill that walks through the scan, index and rerun steps described under Try this. Each call loads its CSV files into a fresh in-memory database, so nothing persists between calls. The server reads only .csv files, writes nothing, and never touches the network. To try it from a clone:

claude --plugin-dir ./plugin

The server in src/mcp is a hand-written JSON-RPC loop with no dependencies. It answers both the initialize handshake that current Claude Code clients send and the stateless 2026-07-28 protocol revision. A plugin ships only its own folder, so npm run build:plugin transpiles the server, and the engine modules it imports, into plugin/server/: one .mjs file per module, comments kept. tests/plugin.test.ts fails if that copy goes stale, drives the shipped server over stdio, and checks the folder against the rules of Anthropic's plugin directory. plugin/README.md lists what the plugin reads, runs and sends.

Try this

Open the demo. The sample join has already run, and its plan shows a SeqScan of employees feeding the join. Press Create index and rerun above the plan: the scan becomes an IndexLookup, and the status bar reads 71 rows touched (was 108). Press it again and the join gets an index too. Instead of rereading all of departments for every employee, it probes an index on departments.id with each one, and the count drops to 14 rows touched (was 71). Same seven rows every time, less work.

That button appears whenever an index would change the plan, and it names that index. The planner works it out by reading its own index rules backwards (see ARCHITECTURE.md), and a test checks, across filter, join, DISTINCT and LIMIT shapes, that taking the suggestion really produces the lookup. To do it by hand, run

EXPLAIN SELECT e.name AS employee, d.name AS department
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.dept_id = 3;

then CREATE INDEX emp_dept ON employees (dept_id);, then the EXPLAIN again. Note the aliases on the output columns — without them the result would have two columns both called name.

Forty rows are too few to feel. Press Generate 50k orders in the schema panel and run the query it writes: employee 17's largest orders, joined to the employee's name. Then press Create index and rerun twice. The first click indexes the WHERE column; the second stops the join rereading all 44 employees for each of the 1,149 orders. The three runs read 100,600, then 51,749, then 2,298 rows, and each click at least halves the time: across three fresh loads of the production build on this machine, 14–33 ms, then 6–11 ms, then 3–5 ms. Same answer, less work — which is the entire point of a planner.

How it fits together

flowchart LR
  SQL["SQL text"] --> LEX[lexer]
  LEX --> PARSE[parser]
  PARSE --> AST["AST"]
  AST --> PLAN[planner]
  CAT[(catalog + indexes)] --> PLAN
  PLAN --> TREE["plan tree"]
  TREE --> EXEC[executor]
  CAT --> EXEC
  EXEC --> ROWS["rows + per-node counters"]

The catalog is the only mutable state. The lexer, parser, planner and executor are pure functions of their input and the catalog. See ARCHITECTURE.md for the data flow and the estimate formulas.

How indexes work

An index is a hash map from a tagged key to a list of row ids, built over one column. Keys are tagged by runtime type — n:1, t:1, b:true — so 1 and '1' land in different buckets while 1 and 1.0 land in the same one. That last part matters: INTEGER and REAL are one runtime numeric domain, and an index that split them would make WHERE x = 1.0 miss rows that a scan would find.

The planner uses an index when all of the following hold:

1. A WHERE conjunct (after splitting on AND) has the form col = literal, in either operand order, with a non-NULL literal. = NULL is UNKNOWN and never an index hit.
2. The column resolves to a (table, column) pair that has an index. Resolution follows table aliases, so WHERE e.dept_id = 3 finds the index on employees(dept_id).
3. The conjunct references exactly one relation, so it can be pushed below the join. Without pushdown, an index could never fire in a join query.

The remaining conjuncts stay in a Filter: single-relation ones directly above that relation's scan, join-spanning ones above the join. At most one IndexLookup per relation, and at most one index per column: a second CREATE INDEX on an indexed column is an error.

Joins get the same idea on their inner side. Normally the JOIN table is read once and then rescanned for every outer row. If that side would scan its whole table and its join column has an index, the planner probes the index with each outer row's key instead, an index nested loop: IndexLookup(departments AS d using dept_id on id = e.dept_id). A WHERE lookup on that side still wins, because it runs once rather than once per outer row, and only the JOIN table is ever probed: the planner does not reorder a join to reach an index on the FROM table.

These are rules, not a cost model. The estimates in the plan view are there to compare against the actual counts; they never decide anything.

tests/planner.test.ts asserts on the serialized plan tree, and tests/executor.test.ts runs every probe value against both an indexed and an unindexed catalog and requires identical rows. An index that changes the answer is the worst bug this codebase could have, so it is tested as a property rather than as an example.

Supported SQL

CREATE TABLE name ( col type [, col type]* );
CREATE INDEX name ON table ( col );
DROP TABLE name;
INSERT INTO name ( cols ) VALUES ( v [, v]* ) [, ( v [, v]* )]* ;
SELECT [DISTINCT] items
  [FROM table [alias]]
  [JOIN table [alias] ON qualified.col = qualified.col]
  [WHERE expr]
  [ORDER BY col [ASC|DESC] [, ...]]
  [LIMIT n [OFFSET n]];
EXPLAIN SELECT ...;

• Types: INTEGER, REAL, TEXT, BOOLEAN, NULL. CSV import infers INTEGER / REAL / TEXT from every cell, not a sample; an empty cell is NULL.
• Select items: *, table.*, col, table.col, expr AS alias. FROM is optional, so SELECT 1 AS n works.
• Operators: = != <> < <= > >= AND OR NOT, parentheses, IS NULL, IS NOT NULL, LIKE with % and _.
• Literals: integers, reals, single-quoted strings with '' as an escaped quote, TRUE, FALSE, NULL.
• Identifiers: unquoted [A-Za-z_][A-Za-z0-9_]*, or double-quoted to preserve case. Keywords are case-insensitive; unquoted names fold to lowercase.
• Comments: -- to end of line, and /* ... */.

Value semantics worth knowing

These are decisions, not accidents, and each one has a test:

• Values of different types are never equal. 1 = '1' is FALSE. When ordered, types rank null < boolean < number < text.
• 1 and 1.0 are the same value.
• Declared types hold for every stored value. 1.5 in an INTEGER column is an error; 2.0 is accepted, because it is 2. The check sits in the catalog, so INSERT and CSV import obey it alike.
• Three-valued logic everywhere. Any comparison or LIKE with a NULL operand is UNKNOWN. WHERE keeps TRUE only, so both dept_id = 3 and dept_id != 3 exclude a NULL dept_id.
• DISTINCT treats two NULLs as duplicates of each other — the opposite of =, and what SQL says.
• ORDER BY puts NULLs first ascending, last descending (SQLite's order; Postgres does the reverse).
• ORDER BY may name any column from FROM/JOIN. With DISTINCT it must name a column in the select list, because the sort then has to run after the projection.
• LIKE is case-sensitive.
• Dates are TEXT. The bundled data uses ISO-8601 (2026-07-04) so that > compares chronologically.

Limits

Single-threaded. A CSV over 20 MB or 200,000 rows is refused with a clear error.

What we will never do in v1

No transactions. No UPDATE or DELETE. No LEFT JOIN — inner equijoin only, one join per query. No aggregates, no GROUP BY, no subqueries, no UNION, no views. The web app has no server, no accounts and no AI; the Claude Code plugin is a separate local wrapper around the same engine. Persistence is localStorage for the editor text and nothing more.

Adding a statement type

Four edits, in this order:

1. src/engine/ast.ts — add the node interface and put it in the Statement union.
2. src/engine/parser.ts — add a parseX() and dispatch to it from parseStatement(). Report what you expected; never invent a token.
3. src/engine/executor.ts — add a case to executeStatement(). If it reads rows it belongs in the planner instead, as a new PlanNode kind plus a case in execNode().
4. tests/ — a parser test for the syntax and an executor test for the behaviour. If the test is awkward to write, the API is wrong; fix the API.

The TypeScript union types will tell you everywhere else you need to touch: noImplicitAny is on, there are no any casts, and the switch statements are exhaustive.

Layout

src/engine/   lexer, parser, planner, executor, catalog, hash index, CSV, demo data — no DOM
src/ui/       editor, schema panel, results grid, plan view, status bar, dropzone
src/cli/      the Node entry point
src/mcp/      the MCP server behind the Claude Code plugin
plugin/       the Claude Code plugin; its server/ and examples/ folders are generated
scripts/      build-plugin.ts, which generates them
tests/        one file per engine module, a golden test over the bundled CSVs, and the plugin's tests
public/datasets/  employees, departments, orders
docs/         the build specification this repo was written against

npx tsc -p tsconfig.node.json typechecks the engine, the CLI and the MCP server with DOM types removed, which proves none of them has a browser dependency.

License

MIT — see LICENSE.