Skip to content

Two more functions for REST API development using the JSON component #316

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
stefanov-sm opened this issue May 13, 2024 Discussed in #315 · 6 comments
Closed

Two more functions for REST API development using the JSON component #316

stefanov-sm opened this issue May 13, 2024 Discussed in #315 · 6 comments
Labels
functions Related to built-in sqlpage functions good first issue Good for newcomers help wanted Extra attention is needed

Comments

@stefanov-sm
Copy link

Discussed in #315

Originally posted by stefanov-sm May 13, 2024

Motivation

Build REST services that support GET, POST, PUT, DELETE, ... request methods and arbitrary length raw JSON input

Suggestion - add two more functions

  • sqlpage.request_method() function that returns the request method as text. This would allow for code like
select case sqlpage.request_method()
  when 'GET' then (...)
  when 'POST' then (...)
  when 'PUT' then (...)
 ...
end;
  • sqlpage.request_body() function that returns the request body payload as raw text. This would allow for code like (PostgreSQL)
with t(j) as 
(
  select jsonb_array_elements(sqlpage.request_body()::jsonb)
)
...

Expected result

A fully functional REST services development toolset based on the JSON component.
@lovasoa lovasoa added help wanted Extra attention is needed good first issue Good for newcomers functions Related to built-in sqlpage functions labels May 14, 2024
@lovasoa
Copy link
Collaborator

lovasoa commented May 19, 2024

0.21 is now live with sqlpage.request_method()

@lovasoa
Copy link
Collaborator

lovasoa commented May 22, 2024

I think GitHub messed the formatting of your response... Can you open this thread directly on GitHub on the web and paste your message?

@stefanov-sm
Copy link
Author

Great!
Here is a REST service template (working) that might be useful for the documentation, in two flavours -

  • using separate method implementation files (file "Using run_sql.sql")
  • using stored functions (file "Using stored functions.sql").

Method implementation queries and stored functions are purely illustrative and serve no particular purpose.

Tested successfully using
curl -X POST -i http://localhost:83/svc/ --data "one=1&two=2&three=3"
curl -X GET -i "http://localhost:83/svc/?one=1&two=2&three=3"

Both run under PostgreSQL. For versions older than PG14 use JSONB arrow syntax instead of subscripting.
Function sqlpage.variables('post') may be replaced by sqlpage.request_body() in the future.

REST template.zip

@lovasoa
Copy link
Collaborator

lovasoa commented Feb 10, 2025

I'll implement this for SQLPage v0.33.

Let's create great HTTP POST APIs entirely in SQL !

@stefanov-sm
Copy link
Author

stefanov-sm commented Feb 13, 2025 via email

@lovasoa
Copy link
Collaborator

lovasoa commented Feb 13, 2025

Great! If you want to try it now before the official release, you can run lovasoa/sqlpage:main on docker!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
functions Related to built-in sqlpage functions good first issue Good for newcomers help wanted Extra attention is needed
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@lovasoa @stefanov-sm