Fetch and parse the web

sqx can fetch URLs and read HTML right inside a SQL query. Check status codes, follow redirects, read headers and extract titles, meta tags, headings and links, without a browser or a scraping script.

Your first request

sqx "SELECT http_status('https://example.com') AS status, html_title('https://example.com') AS title"

Both functions use the same download: sqx fetches the page once and keeps it in the cache.

Check a list of URLs

Put one URL per line in a file and read it with -i lines:

cat data/urls.txt | sqx -i lines "
  SELECT url, http_status(url) AS status, http_time_ms(url) AS ms,
         http_final_url(url) AS final_url, http_header(url, 'content-type') AS type,
         html_title(url) AS title, http_error(url) AS error
  FROM stdin ORDER BY status"

A network failure gives status 0 and the reason in http_error(url), so broken links are easy to filter:

cat urls.txt | sqx -i lines "SELECT url, http_status(url) AS status FROM stdin
  WHERE http_status(url) >= 400 OR http_status(url) = 0"

Speed up big lists with prefetch

SQLite calls functions one row at a time, so a query over hundreds of URLs fetches them one after another. Warm the cache in parallel first:

cat data/urls.txt | sqx prefetch -c 25

prefetch prints url, final_url, status_code, response_ms, size_bytes, title, error for every URL, then your real query reads everything from the cache instantly. See sqx prefetch.

Extract from HTML

Every html_* function accepts either a URL (fetched through the cache) or a raw HTML string, such as a column of stored pages.

cat data/urls.txt | sqx -i lines "
  SELECT url, html_meta(url, 'description') AS description,
         json_array_length(html_tag_texts(url, 'h1')) AS h1_count
  FROM stdin WHERE http_status(url) = 200"

One row per link, heading or image

html_links, html_tag_texts and html_attr return JSON arrays. Combine them with SQLite's json_each to get one row per item. This query builds a graph of which sites link to which:

cat data/urls.txt | sqx -i lines "
  SELECT url_host(stdin.url) AS source, url_host(link.value) AS target, COUNT(*) AS links
  FROM stdin, json_each(html_links(stdin.url)) AS link
  WHERE url_host(link.value) != url_host(stdin.url)
  GROUP BY 1, 2 ORDER BY links DESC LIMIT 15"

When x is a URL, links are made absolute. Raw HTML has no base URL, so resolve relative links with url_join(page_url, link.value).

Stored HTML

Already have pages saved in a file? Point the same functions at the HTML column, with no network at all:

sqx -f pages=data/pages.jsonl "SELECT url_path(url) AS page, html_title(html) AS title FROM pages"

+--------------------+------------------------+
| page               | title                  |
+--------------------+------------------------+
| /menu/index.html   | Menu | Pizza Example   |
| /offers/index.html | Offers | Pizza Example |
| /menu/copy.html    | Menu copy              |
| /menu/copy2.html   | Menu copy              |
+--------------------+------------------------+

Be a polite crawler

• Set a descriptive user agent with -ua "my-audit/1.0 (+https://example.com)".
• Keep prefetch -c low for a single site.
• Use -offline to re-run queries on cached pages without sending any requests.
• Only crawl sites you are allowed to.

All functions are listed in the SQL function reference.