HTTP Functions

ERPL-Web turns DuckDB into a powerful HTTP client. Call any REST API, query web services, or integrate with external systems directly from SQL.

Overview

The HTTP functions let you make web requests and process responses as regular table rows. Think of every API endpoint as a queryable table.

What you can do:

• ✅ Call REST APIs with GET, POST, PUT, PATCH, DELETE
• ✅ Send custom headers and authentication
• ✅ Handle JSON, XML, or plain text responses
• ✅ Set timeouts and retry logic
• ✅ Process API responses with SQL

Quick Start

-- Load the extension
LOAD erpl_web;

-- Make your first API call
SELECT content
FROM http_get('https://httpbun.com/ip');

-- Parse JSON response
SELECT content::JSON->>'ip' AS ip_address
FROM http_get('https://httpbun.com/ip');

That's it! You just called an API from SQL.

---

HTTP Functions Reference

http_get

Fetch data from a URL.

Signature:

http_get(
  url VARCHAR,
  headers MAP(VARCHAR, VARCHAR) := NULL,
  accept VARCHAR := NULL,
  auth VARCHAR := NULL,
  auth_type VARCHAR := NULL,
  timeout BIGINT := NULL
)

Parameters:

• url: The URL to request
• headers: Custom HTTP headers (e.g., {'Authorization': 'Bearer token'})
• accept: Accept header (e.g., 'application/json')
• auth: Authentication credential (username:password or token)
• auth_type: 'BASIC' or 'BEARER'
• timeout: Timeout in milliseconds

Returns: Table with columns:

• method (VARCHAR): HTTP method used
• status (INTEGER): HTTP status code
• url (VARCHAR): Request URL
• headers (MAP): Response headers
• content_type (VARCHAR): Response content type
• content (VARCHAR): Response body

Example:

SELECT status, content
FROM http_get('https://api.github.com/repos/duckdb/duckdb');

---

http_post

Send data to a URL.

Signature:

http_post(
  url VARCHAR,
  body VARCHAR,
  content_type VARCHAR := 'application/json',
  headers MAP(VARCHAR, VARCHAR) := NULL,
  accept VARCHAR := NULL,
  auth VARCHAR := NULL,
  auth_type VARCHAR := NULL,
  timeout BIGINT := NULL
)

Parameters:

• url: The URL to request
• body: Request body content
• content_type: Content-Type header (default: 'application/json')
• headers: Custom HTTP headers
• accept: Accept header
• auth: Authentication credential
• auth_type: 'BASIC' or 'BEARER'
• timeout: Timeout in milliseconds

Example:

-- POST JSON data
SELECT status, content
FROM http_post(
  'https://httpbin.org/post',
  '{"name": "DuckDB", "type": "database"}',
  content_type := 'application/json'
);

---

http_put

Update a resource.

Signature:

http_put(
  url VARCHAR,
  body VARCHAR,
  content_type VARCHAR := 'application/json',
  headers MAP(VARCHAR, VARCHAR) := NULL,
  accept VARCHAR := NULL,
  auth VARCHAR := NULL,
  auth_type VARCHAR := NULL,
  timeout BIGINT := NULL
)

Example:

SELECT status, content
FROM http_put(
  'https://api.example.com/users/123',
  '{"name": "Updated Name"}',
  content_type := 'application/json'
);

---

http_patch

Partially update a resource.

Signature:

http_patch(
  url VARCHAR,
  body VARCHAR,
  content_type VARCHAR := 'application/json',
  headers MAP(VARCHAR, VARCHAR) := NULL,
  accept VARCHAR := NULL,
  auth VARCHAR := NULL,
  auth_type VARCHAR := NULL,
  timeout BIGINT := NULL
)

Example:

SELECT status, content
FROM http_patch(
  'https://api.example.com/users/123',
  '{"email": "newemail@example.com"}'
);

---

http_delete

Delete a resource.

Signature:

http_delete(
  url VARCHAR,
  headers MAP(VARCHAR, VARCHAR) := NULL,
  accept VARCHAR := NULL,
  auth VARCHAR := NULL,
  auth_type VARCHAR := NULL,
  timeout BIGINT := NULL
)

Example:

SELECT status
FROM http_delete('https://api.example.com/users/123');

---

Authentication

ERPL-Web supports multiple authentication methods.

Basic Authentication

Use username and password:

SELECT content
FROM http_get(
  'https://api.example.com/data',
  auth := 'username:password',
  auth_type := 'BASIC'
);

Bearer Token

Use an API token:

SELECT content
FROM http_get(
  'https://api.example.com/data',
  auth := 'your-api-token-here',
  auth_type := 'BEARER'
);

Custom Headers

For other authentication schemes, use headers:

SELECT content
FROM http_get(
  'https://api.example.com/data',
  headers := {'X-API-Key': 'your-api-key'}
);

Using DuckDB Secrets

Store credentials securely with DuckDB secrets:

-- Create a secret
CREATE SECRET api_token (
  TYPE http_bearer,
  token 'your-secret-token'
);

-- Use it in requests (automatically applied)
SELECT content
FROM http_get('https://api.example.com/data');

tip

Always use secrets for production deployments. Never hardcode credentials in SQL queries!

---

Advanced Examples

POST JSON with Headers

SELECT status, content
FROM http_post(
  'https://api.example.com/webhooks',
  '{"event": "user.created", "user_id": 123}',
  content_type := 'application/json',
  headers := {
    'X-Webhook-Secret': 'secret123',
    'X-Request-ID': 'req-456'
  }
);

Form Data Submission

SELECT status, content
FROM http_post(
  'https://api.example.com/forms',
  'name=John&email=john@example.com',
  content_type := 'application/x-www-form-urlencoded'
);

API Pagination

