Using fetch_with_meta() on a list of elements to make multiple requests to a REST API

[olivierauverlot]

Hi,

I am encountering difficulties with the sqlpage.fetch_with_meta() function. For a project, I need to call a REST service that returns a list of identifiers. Subsequently, I need to use another REST service for each identifier to retrieve the data I require.
I have no issues calling the first service, which returns a JSON array. However, I am struggling to find a way to apply the fetch_with_meta() function to each element of this array.

What is a recommended approach to use fetch_with_meta() while iterating over a list of elements?

Best regards
Olivier

[lovasoa]

Unfortunately there is no nice built-in way to do this in SQLPage.

Either you do it from inside the database with https://github.com/pramsey/pgsql-http

Or you do it from an external script with sqlpage.exec.

[olivierauverlot]

Thank you for your response.

Unfortunately, I was expecting this answer. I have tried several approaches without success. It would be interesting to find a solution, as this is a common need when interfacing an SQLPage application with a REST API to cross-reference information with other applications in an information system.

Could we consider a function batch_fetch_with_meta() that takes a JSON array as a parameter, makes the different requests, and returns the result in a JSON-formatted string?

Something like this:

set request_1 = json_object(
    'method', 'POST',
    'url', 'https://sqlpage.free.beeceptor.com',
    'headers', json_object(
        'Content-Type', 'application/json',
        'Authorization', 'Bearer ' || sqlpage.environment_variable('API_TOKEN')
    ),
    'body', json_object(
        'key', 'value'
    )
);

set request_2 = json_object(
    'method', 'POST',
    'url', 'https://sqlpage.free.beeceptor.com',
    'headers', json_object(
        'Content-Type', 'application/json',
        'Authorization', 'Bearer ' || sqlpage.environment_variable('API_TOKEN')
    ),
    'body', json_object(
        'key', 'value'
    )
);

set responses = sqlpage.batch_fetch_with_meta(json_array($request_1, $request_2));

The response would be composed of JSON objects, each describing the result of one of the requests.

[lovasoa]

@olivierauverlot Thinking about it a little more, you can achieve what you need with sqlpage.run_sql:

index.sql

drop table if exists fetch_results;
create temp table fetch_results as
select
    result ->>'url' as url,
    (result ->>'fetch_result')::jsonb as fetch_result
from 
  jsonb_array_elements(
    sqlpage.run_sql(
        'fetch_all.sql',
        json_object('url', 'https://pokeapi.co/api/v2/pokemon-species/')
  )::jsonb) as r(result); -- returns a table with a column named value for each row returned by fetch_all.sql

select 'card' as component;
select
    url as title,
    'status: ' || (fetch_result->>'status') as description
from fetch_results;

fetch_all.sql

-- $url should be the url of a json resource that returns
-- {"results": [{"url": "https://..."}]}
set list = sqlpage.fetch($url)::jsonb->> 'results';

select
    value->>'url' as url,
    sqlpage.fetch_with_meta(value->>'url') as fetch_result
from
  jsonb_array_elements($list::jsonb); -- returns a table with a column name `value` of type jsonb for each result in $list

[olivierauverlot]

Thank you !

That's impressive and it's a well-written piece of code. I'll test it with a real-world scenario. It could be a valuable addition to the fetch documentation, as it demonstrates a common use case with APIs.