make external http requests from sqlpage

[dawsongzhao]

does it support to call threeparty api in .sql file?

[lovasoa]

Hello! No, there is currently no support for calling third party APIs. How would you see such a feature working?

Something like:

SELECT
  'fetch' AS component,
  'https://example.com/' AS url,
  'my_table' AS into_table;

?

Or maybe

INSERT INTO my_table
 VALUES (sqlpage_fetch('https://example.com'));

?

[dawsongzhao]

Thank you for your response. I would like to use SQLPage to implement the functionality of the Google search page. The page consists of an input box and a button. The user inputs a query into the input box and clicks the button to submit the request. The current page displays the results/chart or table.

[dawsongzhao]

how to find the docs of 'fetch' component?

[lovasoa]

There is no fetch component or function at the moment. I was asking you how you would prefer to see the feature implemented.

I would like to use SQLPage to implement the functionality of the Google search page.

You want to make a new interface for google search ? Do you want to search in your own database, or do you instead want to have your website make a query to google and then display the results ?

The page consists of an input box and a button.

That you can already implement with the form component

The user inputs a query into the input box and clicks the button to submit the request. The current page displays the results/chart or table.

If you are searching in you own database, you can do something like

SELECT 'list' AS component,
    'Results' AS title;
SELECT page_title AS title,
    page_description as description,
    page_url as link
FROM websites
WHERE 
  page_title LIKE '%' || $search || '%'
OR   page_description LIKE '%' || $search || '%';

Have a look at the new get started section of the website

[dawsongzhao]

Thank you for your detailed explanation. I apologize for the lack of precision in my description, which caused you trouble. Originally, I had planned to use chartgpt (https://github.com/whoiskatrin/chart-gpt) and either sqlchat (https://github.com/sqlchat/sqlchat) or chat2db (https://github.com/alibaba/Chat2DB) to build ChatBI. However, I now plan to use sqlpage to replace chartgpt.

When the user enters a piece of text data into the input box, such as the national birth rate trend from 1986 to 2021, and clicks the confirmation button, sqlpage triggers the button's action, calls the JavaScript API or Rust API, which in turn calls the OpenAI API to obtain data display styles, such as bar charts, pie charts, etc. Then, it dynamically generates a .sql file, queries local database, and visualizes it by sqlpage web.

[dawsongzhao]

Maybe i need a search bar component, and the action of the confirmation button can be customized through a JavaScript API.

[lovasoa]

If you want to execute random code on the client side, you can already do that by creating your own components. Just create a file named sqlpage/templates/mycomponent.handlebars with the following contents :

<script>
console.log(
  {{stringify myprop}}
)
</script>

<b>logged  {{myprop}} </b>

then use it with

select 'mycomponent' as component, 'hello world' as myprop;

[dawsongzhao]

Thank you for your reply, I will try it by myself.

[f8dca]

I think having a dedicated built in component for API calls would be a great addition to the core list of components. This would open up a lot of possibilities like calling webhooks.

While it is possible to come up with an easy, proof of concept to demonstrate this capability there are quite a few details that needs to be considered for a production quality component:

• Support for different authentication methods( basic, digest, API key pairs)
• Support for proxys.
• Secrets management ( We do not want to hardcode any api keys and need to protect these somehow)
• Proper CORS support.
• Passing parameters to the API without hardcoding.
• Handling the returned status code and json value as well ass dealing with timeouts.
• Proper and secure session management for successive API calls.

For the record this is what I currently try to mimic with my custom component:

[lovasoa]

The upcoming v0.12.0 (which is already available on docker hub) adds a sqlpage.exec function, that can be used to execute any external command. This is a real game changer that allows many new usecases, including calling external APIs with curl :star_struck:

Native external API calls (without shelling out to curl) are still on the roadmap, but are now less of a priority.

[mnesarco]

Hello Friends, I have a small example of calling external http services directly from SQLPage and Postgresql.

https://github.com/mnesarco/sqlpage_rest_example

Basically:

-- GET
SELECT ... FROM rest_get('https://....');

-- POST
SELECT ... FROM rest_post('https://....', '{...}'::json);

[lovasoa]

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

• https://github.com/lovasoa/SQLpage/discussions/197
• https://github.com/lovasoa/SQLpage/discussions/215
• https://github.com/lovasoa/SQLpage/discussions/254

[lovasoa]

:tada:

This is implemented in https://github.com/lovasoa/SQLpage/releases/tag/v0.20.3