Connect to Materialize via WebSocket

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
• Supports specifying run-time configuration parameters (session variables) via the initial authentication message.

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>",
    "options": { <Optional map of session variables> }
}

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

{
    "token": "<Your access token>",
    "options": { <Optional map of session variables> }
}

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:

{
    "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.

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:

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

Error

Executing a statement resulted in an error. The payload has the following structure:

{
    "message": <informational message>,
    "code": <error code>,
    "detail": <optional error detail>,
    "hint": <optional error hint>,
}

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; options?: { [name: string]: string } }
    | { token: string; options?: { [name: string]: 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;
	detail?: string;
	hint?: string;
}

interface Error {
	message: string;
	code: string;
	detail?: string;
	hint?: string;
}

type WebSocketResult =
    | { type: "ReadyForQuery"; payload: string }
    | { type: "Notice"; payload: Notice }
    | { type: "CommandComplete"; payload: string }
    | { type: "Error"; payload: Error }
    | { 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

• SQL Clients