Piercing the Data Layer with Code Generation

When people think about dealing with a data or persistence layer, they are often thinking about a database, and when they think about a database they often are thinking about an Object Relational Mapping (ORM) tool.

ORM tools are great, if you're in a position to use them. In particular ORM tools are great with green field development, rapid prototyping, small scale projects, or on teams without the need for dedicated database expertise. With some additional ORM expertise, larger, more database performance sensitive projects can also be tackled.

But what happens when you have an existing, large, and very database performance sensitive code base? One with hundreds of existing stored procedure (Sproc) calls to access the data layer. A traditional ORM tool may not be the right approach. ORM's often have a code first, generate SQL mentality. Your app is already in SQL, how can we get the code to talk to it?

Enter code generation. The modern web programmer is already experienced at least in part with code that writes code. Either by accident or design, code ends up in the view layer that is modified by other code statements. This same type of view engine can be used to write more than dynamically shifting client-side javascript. You can also generate code in whatever server-side language you'd like.

To do this you might do some type of basic reflection and string manipulation developed in an internal tool. Web frameworks give an example of how to create templates and might be repurposed for the task. On the other hand you might find a tool like CodeSmith which is built specifically for the task of code generation.

There are many benefits from automating this process. Beyond not having to write simple wrappers for every new or updated sproc, code generation can allow you to create a standardized interface to your database. Under this interface an implementation can be auto-generated to call the underlying Sproc. Having a pre-existing interface to code against greatly simplifies adding additional unit tests to your project, instead of having to hand roll one specifically for testing. It also gives you a central point of control outside of your database to consider other persistence options.

In particular, I like to think of by data layer as a single API. Everything behind it should be a black box to the code. In practice, this has some problems. As multiple developers work against a shared database, changes have to be propagated out as the interface changes. Local database development would be a solution, but comes with its own challenges. A potential solution is to make partial interfaces and classes, one for each sproc so that changes can be taken care of a bit more piecemeal. Partial interfaces and classes however do seem to come with a substantial performance hit to autocompletion tools and some compilers.

A separate but related issue is the addition of additional frameworks or languages. An API interpretation would suggest the same methods are available to each. Developers might however want to keep data layer access code limited on a per technology basis. For example if the GetPersons sproc is used in Java section of a project, but not the Python sections, the corresponding Python data layer access code might omit the GetPersons sproc. Partial interfaces help facilitate some flexibility in this regard.

I hope this article has given you some things to think about when tackling your data layer on your next project. ORM tools solve a code generation problem, but sometimes from the wrong side of the data layer. Make sure to consider other code generation tools when you got SQL written first.

Personal Development Environment

Choice of development environment is often a choice that is preordained, either by your job or by the project. When I get home and just want to explore on my own, I've come to appreciate things a particular way.

I use the following for my personal projects:

Host OS - Windows 7

One of the best Windows versions in my opinion, appears better than the past. This is augmented by Microsoft's free virus protection software, Microsoft Security Essentials. The UI is very responsive with a minimalist system tray. I take the minimalism a step farther by removing all icons, including the Recycle Bin, from the desktop. The search bar under the 'Start' button is also so quick that I don't ever navigate the 'All Programs' start menu manually. To top it off PowerShell gives you a terminal with a lot of the Unix goodness plus objects, to make scripting much more palatable in Windows.

Virtualization Tool - Virtualbox

If you're going to go exploring on your personal machine, you need a safety net. If you spread out all your tools on your Host OS, for multiple technology stacks, you'll end up with a slow buggy mess. Virtualization lets you divy stacks between environments, so no one steps on anyone else's toes. You screwed up an install? Nuke the OS. It isn't like it will prevent you from using your Host OS.

Virtual Machine OS - Ubuntu

I love Windows, but it really doesn't do everything I need it to. Actually, outside of being a consumer of programs or developing against the CLR, Windows still is pretty bad. Too many open source projects have a Linux first mentality. Also, I'm in it to learn something when I'm playing around at home. Linux is a foreign land to me still in a lot of ways, so I still have plenty to learn. It also doesn't hurt that it is free (as in beer).

Version Control - Git

Version control is a godsend when exploring a new technology stack. Not only can you save your work, but with Github you have a way to present it to the world. It also is the method of choice for open source projects, which I want to contribute more to. Also I use Mercurial at work and Git is just different enough to be a good learning target.

Text Editor - Vim

Beautiful. Powerful. Arcane. More learning that I'll probably be able to do in a lifetime. Vim proficiency is a life goal and my free time is when I can justify looking up a new text editor command every ten minutes. Also, it forces me to think in Regex.

Web Browser - Chrome

I use it everyday, at work and home, and I still have a lot to learn. The Chrome developer tools have depth that I have not begun to fathom.  It makes browsing and developing web applications a pleasure.

Choice SQL Statements

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.

Learning JavaScript: What You Might Have Missed

