7

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?)

asthasr
  • 3,439
  • 3
  • 17
  • 24

7 Answers7

5

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.

Wyatt Barnett
  • 20,685
  • 50
  • 69
5

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 ( sometimes referred to as locality ), 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.

3

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.

Dynamic
  • 5,746
  • 9
  • 45
  • 73
Morons
  • 14,674
  • 4
  • 37
  • 73
2

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.

TMN
  • 11,313
  • 1
  • 21
  • 31
1

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.

Otávio Décio
  • 5,299
  • 23
  • 34
1

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.

Morgan Herlocker
  • 12,722
  • 8
  • 47
  • 78
  • 1
    Unfortunately, we're based on MySQL, which doesn't perform spectacularly with stored procedures. – asthasr Feb 17 '12 at 19:55
1

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.

Patrick Moloney
  • 111
  • 1