Read data¶

Select rows from a table¶

Let's create a table and insert some data:

skdb.exec(`
INSERT INTO
  test (n, f, t)
VALUES
  (0, 0.1, 'foo'),
  (1, 0.2, 'bar'),
  (2, 0.3, 'foo')`);

We can run SELECT queries using exec. Here are a few examples:

console.log("Raw data:", await skdb.exec("SELECT * FROM test"));

console.log("\nNow we'll pretty print results.\n");

console.log("Choose columns with a simple filter");
console.table(await skdb.exec("SELECT t FROM test WHERE n < 2"));

console.log("Simple aggregation");
console.table(await skdb.exec(`
SELECT
  t,
  sum(n) AS total
FROM
  test
GROUP BY
  t`));

console.log("Nested query");
console.table(await skdb.exec(`
SELECT
  sum(derived) AS total
FROM
  (
    SELECT
      cast(n AS float) + f AS derived
    FROM
      test
  )`));

console.log("Union");
console.table(await skdb.exec(`
SELECT
  t,
  n
FROM
  test
WHERE
  t = 'foo'
UNION
ALL
SELECT
  t,
  n
FROM
  test
WHERE
  n % 2 = 0`));

Raw data: SKDBTable(3) [
  { id: '3g476VldeHN8sJBsr5brOM6UIp1', n: 1, f: 0.2, t: 'bar' },
  { id: '3g476ZNkj9-b3vj6issFQDLSux1', n: 0, f: 0.1, t: 'foo' },
  { id: '3g476eop_aeUKPC_AhCVN3cE-XE', n: 2, f: 0.3, t: 'foo' }
]

Now we'll pretty print results.

Choose columns with a simple filter
┌─────────┬───────┐
│ (index) │ t     │
├─────────┼───────┤
│ 0       │ 'bar' │
│ 1       │ 'foo' │
└─────────┴───────┘
Simple aggregation
┌─────────┬───────┬───────┐
│ (index) │ t     │ total │
├─────────┼───────┼───────┤
│ 0       │ 'bar' │ 1     │
│ 1       │ 'foo' │ 2     │
└─────────┴───────┴───────┘
Nested query
┌─────────┬───────┐
│ (index) │ total │
├─────────┼───────┤
│ 0       │ 3.6   │
└─────────┴───────┘
Union
┌─────────┬───────┬───┐
│ (index) │ t     │ n │
├─────────┼───────┼───┤
│ 0       │ 'foo' │ 0 │
│ 1       │ 'foo' │ 0 │
│ 2       │ 'foo' │ 2 │
│ 3       │ 'foo' │ 2 │
└─────────┴───────┴───┘

Parameterize queries¶

Query parameters allow you to easily include application data in your queries. This is convenient but also ensures that the input data is not interpreted as SQL, avoiding SQL injection attacks.

Recommendation: Always use parameterized queries to avoid SQL injection vulnerabilities.

Continuing from our basic SELECT statements example, this illustrates the use of query parameters.

const maxN = 2;
const interestingText = "foo";
const rows = await skdb.exec(
  "SELECT * FROM test WHERE n < @maxN AND t = @interestingText",
  { maxN, interestingText }
);

console.log(rows);

SKDBTable(1) [
  { id: '3g476GCwJf9LUp0O3UTzbpETDcA', n: 0, f: 0.1, t: 'foo' }
]

Parameters are indicated with the '@' symbol in the SQL query. They are replaced with values from the application as per the map that is passed to exec.

Work with an `SKDBTable` object¶

When SKDB provides the results of a query, it does so using an SKDBTable object. exec returns an SKDBTable, watch and watchChanges invoke callbacks with an SKDBTable.

SKDBTable objects are Array objects -- they are arrays of JavaScript records corresponding to the select parameters -- so any of the usual JavaScript array methods will work. e.g. you can index, use length, concat() and so on.

SKDBTable objects also support the following convenience methods when working with table data:

scalarValue(): any | undefined
- Get the only value, from a single column, single row table. Or undefined if there are no rows or no columns.
onlyRow(): Record<string, any>
- Get the only row, or throw if there are no rows or multiple rows.
firstRow(): Record<string, any> | undefined
- Get the first row or undefined if there are no rows.
lastRow(): Record<string, any> | undefined
- Get the last row or undefined if there are no rows.
onlyColumn(): any[]
- Extract the only column as an array of values. Returns an empty array if there are no rows or columns, throws if there are multiple columns.
column(name: string): any[]
- Extract the column named name as an array of values.

Continuing from our basic SELECT statements example, this illustrates some of the methods on SKDBTable.

const fullTable = await skdb.exec("SELECT * FROM test");
const oneCol = await skdb.exec("SELECT id FROM test");
const oneRow = await skdb.exec("SELECT * FROM test WHERE t = 'bar'");
const oneValue = await skdb.exec("SELECT count(*) as n FROM test");

console.log("Full table:", fullTable);
console.log("Only column:", oneCol.onlyColumn());
console.log("Only row:", oneRow.onlyRow());
console.log("Only value:", oneValue.scalarValue());
console.log("'f' col:", fullTable.column('f'));

Full table: SKDBTable(3) [
  { id: '3g475ZhNXCKN09Wu5mq2YwjBLFE', n: 1, f: 0.2, t: 'bar' },
  { id: '3g475hqMhIc_Y8LQbpWvmlQxWq0', n: 0, f: 0.1, t: 'foo' },
  { id: '3g475iUoTDY2YltoLjelAGeSCiE', n: 2, f: 0.3, t: 'foo' }
]
Only column: SKDBTable(3) [
  '3g475ZhNXCKN09Wu5mq2YwjBLFE',
  '3g475hqMhIc_Y8LQbpWvmlQxWq0',
  '3g475iUoTDY2YltoLjelAGeSCiE'
]
Only row: { id: '3g475ZhNXCKN09Wu5mq2YwjBLFE', n: 1, f: 0.2, t: 'bar' }
Only value: 3
'f' col: SKDBTable(3) [ 0.2, 0.1, 0.3 ]

