HTTP Client

Read the response shape

Every http_* function returns a JSON object with three fields. Pull them out with ->>:

WITH r AS (
  SELECT http_get('https://httpbin.org/get') AS res
)
SELECT
  (res->>'status')::INT  AS status,
  (res->>'reason')       AS reason,
   res->>'body'          AS body
FROM r;

To dive into a JSON response body, cast it on the way out: ((res->>'body')::JSON)->>'$.field'. See http_get for the full signature.

GET a JSON API and project a field

SELECT ((http_get('https://api.github.com/repos/duckdb/duckdb')->>'body')::JSON)->>'$.stargazers_count' AS stars;

Same pattern for any read endpoint that returns JSON — fetch, cast body to JSON, project with ->>.

POST a JSON body

params becomes the JSON body. Set content-type so the server knows to parse it as JSON:

SELECT http_post(
  'https://api.example.com/users',
  headers => MAP{
    'accept':       'application/json',
    'content-type': 'application/json'
  },
  params  => MAP{
    'name':  'Alice',
    'email': '[email protected]'
  }
);

See http_post.

POST as application/x-www-form-urlencoded

For HTML forms, older REST APIs, and most OAuth 2.0 token endpoints. The extension sets the Content-Type for you — you don’t need to put it in headers:

SELECT http_post_form(
  'https://oauth.example.com/token',
  headers => MAP{},
  params  => MAP{
    'grant_type':    'client_credentials',
    'client_id':     :client_id,
    'client_secret': :client_secret
  }
);

See http_post_form.

Authorize with a Bearer token from the environment

Source credentials from the environment with getenv and bind them via SET VARIABLE so they don’t leak into cached query plans or EXPLAIN output:

SET VARIABLE api_token = getenv('API_TOKEN');

SELECT http_post(
  'https://api.example.com/comments',
  headers => MAP{
    'authorization': 'Bearer ' || :api_token,
    'content-type':  'application/json'
  },
  params  => MAP{'body': 'Hello from DuckDB'}
);

Exchange OAuth credentials for an access token

The classic application/x-www-form-urlencoded handshake — followed by a JSON parse on the response body to lift the access token out:

SET VARIABLE client_id     = getenv('OAUTH_CLIENT_ID');
SET VARIABLE client_secret = getenv('OAUTH_CLIENT_SECRET');

WITH t AS (
  SELECT http_post_form(
    'https://oauth.example.com/token',
    headers => MAP{},
    params  => MAP{
      'grant_type':    'client_credentials',
      'client_id':     :client_id,
      'client_secret': :client_secret
    }
  ) AS res
)
SELECT ((res->>'body')::JSON)->>'$.access_token' AS access_token
FROM t;

Per-row enrichment from a REST API

SELECT
  u.id,
  u.email,
  ((http_get('https://api.example.com/score/' || u.id::VARCHAR)->>'body')::JSON)->>'$.score' AS score
FROM users u
WHERE u.last_seen > now() - INTERVAL 1 HOUR
LIMIT 100;

Practical for tens or hundreds of rows. For thousands, pre-aggregate to distinct keys first or cache the result into a DuckDB table — one HTTP call per row adds up fast.

Cache responses into a table

Hit the API once, query the cache forever after:

CREATE TABLE api_scores AS
SELECT
  u.id,
  ((http_get('https://api.example.com/score/' || u.id::VARCHAR)->>'body')::JSON)->>'$.score' AS score
FROM (SELECT DISTINCT id FROM users) u;

Re-run this only when you want a refresh. Subsequent joins against api_scores are local and fast.

Conditional logic on the status code

Branch in SQL based on the HTTP status — use CASE on the status field:

WITH r AS (
  SELECT http_get('https://api.example.com/things/42') AS res
)
SELECT
  CASE (res->>'status')::INT
    WHEN 200 THEN 'ok'
    WHEN 404 THEN 'missing'
    WHEN 429 THEN 'rate-limited'
    ELSE       'unexpected'
  END AS outcome,
  res->>'body' AS body
FROM r;

Liveness check with HEAD

SELECT (http_head('https://httpbin.org/')->>'status')::INT AS status;

http_head returns the same response metadata as http_get but the server skips the body. Useful for “is this URL reachable?” probes before a heavier GET.

Trigger a webhook from a SQL pipeline

End a pipeline by POSTing a summary to a webhook — synchronous, so the webhook either succeeds or the surrounding statement sees the error:

SET VARIABLE hook = getenv('WEBHOOK_URL');

SELECT http_post(
  :hook,
  headers => MAP{'content-type': 'application/json'},
  params  => MAP{
    'event':     'pipeline_complete',
    'rows':      (SELECT count(*)::VARCHAR FROM staging),
    'finished':  now()::VARCHAR
  }
);