sql-jot

English | 日本語

Emmet-style shorthand that compiles into SQL. Designed for the Monaco editor but the core is pure TypeScript with no editor dependency.

tbl@a>name,sum(price)@x#name:x<5000$-x
↓
SELECT name, sum(price) AS x
FROM tbl a
GROUP BY name
HAVING x < 5000
ORDER BY x DESC

Why

SQL clients tend to grow heavy with features. sql-jot is the opposite — a one-line shorthand for the keystroke-bound minority who'd rather type sigils than walk through wizards.

It is not a SQL replacement. It compiles to standard SQL that any database or tool can run.

Quick start

npm install sql-jot

import { expand } from "sql-jot";

expand("users@u>u.name?u.id=1");
// → "SELECT u.name FROM users u WHERE u.id = 1"

expand("+users<name=\"alice\",age=30");
// → "INSERT INTO users (name, age) VALUES ('alice', 30)"

expand("=users<count+=1?id=5");
// → "UPDATE users SET count = count + 1 WHERE id = 5"

expand("users?^(orders?user_id=1)");
// → "SELECT * FROM users WHERE EXISTS (SELECT * FROM orders WHERE user_id = 1)"

expand("users?!status[\"deleted\",\"banned\"]");
// → "SELECT * FROM users WHERE status NOT IN ('deleted', 'banned')"

expand("+users<name=\"alice\">id,created_at");
// → "INSERT INTO users (name) VALUES ('alice') RETURNING id, created_at"

expand('users>?{score>=80?"pass":"fail"}@result');
// → "SELECT CASE WHEN score >= 80 THEN 'pass' ELSE 'fail' END AS result FROM users"

expand('users>?{nickname??"anon"}@display');
// → "SELECT COALESCE(nickname, 'anon') AS display FROM users"

expand("users?deleted_at=null,active=true");
// → "SELECT * FROM users WHERE deleted_at IS NULL AND active = TRUE"
//   ( =null is auto-rewritten to IS NULL; !=null / <>null give IS NOT NULL )

expand("users|>dept");
// → "SELECT DISTINCT dept FROM users"

expand("orders>count(|>user_id)@uniq");
// → "SELECT count(DISTINCT user_id) AS uniq FROM orders"

expand("users?age~[18,65]");
// → "SELECT * FROM users WHERE age BETWEEN 18 AND 65"

expand('orders>user_id?status="paid" && reviews>user_id?published=true');
// → "SELECT user_id FROM orders WHERE status = 'paid'
//    INTERSECT
//    SELECT user_id FROM reviews WHERE published = TRUE"

expand("products>id \\\\ order_items>product_id");
// → "SELECT id FROM products EXCEPT SELECT product_id FROM order_items"

Syntax at a glance

| Symbol | Role | Sample | |---|---|---| | > | SELECT columns / RETURNING (trailing CUD) | users>name, +users<...>id | | ? | WHERE | ?id=1 | | + | JOIN (or INSERT verb) | +orders[u.id=o.user_id] | | [ ] | ON / IN | ?id[1,2,3], ?id[(subq)] | | ( ) | subquery / column list | +users<(other>name?active=1) | | { } | CTE / row block | {src>id}@s | | ^( ) | EXISTS subquery | ?^(orders?user_id=1) | | ! | NOT marker (IN / LIKE / EXISTS) | ?!id[1,2,3], ?!^(...) | | ?{ } | CASE block (PHP-style ternary inside) | ?{x>0?"pos":"neg"} | | ?? | null-coalesce (chains into COALESCE) | ?{a??b??"x"} | | \|> | SELECT DISTINCT / DISTINCT inside aggregate | users\|>dept, count(\|>uid) | | ~[ , ] | BETWEEN (with ! prefix → NOT BETWEEN) | ?age~[18,65] | | \|\| \|\|* | UNION / UNION ALL between two SELECTs | a>id \|\| b>id | | && &&* | INTERSECT / INTERSECT ALL | a>id && b>id | | \\ \\* | EXCEPT / EXCEPT ALL | a>id \\ b>id | | # | GROUP BY | #user_id | | : | HAVING | :count>5 | | $ | ORDER BY | $-created_at | | ~ | LIMIT/PAGE | ~20p3 | | % | LIKE | ?name%"john" | | @ | alias | users@u | | + = - (leading) | INSERT / UPDATE / DELETE verbs | +users<..., =users<...?..., -users?... |

