Put SQL together with the code that uses it

The following question was asked on Stack Overflow:

I have heard some strong developers say that "inline SQL in and of itself is not evil". I do not understand how inline SQL is acceptable. To me its just like hard coding. Many a developer would scoff in my face for putting a connection string in the code vs a config file. So why is it that "SELECT value1,value2 FROM TABLE" is perfectly acceptable in compiled code?

There is a lot of intrinsic coupling between a database query and the code that surrounds it.

For example, a query might fetch first name and last name for a particular user from a database, and then create an XML file containing the first name and last name. If you put the database query somewhere else (e.g. config file) it might seem like you've increased configurability and flexibility. However, if you want to actually change it, say add a field called "age" to the XML file, you can't just change the query alone, you need to change the code writing the XML file.

SELECT statements produce rows with certain columns, and take parameters either in a certain order ("?") or with certain names (":foo"). Your code needs to consume the columns produced by the SELECT, and needs to set all the parameters. If you change what the SELECT is doing, you'll almost certainly need to change the columns it returns, or the parameters it takes. That means you'll need to change the code that consumes those columns or sets those parameters.

By separating the code and SQL, you've replaced the problem of having to change one thing (code), with the problem of having to change two things which depend on one another (code and configuration). Having two things introduces the danger the things might not be consistent, and you haven't gained any extra flexibility.

This article is © Adrian Smith.
It was originally published on 22 Jan 2013
More on: Coding | Databases