SQL over HTTP and gRPC

The gateway that speaks the PostgreSQL protocol also takes SQL over HTTP and gRPC. Serverless functions, browsers behind a backend, and services without a PostgreSQL driver can run the same statements with the same token, and get the same answers and the same errors.

What you can rely on

• The same rules as psql. A request over HTTP or gRPC runs exactly as a PostgreSQL session would: the same login, tenant isolation, row-level security, column clearances, transactions, timeouts and SQLSTATEs. See SQL and psql.
• Checked twice. Every request carries a bearer token, the same signed token you use as the PostgreSQL password. The gateway checks it, and the data node checks it again before anything runs.
• All or nothing. Several statements in one request run as one transaction. Interactive transactions span several requests, and a transaction opened over HTTP can be continued over gRPC, and back, with the same token.
• Results of any size. Rows can be streamed as they are produced, so a large result never has to fit in one answer. The same holds for PostgreSQL clients: portals with a row limit and COPY TO stream too, as in PostgreSQL.
• A lost write is never repeated for you. When the connection to the database breaks after a write may have happened, you are told the outcome is unknown. Retry with the same idempotency key, or read back before retrying without one.
• Safe retries. A write sent with an idempotency key is applied at most once, however often it is retried, over HTTP, gRPC or the PostgreSQL protocol.
• Local unless encrypted. Without TLS the HTTP and gRPC listeners accept only loopback addresses, as the PostgreSQL listener does.

HTTP

Every request carries Authorization: Bearer <token>. A missing or refused token is 401 with SQLSTATE 28000.

One statement, or a batch

curl -s http://127.0.0.1:45433/v1/sql \
  -H "Authorization: Bearer $TOKEN" \
  -d '{"sql": "SELECT id, name FROM customers WHERE region = $1", "params": ["north"]}'

{"results": [{"command": "SELECT", "rows_affected": 1,
  "columns": [{"name": "id", "type": "int4", "oid": 23}, {"name": "name", "type": "text", "oid": 25}],
  "rows": [[1, "ann"]]}]}

A batch of up to 100 statements runs as one transaction:

{"statements": [
  {"sql": "INSERT INTO orders VALUES ($1, $2)", "params": [1, 10]},
  {"sql": "UPDATE stock SET n = n - 1 WHERE id = $1", "params": [10]}
]}

Values: integers and floats are JSON numbers (int8 optionally strings); numeric is a string, so it is never rounded; bool is true or false; dates and timestamps are PostgreSQL's text; json and jsonb are JSON; NULL is null.

BEGIN, COMMIT and other transaction control inside a statement are 0A000: use a batch or an interactive transaction.

Streaming large results

Send Accept: application/x-ndjson to stream one statement's rows as newline-delimited JSON: a first line {"columns": […]}, one line per row, and a last line {"command": …, "rows_affected": …}. An error after rows have started arrives as a last {"error": {…}} line instead. A buffered JSON answer over 64 MiB is refused with 54000; stream it instead.

curl -sN http://127.0.0.1:45433/v1/sql \
  -H "Authorization: Bearer $TOKEN" -H "Accept: application/x-ndjson" \
  -d '{"sql": "SELECT * FROM events"}'

Interactive transactions

An id works only with the token that opened it. One request runs at a time per transaction; a second concurrent one is 409. A transaction idle for 30 seconds is rolled back, and an error inside it rolls it back and says so ("transaction": "rolled_back").

Errors

{"error": {"code": "23505", "message": "duplicate key value violates unique constraint \"orders_pkey\"", "statement": 0}}

code is PostgreSQL's SQLSTATE; statement is the index of the failing statement in a batch. detail, hint, where and position appear when PostgreSQL would give them.

A client that disconnects mid-request cancels its statement, as a PostgreSQL cancel request does.

gRPC

The service is ultrafabric.v1.SqlService. Every call carries authorization: Bearer <token> metadata; a missing or refused token is UNAUTHENTICATED. Server reflection is on, so grpcurl works without the .proto file, and other languages generate their clients from the same service definition.

