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

View post

Replies for: 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 pa...

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": "usr_name_1@gmail.com",
        "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": "usr_name_2@gmail.com",
        "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"
    }]
}