watch allows you to run any SQL query that SKDB supports and get the whole result set each time it changes.

This is very useful when wanting to rebuild views over the whole result set, e.g. when re-rendering react components or re-writing documents into a cache.

You can watch a mirrored table to learn about changes other users are making in real time.

Thanks to SKDB's incremental engine, a watched query is never re-run. It is incrementally updated only when relevant data changes in the database. Due to the incremental engine, watched queries are very efficient and you can run a large number of them at once.

Assuming we've created a 'test' table. Let's see how watch works:

const handle = await skdb.watch(
  "select * from test where t = @search",
  { search: "foo" },
  (table) => {
    console.log("Update from watch:", table);
  }
);

await skdb.exec("INSERT INTO test(t) VALUES ('foo')");
await skdb.exec("INSERT INTO test(t) VALUES ('bar')");
await skdb.exec("UPDATE test SET t = 'foo' WHERE t = 'bar'");
await skdb.exec("DELETE FROM TEST");

await handle.close();

Update from watch: SKDBTable(0) []
Update from watch: SKDBTable(1) [
  { id: '3g476V8MILv_R-Y01i4Hxu9hUI0', n: null, f: null, t: 'foo' }
]
Update from watch: SKDBTable(2) [
  { id: '3g476V8MILv_R-Y01i4Hxu9hUI0', n: null, f: null, t: 'foo' },
  { id: '3g476cdIUmUA5CTd_lMJCHxOQ47', n: null, f: null, t: 'foo' }
]
Update from watch: SKDBTable(0) []

watchChanges allows you to run any SQL query that SKDB supports, get the results, and then learn how the results are changing as the underlying data changes.

You can use watchChanges with a mirrored table to learn about changes other users are making in real time.

Thanks to SKDB's incremental engine, a watched query is never re-run. It is incrementally updated only when relevant data changes in the database. Due to the incremental engine, watching for changes is very efficient and you can watch a large number of queries at once.

Assuming we've created a 'test' table. Let's see how watchChanges works:

await skdb.exec("INSERT INTO test(t) VALUES ('foo')");

const handle = await skdb.watchChanges(
  "select id, t from test",
  {},
  (table) => {
    console.log("Initial result set:", table);
    console.log("");
  },
  (added, removed) => {
    console.log("The query result has changed.");
    console.log("Rows added:", added);
    console.log("Rows removed:", removed);
    console.log("");
  }
);

await skdb.exec("UPDATE test SET t = 'bar' WHERE t = 'foo'");

await handle.close();

Initial result set: SKDBTable(1) [ { id: '3g477R_vQUOXHpKF1ILKxxiNCR0', t: 'foo' } ]

The query result has changed.
Rows added: SKDBTable(1) [ { id: '3g477R_vQUOXHpKF1ILKxxiNCR0', t: 'bar' } ]
Rows removed: SKDBTable(1) [ { id: '3g477R_vQUOXHpKF1ILKxxiNCR0', t: 'foo' } ]

Build reactive views¶

Reactive views materialize queries - they build and maintain a new read only table defined by any SQL query supported by SKDB. This allows the resulting table to be queried without recomputing the view constantly.

This allows you to very efficiently maintain expensive joins, filters, and aggregations, for example.

The SKDB engine was purpose built to maintain reactive views incrementally. As data is changed in source table(s) any dependent views are updated very efficiently. SKDB knows if the view is affected by the update and will update just the rows that need to be.

Let's assume that we have created a local table -- but this of course works for mirrored tables too.

We can build a reactive view using exec:

await skdb.exec(
  `CREATE REACTIVE VIEW test_totals AS
SELECT
  t,
  sum(n) AS total
FROM
  test
GROUP BY
  t;`
);
console.log("Schema with the reactive view:")
console.log(await skdb.schema());

await skdb.exec("INSERT INTO test (t, n) VALUES ('foo', 1), ('bar', 1), ('foo', 7)");

console.log("Querying the table:")
console.table(await skdb.exec("SELECT t, n FROM test"));
console.log("Querying the view:")
console.table(await skdb.exec("SELECT * FROM test_totals ORDER BY total"));

Schema with the reactive view:
CREATE TABLE test (
  id TEXT PRIMARY KEY,
  n INTEGER,
  f FLOAT,
  t TEXT
);
CREATE REACTIVE VIEW test_totals AS SELECT t, sum(n) AS total FROM test GROUP BY t;

Querying the table:
┌─────────┬───────┬───┐
│ (index) │ t     │ n │
├─────────┼───────┼───┤
│ 0       │ 'bar' │ 1 │
│ 1       │ 'foo' │ 7 │
│ 2       │ 'foo' │ 1 │
└─────────┴───────┴───┘
Querying the view:
┌─────────┬───────┬───────┐
│ (index) │ t     │ total │
├─────────┼───────┼───────┤
│ 0       │ 'bar' │ 1     │
│ 1       │ 'foo' │ 8     │
└─────────┴───────┴───────┘

Reactive views can be built on top of reactive views and the whole graph will be maintained incrementally.

You can use watch and watchChanges with a reactive view too.

Read data¶

Select rows from a table¶

Parameterize queries¶

Work with an SKDBTable object¶

Subscribe to SQL queries and react to updates¶

Subscribe to SQL queries and monitor only the changes¶

Build reactive views¶

Work with an `SKDBTable` object¶