Connect to Materialize via HTTP

You can access Materialize through its “session-less” HTTP API endpoint:

https://<MZ host address>/api/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.
• Requires that you provide the entirety of your request. The API does not provide session-like semantics, so there is no way to e.g. interactively use transactions.
• Ceases to process requests upon encountering the first error.
• Does not support statements whose semantics rely on sessions or whose state is indeterminate at the end of processing, including:
  • CLOSE
  • COPY
  • DECLARE
  • FETCH
  • SUBSCRIBE

Transactional semantics

The HTTP 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

https://<MZ host address>/api/sql

Accessing the endpoint requires basic authentication. Reuse the same credentials as with psql:

• User ID: Your email to access Materialize.
• Password: Your app password.

Input format

Simple

The request body 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 request body 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 output format is a JSON object with one key, results, whose value is an array of the following:

Each committed statement returns exactly one of these values; e.g. in the case of “complex responses”, such as INSERT INTO...RETURNING, the presence of a "rows" object implies "ok".

The "notices" array is present in all types of results and contains any diagnostic messages that were generated during execution of the query. It has the following structure:

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

Note that the returned values include the results of statements which were ultimately rolled back because of an error in a later part of the transaction. You must parse the results to understand which statements ultimately reflect the resultant state.

TypeScript definition

You can model these with the following TypeScript definitions:

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 SqlResult =
  | {
	tag: string;
	rows: any[][];
	col_names: string[];
	notices: Notice[];
} | {
	ok: string;
	notices: Notice[];
} | {
	error: string;
	notices: Notice[];
};

Examples

Run a transaction

Use the extended input format to run a transaction:

curl 'https://<MZ host address>/api/sql' \
    --header 'Content-Type: application/json' \
    --user '<username>:<passsword>' \
    --data '{
        "queries": [
            { "query": "CREATE TABLE IF NOT EXISTS t (a int);" },
            { "query": "CREATE TABLE IF NOT EXISTS s (a int);" },
            { "query": "BEGIN;" },
            { "query": "INSERT INTO t VALUES ($1), ($2)", "params": ["100", "200"] },
            { "query": "COMMIT;" },
            { "query": "BEGIN;" },
            { "query": "INSERT INTO s VALUES ($1), ($2)", "params": ["9", null] },
            { "query": "COMMIT;" }
        ]
    }'

Response:

{
  "results": [
    {"ok": "CREATE TABLE", "notices": []},
    {"ok": "CREATE TABLE", "notices": []},
    {"ok": "BEGIN", "notices": []},
    {"ok": "INSERT 0 2", "notices": []},
    {"ok": "COMMIT", "notices": []},
    {"ok": "BEGIN", "notices": []},
    {"ok": "INSERT 0 2", "notices": []},
    {"ok": "COMMIT", "notices": []}
  ]
}

Run a query

Use the simple input format to run a query:

curl 'https://<MZ host address>/api/sql' \
    --header 'Content-Type: application/json' \
    --user '<username>:<passsword>' \
    --data '{
        "query": "SELECT t.a + s.a AS cross_add FROM t CROSS JOIN s; SELECT a FROM t WHERE a IS NOT NULL;"
    }'

Response:

{
  "results": [
    {
        "tag": "SELECT 4",
        "rows": [[null],[null],[109],[209]],
        "col_names": ["cross_add"],
        "notices": []
    },
    {
        "tag": "SELECT 2",
        "rows":[[100],[200]],
        "col_names":["a"],
        "notices": []
    }
  ]
}

See also

• SQL Clients