I just started teaching my first college course this week in JavaScript, a language I'm sometimes ashamed to say I love. Teaching this class has gotten off to a fantastic start. It became quickly apparent that I've been hoarding a number of golden nuggets of JavaScript resources. I really enjoyed sharing some of them with my class, so I also gave a similar lightning talk at the Memphis Java User Group where it was well received.

The resources below are loosely ordered from beginner to expert. All of them have give me something to think about.


w3schools was really the first introduction I had to web programming. Much of the material is out of date, but the quantity and diversity of the tutorials provided more than makes up for it. Each tutorial comes with a quiz, so you can make sure your eyes didn't glaze over during the process. I find it a great reference for quickly getting a start or just refreshing my mind, even if the examples are not exactly idiomatic for modern development.

Komodo Edit

Is a tool I've just learned about, but I can already see why people like it. My students use it almost exclusively for editing HTML, CSS, and now JavaScript. It also claims to support popular scripting languages such as Perl, Python, PHP, and Ruby. The editor has an toolbar icon for launching multiple different browsers to test your web pages. Syntax highlight and auto-completion round out the feature set.


When my mind tires of trying to remember my vim commands, I usually do my text editing in notepad++. Syntax highlighting for more languages than I can list is built in, as well as a tabbed layout. Notepad++ is a very responsive and light weight solution for such a fully featured text editor. I make heavy use of it at home and work.

Chrome Developer Tools
Internet Explorer Developer Tools

If you haven't used a modern web browser's developer tools, do yourself the service of checking out the ones in listed above (all should open with shortcut F12). Chrome and IE tools are built right into the browser, and Fiefox's FireBug Add-on gives you the same options. These tools let you live edit your html and css, check javascript errors or run javascript against the page in a console, and let you see the name, speed, and size of resources downloaded on page refresh.


Liked the live editing in the developer tools, but want a lighter weight tool for prototyping or the ability to easily share? Enter jsFiddle, a tool that shows html, css, and js files all on one page. A number of scripting libraries are quickly included under the panel on the right, rendered pages appear on the bottom right. Save your file and the URL changes to a link you can share with anyone, allowing them to edit and fork your work.

JavaScript: The Good Parts

If I told you this was the best hour spent in learning JavaScript, I'd probably not be lying. A talk given by Douglas Crockford on topics covered in his book of the same name, this Youtube video is part of the very informative Google Tech Talk series. This talk gives a great depth of understanding of the gotchas of the language as well as some history.


Again by Douglas Crockford, jsLint proudly states that it will "hurt your feelings". This is because most of our JavaScript is terrible, since the interpreter accepts all types of misconstructed JavaScript. jsLint says no, giving you a long list of warning to pour over to clean up your code (and probably reveal a few bugs along the way). Not for the faint of heart.


Reddit, the big bad link aggregator has sometimes been referred to as a milder version of 4chan. While you might not get much JavaScript help out of the main site, there are many subreddits that can give you a step up. After you tire of the /r/javascript subreddit, check out /r/programming, or /r/compsci.


Whereas language specific subreddits are usually very beginner friendly, StackOverflow demands a little more effort to submit questions. In return you often get better answers, often found with a Google search before you even post. The large size of the community means most common questions have been answered before and fantastic SEO makes the site very navigable from the Google Search bar. Tagging let's you narrow your scope to JavaScript.


A JavaScript library for all seasons, jQuery should be the standard library for the JavaScript language. It provides everything you could want for DOM manipulation and event handling.


If you thought jQuery was the bees knees (hint: it is), you'll probably be impressed with Backbone. Backbone let's you create a fully featured MVC application, in JavaScript. That's no joke, your entire model can run client side using AJAX for REST calls for persistence.


Liked the idea of Backbone, but a bigger fan of the MVVM architecture? See Knockout. My employer makes heavy use of this JavaScript framework to create very interactive web pages that responds to user events. Even better the tutorial is written in knockout, so getting a feel for what knockout does for users happens as you learn how to use it in code.


Thought that your JavaScript application was bound to the browser? Think again. Node.js is a very powerful JavaScript engine for building server-side JavaScript application. The event model of JavaScript is appealing for many server-side apps.


Tired of manipulating DOM elements? Start drawing. The HTML5 Canvas element let's you go nuts with creativity. You've probably already seen what it can do on the Google home screen and plenty of games are now giving it a shot. If you need some graphical splash in your app, Canvas is a good tool to research.

Trello Tech Stack

Not convinced that all of this fancy new tech can be used to build real software? Heard of FogBugz, Fog Creek Software, or Joel Spolsky? If you haven't go search, I'll wait.

No seriously go look them up if you don't, it is more important than this article.

Now that you know I'm about to make an argument from authority. Check out the link above on the Tech Stack for Fog Creek's new product Trello. This is a massively scalable freemium web app, built on Backbone and Node.js. It is expected to be a substantial part of Fog Creek's business in the future.


Whether you are just getting started developing in JavaScript or are a mature web developer looking to expand your reach, there are plenty of tutorials and tools still to be found. Make sure to check out some of the links above and happy coding.