HTTP from SQL

DB9’s http extension lets you make HTTP requests from SQL — fetch data from APIs, post to webhooks, or check service health without leaving the database. This is useful when agents need to call external services as part of a SQL workflow, or when you want to enrich query results with live data.

This guide covers the common patterns, shows working examples, and explains the security and operational limits.

Prerequisites

• A DB9 database (see Quick Start)
• The http extension is enabled by default — no CREATE EXTENSION needed

The http extension is one of two extensions enabled by default (along with pg_cron). Because it is already registered, a bare CREATE EXTENSION http does not silently succeed — it fails with ERROR: extension "http" already exists (42710). Write CREATE EXTENSION IF NOT EXISTS http if a migration needs the statement to be a no-op.

Call the scalar form by its bare name

Every example on this page uses the scalar form, which returns JSONB — call it as http_get(url), without a schema qualifier. Writing extensions.http_get(url) in a SELECT list fails:

ERROR:  function extensions.http_get(unknown) does not exist
SQLSTATE: 42883

The argument type in that message reflects how the URL is spelled: a bare string literal is untyped and reports (unknown), while a ::text cast or a column reference reports (text).

The extensions. prefix selects a different, table-valued form that is only valid in FROM position (SELECT * FROM extensions.http_get(url)). See the http extension reference for the full comparison.

1. Make a GET Request

Fetch data from an external API:

SQL

SELECT http_get('https://httpbin.org/get?name=db9');

The function returns a JSONB object with four keys:

Key	Type	Description
status	number	HTTP status code (200, 404, 500, etc.)
content_type	string	Response Content-Type header, or NULL
headers	array	Response headers as [{"field": "...", "value": "..."}]
content	string	Response body as text

Extract individual fields using the JSONB ->>'key' operator:

SQL

SELECT
  (http_get('https://httpbin.org/get?name=db9')->>'status')::int AS status,
  http_get('https://httpbin.org/get?name=db9')->>'content' AS content;

Parse JSON responses

Most APIs return JSON. Cast content to JSONB to extract fields:

SQL

SELECT
  (r->>'status')::int AS status,
  (r->>'content')::jsonb->>'origin' AS origin_ip
FROM (SELECT http_get('https://httpbin.org/get') AS r) _;

2. POST JSON to an API

Send data with http_post():

SQL

SELECT
  (r->>'status')::int AS status,
  (r->>'content')::jsonb->>'json' AS echoed_body
FROM (SELECT http_post(
  'https://httpbin.org/post',
  '{"event": "user_signup", "user_id": 42}',
  'application/json'
) AS r) _;

The three required arguments are:

1. url — the endpoint
2. body — request body as text
3. content_type — the Content-Type header value

Send form data

SQL

SELECT (http_post(
  'https://httpbin.org/post',
  'username=alice&action=login',
  'application/x-www-form-urlencoded'
)->>'status')::int AS status;

3. Add Custom Headers

Pass headers as JSONB — either object or array format:

SQL

-- Object format (simpler)
SELECT
  (r->>'status')::int AS status,
  r->>'content' AS content
FROM (SELECT http_get(
  'https://httpbin.org/headers',
  '{"Authorization": "Bearer sk-test-123", "X-Request-ID": "req-abc"}'::jsonb
) AS r) _;

-- Array format (pgsql-http compatible)
SELECT
  (r->>'status')::int AS status,
  r->>'content' AS content
FROM (SELECT http_get(
  'https://httpbin.org/headers',
  '[{"field": "Authorization", "value": "Bearer sk-test-123"}]'::jsonb
) AS r) _;

4. Other HTTP Methods

Six methods are available — GET, POST, PUT, DELETE, HEAD and PATCH. Any other method, including OPTIONS and TRACE, raises HTTP method is unsupported (22023) — worth knowing for the http() form below, where the method can come from a column:

SQL

-- PUT
SELECT (http_put(
  'https://httpbin.org/put',
  '{"name": "updated"}',
  'application/json'
)->>'status')::int AS status;

-- PATCH
SELECT (http_patch(
  'https://httpbin.org/patch',
  '{"status": "active"}',
  'application/json'
)->>'status')::int AS status;

-- DELETE
SELECT (http_delete('https://httpbin.org/delete')->>'status')::int AS status;

-- HEAD (returns headers only, no body)
SELECT
  (http_head('https://httpbin.org/get')->>'status')::int AS status,
  http_head('https://httpbin.org/get')->>'headers' AS headers;

Universal function

The http() function accepts the method as a string — useful when the method comes from a column or variable:

SQL

SELECT
  (r->>'status')::int AS status,
  r->>'content' AS content
FROM (SELECT http(
  'POST',
  'https://httpbin.org/post',
  '{"Authorization": "Bearer token"}'::jsonb,
  'application/json',
  '{"payload": "data"}'
) AS r) _;

Arguments: method, url, [headers], [content_type], [body].

5. Practical Patterns

Send a webhook notification

SQL

SELECT (http_post(
  'https://hooks.slack.com/services/T00/B00/xxx',
  '{"text": "New signup: user_id=42"}',
  'application/json'
)->>'status')::int AS status;

Enrich rows with API data

SQL

SELECT
  u.id,
  u.email,
  (h.r->>'content')::jsonb->>'company' AS company
FROM users u
CROSS JOIN LATERAL (SELECT http_get(
  'https://api.example.com/enrich?email=' || u.email
) AS r) h
WHERE u.needs_enrichment = true
LIMIT 10;

Store API responses in a table

SQL

CREATE TABLE api_snapshots (
  id SERIAL PRIMARY KEY,
  fetched_at TIMESTAMPTZ DEFAULT now(),
  status INT,
  body JSONB
);

