sqlite-http

The sqlite-http extension provides several categories of functions for interacting with HTTP resources directly via SQL:

• Full Content Requests: Returns headers, body, timings, and metadata. Functions include http_get, http_post, and http_do (table functions).
• Body-only Requests: Returns only the response body. Functions include http_get_body, http_post_body, and http_do_body.
• Header-only Requests: Returns only the response headers. Functions include http_get_headers, http_post_headers, and http_do_headers.
• Header Utilities: Functions to create and manipulate headers in wire format (e.g., http_headers, http_headers_get).
• Cookie Utilities: Functions to create cookies (e.g., http_cookies).
• Configuration: Functions to set rate limits (http_rate_limit) and timeouts (http_timeout_set).

You can install sqlite-http bindings for various programming languages using their respective package managers:

• Python: pip install sqlite-http
• Datasette: datasette install datasette-sqlite-http
• Node.js: npm install sqlite-http
• Ruby: gem install sqlite-http

Network errors (DNS, connection failures) and timeout errors will cause the SQLite function to fail and return a SQLite error.

However, HTTP error status codes (like 400 or 500) do not result in a SQLite error. To handle these, you should inspect the response_status_code field returned by the request functions.

Example of checking for non-2xx status codes:

select
  iif(
    response_status_code not between 200 and 299,
    'An error happened!',
    null
  ) as status,
  response_body
from http_get("http://httpbin.org/status/503");

For security-sensitive environments where untrusted users might execute arbitrary SQL, you can use a version of sqlite-http compiled with the -X main.OmitNet=1 option. This disables all functions that perform actual network I/O (like http_get(), http_get_body(), etc.) to prevent DDoS vectors, while still allowing the use of utility functions like http_headers_each().

Releases with the -no-net suffix are available on the Releases page.

By default, sqlite-http performs requests as fast as possible with no delay between them. To prevent overwhelming a server or to introduce a delay, use http_rate_limit(duration_ms). This sets a pause in milliseconds between sequential requests.

Example: To pause 250ms between requests:

select http_rate_limit(250);

select http_get_body(
  printf('http://localhost:8000/%d', value)
from generate_series(1, 1e7);
);

The default timeout for HTTP requests is 5 seconds. You can change this using http_timeout_set(duration_ms). If a request exceeds this duration, the function will return a SQLite error.

Example: To set a 5-second timeout:

select http_timeout_set(5 * 1000);

-- will return an error if this takes more than 5 seconds
select http_get_body('http://localhost:8000');

To use sqlite-http as a runtime-loadable extension, download the appropriate binary for your platform from the GitHub Releases page:

• MacOS: http0.dylib
• Linux: http0.so
• Windows: http0.dll

Note: The 0 in the filename denotes the major version. Since the project is currently pre-v1, expect breaking changes in future versions.

You can load the extension in the SQLite CLI using .load ./http0.

You can control the behavior of all sqlite-http requests within a specific connection. These settings do not persist after the connection is closed.

Functions:

• http_rate_limit(duration_ms): Ensures a minimum delay of duration_ms between all HTTP requests. Useful for preventing rate-limiting from servers.
• http_timeout_set(duration_ms): Sets the timeout for all HTTP requests to duration_ms milliseconds. The default is 5 seconds.

-- Set a 100ms delay between requests
select http_rate_limit(100);

-- Set a 2.5 second timeout
select http_timeout_set(2500);

When using the standard sqlite3 module in Python, you must explicitly enable extension loading before calling load_extension().

import sqlite3

con = sqlite3.connect(":memory:")

con.enable_load_extension(True)
con.load_extension("./http0")

print(con.execute("select http_version()").fetchone())
# ('v0.0.1',)

Request functions accept a headers parameter. Headers must be provided in "wire" format. Use the http_headers(name1, value1, ...) function to construct these arguments.

Example:

select http_get_body(
  'http://localhost:8000',
  http_headers(
    'Name', 'alex',
    'Username', 'asg017'
  )
);

Use the http_get_body() function to perform a GET request and retrieve the response body as a string. This is useful for downloading HTML or raw text content directly within a query.

-- Assuming the extension is loaded
select http_get_body('https://text.npr.org/');