Best way to organize SQL queries stored in your code ? (or should you?)

Question

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

Answer 1

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.

Answer 2

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