Connect to Materialize via WebSocket

ALPHA! This feature is in alpha. It has known performance or stability issues and is under active development. It is not subject to our backwards compatibility guarantees.

You can access Materialize through its interactive WebSocket API endpoint:

wss://<MZ host address>/api/experimental/sql

Details

General semantics

The API:

  • Requires username/password authentication, just as connecting via psql. Materialize provides you the username and password upon setting up your account.
  • Maintains an interactive session.
  • Does not support some statements:
    • CLOSE
    • COPY
    • DECLARE
    • FETCH

Transactional semantics

The WebSocket API provides two modes with slightly different transactional semantics from one another:

  • Simple, which mirrors PostgreSQL’s Simple Query protocol.
    • Supports a single query, but the single query string may contain multiple statements, e.g. SELECT 1; SELECT 2;
    • Treats all statements as in an implicit transaction unless other transaction control is invoked.
  • Extended, which mirrors PostgreSQL’s Extended Query protocol.
    • Supports multiple queries, but only one statement per query string.
    • Supports parameters.
    • Eagerly commits DDL (e.g. CREATE TABLE) in implicit transactions, but not DML (e.g. INSERT).

Usage

Endpoint

wss://<MZ host address>/api/experimental/sql

To authenticate using a username and password, send an initial text or binary message containing a JSON object:

{
  "user": "<Your email to access Materialize>"
	"password": "<Your app password>",
}

To authenticate using a token, send an initial text or binary message containing a JSON object:

{
	"token": "<Your access token>",
}

Successful authentication will result in an initial ReadyForQuery response from the server. Otherwise an error is indicated by a websocket Close message.

HTTP Authorization headers are ignored.

Messages

WebSocket Text or Binary messages can be sent. The payload is described below in the Input format section. Each request will respond with some number of response messages, followed by a ReadyForQuery message. There is exactly one ReadyForQuery message for each request, regardless of how many queries the request contains.

Input format

Simple

The message payload is a JSON object containing a key, query, which specifies the SQL string to execute. query may contain multiple SQL statements separated by semicolons.

{
    "query": "select * from a; select * from b;"
}

Extended

The message payload is a JSON object containing a key queries, whose value is array of objects, whose structure is:

Key Value
query A SQL string containing one statement to execute
params An optional array of text values to be used as the parameters to query. null values are converted to null values in Materialize. Note that all parameter values' elements must be text or null; the API will not accept JSON numbers.
{
    "queries": [
        { "query": "select * from a;" },
        { "query": "select a + $1 from a;", "params": ["100"] }
        { "query": "select a + $1 from a;", "params": [null] }
    ]
}

Output format

The response messages are WebSocket Text messages containing a JSON object that contains keys type and payload.

type value Description
ReadyForQuery Sent at the end of each response batch
Notice An informational notice.
CommandComplete Executing a statement succeeded.
Error Executing a statement resulted in an error.
Rows A rows-returning statement is executing, and some Row messages may follow.
Row A single row result.

ReadyForQuery

Exactly one of these is sent at the end of every request batch. It can be used to synchronize with the server, and means the server is ready for another request. (However, many requests can be made at any time; there is no need to wait for this message before issuing more requests.) The payload is a string describing the current transaction state:

  • I for idle: not in a transaction.
  • T for in a transaction.
  • E for a transaction in an error state. A request starting with ROLLBACK should be issued to exit it.

Notice

A notice can appear at any time and contains diagnostic messages that were generated during execution of the query. The payload has the following structure:

{"severity": <"warning"|"notice"|"debug"|"info"|"log">, "message": <informational message>}

Error

Executing a statement resulted in an error. The payload is a string containing the error.

CommandComplete

Executing a statement succeeded. The payload is a string containing the statement’s tag.

Rows

A rows-returning statement is executing and some number (possibly 0) of Row messages will follow. The payload is an array of string containing the column names of the row results. Either a CommandComplete or Error message will always follow indicating there are no more rows and the final result of the statement.

Row

A single row result. Will only occur after a Rows message. The payload is an array of JSON values corresponding to the columns from the Rows message.

TypeScript definition

You can model these with the following TypeScript definitions:

type Auth =
    | { user: string; password: string }
    | { token: string }
    ;

interface Simple {
    query: string;
}

interface ExtendedRequest {
    query: string;
    params?: (string | null)[];
}

interface Extended {
    queries: ExtendedRequest[];
}

type SqlRequest = Simple | Extended;

interface Notice {
    message: string;
    severity: string;
}

type WebSocketResult =
    | { type: "ReadyForQuery"; payload: string }
    | { type: "Notice"; payload: Notice }
    | { type: "CommandComplete"; payload: string }
    | { type: "Error"; payload: string }
    | { type: "Rows"; payload: string[] }
    | { type: "Row"; payload: any[] }
    ;

Examples

Run a query

$ echo '{"query": "select 1,2; values (4), (5)"}' | websocat wss://<MZ host address>/api/experimental/sql
{"type":"Rows","payload":["?column?","?column?"]}
{"type":"Row","payload":["1","2"]}
{"type":"CommandComplete","payload":"SELECT 1"}
{"type":"Rows","payload":["column1"]}
{"type":"Row","payload":["4"]}
{"type":"Row","payload":["5"]}
{"type":"CommandComplete","payload":"SELECT 2"}
{"type":"ReadyForQuery","payload":"I"}

See also

Back to top ↑