HTTP Client

What you can do with one query

The shortest path from “REST API” to “DuckDB row”:

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

http_get issues a synchronous HTTP GET, returns the response as a JSON object with status / reason / body, and lets you cast and chain ->> right where you need the field. No Python wrapper, no curl in the middle, no temp file.

Architecture

The extension is a thin SQL surface over a synchronous HTTP request. There is no ATTACH, no secrets type, no connection pool, no background fetcher — just three scalar functions:

• http_get(url) — GET request, no headers parameter in the documented signature.
• http_post(url, headers, params) — POST with the params MAP JSON-encoded into the body.
• http_post_form(url, headers, params) — POST with params URL-encoded as application/x-www-form-urlencoded.

Each call returns a JSON object — {status, reason, body} — that you extract with DuckDB’s JSON operators. The body is always a string; cast it with (body)::JSON if the response is JSON and you want to drill in.

Because the function shape is stable and synchronous, the result of an http_* call composes cleanly into CTEs, lateral joins, and CREATE TABLE AS — anywhere a scalar JSON value is acceptable. The cost is the obvious one: one HTTP call per evaluation, in the order DuckDB chooses to evaluate the scalar.

Securing credentials

Any value passed inline to headers or params ends up in the cached query plan and EXPLAIN output. For Bearer tokens, OAuth client secrets, and other credentials, source them from the environment with getenv and bind them via SET VARIABLE:

SET VARIABLE api_token = getenv('API_TOKEN');

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

getenv resolves at parse time, so the literal credential never appears in any cached query plan or session log. The same pattern works for OAuth client credentials passed to http_post_form — see the Cookbook for that recipe.

Scoping per-row calls

Per-row enrichment is the most common reason to reach for this extension, and the most common reason to regret it. A query like

SELECT u.id, http_get('https://api.example.com/score/' || u.id::VARCHAR) AS r
FROM users u;

issues one HTTP call per row, sequentially, with no connection reuse exposed at the SQL layer. For ten rows it’s instant; for ten thousand rows it’s slow and very likely rate-limited by the upstream server.

Practical guardrails:

• Pre-aggregate to a small set of distinct keys before joining against the API. If users has 100k rows but only 200 unique scores, fetch the 200 unique scores into a CTE first.
• LIMIT aggressively during development to avoid accidentally hammering an endpoint while you iterate.
• Cache results into a DuckDB table with CREATE TABLE AS — repeat queries hit the cache, not the API.
• Sleep is not exposed. If you need rate limiting between calls, run the query in batches from your application layer rather than in one large SQL statement.

For genuinely large fetches, prefer one bulk call that returns many rows over many small per-row calls — most REST APIs offer a list endpoint for that reason.

Compared to alternatives

• shellfs + curl — pipe curl output through read_csv / read_json. Use shellfs when you need streaming reads of large responses (HTTP Client buffers the whole body), curl-only features (cookies, complex TLS, client certs), or shell-pipeline composition with awk / jq. Use HTTP Client when the response is small and you want the result as a JSON value directly inside a SELECT.
• httpserver — the inverse direction: serve DuckDB query results as an HTTP API rather than consume one. Pair them when you want one DuckDB instance to fetch from upstream APIs and another to expose results to downstream consumers.
• Application-layer Python / Node + DuckDB — appropriate when you need retries, parallelism, connection reuse, or any HTTP feature this extension doesn’t expose. The tradeoff is the boilerplate and the round-trip back into SQL.

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
  }
);