It’s all code

I’ve never really liked stored procedures. I always put it down to the fact that I’m a programmer, not a database person. I like keeping the functionality in the code. The database is just a place where objects go when they sleep.

My current client uses stored procedures for all database reads and writes. It probably made sense when the code was ASP, but Java JDBC code has all the expressibility you could want.

Today, I learned a new reason to not like stored procedures. Someone working on a stored procedure has broken the checked-in DAO code. Until they finish with the stored procedure, and check in the associated DAO changes, everybody using that database is dead in the water.

I suppose you could look at this as a strike against sharing a common database among all the developers, rather than against stored procedures. I could accept that.

It’s also a strike against making live changes to the database rather than scripting them (and checking the scripts into version control). Many times in my career I’ve come across the attitude that stuff in the database doesn’t need to be versioned–it’s just data. My take is that if it can affect the program execution, it’s code.

Ultimately, that’s the problem. The currently checked in code is out of sync with the current state of the database. One is version controlled and one is not. There’s no way to roll back the database change and keep working. There’s no way to go back to a known good state, and that’s bad.

It doesn’t have to be this way.

—-

I’ve tried to clarify this posting where it was misunderstood. I don’t mean that different projects should use different database instances until a big-bang integration. I mean that developers should have their own sandbox instance, so they can roll out changes to the schema in concert with changes to the code. I rarely see this done, because it does mean scripting both the database changes and any data necessary for functionality. When it is done, it pays off in spades. A little discipline in development practices goes a long way to making development easier and faster. Like working out at the gym, however, it’s difficult for people to get over the hump to the payoff.

—-

This posting is really about versioning and keeping all parts of the project in alignment. There’s a secondary question of where the business logic should reside. Some people say that stored procedures in the database is ideal, because it’s a central shared location. I find that the downside to that is stored procedures are necessarily procedural, and you lose the flexibility of object-oriented development for handling more complex situations. What do you think? I welcome your comments.

Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

Comments (2) to “It’s all code”

  1. I agree, stored procedures aren’t the problem. In fact, in the Microsoft world where I work, nobody debates stored procedures any more; they’re just a given. I think it’s a problem of treating the database different from the rest of the application. When I coach teams, I try to help them get their database scripted and in source control and part of the automated build so that (1) it’s clear what version of the database goes with what version of the code and (2) a fresh database can be built to any version in a minute or two with one click on a developer machine or on a build/test server. I’m still not happy with the tooling around SQL scripts, whether stored procedures or other DDL, but this approach at least solves the problems you’re having.

    BTW, I appreciated your contributions in the sessions we were both in at Agile 2007 this week. Never got a chance to meet, but hopefully will at AYE.

    Best,

    Richard

  2. I never discount Stored Procedures but we try to avoid them because it just adds another layer of stuff to keep track of. What really surprises me is that you have people working on the LIVE database. Wow! This is an old post so I can only hope that you have been able to convince your team to implement better configuration management.

Post a Comment
*Required
*Required (Never published)