Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

My current application is running into issues with its ORM, and we're relying on some pretty hairy SQL to express queries that the ORM can't. Are there best practices in terms of how to manage the SQL queries? For example, where do these usually live in relation to other parts of the code? Are they in separate .sql files, or do you edit them directly in your other source files? If the latter, does your IDE (or editor) allow syntax highlighting? Are there schemes for Vim or Eclipse which will perform SQL syntax highlighting given a set of tags (like #begin sql and #end sql, or something similar?)

share|improve this question
add comment

7 Answers

up vote 5 down vote accepted

I'd typically vote against stored procs or views unless you've got a great database build and migration system in place -- it is just too easy to get things out of synch.

Insofar as how to store them in your code, typically I'd vote they travel with the appropriate classes that are touching the database and executing the SQL. I don't see any downside to having the queries reside in the normal source so long as you don't have SQL strewn all over the place in your codebase.

share|improve this answer
add comment

If you don't like SP's you can always use a View. So your SQL is stored in the DB.

You can create an Entity in your ORM based on your view's data structure.

See this link to the MySQL docs.

share|improve this answer
add comment

We typically segregate DB-specific stuff into one or more Strategy classes, and then configure them at deployment time. I once over-engineered a solution that would auto-wire everything at run time after introspecting the database connection, but it was overkill for the trivial effort required for a one-time set-up procedure.

share|improve this answer
add comment

First off all non-trivial applications have issues with ORM. They are great for CRUD operations but beyond that they just don't work. This isn't a unique problem to your situation. Don't let anyone tell you that you are doing something wrong by running up on the limits of whatever ORM you are using, they all suck at something, most of the times performance and flexibility.

SQL is just source code. Whether it is embedded in your application language, or stored in its own files, it is just source code that needs to be version managed just like any other source code asset.

The idea that separating this logic out and storing it somewhere else (stored procedures) is a panacea, is false, it just moves the problem to somewhere (and probably someone) else, which will cause more synchronization issues in the long run.

All software systems should strive for high cohesion, this means in your case, put the logic closest to where it is used so it is easily found, managed and follows the Principle of Least Astonishment.

Even the act of putting it in its own file that is loaded as a external resource by the application can put an artificial level of indirection that can cause confusion and maintenance hassles.

share|improve this answer
add comment

For those cases I normally create a stored procedure, in great part because what you just mentioned - I have the database to check syntax and I can use its tools to edit it, and I can also test it.

share|improve this answer
    
That's my first instinct as well, but we're using MySQL and its SP support is not great. –  syrion Feb 17 '12 at 19:56
    
@syrion - even so I doubt it is worse than what you can write in a SQL string. –  A----------------------- ----- Feb 17 '12 at 20:11
add comment

Definitely the route to go here is stored procedures. You might be able to maintain discipline for a while with embedded SQL, but IMO this always breaks down eventually. All it takes is someone going in to modify something a year or two from now and leaving SQL injection vulnerabilities everywhere, which is particularly easy to do with embedded SQL.

share|improve this answer
1  
Unfortunately, we're based on MySQL, which doesn't perform spectacularly with stored procedures. –  syrion Feb 17 '12 at 19:55
add comment

If you're using version control and some kind of compare tool you should be able to easily see if someone changes any embedded sql when they check something in. And if something breaks you should also be able to easily find which checkin had the changed code. You may be able to establish some practice of putting sql in variables so it is all defined at the top of your program - or something like that - just to make it easier to check.

I've found mysql to be difficult with SPs, especially where you are dependent on a hoster or isp who may not even providing everything that mysql could do. Otherwise I'm all for SPs, but you should be able to get control of your embedded sql.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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