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
Best way to organize SQL queries stored in your code ? (or should you?)
sql;code formatting
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.