I am planning to make a simple rest service application, and I am currently deciding the architecture. I have decided that I want to write the middle layer in multiple languages, so that it is easy to deploy/integrate with as wide a range of systems as possible.
Due to the necessity of porting the code to multiple languages, I want to keep the middle layer logic as simple as possible, to alleviate the coding effort involved.
I am planning to put as much code into SQL as possible. For example, traditionally, I might select some data from the database, then loop through the array of results, and process them further before sending back as a well formed response, like perhaps some HTML derived from the data.
However, given I want the middle layer to be as simple as possible, I would prefer to do all the processing as SQL. I have prepared sql statements that select from my results by concatenating and using group aggregates for repeating elements. It all seems to work fine, but I have a niggling doubt that I have left the reservation.
I am concerned that I have not seen this kind of thing done, so perhaps there is a good reason I have not thought of.
My question is...
Is it a bad idea to write complex formatting logic in SQL instead of application code?
In terms of...
- maintenance of code
- limitations of language
- anything else
n.b. I don't want to use PL_SQL or anything like that, I am only interested in using very basic SQL commands to achieve my formatting, as I want the database code to be portable between different databases too