grpcurl -plaintext -H "authorization: Bearer $TOKEN" \
  -d '{"statements":[{"sql":"SELECT id, name FROM customers WHERE region = $1","params":[{"text":"north"}]}]}' \
  127.0.0.1:45434 ultrafabric.v1.SqlService/Execute

Transactions behave as over HTTP: another token gets NOT_FOUND, a second concurrent call is FAILED_PRECONDITION, 30 seconds idle rolls back. The call's deadline becomes the statement timeout, and a deadline that passes or a cancelled call cancels the statement (DEADLINE_EXCEEDED or CANCELLED, 57014). Arrow-format results are UNIMPLEMENTED for now; idempotency keys are described below.

Values

Errors

A failed call has a gRPC status and a google.rpc.ErrorInfo detail with domain ultrafabric, reason the SQLSTATE, and metadata holding sqlstate and, when present, detail, hint, where, position, statement and transaction.

Idempotency keys

A write sent with an idempotency key is applied at most once. When a reply is lost, send the same request again with the same key: either it had been applied, and you get the first answer back without anything running again, or it had not, and it runs now.

• The key commits with the writes. The key is recorded in the same commit as the rows, so whatever crashes, there are never rows without their key or a key without its rows.
• The same request. The same statements with the same parameter values (compared as PostgreSQL text, so JSON spacing does not matter) and the same read_only; for a commit, the same transaction. The same key with a different request is refused with SQLSTATE 22U01, and nothing runs. A request retried over HTTP after being sent over gRPC, or the other way round, is the same request.
• Your keys only. Keys belong to the token's tenant and user: another tenant, or another user of the same tenant, never sees or collides with them.
• 24 hours. A key is remembered for 24 hours after its commit; after that it is new again.
• Concurrent duplicates. Several identical keyed requests sent at once apply exactly once; the others are replays.
• Counts, not rows. A replay returns each statement's command and rows affected as first answered, but not rows: a replayed SELECT or RETURNING comes back without columns and rows.
• A request that fails, with any error other than outcome unknown, records nothing.

Over HTTP

Send Idempotency-Key: <key> (1 to 128 visible ASCII characters) on POST /v1/sql or on POST /v1/transactions/{id}/commit. A keyed /v1/sql request always runs in a transaction of its own, a single statement too.

curl -s http://127.0.0.1:45433/v1/sql \
  -H "Authorization: Bearer $TOKEN" -H "Idempotency-Key: order-1042" \
  -d '{"sql": "INSERT INTO orders VALUES ($1, $2)", "params": [1042, 10]}'

A retry of a request already committed under the key answers 200 with the header UF-Idempotent-Replay: true; a replayed commit answers {"committed": true}. Retrying a keyed commit whose transaction is no longer known answers the replay when that commit was made, and 404 when it was not. The same key with a different request is 422 (22U01). Keys are not accepted on other routes, on /v1/transactions/{id}/sql (key the commit instead) or with Accept: application/x-ndjson: those are 400.

Over gRPC

Execute without transaction_id and CommitTransaction take the key in their idempotency_key field or in idempotency-key metadata; if both are given they must be equal (INVALID_ARGUMENT, 22023). A replay answers with idempotent_replay = true and the response header metadata uf-idempotent-replay: true. The same key with a different request is INVALID_ARGUMENT with 22U01. A key on Execute with transaction_id is INVALID_ARGUMENT: key the commit instead.

Over the PostgreSQL protocol

Inside a transaction block, set the key for that block's COMMIT:

BEGIN;
SET LOCAL uf.idempotency_key = 'order-1042';
INSERT INTO orders VALUES (1042, 10);
COMMIT;

When the key was already committed for the same request, the SET LOCAL answers with a warning, SQLSTATE 01U01, whose detail holds the command tags first answered, and the COMMIT applies nothing. A key already used for a different request is 22U01. Only SET LOCAL in a block is accepted: a plain SET is 0A000, and outside a block it is 25P01. This is an UltraFabric extension; PostgreSQL has no such setting.

What comes next

Reading the change feed: changes are already captured per tenant, and reading them is being built. See Status and roadmap.