Skip to main content

Your First Query

In this lesson you will build a working DortDB engine and run a SQL query over a plain JavaScript array. It takes three steps, and at the end you will have a result you can print.

You need @dortdb/core and @dortdb/lang-sql installed.

1. Construct the engine​

Create a new file and start with the engine. A DortDB instance takes a configuration object with two required fields: the language to parse queries with, and the optimizer rules to apply.

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 },
});

SQL() is a factory that returns a language descriptor, and defaultRules is the recommended, pre-ordered set of optimizer rules. You now have an engine, but it has no data.

2. Register your data​

Add an array of objects and hand it to registerSource, which pairs the value with a name:

const people = [
  { id: 1, name: 'Alice', city: 'Prague' },
  { id: 2, name: 'Bob', city: 'Ankara' },
  { id: 3, name: 'Carol', city: 'Prague' },
  { id: 4, name: 'Dan', city: 'Prague' },
];

db.registerSource(['people'], people);

Nothing was copied or converted. people is still your array; DortDB now just knows it can be queried under the name people.

3. Query it​

db.query() parses, plans, optimizes, executes, and collects the results in one call:

const result = db.query(`
  SELECT city, count(*) AS residents
  FROM people
  GROUP BY city
  ORDER BY residents DESC
`);

console.log(result.schema);
console.log(result.data);

Run the file. You should see:

[ 'city', 'residents' ]
[ { city: 'Prague', residents: 3 }, { city: 'Ankara', residents: 1 } ]

That is the whole cycle: construct, register, query. result.data is an array of result rows and result.schema is the ordered list of column names. See QueryResult.

Always name your columns

DortDB sources have no declared schema, so SELECT * is not supported and an unqualified column name is only unambiguous when a single source is in scope. List the columns you want and give them names with AS. The full set of consequences is in SQL Dialect & Restrictions.

Next step​

You have queried one array with one language. In Mixing Languages you will add a graph and query both in a single statement.