Live Query API

[samwillis]

Stacked on #99

This added a new "Live Query" extension/plugin (our first one).

To use the extension it needs adding to the PGlite instance when creating it:

import { PGlite } from "@electric-sql/pglite";
import { live } from "@electric-sql/pglite/live";

const pg = new PGlite({
  extensions: {
    live,
  },
});

There are three methods on the live namespace:

• live.query() for basic live queries. Less machinery in PG so quicker for small results sets and narrow rows.
• live.incrementalQuery() for incremental queries. It materialises the full result set on each update in js. Perfect for feeding into React
• live.changes() a lower level api that emits the changes (insert/update/delete) that can then be mapped to mutation in a UI or other datastore.

query()

This is very similar to a standard query, but takes an additional callback that receives the results whenever they change:

const ret = pg.live.query("SELECT * FROM test ORDER BY rand;", null, (res) => {
  // res is the same as a standard query result object
});

The returned value from the call is an object with this interface:

https://github.com/electric-sql/pglite/blob/a678da01e34519bbc5bd9545d5b1712f62b24430/packages/pglite/src/live/interface.ts#L49-L53

• initialResults is the initial results set (also sent to the callback
• unsubscribe allow you to unsubscribe from the live query
• refresh allows you to force a refresh of the query

Internally it watches for the tables that the query depends on, and reruns the query whenever they are changes.

incrementalQuery()

Similar to above, but maintains a temp table inside of Postgres of the previous state. When the tables it depends on change the query is re-run and diffed with the last state. Only the changes from the last version of the query are copied from WASM into JS.

It requires an additional key argument, the name of a column (often a PK) to key the diff on.

const ret = pg.live.incrementalQuery(
  "SELECT * FROM test ORDER BY rand;", [], "id",
  (res) => {
   // res is the same as a standard query result object
  }
);

The returned value is of the same type as the query method above.

live.changes()

A lower level API that is the backend for the incrementalQuery, it emits the change that have happed. It again requires a key to key the diff on:

const ret = pg.live.changes(
  "SELECT * FROM test ORDER BY rand;", [], "id",
  (res) => {
   // res is a change result object
  }
);

the returned value from the call is defined by this interface:

https://github.com/electric-sql/pglite/blob/a678da01e34519bbc5bd9545d5b1712f62b24430/packages/pglite/src/live/interface.ts#L55-L60

The results passed to the callback are array of Change objects:

https://github.com/electric-sql/pglite/blob/a678da01e34519bbc5bd9545d5b1712f62b24430/packages/pglite/src/live/interface.ts#L62-L80

Each change has it's new values as part of the object along with:

• __changed_columns__ the columns names that were changes
• __op__ the operation that is required to update the state (INSERT, UPDATE, DELETE)
• __after__ the key of the row that this row should be after, it will be included in __changed_columns__ if it has been changed.

To do:

• [ ] Docs
• [ ] Tests