It was time to refactor the API where database queries are made. There have been some changes in application architecture that needed to be implemented and it was time to confirm, again, that the API respects user and API key permissions for data access.
The API does respect permissions so a super-user or system administrator could issue an API key to a user granting them permissions they otherwise do not have. The API query will then run according to the permissions associated with the individual API key used rather than the user account associated with it.
While working on the API I realized it was very easy to add a few features that are planned for the future.
1.) debug=1 - There is now a debug flag and without that errors will not be displayed. There is a challenge around API's that output in CSV format, how should errors be incorporated into the output without breaking whatever is parsing the data.
2.) download=1 - There is now a download flag and if set the headers will be sent to download the query results. Later this can be incorporated into the visual query builder to "download the results of this query".
3.) blanks=1 - Without this, the API will remove rows that are entirely blank (all returned fields are empty). This is a big change since previously there was a "no_nulls" flag and that had to be set to perform this behaviour. Now instead, it will default to removing empty rows, thereby reducing bandwidth, unless specifically requested not to.
4.) Saved Queries - A unique API key can now be created that saves/stores not only the permissions but also the API query. So, in the future there will be functionality where an expert can create a query, save the query, and share that query with another, less-experienced, person. Or, a person can build a query, confirm it is working, save it, and copy/paste it to their application knowing nothing will change.