Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am sure I am not the only one who gets frustrated when they see a page of code littered with SQL queries. The ActiveRecord and other ORM patterns help mitigate a good amount of SQL used in a project, but in many cases of complex queries, the use of SQL is seemingly unavoidable.

I am looking for opinions on how SQL queries should be organized with the rest of the code (or externally to it) to keep it from being scattered all over the place? One obvious idea is the use of Views, but often Views can be a source of performance issues when dealing with multiple large indexed tables, etc.

(If this belongs in StackOverflow let me know, but it seems subjective enough for here..)

EDIT 1 - I am assuming you've already got it separated into the model layer

share|improve this question
2  
This question is definately appropriate here - code organization: 'inspire[s] answers that explain “why” and “how”.' and is of the subjects 'Design patterns' and 'Architecture' (from the Faq) –  Michael K Dec 13 '10 at 14:41
    
I was just about to ask this same question. I wish there were more answers here. –  Kristo Oct 13 '11 at 19:41
add comment

2 Answers 2

up vote 9 down vote accepted

Hey, this is a good question. I've been on both sides of this, and ultimately after quite a few years and a lot of projects, settled on less abstraction.

To me, SQL is a fundamental part (in a lot of cases, the majority) of the business logic code. If you try to separate it from the code that operates on the returned data, you are more prone to unbalance the understandability and maintainability of the code.

As I look at it, reading data, processing data, writing data, searching data... they are all similar operations, and best kept in the same place.

If you start to sense a duplication of efforts with queries, then perhaps you need a database view or an object that can encapsulate that aspect of database access.

Another tip is to actually have a good database query method. In software I write (PostgreSQL, MySQL, SQL Server), I have ensured that the bulk of my query operations can take place as a single statement of code.

GetValue(SQL, [transaction], [array_of_params])
GetRow(SQL, [transaction], [array_of_params])
GetRowList(SQL, [transaction], [array_of_params])
GetValueList(SQL, [transaction], [array_of_params])
Execute(SQL, [transaction], [array_of_params])

Those are (roughly) the main function calls that I ensure are part of my "connection object". It depends on the language, what you actually implemente, but my point is to keep it really, really simple and painless.

In summary, treat SQL as a native part of programming, and do not abstract for the sake of abstraction.

share|improve this answer
1  
a great answer. maybe I just need to step back and start looking at the SQL as part of the code, not just scattered amongst it. –  jellyfishtree Dec 13 '10 at 2:11
1  
"do not abstract for the sake of abstraction" - Good point. Abstract for the sake of more understandable code. –  Jason Baker Dec 13 '10 at 2:14
    
'Another tip is to actually have a good database query method': I definately agree. It helps a lot when there's only one place to modify code when business logic changes. –  Michael K Dec 13 '10 at 14:38
1  
Where do you put the SQL? Is it compiled into the application and sent using the methods above? –  johnny Mar 24 at 2:02
    
Based on comment by OP on Jason Baker's answer, "staring down the barrel of a giant SQL query...", how does this address the problem of reading large blocks of SQL text? –  JeffO Apr 7 at 13:57
add comment

Generally, having a separate model layer is the best approach. There are a number of enterprise design patterns that give ways to architect this.

share|improve this answer
    
sorry, i should have been more specific...I am already assuming you've got them separated into a model layer. But a model layer can still become quite scattered with SQL code. Maybe this is unavoidable. The other thing that freaks me out in the model code is code that "builds a SQL query" based on some logic...maybe this should be separated out into its own factory or something... –  jellyfishtree Dec 13 '10 at 1:26
2  
@jellyfishtree - I'm afraid I don't understand what the problem is then. I mean, you're afraid your model layer might end up with too much model code? –  Jason Baker Dec 13 '10 at 2:11
    
a valid rebuttal. I am concerned about readability. Good model code is usually pretty easy to understand, but staring down the barrel of a giant SQL query doesn't exactly have its meaning jump out at you. Obviously the first thing to do is properly comment those queries, but its not the same as good, self documenting code and these type of sections get scattered across the model. I accept it, but I was wondering if there is a better way to isolate or organize crazy SQL statements in the model... –  jellyfishtree Dec 13 '10 at 2:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.