I’m Done With Inline SQL


Photo courtesy of ViaMoi

When I began working "in the real world" almost 4 years ago, I came into an environment with SQL embedded directly into the code as strings.  At the time, it seemed simple. We only had 4 apps: data importer, data exporter, ui for employees to search/view/edit/create, and a web site.  There wasn't much that could go wrong.  We basically had one table of maybe 200,000 rows that our entire company was built off of.  The only thing that really could go wrong was forgetting to truncate a long string or escaping a tick mark.  Even if this did come up, the impact was small and not a big deal.

I managed to move away from the concatenated SQL statements to parameterized SQL relatively quickly if for nothing else just readability.  That's basically where I stopped.  I kept hearing and reading the debates for and against stored procs, and was never swayed from the ways I had learned already.  I started hearing about ORM software and gave a few a try.  Nothing really swayed me while we had our single table.

A few years back we did a massive database conversion to normalize our defunct single table.  The company was growing and our data needs were expanding.  All software was retooled using existing methods and everything painfully got switched over.  Now, here I am 4-years hardened into the real world, and I'm tired of buggy SQL strings.  Our business has expanded so much, and our simple single table SQL statements have turned into crazy multi-table joined monsters.  It just didn't scale and now the code is uglier for it.  Now we have maintenance and testing issues that we're working through.

I've just recently had a few experiences with ORM software and it's so much easier to write and debug code.  I want all of the SQL interactions to be isolated so that they can be tested by someone else (preferably, the ORM vendor).  I just want a platform to use and not worry about it.  If I need to do something crazy, I can create a view or stored proc and have the db check my syntax for me.  I'm sure there will be cases where it makes sense to write some inline SQL , and that's okay.  I'll isolate it and test the snot out of it.  I'm just done having code littered with SQL strings.

This is all just a part of learning and growing.  I'm sure that if I had come into an environment with an existing ORM in place, then I wouldn't have learned half the SQL I know now.  My SQL-Fu seems to have grown by leaps and bounds in the last year or two.  Knowing that, I don't regret the path I took to get to this point.  I do know that I'm sure as hell not looking forward to re-writing those legacy apps as they start giving me issues.

1 Comment so far

  1. Aaron @ March 17th, 2008

    I agree. Especially when I’m rushed. Who knows when I’m going to forget the where clause on an update statement.

Leave a reply