Skip to main content

SQL

@dortdb/lang-sql adds a PostgreSQL-flavored SELECT language to DortDB. It is the natural choice for row-shaped data: arrays of plain JavaScript objects, where each object is a row and each property is a column.

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
`);
// data: [ { city: 'Prague', total: 2 }, { city: 'Ankara', total: 1 } ]

What it targets​

By default SQL reads sources through the ObjectDataAdapter, which treats each element of a registered array as a row and reads columns as object properties. You can point SQL at differently-shaped data (for example Map-backed rows) by supplying a custom adapter; see Data Sources & Adapters.

Result column names​

Selected expressions keep their qualifier in the result schema. Selecting t1.id produces a result key "t1.id"; use AS to control the name:

db.query('SELECT t1.id AS id, t2.w AS weight FROM t1 JOIN t2 ON t1.id = t2.id');
// keys: id, weight

Learn more​