> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Enriching Rows with External API Data

> How to pull live data from external REST APIs into MySQL query results using VillageSQL's vsql_http extension.

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> All examples on this page work on VillageSQL. Install Now →

Some data doesn't live in your database — exchange rates, shipping status, geolocation lookups, third-party product details. The standard approach is to fetch it in application code and either cache it in a table or stitch it together with query results at the application layer. VillageSQL's `vsql_http` extension lets you pull that data directly from a query or store it in a column with a single UPDATE.

## Setup

```sql theme={null}
INSTALL EXTENSION vsql_http;
```

## Pattern 1: Store Enrichment in a Column

The most common use: backfill a column with data fetched from an external API. Run an UPDATE that calls the API per row, store the result, then query the stored column.

This example fetches a shipping status for each order:

```sql theme={null}
ALTER TABLE orders ADD COLUMN shipping_status VARCHAR(100);

UPDATE orders
SET shipping_status = JSON_VALUE(
  JSON_UNQUOTE(JSON_EXTRACT(
    CONVERT(
      vsql_http.http_get(
        CONCAT('https://api.shipping.com/track/', tracking_number)
      ) USING utf8mb4
    ),
    '$.content'
  )),
  '$.status'
)
WHERE tracking_number IS NOT NULL
  AND shipping_status IS NULL
LIMIT 50;
```

Run this in a loop from your application until no rows with `shipping_status IS NULL` remain. Process in batches to stay within API rate limits.

After backfilling, queries hit the stored column — no API call per query:

```sql theme={null}
SELECT id, customer_id, shipping_status
FROM orders
WHERE shipping_status = 'delivered'
ORDER BY updated_at DESC;
```

## Pattern 2: Live Lookup in a Query

For data that changes too fast to cache — exchange rates, real-time pricing — you can call the API inline in the SELECT. Every row in the result triggers a request, so keep the result set small.

```sql theme={null}
-- Fetch the current USD/EUR exchange rate and apply it to each row
SET @rate_resp = CONVERT(
  vsql_http.http_get('https://api.exchangerate.example.com/latest?base=USD')
  USING utf8mb4
);
SET @rate_body = JSON_UNQUOTE(JSON_EXTRACT(@rate_resp, '$.content'));
SET @eur_rate = CAST(JSON_VALUE(@rate_body, '$.rates.EUR') AS DECIMAL(10,6));

SELECT
  id,
  amount_usd,
  ROUND(amount_usd * @eur_rate, 2) AS amount_eur
FROM invoices
WHERE currency = 'USD'
LIMIT 100;
```

Fetch the shared value once into a variable, then use the variable in the query — don't call `http_get()` inside the SELECT expression unless each row genuinely needs a different API call.

## Pattern 3: Conditional Enrichment

Only fetch for rows that actually need it using WHERE to filter before calling the API:

```sql theme={null}
-- Only enrich rows missing geocoordinates
UPDATE locations
SET
  lat = CAST(JSON_VALUE(
    JSON_UNQUOTE(JSON_EXTRACT(
      CONVERT(
        vsql_http.http_get(
          CONCAT('https://api.geocode.example.com/json?address=',
                 vsql_http.url_encode(address))
        ) USING utf8mb4
      ),
      '$.content'
    )),
    '$.lat'
  ) AS DECIMAL(10,7)),
  lng = CAST(JSON_VALUE(
    JSON_UNQUOTE(JSON_EXTRACT(
      CONVERT(
        vsql_http.http_get(
          CONCAT('https://api.geocode.example.com/json?address=',
                 vsql_http.url_encode(address))
        ) USING utf8mb4
      ),
      '$.content'
    )),
    '$.lng'
  ) AS DECIMAL(10,7))
WHERE lat IS NULL
  AND address IS NOT NULL
LIMIT 25;
```

Note `url_encode()` wrapping the address — always encode user data before embedding it in a URL.

## Handling API Errors

All `vsql_http` functions return NULL on connection failure, with a warning carrying curl's error message. Check the status code before using the content:

```sql theme={null}
SET @resp = CONVERT(
  vsql_http.http_get('https://api.example.com/data/123') USING utf8mb4
);

SELECT
  CASE
    WHEN @resp IS NULL          THEN 'connection_failed'
    WHEN JSON_VALUE(@resp, '$.status') = '200' THEN
      JSON_UNQUOTE(JSON_EXTRACT(@resp, '$.content'))
    WHEN JSON_VALUE(@resp, '$.status') = '404' THEN 'not_found'
    ELSE CONCAT('error_', JSON_VALUE(@resp, '$.status'))
  END AS result;
```

For bulk UPDATE loops, rows that get a NULL or non-200 response keep their column value NULL, so you can re-run the UPDATE to retry failed rows.

## Performance Considerations

**Each HTTP call is synchronous and blocks the query.** A batch of 50 rows each hitting a 200ms API takes 10 seconds minimum. `max_execution_time` only bounds `SELECT` statements — it has no effect here. To cap how long this `UPDATE` can run, get its connection ID with `SELECT CONNECTION_ID()` beforehand and run `KILL QUERY <id>` from another session if it runs too long:

```sql theme={null}
UPDATE orders
SET shipping_status = JSON_VALUE(
  JSON_UNQUOTE(JSON_EXTRACT(
    CONVERT(vsql_http.http_get(
      CONCAT('https://api.shipping.com/track/', tracking_number)
    ) USING utf8mb4),
    '$.content'
  )),
  '$.status'
)
WHERE shipping_status IS NULL
LIMIT 50;
```

Prefer storing results over querying live — once a value is in a column, subsequent queries don't touch the network.

## Troubleshooting

| Problem | Solution |
| :- | :- |
| UPDATE runs too long | Reduce `LIMIT`; `max_execution_time` does not apply to `UPDATE`, use `KILL QUERY <connection_id>` from another session instead |
| Returns NULL for all rows | API unreachable from server host; check `SHOW WARNINGS` for curl's error message, and verify network access |
| JSON extraction returns NULL | `content` is a JSON-encoded string, not a nested object, so a single-step `'$.content.field'` path never resolves. Unquote it first — `JSON_UNQUOTE(JSON_EXTRACT(resp, '$.content'))` — then extract fields from that. This is independent of response size |
| URL parameters not encoding correctly | Wrap values with `vsql_http.url_encode()` |
| Rate limit errors from API | Reduce LIMIT per batch; add `SELECT SLEEP(1)` between batches |

## Next Steps

* [Making HTTP requests from SQL](https://villagesql.com/docs/en/guides/http-requests-in-mysql.md) — full function reference and response parsing
* [Sending webhooks from triggers](https://villagesql.com/docs/en/guides/http-webhooks.md) — push changes out instead of pulling data in

## See also

* [Making HTTP Requests from SQL](https://villagesql.com/docs/en/guides/http-requests-in-mysql.md) — the HTTP functions used for API calls
* [JSON in MySQL](https://villagesql.com/docs/en/guides/json-in-mysql.md) — parsing the JSON responses returned by APIs
* [Serving Tables as a REST API](https://villagesql.com/docs/en/guides/rest-api-from-tables.md) — publishing your own tables over HTTP


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.