Calling web services from SQL (http_get, http_post)

http_get and http_post call a web address from SQL, a procedure or a timer. Off until an admin lists the hosts they may call.

• Category: Stored procedures
• Keywords: http_get, http_post, http, https, web service, webhook, call url, rest api, outbound, http_functions, http_timeout_ms, KDB_HTTP_FUNCTIONS, allowlist, fetch, curl
• See also: Stored procedures, Timers, Triggers, JSON functions, Users and roles, Settings page (data folder, old log, capacity, program files and users)

http_get and http_post let SQL call another service. A procedure can tell a settlement service that a day is done. A timer can ask a checker every hour.

• They are off until an admin lists the hosts they may call (the setting http_functions).
• They need role write on the database, because a call changes things outside it.
• Every call really runs. The answer is never kept in the result cache.
• The headers you send are never written to a log. They often hold a token.

Example: On a server where nobody turned them on, a call is refused (shows an error)

SELECT http_get('http://10.0.0.5:8080/health');

Result:

Error: line 1, column 8: http_get: calling web addresses from SQL is off on this server; an admin turns it on by listing the hosts it may call in the setting http_functions (kdb.conf, KDB_HTTP_FUNCTIONS or the Settings page)

http_get

Syntax:

http_get(url)
http_get(url, headers)

Sends a GET request and waits for the whole answer.

• Returns: JSON like {"status":200,"headers":{"content-type":"application/json"},"body":"..."}.
  • status is the HTTP status. A 404 or a 500 is an answer, not an error.
  • headers has the answer's headers, names in lower case.
  • body is the answer as text.
• NULL: a NULL url gives NULL. NULL headers count as none.
• Errors: the statement fails when the host is not allowed, the address cannot be reached, the time limit passes, or the answer is over 16 MB.

http_post

Syntax:

http_post(url, body)
http_post(url, body, content_type)
http_post(url, body, content_type, headers)

Sends a POST request with a body.

• Returns: the same JSON as http_get.

Reading the answer

Use the JSON operators and functions on the result:

A procedure that calls a service and keeps what it said (it runs once http_functions lists 10.0.0.5:8080):

CREATE TABLE settle_log (at TIMESTAMP, status INT, body STRING);

CREATE PROCEDURE settle_day() AS BEGIN
  INSERT INTO settle_log
  SELECT now(), json_value_long(r, '$.status'), r ->> 'body'
  FROM (SELECT http_post('http://10.0.0.5:8080/settle', json_object('day', current_date),
                         'application/json', '{"Authorization":"Bearer my-token"}') AS r) AS call;
END;

Turning them on

kdb.conf wins over the environment. A save on the Settings page applies at once, without a restart.

How to write the list:

{"http_functions": "settle.internal:8080, 10.0.0.5", "http_timeout_ms": 5000}

What a call may do

• A self-signed certificate is not trusted. Call such a service on the local network over http://.
• On Linux the trusted authorities come from the usual CA bundle file (/etc/ssl/certs/ca-certificates.crt and others). Set SSL_CERT_FILE to use another file.
• A build without TLS says so when asked for https://.

Who may call

The text of a statement goes to the activity log and the slow-query log. There, every argument after the address shows as '***', so a token in the headers is never kept.