How to Build a REST API from Your SQL Queries

Every useful SQL query eventually gets the same request: "Can I get this as an API?"

A frontend needs it. A partner wants a feed. An internal tool needs to pull numbers without a database password. Reasonable ask. And then you start counting what "expose it as an API" actually means: a server framework, connection pooling, parameterized queries to prevent injection, authentication, rate limiting, pagination, caching, error handling, deployment. You're suddenly building infrastructure instead of shipping data.

I've done this the hard way enough times. Here's how I think about it now.

Start with the query

Say you have a query that pulls monthly revenue by product line. It's useful. Product managers ask for it, finance asks for it, someone on the partnerships team wants to show it to a customer.

select
  date_trunc('month', o.created_at) as month,
  p.product_line,
  sum(o.amount_cents) / 100.0 as revenue,
  count(distinct o.customer_id) as customers
from orders o
join products p on p.id = o.product_id
where o.created_at >= :start_date
  and o.created_at < :end_date
  and o.status = 'completed'
group by 1, 2
order by 1, 2;

The :start_date and :end_date parameters are the key. A static query is a report. A parameterized query is an API endpoint waiting to happen.

The DIY approach (and why it stalls)

The obvious path is Express or FastAPI, a connection pool, and a route per query. Something like this:

// server.js — the "quick" version
app.get('/api/revenue-by-product', authMiddleware, async (req, res) => {
  const { start_date, end_date } = req.query;
 
  // validate params
  if (!start_date || !end_date) {
    return res.status(400).json({ error: 'start_date and end_date required' });
  }
 
  try {
    const result = await pool.query(
      `select date_trunc('month', o.created_at) as month,
              p.product_line,
              sum(o.amount_cents) / 100.0 as revenue,
              count(distinct o.customer_id) as customers
       from orders o
       join products p on p.id = o.product_id
       where o.created_at >= $1 and o.created_at < $2
         and o.status = 'completed'
       group by 1, 2 order by 1, 2`,
      [start_date, end_date]
    );
    res.json({ data: result.rows, generated_at: new Date().toISOString() });
  } catch (err) {
    res.status(500).json({ error: 'Query failed' });
  }
});

That's one endpoint. Now multiply it by ten queries, add API key management, add caching so the same request doesn't hammer your database every time, add pagination for queries that return thousands of rows, add rate limiting so a misbehaving consumer doesn't bring your DB down. You're building a platform, not shipping a feature.

This is the trap. The first endpoint takes an afternoon. The tenth takes a week, because by then you're maintaining middleware, a key store, a cache layer, and a deployment pipeline for a service that was supposed to be "just a few endpoints."

What a query-as-endpoint setup actually needs

After building this pattern three or four times, here's the minimum viable checklist:

Parameterized queries with type validation. Not just string interpolation. Actual typed parameters with defaults, so GET /api/revenue-by-product?start_date=2026-01-01&end_date=2026-07-01 works and GET /api/revenue-by-product?start_date=DROP TABLE orders doesn't.

Authentication that isn't your app's auth. API keys, scoped by consumer. Your frontend, your partner, and your internal tool should have different keys with different permissions. JWT works too if you already have an identity provider — the point is that each consumer is identifiable and revokable independently.

Caching with TTL. If five people request the same revenue data in the same minute, the query should run once. Cache keys must include query parameters — revenue?start_date=2026-01-01 and revenue?start_date=2026-06-01 are different entries. I've seen teams cache by URL only and serve the wrong data. Match the TTL to how stale the data can be before someone notices.

Pagination. Any query that might return more than a few hundred rows needs cursor-based or offset pagination. Dumping 50,000 rows into a single JSON response is a denial-of-service attack on your own API.

Rate limiting. Per-key, 100 requests per minute is generous for analytics endpoints. Without it, a while True loop in someone's script saturates your connection pool.

The faster path: query-to-endpoint platforms

The pattern above is well understood enough that you shouldn't have to build it from scratch. Fastero's Public API & Webhooks feature does exactly this — you write a SQL query (or generate one with natural language), and it becomes a REST endpoint with auth, rate limiting, pagination, and OpenAPI documentation generated automatically.

The endpoint configuration looks like this:

