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 status, content
FROM extensions.http_get('https://httpbin.org/get?name=db9');

The function returns a single row with four columns:

Column	Type	Description
status	INT	HTTP status code (200, 404, 500, etc.)
content_type	TEXT	Response Content-Type header, or NULL
headers	JSONB	Response headers as a JSON array
content	TEXT	Response body as text

Parse JSON responses

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

SQL

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

2. POST JSON to an API

Send data with http_post():

SQL

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

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 status
FROM extensions.http_post(
  'https://httpbin.org/post',
  'username=alice&action=login',
  'application/x-www-form-urlencoded'
);

3. Add Custom Headers

Pass headers as JSONB — either object or array format:

SQL

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

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

4. Other HTTP Methods

All standard methods are available:

SQL

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

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

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

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

Universal function

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

SQL

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

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

5. Practical Patterns

Send a webhook notification

SQL

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

Enrich rows with API data

SQL

SELECT
  u.id,
  u.email,
  (h.content::jsonb->>'company') AS company
FROM users u
CROSS JOIN LATERAL extensions.http_get(
  'https://api.example.com/enrich?email=' || u.email
) 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 status, content::jsonb
FROM extensions.http_get('https://api.example.com/metrics');

Health check multiple endpoints

SQL

SELECT
  url,
  (extensions.http_head(url)).status 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