The complete reference is in SYNTAX.md.

Schema integration

sql-jot doesn't own your schema. The host application provides a small resolver and sql-jot uses it for FK auto-resolution, multi-hop JOIN inference, implicit column qualification, validation and completion.

import { expand, staticResolver } from "sql-jot";

const schema = staticResolver({
  tables: [
    { name: "users", columns: ["id", "name"] },
    { name: "orders", columns: ["id", "user_id", "total"] },
  ],
  foreignKeys: [
    {
      fromTable: "orders",
      fromColumns: ["user_id"],
      toTable: "users",
      toColumns: ["id"],
    },
  ],
});

expand("users@u+orders@o", { schema });
// → "SELECT * FROM users u INNER JOIN orders o ON u.id = o.user_id"

For real applications, implement SchemaResolver directly against your catalogue. See src/types.ts.

APIs

import {
  expand,                 // shorthand → SQL string
  parse, compile,         // separate parse / compile steps
  validate,               // schema-aware validation issues
  getCandidates,          // cursor-position completion candidates
  longestCommonPrefix,    // helper for Tab-style prefix expansion
  staticResolver,         // build a SchemaResolver from a static schema
} from "sql-jot";

The completion API is UX-agnostic: it returns candidates and the host decides how to present them (popup, inline ghost text, Tab-only, Ctrl+Space). The Emmet ethos is "no popup interrupts" — the included example uses Tab prefix-expansion plus inline validation squiggles, with no autocomplete dropdown.

Example app

A Vite + Monaco demo lives in example/:

cd example
npm install
npm run dev
# → http://localhost:5173

You get a two-pane editor with live SQL preview, syntax highlighting, quick-load samples, validation markers and Tab prefix expansion.

Status

The core covers:

• SELECT / INSERT / UPDATE / DELETE, with RETURNING as trailing >cols (PostgreSQL-style; CompileOptions.returning hook for MySQL/SQL Server)
• CTE blocks ({...}@name), multiple CTEs, optional FROM
• JOINs: INNER / LEFT / RIGHT / FULL / CROSS, with FK auto-resolve and multi-hop inference
• IN with literal list / table-or-CTE reference / parenthesized subquery
• EXISTS / NOT EXISTS via ^(...) and !^(...)
• NOT IN / NOT LIKE via ! prefix
• CASE WHEN via ?{cond?then:else} (PHP-style ternary; right-recursive chains collapse to flat multi-arm CASE)
• COALESCE via ?? chains inside ?{...}
• null / true / false literals, with auto IS NULL / IS NOT NULL rewrite for =null / !=null / <>null. CompileOptions.bool hook for SQL Server's 1 / 0 boolean rendering
• count(*) and other *-arg function calls
• DISTINCT at SELECT level (|>cols) and inside aggregates (count(|>col))
• BETWEEN / NOT BETWEEN — ?col~[low,high] / ?!col~[low,high]
• Set operations — || UNION, && INTERSECT, \\ EXCEPT (with *-suffix for ALL variants); composable in CTE bodies and subqueries
• Implicit column qualification, schema-aware validation, completion candidates, qualified-star (t.*)

See SYNTAX.md §11 for the current limitation list. Notably absent: arithmetic in expressions, window functions, recursive CTE.

Development

npm install        # also runs the grammar build via the prepare hook
npm test           # runs vitest

License

MIT © msd.shsk