Select custom output formats from database with SQL

Question

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...

1. performance
2. maintenance of code
3. limitations of language
4. 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

Answer 1

What you are describing is fairly common. I wouldn't use SQL to generate actual HTML formatting presented to the user. But there's nothing wrong with putting some of the heavy lifting onto the database server. In fact, there's a school of thought that encourages that. Examples might be date and number formatting, or even using stored procedures for geographic calculations. You just have to be aware of any performance issues if your queries start to get too complex, and be ready to profile them if they show up. A lot of programmers, especially those fairly new to databases, will greatly underestimate what kind of workload a well-tuned DBMS can handle.

Addendum: Personally, I prefer most logic to be in code because I'm a programmer and that's what I work with. But there are cases when you want to rely on the SQL server more. Yours might be one of them.

Answer 2

In terms of...

1. performance
2. maintenance of code
3. limitations of language
4. anything else

1. I don't see any major problems unless your formatting functions get very complex. I suppose it could happen. I've done similar things and never had a problem with performance, but it could still happen.
2. Your formatting logic is in one central place. Not a bad thing.
3. You're limited to the functions your database provides. This may or may not be a problem.

This is not such a bad idea. It gives you the ability to have different clients get the same sort of results and it doesn't require you to write a separate formatting component.

I would recommend that if you do this, have one base view that presents the data, unformatted. That way, you can write different views on top of that view with different formatting logic, as you start to need differently formatted outputs (text, HTML, HTML for mobile, XML, etc...)

---

If you decide that all formatting in the database is not so great, you could change your database to format the output as XML, and then use an XSLT in your many clients to transform to your target format. In that case, all you need to do is run the XSLT in your client (provided that the many different languages can all handle XML and XSL).

Answer 3

You wrote you want to

write complex formatting logic in SQL

but then

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

Well, to my experience, you cannot have both - either you decide to do complex formatting logic in SQL, or you decide to use portable SQL - which means no complex formatting will be possible. The reason for this is that ANSI SQL supports only a small set of string formatting functions, far from beeing enough for implementing any real-world formatting requirements. However, the big database vendors like Oracle, MS, IBM or Sybase have added enough proprietary extensions, especially into the stored procedure dialects, to allow things like custom number or date formatting, HTML or XML generation etc.