13

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.

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

Improve this question
2
  • 3
    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, 2010 at 14:41
  • 1
    I was just about to ask this same question. I wish there were more answers here.
    – Michael Kristofik
    Oct 13, 2011 at 19:41

3 Answers 3

Reset to default
11

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.

Improve this answer
5
  • 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, 2010 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, 2010 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, 2010 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, 2014 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, 2014 at 13:57
0

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

Improve this answer
3
  • 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, 2010 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, 2010 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, 2010 at 2:40
0

It could be good idea to separate your model layer into 3 sub-layers - "entities", "repositories" and "services". This will give you separation of concerns and gather SQL in one place, out of your business logic.

In this scenario all the data retrieval code, including complex SQL - will be located in repositories. So the goal of repository is to hide complex SQL statements behind self-explanatory methods like getUsersWithActiveSubscription().

Entity abstracts real DB table field names with getters and setters, may provide some data conversion between DB field types and types available in your application / programming language. If your ORM supports that - entities may handle associations.

Service layer is the place for the business logic. Service retrieves entities using repositories, acts on them and stores them back.

Improve this answer

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