# Fastero endpoint config
endpoint: /api/v1/revenue-by-product
method: GET
query: saved_query_id_here
parameters:
  start_date:
    type: date
    required: true
    description: Start of date range (inclusive)
  end_date:
    type: date
    required: true
    description: End of date range (exclusive)
  product_line:
    type: string
    required: false
    description: Filter to a single product line
cache:
  ttl: 300  # 5 minutes
  vary_by: [start_date, end_date, product_line]
rate_limit:
  requests_per_minute: 120
pagination:
  default_page_size: 100
  max_page_size: 1000
auth:
  type: api_key
  scopes: [read:revenue]

You get an OpenAPI spec out of this. Consumers can generate typed clients in whatever language they want. The endpoint is versioned, documented, and rate-limited before you write a single line of application code.

Consuming the API

Once the endpoint is live, consuming it is standard REST:

# curl example
curl -H "Authorization: Bearer fst_k_abc123..." \
  "https://api.fastero.com/v1/revenue-by-product?start_date=2026-01-01&end_date=2026-07-01"

The response comes back paginated with metadata:

{
  "data": [
    { "month": "2026-01-01T00:00:00Z", "product_line": "Enterprise",
      "revenue": 142500.00, "customers": 28 },
    { "month": "2026-01-01T00:00:00Z", "product_line": "Growth",
      "revenue": 67800.00, "customers": 113 }
  ],
  "pagination": { "page": 1, "page_size": 100, "total_rows": 42, "has_more": false },
  "meta": { "cached": true, "query_ms": 0, "generated_at": "2026-08-05T14:30:00Z" }
}

meta.cached tells consumers whether they got a fresh result or a cached one. query_ms: 0 means it was served from cache — the actual query took 180ms when it first ran.

When the data should come to you: webhooks

REST endpoints are pull-based. The consumer asks, the API answers. That's fine for dashboards and periodic fetches. But some use cases are reactive — you want to know when something changes without polling.

A webhook subscription flips the model. Instead of asking "what's the revenue?" every five minutes, you say "tell me when monthly revenue crosses $200k" and the system pushes a POST to your URL when the condition is met.

This is the part most DIY API setups skip entirely, and it's the part that turns a data endpoint into a data product. Polling is wasteful and introduces latency. Webhooks with a dead-letter queue and retry logic give you event-driven delivery with guaranteed delivery semantics. Fastero handles the DLQ and retries so you don't have to build that infrastructure yourself — see the full webhook and event subscription model on the API & Webhooks page.

Common mistakes

Exposing raw database connections instead of query endpoints. Giving a partner a read replica connection string feels faster than building an API. It's also a security incident and a performance problem wrapped in a shortcut.

No versioning. Your query will change. If you don't version your endpoints, every consumer breaks at once. /v1/revenue-by-product and /v2/revenue-by-product can coexist while consumers migrate.

Skipping the OpenAPI spec. Undocumented APIs create support tickets. A generated spec means consumers discover parameters, types, and response shapes without asking you. Fastero generates this automatically. If you're rolling your own, swagger-jsdoc or FastAPI's built-in docs work.

Building a generic query API instead of specific endpoints. "Just let them pass any SQL" is not an API — it's a database console with a URL. Curated, parameterized endpoints are more secure, more cacheable, and more understandable.

Tying it together

The pattern is straightforward: save a parameterized SQL query, expose it as a versioned REST endpoint with typed parameters, add auth and rate limiting, cache results with parameter-aware TTLs, and optionally subscribe consumers via webhooks for event-driven delivery.

Building that from scratch means owning a server, a cache layer, a key management system, a retry queue, and documentation tooling. It's real work, and it's work that has nothing to do with the actual data you're trying to share.

If you're already running a collaborative SQL editor where queries are saved, versioned, and tested, the jump to "expose this as an API" should be one click, not one quarter. And if those API results feed into dashboards or embedded analytics, the query layer is shared — you're not maintaining two copies of the same logic.

Start with the query you already have. The one that three people ask you to re-run every week. That's your first endpoint.

Try Fastero free — turn your SQL queries into authenticated REST endpoints with caching, pagination, and webhook delivery. No credit card required.