Discussion on: Should SQL Query through REST API be a GET or a POST?

[Camilo]

In my opinion I would use GET for selects and POST for updates and DELETE for deletes. You can encode them in base64 so you have a single string param.

I find a little weird to send raw sql queries through api rest.

Another approach would be separating the command with the execution.

First you create a queries endpoint that only creates a query resource. It does not execute them.

• POST queries: creates a new query with params

[{
    "query": "select users.username, users.email, orders.id, orders.date, orders.amount from users, orders where users.trid = orders.user_trid and users.user_trid in ( 31, 32, 33 ) and users.status = 1"
}]

Returns an id for that query. Sets its status to pending.

Then you create a new endpoint that executes the queries using the provided id.

• PUT queries/query1234: Executes the query with the id and change its status to done.

• GET queries/query1234: Returns the query result and status.

[Nicolas Penot]

Thank you Camilo :)
I think we're going to use GET to /query?q=.
Your idea of "prepared queries" is very good. Actually, in a later stage, we will be able to POST prepared queries and then GET the result. But we still need to develop the prepared query library.

Example:

POST Payload

[{
        "id": "user_list_with_orders",
        "query": "select users.username, users.email, orders.id, orders.date, orders.amount from users, orders where users.trid = orders.user_trid and users.user_trid in ( :user_trids ) and users.status = :user_status"
    },
    {
        "id": "one_parameter",
        "query": "select name, value from app_settings where name = :parameter_name"
    }
]

GET URL

GET  /query?id=user_list_with_orders&user_trid=31&user_trid=32&user_trid=33&user_status=1

Return

{
    "users": [{
        "username": "usr_name_1",
        "email": "[e-mail]",
        "orders": [{
            "id": 2543,
            "date": "2020.08.11",
            "amount": 35.99
        },{
            "id": 2843,
            "date": "2020.10.01",
            "amount": 45.99
        },{
            "id": 3843,
            "date": "2020.12.23",
            "amount": 395.99
        }]
    },{
        "username": "usr_name_2",
        "email": "[e-mail]",
        "orders": [{
            "id": 3815,
            "date": "2021.01.11",
            "amount": 35.99
        },{
            "id": 3176,
            "date": "2021.02.03",
            "amount": 45.99
        },{
            "id": 3898,
            "date": "2020.03.05",
            "amount": 395.99
        }]
    }]
}

GET  /query?id=one_parameter&parameter_name=params1

{
    "app_settings": [{
        "name": "params1",
        "value": "value_param1"
    }]
}