How to Query REST APIs with SQL (No ETL Required)

You can query REST APIs with SQL today without building an ETL pipeline. The main approaches are Postgres Foreign Data Wrappers (Supabase Wrappers, Steampipe), DuckDB's read_json_auto() function, Trino/Presto connectors, and dedicated platforms like Fastero that handle the connection and storage layer for you. Which one you pick depends on how many APIs you're hitting and whether you need historical data.

The traditional approach (and why it's overkill for most teams)

Here's what the textbook says: stand up an ETL pipeline, extract data from each API, load it into a warehouse, then query the warehouse.

Traditional ETL pipeline:
                                                    
  Stripe API ──┐                               ┌── SELECT * FROM ...
               │    ┌─────────┐  ┌───────────┐ │
  HubSpot API ─┼───>│ Fivetran │─>│ Snowflake │─┼── JOIN charges ON ...
               │    │ Airbyte  │  │ BigQuery  │ │
  Shopify API ─┤    │ Stitch   │  │ Redshift  │ └── GROUP BY month ...
               │    └─────────┘  └───────────┘
  GitHub API ──┘     $300+/mo     $500+/mo
                    + setup       + maintenance

For three to six SaaS sources and one person who knows SQL, that's a $10k+/year stack before you run your first query. The pipeline breaks at 2 AM, the warehouse costs grow with data volume, and you spend more time debugging sync failures than actually analyzing data.

There's a simpler path. Query the APIs directly with SQL.

Direct SQL-to-API:
 
  Stripe API ──┐
               │    ┌────────────────────┐
  HubSpot API ─┼───>│  SQL Engine         │──> SELECT * FROM ...
               │    │  (DuckDB / FDW /   │──> JOIN charges ON ...
  Shopify API ─┤    │   Trino / Fastero) │──> GROUP BY month ...
               │    └────────────────────┘
  GitHub API ──┘     No warehouse.
                     No pipeline.

Same SQL. No intermediate infrastructure. Let me walk through each approach.

Approach 1: DuckDB + read_json_auto()

DuckDB can query JSON APIs directly. No extensions, no server, no config. If the API returns JSON over HTTP, DuckDB can read it.

-- Query a public JSON API directly
SELECT
    id,
    name,
    stargazers_count AS stars,
    language
FROM read_json_auto(
    'https://api.github.com/users/duckdb/repos'
)
WHERE stargazers_count > 100
ORDER BY stargazers_count DESC;

That's it. One function call. DuckDB fetches the JSON, infers the schema, and returns a table you can filter, aggregate, and join like any other.

For authenticated APIs, you'll need to pass headers. DuckDB doesn't support custom HTTP headers in read_json_auto() natively, so you'd typically curl the data into a file first:

curl -H "Authorization: Bearer sk_live_..." \
  "https://api.stripe.com/v1/charges?limit=100" \
  -o charges.json
 
duckdb -c "SELECT * FROM read_json_auto('charges.json')"

Where DuckDB shines: ad hoc exploration, one-off analysis, small-to-medium datasets that fit on a single machine. If you need to quickly check what an API returns and run some SQL against it, DuckDB is the fastest path from zero to results.

Where it falls short: no built-in pagination handling, no automatic scheduling, no auth management. You're writing shell scripts to handle the API plumbing. That's fine for a single API; it gets tedious at five or six.

For more on DuckDB's strengths as an analytics engine, see DuckDB vs PostgreSQL for Analytics.

Approach 2: Postgres Foreign Data Wrappers (FDW)

Foreign Data Wrappers let Postgres treat external data sources — including REST APIs — as regular tables. You create a "foreign table" that maps to an API endpoint, and then query it with standard SQL alongside your regular Postgres tables.

Three FDW implementations worth knowing:

Steampipe is an open-source tool that exposes 150+ APIs as Postgres tables. It runs its own embedded Postgres instance and ships plugins for Stripe, AWS, GitHub, Slack, Jira, and dozens more.

-- Steampipe: query Stripe charges as a Postgres table
SELECT
    id,
    amount / 100.0 AS amount_usd,
    currency,
    customer ->> 'email' AS customer_email,
    created
FROM stripe_charge
WHERE status = 'succeeded'
    AND created > now() - interval '30 days'
ORDER BY amount DESC
LIMIT 50;

No ETL, no warehouse. Steampipe calls the Stripe API behind the scenes, paginates through the results, and returns them as rows. You write SQL; it handles the REST.

Supabase Wrappers (built on Wasm FDW) bring the same concept to Supabase's hosted Postgres. If you already run on Supabase, you can add foreign tables for Stripe, Firebase, ClickHouse, and others without leaving your existing database.

multicorn2 is a Python-based FDW framework for vanilla Postgres. You write a Python class that fetches data from any API, and Postgres treats it as a table. Maximum flexibility, maximum effort.

-- Generic FDW setup for a REST API (conceptual)
CREATE SERVER stripe_server
    FOREIGN DATA WRAPPER multicorn2
    OPTIONS (
        wrapper 'myproject.stripe_fdw.StripeFDW'
    );
 
CREATE FOREIGN TABLE stripe_charges (
    id TEXT,
    amount INTEGER,
    currency TEXT,
    customer_email TEXT,
    status TEXT,
    created TIMESTAMP
)
SERVER stripe_server
OPTIONS (
    endpoint '/v1/charges',
    api_key 'sk_live_...'
);
 
-- Now query it like any Postgres table
SELECT * FROM stripe_charges
WHERE status = 'succeeded'
    AND created > '2026-01-01';

Where FDWs shine: you already run Postgres, you want API data to live alongside your transactional data, and you need real SQL JOINs between local and remote tables.

Where they fall short: performance. Every query hits the API in real time. A slow API means a slow query. Pagination, rate limits, and API outages all become your query's problem. And FDWs don't cache — run the same query twice, you hit the API twice.

Approach 3: Trino / Presto connectors

Trino (formerly PrestoSQL) is a distributed SQL engine designed to query data wherever it lives. It supports connectors for databases, object stores, and — with some custom work — REST APIs.

The idea is the same as FDW, but at cluster scale. Write one SQL query that joins your Postgres production data with your Kafka streams and your Elasticsearch logs. Trino federates the query across all three.

For REST APIs specifically, you'd write a custom connector or use the generic HTTP connector with a JSON parser. It's not turnkey. Trino is built for organizations that have a platform team and multiple petabyte-scale data sources.

Where Trino shines: enterprise environments with 10+ data sources, large volumes, and an existing platform team. It handles the scale that DuckDB and FDWs don't.

Where it falls short: setup overhead. You're running a coordinator, workers, and managing a cluster. For "I want to query the Stripe API with SQL," Trino is a 747 when you need a bicycle.

Approach 4: Dedicated platforms

This is where tools like Fastero, Steampipe Cloud, and CData fit.

Fastero connects to 30+ SaaS APIs natively — Stripe, HubSpot, Shopify, GitHub, Jira, Google Sheets, and more — and syncs the data into a DuckDB-powered store. You get the ergonomics of DuckDB's fast columnar engine with the convenience of managed connectors that handle authentication, pagination, rate limiting, and incremental syncing.

The workflow is: connect your accounts, pick which objects to sync, then write SQL against the data. You can run queries across multiple sources in a single statement — joining Stripe charges with HubSpot contacts with Shopify orders — without building any pipeline infrastructure.

The difference between this and raw DuckDB: you don't write curl scripts or handle API pagination. The difference between this and FDWs: the data is cached locally, so queries are fast and don't hammer the API on every run. The difference between this and Trino: you don't need a platform team.

If you're a small team that needs to query multiple SaaS APIs with SQL and can't afford months of pipeline work, a dedicated platform is the shortest path from "I have API credentials" to "I have answers."

When you still need ETL

I'd be lying if I said you never need a pipeline. There are real cases where direct SQL-to-API breaks down:

Historical data. Most APIs only return current state or recent records. If you need two years of Stripe charges for trend analysis, you need to sync them somewhere persistent. The API won't reconstruct history for you.

High-volume data. If you're processing millions of events per day from an API — webhook logs, clickstream data, IoT sensors — you need a proper ingestion layer. read_json_auto() against a paginated API won't cut it at that scale.

Scheduled reporting. If a dashboard needs to refresh every 15 minutes, you want the data pre-materialized. Hitting the API on every dashboard load is slow, fragile, and likely to blow through rate limits.

Compliance and auditing. Some organizations need immutable records of what data looked like at a specific point in time. That requires a pipeline that captures snapshots, not a live query against the current API state.

For everything else — ad hoc analysis, cross-source joins, weekly reports, data exploration — direct SQL against APIs is usually enough. Start without the pipeline. Add it when (and if) you actually need it.

Choosing the right approach

FAQ

Can I write to APIs using SQL (INSERT/UPDATE)?

Generally no. These tools treat APIs as read-only data sources. SQL queries map to GET requests, not POST/PUT. If you need to push data back — creating records, updating fields — you'll need the API's native client or a reverse ETL tool. Some FDW implementations support writable foreign tables, but it's rare and fragile.

How do these approaches handle API rate limits?

DuckDB doesn't — you're responsible for not hammering the endpoint. Steampipe and most FDW plugins implement rate-limit awareness per plugin (Steampipe's Stripe plugin, for example, respects Stripe's rate headers). Dedicated platforms like Fastero handle rate limiting internally during sync, so your queries hit cached data and never touch the API directly.

Is the data real-time?

With FDWs and raw DuckDB, yes — every query hits the live API (which is both the advantage and the problem). With dedicated platforms, it depends on your sync schedule. Fastero syncs can run on intervals you configure. For most analytical use cases, a 15–60 minute delay is fine. If you need sub-second freshness, you're looking at webhooks and streaming infrastructure, not SQL-over-API.

Can I JOIN data from different APIs in a single SQL query?

Yes, with all four approaches. DuckDB can join multiple JSON files. Postgres FDWs let you join foreign tables with local tables and other foreign tables. Trino federates across connectors. Dedicated platforms store all your connected data in one engine — Fastero uses DuckDB as its analytics store, so cross-source joins are native. See our guide to connecting multiple databases without a warehouse for detailed examples.

How does this compare to building a REST API from my queries?

Different direction. This post is about querying into APIs (API as data source). Building a REST API from SQL queries is about querying out of your database (database as API). They're complementary — you might query Stripe's API with SQL to build a revenue report, then expose that report as its own API endpoint for a frontend to consume.

Try Fastero free — SQL queries against Stripe, HubSpot, Shopify, and 30+ APIs. No ETL pipeline, no warehouse setup. Just connect and query. No credit card required.