REST API with 1000 query templates

Some quick thoughts… If you have X queries, I suspect you have X REST API endpoints. So the client (Javascript) code running in the browser will invoke one the X endpoints on some user action on the pages. Now you have to take the data from the user (embedded in the URL or POST - unless you are using something like GraphQL) and combine it with the SQL to complete the intended query/update and send something back to the user.

So only a given endpoint knows what to do the data from the user and is the only place that needs to know the SQL, plus the logic of how to generate the full SQL statement with the user data. At this point both the SQL fragments and user data are treated as data by the endpoint… so I would keep the SQL in the endpoint itself. Only if the processing logic was extremely regular would I consider storing it outside the endpoint logic; “extremely regular” means that a given endpoint serves multiple forms/pages on the client and the combining rules are simple and regular.

You must take care to sanitize the input, since this approach is amenable to SQL injection attacks.

Hope this helps.

2 Likes