Node.js Cheatsheet

Materialize is PostgreSQL-compatible, which means that Node.js applications can use any existing PostgreSQL client to interact with Materialize as if it were a PostgreSQL database. In this guide, we’ll use the node-postgres library to connect to Materialize and issue PostgreSQL commands.

Connect

You connect to Materialize the same way you connect to PostgreSQL with node-postgres.

Local Instance

You can connect to a local Materialize instance with code that uses the connection URI shorthand (postgres://<USER>@<HOST>:<PORT>/<SCHEMA>) to define the URI:

const { Client } = require('pg');
const client = new Client('postgres://materialize@localhost:6875/materialize');

async function main() {
    await client.connect();
    /* Work with Materialize */
}

main();

Materialize Cloud Instance

Download your instance’s certificate files from the Materialize Cloud Connect dialog and specify the path to each file in the ssl property. Replace MY_INSTANCE_ID in the connectionString property with your Materialize Cloud instance ID.

const { Client } = require('pg');
const fs = require('fs');
const client = new Client({
    connectionString: "postgresql://materialize@MY_INSTANCE_ID.materialize.cloud:6875/materialize",
    ssl: {
        ca   : fs.readFileSync("ca.crt").toString(),
        key  : fs.readFileSync("materialize.key").toString(),
        cert : fs.readFileSync("materialize.crt").toString(),
    }
});

async function main() {
    await client.connect();
    /* Work with Materialize */
}

main();

Stream

To take full advantage of incrementally updated materialized views from a Node.js application, instead of querying Materialize for the state of a view at a point in time, use a TAIL statement to request a stream of updates as the view changes.

To read a stream of updates from an existing materialized view, open a long-lived transaction with BEGIN and use TAIL with FETCH to repeatedly fetch all changes to the view since the last query.

const { Client } = require('pg');

async function main() {
  const client = new Client('postgres://materialize@localhost:6875/materialize');
  await client.connect();

  await client.query('BEGIN');
  await client.query('DECLARE c CURSOR FOR TAIL my_view');

  while (true) {
    const res = await client.query('FETCH ALL c');
    console.log(res.rows);
  }
}

main();

The TAIL Output format of res.rows is an array of view update objects. When a row of a tailed view is updated, two objects will show up in the rows array:

[
    ...
    {
        mz_timestamp: '1627225629000',
        mz_diff: '1',
        my_column_one: 'ABC',
        my_column_two: 'new_value'
    },
    {
        mz_timestamp: '1627225629000',
        mz_diff: '-1',
        my_column_one: 'ABC',
        my_column_two: 'old_value'
    },
    ...
]

An mz_diff value of -1 indicates Materialize is deleting one row with the included values. An update is just a deletion (mz_diff: '-1') and an insertion (mz_diff: '1') with the same mz_timestamp.

Query

Querying Materialize is identical to querying a traditional PostgreSQL database: Node.js executes the query, and Materialize returns the state of the view, source, or table at that point in time.

Because Materialize maintains materialized views in memory, response times are much faster than traditional database queries, and polling (repeatedly querying) a view doesn’t impact performance.

Query a view my_view with a select statement:

const { Client } = require('pg');
const client = new Client('postgres://materialize@localhost:6875/materialize');

async function main() {
  await client.connect();
  const res = await client.query('SELECT * FROM my_view');
  console.log(res.rows);
};

main();

For more details, see the node-postgres query and pg.Result documentation.

Push data to a source

Materialize processes live streams of data and maintains views in memory, relying on other systems (traditional databases, S3, or Kafka) to serve as “systems of record” for the data. Instead of updating Materialize directly, Node.js should send data to an intermediary system. Materialize connects to the intermediary system as a “source” and reads streaming updates from that.

The table below lists the intermediary systems a Node.js application can use to stream data for Materialize:

Insert data into tables

Most data in Materialize will stream in via a SOURCE, but a TABLE in Materialize can be helpful for supplementary data. For example, use a table to join slower-moving reference or lookup data with a stream.

Basic Example: Insert a row of data into a table named countries in Materialize.

const { Client } = require('pg');
const client = new Client('postgres://materialize@localhost:6875/materialize');

const text = 'INSERT INTO countries(code, name) VALUES($1, $2);';
const values = ['GH', 'GHANA'];

async function main() {
    await client.connect();
    const res = await client.query(text, values);
    console.log(res);
}

main();

Manage sources, views, and indexes

Typically, you create sources, views, and indexes when deploying Materialize, although it is possible to use a Node.js app to execute common DDL statements.

Create a source from Node.js

const { Client } = require('pg');
const client = new Client('postgres://materialize@localhost:6875/materialize');

async function main() {
    await client.connect();
    const res = await client.query(
        `CREATE SOURCE market_orders_raw_2 FROM PUBNUB
            SUBSCRIBE KEY 'sub-c-4377ab04-f100-11e3-bffd-02ee2ddab7fe'
            CHANNEL 'pubnub-market-orders'`
        );
    console.log(res);
}

main();

For more information, see CREATE SOURCE.

Create a view from Node.js

const { Client } = require('pg');
const client = new Client('postgres://materialize@localhost:6875/materialize');

async function main() {
    await client.connect();
    const res = await client.query(
        `CREATE VIEW market_orders_2 AS
            SELECT
                val->>'symbol' AS symbol,
                (val->'bid_price')::float AS bid_price
            FROM (SELECT text::jsonb AS val FROM market_orders_raw)`
        );
    console.log(res);
}

main();

For more information, see CREATE VIEW.

Node.js ORMs

Materialize doesn’t currently support the full catalog of PostgreSQL system metadata API endpoints, including the system calls that object relational mapping systems (ORMs) like Prisma, Sequelize, or TypeORM use to introspect databases and do extra work behind the scenes. This means that ORM system attempts to interact with Materialize will currently fail. Once full pg_catalog support is implemented, the features that depend on pg_catalog may work properly.