Create an HTTP endpoint with SQL
The SQL component allows you to automatically generate an HTTP web API endpoint wrapping some arbitrary SQL statement. It is similar to the backend CRUD generator, but instead of automatically creating your SQL, it allows you to provide your own custom SQL, and then securely wrap your SQL into an HTTP endpoint. Below is a screenshot of the component.
The process works as follows.
- Choose your database
- Choose an HTTP verb and a URL for your endpoint
- Select which roles should be allowed to invoke your endpoint (optional)
- Provide arguments to your endpoint (optional)
- Write your SQL referencing arguments if you provided arguments in the above step
When you’ve done with the above, simply click the “Generate” button, and you’ve got an HTTP endpoint wrapping your SQL. Read this article for a hands on tutorial about the component.
Settings
The SQL generator allows you to override authorisation requirements, the URL of your endpoint, and which arguments
your endpoint requires. The last parts is important since it allows you to add arguments to your endpoint that
you can reference in your SQL somehow. In the above screenshot we have created a “filter” argument, that we’re
referencing inside our SQL using @filter
. Above we’re using this argument to make sure we’re only returning
items having either a “first_name”, “title”, or “description” that contains the specified filter value - But you
can also reference arguments for insert, update, or delete SQL statements. If you generate an SQL endpoint using the
PUT, PATCH or POST verb, the caller will have to supply his or her arguments as a JSON payload. If you generate a GET or
DELETE endpoint, the endpoint requires your arguments as query parameters instead. When you add arguments to your
endpoint’s declaration, you’ll have to choose a “type”. This ensures that Magic is automagically converting
the incoming argument’s value to the type of your choice. Below is a screenshot of this process.