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). Running CREATE EXTENSION http is harmless but unnecessary. All functions live in the extensions schema.

1. Make a GET Request

Fetch data from an external API:

SQL

SELECT extensions.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
  (extensions.http_get('https://httpbin.org/get?name=db9')->>'status')::int AS status,
  extensions.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 extensions.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 extensions.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 (extensions.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 extensions.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 extensions.http_get(
  'https://httpbin.org/headers',
  '[{"field": "Authorization", "value": "Bearer sk-test-123"}]'::jsonb
) AS r) _;

4. Other HTTP Methods

All standard methods are available:

SQL

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

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

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

-- HEAD (returns headers only, no body)
SELECT
  (extensions.http_head('https://httpbin.org/get')->>'status')::int AS status,
  extensions.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 extensions.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 (extensions.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 extensions.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 extensions.http_get('https://api.example.com/metrics') AS r) _;

Health check multiple endpoints

SQL

SELECT
  url,
  (extensions.http_head(url)->>'status')::int AS status_code
FROM (VALUES
  ('https://api.example.com/health'),
  ('https://db9.ai'),
  ('https://httpbin.org/status/200')
) t(url);

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 standard ports (443 for HTTPS, 80 for HTTP) are allowed

If a request is blocked, you get a clear error message:

http: insecure http requests are disabled
http: host is not allowed
http: ip is not allowed

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	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.
• Concurrent load — 20 concurrent requests per tenant prevents one database from monopolizing network resources.

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 — all 9 built-in extensions
• CLI Reference — db9 db sql for running queries from the terminal