@dortdb/lang-sql

A SQL language plug-in for DortDB. It adds a PostgreSQL-flavored SELECT language for row-shaped data, meaning arrays of plain JavaScript objects where each object is a row and each property is a column.

Installation

npm install @dortdb/core @dortdb/lang-sql

@dortdb/core is a peer dependency.

Usage

import { DortDB } from '@dortdb/core';
import { defaultRules } from '@dortdb/core/optimizer';
import { SQL } from '@dortdb/lang-sql';

const db = new DortDB({
  mainLang: SQL(),
  optimizer: { rules: defaultRules },
});

db.registerSource(
  ['people'],
  [
    { id: 1, name: 'Alice', city: 'Prague' },
    { id: 2, name: 'Bob', city: 'Ankara' },
    { id: 3, name: 'Carol', city: 'Prague' },
  ],
);

const result = db.query(`
  SELECT city, count(*) AS total
  FROM people
  GROUP BY city
  ORDER BY total DESC
`);
// result.data -> [{ city: 'Prague', total: 2 }, { city: 'Ankara', total: 1 }]

SQL reads sources through the ObjectDataAdapter, which reads each array element as a row and each property as a column. To query data with another shape, such as rows backed by a Map, pass a custom adapter to SQL().

Dialect

The dialect follows PostgreSQL and covers its data-selection subset. It includes a few features other SQL flavors lack.

• Lateral joins. A JOIN LATERAL (...) subquery can reference tables that appear earlier in the FROM clause, and it runs once per outer row.
• DISTINCT ON. It keeps the first row per distinct value of the given expressions instead of deduplicating whole rows.
• Filtered and ordered aggregates, such as count(...) FILTER (WHERE ...), count(DISTINCT ...), and collect(x ORDER BY x).

There is no data definition or modification. CREATE, INSERT, and UPDATE have no equivalent here, because DortDB queries data that already exists in memory. Window functions and grouping sets (ROLLUP, CUBE, GROUPING SETS) are not implemented.

Restrictions without a schema

DortDB sources declare no schema, so the parser rejects queries that a schema would be needed to read one way.

• An unqualified column requires a single, non-joined source. Otherwise, qualify it as t1.attr. The same holds in ORDER BY, where a SELECT-list alias counts as a bare name, so order by the qualified expression.
• Natural joins are disabled, because there is no schema to infer the join columns from.
• Every FROM subquery needs an alias.
• SELECT * is not supported.
• Identifiers are case-sensitive. A column that a row does not have is absent from the result instead of raising an error, so a case mismatch shows up as a missing column.

Inside a language that does not prefix identifiers, such as Cypher, prefix a column with the nonlocal schema to resolve it against the surrounding scope. Without that prefix the name resolves against the current table, and the query returns nulls instead of failing.

Documentation

See the SQL overview and the dialect reference, or the full docs at filipjezek.github.io/dortdb.