I've been tossing around a few ideas about SQL in my head lately. None of them seem to be bubbling to the top, so I'm writing this blog post to help sort out this stew of statements in hopes of finding the choice ideas to pursue.
SQL CRUD is a fact of life for a modern developer. Simple operations can take an inordinate amount of time to complete, which is one of the reasons why ORM tools have made such a splash. For a company that can afford to lose performance and access to vendor specific SQL constructs, they can be a big win. In my current job, it seems we can't afford either. So while we have some very interesting and elegant stored procedure code for the hard problems, we also end up writing a lot of CRUD by hand.
One of the issues I have with CRUD is that often I have a partial object, or more correctly, I have a few columns of a table that I need to update which could come from a complete object, partial object, or multiple objects. The mapping of tables to objects simply isn't 1 to 1 in our code. So how do I persist my data?
Well I could write custom SQL inline.
Or I could write custom SQL stored procedures.
But this leaves me with a N! set of SQL to write. Instead I'd like to write one piece that takes 1 to N columns of a table and updates them as needed. It also would be swell if it would insert if the row didn't exist.
It can be alleviated a bit by writing SQL stored procedures with optional parameters, but...it's okay. I generally don't like seeing huge amounts of options in my code completion tool. I also have an unfortunate habit of having wide columns. I really just one argument object that gives me the buffet of optional parameters to choose from.
So I could use XML.
Somehow this feels inordinately inelegant. We have a generated data access layer already for our custom stored procedures. I shouldn't be writing custom serialization code for every CRUD operation.
Somehow I should be able to divide up the parts of a table into units of columns that are dependent and mark the others as optional. My generated code should know about that and give me suitable CRUD operations, or more suitably Upserts or Saves paired with soft deletes. I shouldn't have to think about this.
But I do.