When people think of SQL and PostgreSQL, they usually picture databases full of rows and columns – clean, organized, and stored on a persistent volume like a disk. But what if your data isn’t in a database at all? What if it lives on the web, behind a REST API, in JSON format?
Usually, you’d write a script. You’d use Python or JavaScript, send a request to the API, parse the JSON, and maybe insert the results into your database for analysis. But PostgreSQL has a shortcut that many people don’t know about: Foreign Data Wrappers.
Just like FUSE (Filesystem in Userspace) lets you mount cloud drives or remote folders as if they were local, Foreign Data Wrappers lets PostgreSQL mount external resources like other database management systems, files, or web APIs and treat them like SQL tables.
Example
In this article we’ll use http_fdw as an example. With it, you can run a SQL query against a URL and read the result – no extra code, no data pipeline, just SQL. Here’s how you could set that up.
Let’s say you want to explore data from JSONPlaceholder, a free fake API for testing. It has a /posts endpoint that returns a list of blog posts in JSON format.
First, make sure the extension is installed (this may require building from source, depending on your system):
CREATE EXTENSION http_fdw;
Now create a foreign server that points to the API:
CREATE SERVER json_api_server
FOREIGN DATA WRAPPER http_fdw
OPTIONS (uri 'https://jsonplaceholder.typicode.com/posts', format 'json');
Then define a foreign table that maps to the shape of the JSON response:
CREATE FOREIGN TABLE api_posts (
userId integer,
id integer,
title text,
body text
)
SERVER json_api_server
OPTIONS (rowpath '');
Since the API returns an array of posts, and each one is an object with userId, id, title, and body, this table matches that structure.
Now you can query it:
SELECT title FROM api_posts WHERE userId = 1;
Behind the scenes, PostgreSQL sends a GET request to the API, parses the JSON, and returns the result like any other table.
Things to Keep in Mind
Not everything is perfect. http_fdw is read-only, so you can’t use it to send data back to the API. It also relies on the API being available and responsive, and it doesn’t support authentication out of the box – you’ll need to handle that with custom headers if the API requires it. Complex or deeply nested JSON might also require some extra configuration.
But for many use cases, it’s an interesting option to work with external data. You don’t have to leave SQL. You don’t have to wire up a data pipeline. You just run a query.
Schneide Blog 