Where should database queries live?

Question

Should queries live inside the classes that need the data? Should queries live in stored procedures in the database so that they are reusable?

In the first situation, changing your queries won't affect other people (either other code or people generating reports, etc). In the second, the queries are reusable by many and only exist in one place, but if someone breaks them, they're broken for everyone.

Answer 1

I used to be big on the stored proc solution but have changed my tune in the last year as databases have gotten faster and ORMs have entered the main stream. There is certainly a place for stored procs. But when it comes to simple CRUD sql: one liner insert/update/select/delete, using an ORM to handle this is the most elegant solution in my opinion, but I'm sure many will argue the other way. An ORM will take the SQL and DB connection building plumbing out of your code and make the persistence logic much more fluidly integrated with your app.

Answer 2

I suggest placing them as stored procedures in the database. Not only will you have the re-use advantage but also you'll make sure the same query (being a select, update, insert, etc...) it is the same because you are using the same stored procedure. If you need to change it, you'll only change it in one place. Also, you'll be taking advantage of the database server's processing power instead of the server/computer where your application resides. That is my suggestion.

Good luck!

Answer 3

It depends / it's situational. There are very strong arguments for and against either option. Personally, I really don't like seeing business logic get split between the database (in stored procedures) and in code, so I usually want all the queries in code to the maximum extent possible.

In the Microsoft world, there are things like Reporting Services that can retrieve data from classes/objects instead of (and in addition to) a database / stored procedures. Also, there are things like Linq that give you more strongly typed queries in code. There are also strong, mature ORMs like NHibernate that allow for writing pretty much all types of queries from code.

That said, there are still times when you are doing "rowset" types of things with your queries that work much better in a stored procedure than they work from code.

In the majority of situations, either/both options work just fine.