Query paginated APIs using LATERAL joins:

-- Fetch first page
WITH first_page AS (
  SELECT content::JSON AS data
  FROM http_get('https://api.example.com/users?page=1')
)
SELECT data->>'name' AS name, data->>'email' AS email
FROM first_page, LATERAL UNNEST(data->'users') AS data;

Handle Timeouts

Set custom timeout for slow APIs:

SELECT status, content
FROM http_get(
  'https://slow-api.example.com/data',
  timeout := 30000  -- 30 seconds
);

Error Handling

Check status codes and handle errors:

WITH api_response AS (
  SELECT status, content
  FROM http_get('https://api.example.com/data')
)
SELECT 
  CASE 
    WHEN status = 200 THEN content::JSON
    WHEN status = 404 THEN '{"error": "Not found"}'::JSON
    ELSE '{"error": "Request failed"}'::JSON
  END AS result
FROM api_response;

---

Working with JSON APIs

Most modern APIs return JSON. Here's how to work with it:

Parse JSON Response

SELECT 
  content::JSON->>'name' AS name,
  content::JSON->>'email' AS email,
  content::JSON->'address'->>'city' AS city
FROM http_get('https://api.example.com/user/123');

Extract Arrays

SELECT 
  item->>'id' AS id,
  item->>'title' AS title
FROM http_get('https://api.example.com/items'),
LATERAL UNNEST(content::JSON->'items') AS item;

Build API Requests from Data

-- Create POST requests from a table
CREATE TABLE users (name VARCHAR, email VARCHAR);
INSERT INTO users VALUES ('Alice', 'alice@example.com'), ('Bob', 'bob@example.com');

SELECT 
  name,
  response.status
FROM users,
LATERAL (
  SELECT status 
  FROM http_post(
    'https://api.example.com/users',
    json_object('name', name, 'email', email)::VARCHAR
  )
) AS response;

---

Integration Patterns

ETL from API to Parquet

-- Extract data from API and save to Parquet
COPY (
  SELECT 
    data->>'id' AS id,
    data->>'name' AS name,
    data->>'created_at' AS created_at
  FROM http_get('https://api.example.com/export'),
  LATERAL UNNEST(content::JSON->'records') AS data
) TO 'output.parquet' (FORMAT PARQUET);

API Gateway Pattern

-- Create a view that wraps an API
CREATE VIEW github_duckdb_info AS
SELECT 
  content::JSON->>'name' AS name,
  content::JSON->>'stargazers_count' AS stars,
  content::JSON->>'forks_count' AS forks
FROM http_get('https://api.github.com/repos/duckdb/duckdb');

-- Query it like a normal table
SELECT * FROM github_duckdb_info;

Webhook Testing

-- Send a test webhook
SELECT 
  status,
  content::JSON->>'message' AS response_message
FROM http_post(
  'https://your-webhook-url.com/endpoint',
  json_object(
    'event', 'test',
    'timestamp', current_timestamp,
    'data', json_object('test', true)
  )::VARCHAR
);

---

Best Practices

Performance

1. Cache responses - Store API results in tables for repeated queries
2. Use LIMIT - Don't fetch more data than you need
3. Batch requests - Combine multiple operations when possible
4. Set timeouts - Prevent hanging on slow APIs

-- Good: Cache API results
CREATE TABLE api_cache AS
SELECT content::JSON AS data
FROM http_get('https://api.example.com/large-dataset');

-- Query the cache
SELECT * FROM api_cache;

Security

1. Use secrets - Never hardcode credentials
2. HTTPS only - Avoid HTTP for sensitive data
3. Validate responses - Check status codes
4. Rate limiting - Respect API limits

-- Good: Use secrets
CREATE SECRET my_api (
  TYPE http_bearer,
  token 'secret-token'
);

-- Bad: Hardcoded token
-- SELECT * FROM http_get('url', auth := 'secret-token');

Error Handling

Always check HTTP status codes:

WITH response AS (
  SELECT status, content
  FROM http_get('https://api.example.com/data')
)
SELECT 
  CASE 
    WHEN status BETWEEN 200 AND 299 THEN 'Success'
    WHEN status BETWEEN 400 AND 499 THEN 'Client Error'
    WHEN status BETWEEN 500 AND 599 THEN 'Server Error'
    ELSE 'Unknown'
  END AS result_type,
  content
FROM response;

---

Troubleshooting

Connection Refused

-- Error: Connection refused
-- Solution: Check URL and network connectivity
SELECT * FROM http_get('http://localhost:8080');

Fixes:

• Verify the URL is correct
• Check if the service is running
• Ensure firewall rules allow connections

Timeout Errors

-- Error: Request timeout
-- Solution: Increase timeout
SELECT * FROM http_get(
  'https://slow-api.example.com',
  timeout := 60000  -- 60 seconds
);

SSL Certificate Errors

-- Error: SSL certificate verification failed
-- For development only, consider using HTTP instead of HTTPS
-- Or ensure SSL certificates are properly configured

JSON Parsing Errors

-- Error: Invalid JSON
-- Solution: Check content_type and validate JSON
SELECT 
  status,
  content,
  content_type,
  TRY_CAST(content AS JSON) AS parsed_json
FROM http_get('https://api.example.com/data');

---

Next Steps

• Learn about OData integration for structured APIs
• Explore Secrets Management for secure authentication
• See Tracing & Diagnostics for debugging
• Check Examples for real-world use cases

---

Summary

The HTTP functions in ERPL-Web transform DuckDB into a powerful API client:

✅ Simple - Query APIs like database tables
✅ Flexible - Support for all HTTP methods and authentication
✅ Powerful - Combine with SQL for data transformation
✅ Secure - Built-in secrets management

Start making HTTP requests from SQL today!