Our Architecture team is proposing a framework that would see our SQL queries moved from coded strings within our applications, into a file based system where we would invoke them with function calls. Our application makes heavy use of SQL queries ranging from the mundane, to very complex. This is a .NET solution.
The idea is that each query would be written to text files, and we would be able to tag fields, joins, and conditions with attributes. When calling our SQL functions, we could pass arguments to toggle these attributes on/off based on our business logic, allowing for only the portions of the SQL we need to be executed.
Although I like the idea of abstracting away our SQL, I am skeptical that this system will result in any tangible benefits. The major driver of this is our past experience where many coders created some massively bungled dynamic SQL functions which were impossible to understand. The idea is that this will make it easier to define your queries, will make all queries more easily testable, and clearly define the business logic involved. I am unsure if this will survive the test of reality.
I’d like to get some feedback on the pros/cons of this approach.
Also, before anyone suggests it, the Entity Framework was rejected due to questionable support from our database provider (Teradata).
3
If your problem is “impossible to understand” SQL, focus on that problem. Can not imagine that SQL with attributes does anything to make it easier to understand.
Instead it will create a maintenance burden on parsing attributes etc on an already complex language like sql. Sooner or later your extensions will prevent you from using some nice database feature. How much training is needed to teach new employees/contractors your version of sql?
Extra files create a more complicated deployment process and possible support problems. How do you know the version of the file that is in use or if it has been altered by the customer or your own support to solve another issue? Everything can be solved of course but only you knows if it is worth it.
Spend your time setting up strict guidelines for writing readable/maintainable SQL first. Once that is in place and everyone agrees on the rules you can move to the next problem.
What is readable and maintainable sql varies but here is a subset of our rules. (For sql-server)
- you must be able to easily copy/paste code to/from database and run it
I.e. No string concatenations of variables in code.
Use parameters or string.Replace where parameters are not allowed in sql. - one column per line pefixed with table/alias
- explicit AS
- ansi joins
- descriptive aliases (not t1, t2 etc)
- use CTE/CROSS APPLY to reduce complexity
- use parameters with descriptive name for both variables and “magic” numbers.
2
I ran into this same dilemma at my job a while back. I got the same advice you did, and here’s what I thought about it:
Pros:
- SQL queries can be tested on their own, separate from the mainline code.
- Your queries can be modified without having to recompile.
Cons:
- SQL is code. I find it easier to manage when all the code that does something is in the same place.
- I’ve found it rare that you modify a SQL query without a corresponding mainline code change.
I decided that the cons outweighed the pros and left the SQL in the mainline source. It’s been about a year since I made that decision and I haven’t regretted it once.
1
If you said the architects wanted you to extract the SQL and put it in stored procedures inside the database, I’d have said “Give them boys a cookie!”. But switching from SQL assembled via string construction to SQL assembled via templating is too minor a step to deserve a reward.
3
One BIG Pro.
You can support multiple DB’s simply swapping out the SQL files.
So you have multiple sets of SQL Code for the different DB you want to support.
1