How to interact with an external API

[Ajotah98]

Hello everyone,

First of all, I apologize for being so persistent, but I really like SQLPage and I find myself constantly thinking about how to push its limits.

This time, I want to ask about how you would use SQLPage to interact with an external REST API, such as https://pokeapi.co/, to create a webpage based on its content.

Firstly, what I have thought of is to differentiate between backend and frontend.

On the Frontend, we would have SQLPage, which, depending on the Pokémon requested, would perform insertions into a table. On the backend, we would have a backend in any programming language, which would make calls to the API by constantly listening to that table and would delete or update the rows to only make a call once, in addition to inserting what is received from the API into another table, or the same one.

This does not worry me (in terms of how to do it) but it seems slow (because it requires 2 differentiated environments linked through database tables), unreliable and I don't like the idea of mixing another programming language (because then, SQLPage miss the point of why it exists).

So, I was thinking about how to make the call directly from SQLPage, insert the data that I want or in the order that I want from the JSON returned (with the functions provided by the various DBMSs) and load that information.

How would you do it?

[lovasoa]

Hi !
Multiple approaches are possible.

1. You could usesqlpage.exec to run your custom code directly from SQLPage. See for instance Dynamic (and secure) curl requests via sqlpage.exec()? #215
2. Instead of constantly polling events from your database, you could use postgres and NOTIFY/LISTEN to build a beautiful decoupled system, where SQLPage would NOTIFY, and a small service in another language would LISTEN and asynchronously process data.

[Ajotah98]

Hi again,

I tried to study by myself some Rust, in order to make a new "component" or a pseudofunction to accomplish this.

Here is a schema about what I want to implement:

In the schema, I used PokeAPI as an example, but It should work with any JSON-based API.

I want the final user write something like that:

select 'api_interact' as component,
'APIURL' as url,
'get' as method,
'/mySQLfile.sql' as action;

-- Then, accesing to this page, returns the content of the GET /APIURL.
-- Considering the response something like this: (generated with json-generator.com)
--[
--  {
--    "_id": "65fad98d766f786c03674ea6",
--    "index": 0,
--    "guid": "4f04d0af-84a4-4e69-9f24-949052361f47",
--    "isActive": false,
--    "balance": "$3,641.79",
--    "picture": "http://placehold.it/32x32",
--    "age": 27,
--    "eyeColor": "brown",
--    "name": "Phelps Singleton",
--    "gender": "male",
--    "company": "JETSILK",
--    "email": "[email protected]",
--    "phone": "+1 (968) 405-2421",
--    "friends": [
--      {
--        "id": 0,
--        "name": "Beasley Wright"
--      },
--      {
--        "id": 1,
--        "name": "Carissa Mcmahon"
--      }
--    ]
--  }
--]
-- I want to do something like this in "mySQLfile.sql"

SET personid= sqlpage.api_interact('id')
SET friendsid= sqlpage.api_interact(friends/id) -- I don't really know how to manage multiple "rows" here.

INSERT INTO myfictiontable (personid, friendsid) VALUES ($personid, $friendsid);

-- Then, you could just access the data as normally with SQLPage.

I want to ask you something of the code base, because I'm struggling a little with Rust itself, and I do not get how to manage somethings:

• To create a component that doesn't uses a handlebars template, how and where I need to write the code? I expected that it is in "render.rs", because it's used to render the content, but I don't know which type should be the response of the GET parameter (such as RenderContext,

This are the method and the implementation I did, but... Rust doesn't like it, and I understand why, but I'm a bit overwhelmed.

/// Inside render.rs
/// On impl HeaderContext

pub async fn handle_row(self, data: JsonValue) -> anyhow::Result<PageContext<'a, W>> {
        log::debug!("Handling header row: {data}");
        match get_object_str(&data, "component") {
            Some("api_getter") => {
                let (http_response, renderer) = self.api_getter(&data).await?;
                Ok(PageContext::Body { http_response, renderer })
            },
            Some("status_code") => self.status_code(&data).map(PageContext::Header),
            Some("http_header") => self.add_http_header(&data).map(PageContext::Header),
            Some("redirect") => self.redirect(&data).map(PageContext::Close),
            Some("json") => self.json(&data).map(PageContext::Close),
            Some("cookie") => self.add_cookie(&data).map(PageContext::Header),
            Some("authentication") => self.authentication(data).await,
            _ => self.start_body(data).await,
        }
    }
    pub async fn api_getter(&self, data: &JsonValue) -> anyhow::Result<(HttpResponseBuilder, RenderContext<W>)> {
        let url = data["url"]
            .as_str()
            .ok_or_else(|| anyhow::Error::msg("URL missing from api_getter component"))?;
    
        let response = reqwest::get(url).await?;
        let status = response.status();
        let http_response = HttpResponseBuilder::new(status);
    
        if status.is_success() {
            let response_json = response.json::<JsonValue>().await?;
            match self.start_body(response_json).await { /// The compiler tells me the error is here... And I undestand why, but i dont want to implement something like Copy to duplicate the reference
                Ok(PageContext::Body { http_response, renderer }) => {
                    Ok((http_response, renderer))
                },
                _ => Err(anyhow::Error::msg("Failed to get renderer context from start_body")),
            }
        } else {
            Err(anyhow::Error::msg("Failed to fetch the API data"))
        }
    }

I don't know if I expressed my self correctly, but I hope it's understandable atleast.

Thank you all very much.

[lovasoa]

Hello!
I don't think you need a new component for this.
You can already do everything you want using sqlpage.exec with curl. Then process the json using your databases built-in json functions.

If you want to add something to SQLPage itself, that could be a sqlpage.fetch function which would avoid having to depend on an external curl binary.

[Ajotah98]

That's.. so much better approach as I was doing.
But now I'm curious. How would you process the JSON itself? I mean, the curl will return the response into SQLPage as you said into another discussion, but How can I process it?

Also, I'll give myself a try to develop de fetch() method. Thanks!!

[lovasoa]

@Ajotah98 : I posted an example here: https://replit.com/@pimaj62145/SQLPage-external-api-fetch?v=1

insert into friends (user_name, friend_name)
select people.value->>'name', friends.value->>'name'
from json_each(sqlpage.exec('curl', 'https://mocki.io/v1/83a2365c-9392-4a3d-8953-9b3ad4f3acb1')) people
join json_each(people.value, '$.friends') friends;

This is the SQLite syntax to iterate over json data. Depending on your database, the syntax may be different:

• sqlite: https://www.sqlite.org/json1.html
• mysql: https://dev.mysql.com/doc/refman/8.3/en/json-function-reference.html
• microsoft sql server: https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver16
• postgres: https://www.postgresql.org/docs/current/functions-json.html

[lovasoa]

Just for cross-reference in case someone else stumbles on this topic, here are the previous discussions on this topic:

• API call to populate form fields returns error #197
• Dynamic (and secure) curl requests via sqlpage.exec()? #215
• make external http requests from sqlpage #7

[lovasoa]

🎉

This is implemented in v0.20.3 :

• https://sql.ophir.dev/functions.sql?function=fetch#function