---
title: "HTTP Request in Postgres"
description: "Fetching data over HTTP in PostgreSQL using the http extension. Practical notes and code examples on HTTP Request in in JS."
url: "https://h3manth.com/scribe/http-request-postgres/"
canonical_url: "https://h3manth.com/scribe/http-request-postgres/"
markdown_url: "https://h3manth.com/scribe/blog/http-request-postgres.md"
publishDate: "2022-01-20T00:00:00.000Z"
author: "Hemanth HM"
category: "db"
tags: ["postgres","db","http","sql"]
word_count: 289
reading_time_minutes: 2
x_markdown_tokens: 628
content_signal: "ai-train=yes, search=yes, ai-input=yes"
---

# HTTP Request in Postgres

> Fetching data over HTTP in PostgreSQL using the http extension. Practical notes and code examples on HTTP Request in in JS.

Postgres has been my default choice of DB for most of my applications these days and recently I came across an HTTP client for PostgreSQL—retrieve a web page from inside the database! Without more theory, here is a simple example of how useful this can be.

## Example: Fetch Random XKCD Comic

Fetch random comics from XKCD is my version of "Hello World". Here is a simple SQL query to fetch a random XKCD comic from a service I had written and hosted on Heroku:

```sql
SELECT 
  content::json->'url' as url, 
  content::json->'title' as title 
FROM http_get('https://xkcd-imgs.herokuapp.com/');
```

This would fetch the `title` and `url` to a table as below:

```
             url                             |               title                
---------------------------------------------+-----------------------------------
 "https://imgs.xkcd.com/comics/zeppelin.jpg" | "A tribute to Buttercup Festival"
(1 row)
```

## HTTP Verbs Support

The possibilities are immense with this API, we could easily invoke all HTTP verbs!

```sql
CREATE DOMAIN http_method AS text
CHECK (
  VALUE ILIKE 'get' OR
  VALUE ILIKE 'post' OR
  VALUE ILIKE 'put' OR
  VALUE ILIKE 'delete' OR
  VALUE ILIKE 'patch' OR
  VALUE ILIKE 'head'
);
```

## Setting it Up on a GNU/Linux Machine

### Install Dependencies

```bash
# install the basic deps
apt install make gcc g++ postgresql-server-dev-13 libcurl4-openssl-dev
# 13 or your version of pg server

### Fetch, Extract, Make and Install
bash
wget https://github.com/pramsey/pgsql-http/archive/refs/tags/v1.3.1.tar.gz
tar xvfz v1.3.1.tar.gz
cd pgsql-http-1.3.1
make && make install

### Install the Extension
bash
/usr/bin/install -c -m 644 ./http--1.3.sql ./http--1.2--1.3.sql ./http--1.1--1.2.sql ./http--1.0--1.1.sql '/usr/share/postgresql/11/extension/'

### Create the Extension in Your Database
sql
su postgres
psql

CREATE DATABASE http_foo;
\connect http_foo;

CREATE SCHEMA contrib;
GRANT USAGE ON SCHEMA contrib TO some_user_group;
ALTER DATABASE http_foo SET search_path=public,contrib;

CREATE EXTENSION http SCHEMA contrib;
```

Now you should be able to use `http_get` and similar functions in your query!