My company runs a technology forum for its members every month (hosted by my lovely wife).  Tonight’s talk was entitled “Where’s the SQL?” and consisted of a discussion about whether the SQL logic should live in the application, the database or split between the two.  It turned out to be a very calm discussion about treating data as a service and abstracting its retrieval away from the application rather than placing prepared statements or dynamic SQL in the application code.  We discussed the use of ORM tools like Hibernate or iBATIS and how these tools fit within the concepts of separating data access from the application.  Obviously we came to no hard agreement, since we had OO developers and DBAs in the room, but there was one thing that was said (by a Java developer in fact) that struck me as wrong.

I asked how tightly bound to a given database vendor you’d be if you chose to use stored procedures and other database-resident methodologies for accessing data.  The answer was pretty much what I was expecting - that there’s a fairly tight coupling between your SQL and your database vendor.  There are tools that can help you migrate, but it seems like you’re stuck rewriting a fair amount of code (multiple people in the room have migrated from Sybase to Oracle and that seemed to be the universal experience).  One developer made the comment that you should consider swapping database vendors to be the equivalent of switching languages - from Java to C# for example.  While it may be true, this feels wrong to me.  I don’t want to be tied to my database any more than I want to be tied to my application server.  I want the flexibility to move from Oracle to MySQL if the license costs get too much for me - and I don’t want to have to rewrite my application if I choose to move.  I wouldn’t accept a full rewrite if I moved from WebSphere to WebLogic - why would I accept it at my database tier?

I think this lock-in is what makes me most nervous about putting large portions of my code at the database layer.  There are clearly times when it’s necessary - whether for performance reasons or because the logic is being invoked outside of the calling applications, in a batch for example (although you could definitely make that work in Java, perhaps with Spring Batch) - but I don’t think I’d feel comfortable committing to one database vendor for essentially the lifetime of my data.  Using ANSI standard SQL and putting my logic in my application tier essentially protects me from that.  My gut feel is that the cost is worth it in the long term, but I’m not sure if others (including my customers) see it the same way.

This entry was posted on Monday, November 3rd, 2008 at 10:51 pm and is filed under Programming. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.