INSERT INTO api_snapshots (status, body)
SELECT (r->>'status')::int, (r->>'content')::jsonb
FROM (SELECT http_get('https://api.example.com/metrics') AS r) _;

Health check multiple endpoints

A plain SELECT over http_head() works only while every endpoint replies. status is produced by a reply, so 4xx and 5xx come back as values — but a host that cannot be reached at all raises instead, and the error aborts the statement and discards the rows that already succeeded:

SQL

SELECT url, (http_head(url)->>'status')::int AS status_code
FROM (VALUES
  ('https://db9.ai'),
  ('https://nxdomain.invalid')
) t(url);
-- ERROR:  could not resolve HTTP resource host  (58030)
-- Zero rows. The result for https://db9.ai is lost with it.

For a health check an unreachable endpoint is the expected case, so isolate each request in a PL/pgSQL function with its own EXCEPTION handler. A raise then becomes a row:

SQL

CREATE OR REPLACE FUNCTION http_probe(u TEXT)
RETURNS TABLE (status INT, error TEXT)
LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY SELECT (http_head(u)->>'status')::int, NULL::text;
EXCEPTION WHEN OTHERS THEN
  RETURN QUERY SELECT NULL::int, SQLERRM;
END $$;

SELECT t.url, p.status, p.error
FROM (VALUES
  ('https://db9.ai'),
  ('https://nxdomain.invalid'),
  ('https://httpbin.org/status/503')
) t(url),
LATERAL http_probe(t.url) p;

              url               | status |                error
--------------------------------+--------+--------------------------------------
 https://db9.ai                 |    200 |
 https://nxdomain.invalid       |        | could not resolve HTTP resource host
 https://httpbin.org/status/503 |    503 |
(3 rows)

Endpoints that answered report their code in status; endpoints that could not be reached report why in error. Every endpoint gets a row, so the check no longer stops at the first one that is down.

Security Boundaries

The http extension has built-in SSRF (Server-Side Request Forgery) protection:

• HTTPS only — plain HTTP requests are blocked by default
• Private IPs blocked — requests to 127.0.0.0/8, 10.0.0.0/8, 172.16.0.0/12, 192.168.0.0/16, and link-local ranges are rejected
• DNS rebinding protection — domain names are resolved and the resulting IP is validated against the same rules
• No credentials in URLs — user:pass@host syntax is rejected
• Port restricted — only port 443 is allowed

If a request is blocked, it fails with SQLSTATE 42501 and one of these messages. None of them includes the URL:

insecure HTTP requests are disabled
URL host or address is not allowed by network policy
URL port is not allowed by network policy
URL user information is not allowed

Blocked hostnames and blocked IP addresses share one message. The port is checked before the address, so a private IP on a non-443 port reports the port message rather than the network-policy one.

A URL whose scheme is neither http nor https is rejected earlier and under a different code — ftp://example.com/x gives only HTTP and HTTPS URL schemes are supported with SQLSTATE 22023. For timeout, DNS, redirect, request-body and response-size errors, see Common error messages.

Limits

Limit	Value
Request timeout	5 seconds (1 second connect timeout)
Max response body	1 MB
Max request body	256 KB
Max redirects	3
Max requests per SQL statement	100
Concurrent requests per tenant, per node	20 (5 reserved for interactive, 15 shared)

These limits are fixed and cannot be changed with session parameters.

What this means in practice

• Large responses — if an API returns more than 1 MB, the request fails. Paginate or filter on the API side.
• Slow APIs — anything over 5 seconds total (including DNS, connect, and transfer) times out.
• Bulk calls — a single SELECT that calls http_get() for 100+ rows hits the per-statement limit. Break into batches.
• Calls within a statement are serial — the 20-request concurrency limit counts requests in flight across a tenant’s sessions; it does not parallelise the calls inside one statement. Those are issued one after another, so a statement’s wall clock is the sum of its requests. Against https://httpbin.org/delay/2 over the PostgreSQL wire protocol, 1 call took 2.55 s, 5 took 11.98 s, 10 took 24.20 s and 20 took 46.89 s. That is the trade the serial design makes: one query holds one request open at a time, so it can never consume the tenant’s whole budget and starve other sessions — but you must budget a fan-out as rows × per-request latency.
• Statement duration on the HTTP path — db9 db sql and the HTTP SQL API stop waiting after roughly 15 seconds and return Error 504: upstream request timeout. The statement keeps running on the server — its calls are still made, and it can still commit — but you never see its result, and later statements in the same request are not run. Fast endpoints still fit (100 http_head() calls to https://example.com finished in 10.43 s), but 10 calls to a 2-second endpoint do not, while 6 (14.16 s) do. Run slow fan-outs over the wire protocol via db9 db connect, which does not apply that 15-second ceiling.

Caveats

• Superuser only — all http functions require the database admin role.
• UTF-8 responses only — non-UTF-8 response bodies cause an error. Binary APIs (images, protobuf) are not supported.
• HEAD requests don’t follow redirects — http_head() returns the redirect status (301, 302) rather than following it. Use http_get() if you need to follow redirects.
• Redirect method changes — on 301-303 redirects, POST/PUT/PATCH become GET. Only 307-308 preserve the original method.
• No proxy support — environment proxy variables are ignored.

Next Pages

• http Extension Reference — function signatures and header format details
• Scheduled Jobs with pg_cron — combine HTTP calls with cron for periodic API polling
• Analyze Agent Logs with fs9 — store and query structured data in the filesystem
• Extensions Overview — every built-in extension
• CLI Reference — db9 db sql for running queries from the terminal