Dream CRUD ;)

For the last two weeks or so, I have playing with the MindTouch Dream framework (.NET REST) and the new Subsonic dynamic query generator. Initially the idea was to create one or two quick and dirty APIs that allow me to perform basic CRUD operations on resources that I will be storing in a database. I did write these one or two APIs, and pretty soon I realized that this access pattern was fairly powerful. It does not take full advantage of all that REST has to offer, for example it only allows XML formatted payloads, but if it is used correctly I think there is a lot I could gain from this access pattern. So I decided to broaden the scope a bit and create some base functionality that would allow me to leverage this access pattern in a more generic fashion.

Essentially, I've created a way to selectively expose some of my database resources via REST. The whole idea revolves around using Dream's performant and easy to use XDoc class as a DTO wrapper around XML resources. Then I use Subsonic to generate clean, dynamic queries that will execute against any of Subsonic's supported dataproviders; a list that includes MySql, MS Sql, and Oracle.

Here's an example CRUD method, which retrieves a row of data from a table by a specified key name/value pair.

protected static DreamMessage GetTableAsMessageByKey(
string rootNodeTag,
string tableName,
string keyColumn,
object keyValue)
DreamMessage message;
XDoc doc;

if (rootNodeTag == null) throw new ArgumentNullException("rootNodeTag");
if (tableName == null) throw new ArgumentNullException("tableName");
if (keyColumn == null) throw new ArgumentNullException("keyColumn");
if (keyValue == null) throw new ArgumentNullException("keyValue");

doc = new Select().From(tableName).Where(keyColumn).IsEqualTo(keyValue)
.ExecuteXDoc(keyColumn, rootNodeTag, null);

if ((doc == null) || (doc.Count() < 1))
throw new NotFoundException("{3ED0B0F0-2C92-49e1-B710-1CC30CC2F769}",
new[] { rootNodeTag, keyColumn, keyValue });

message = DreamMessage.Ok(doc);
catch (Exception ex)
message = HandleException("{4FCDA723-C0E6-4316-8F0F-3A9D20484C08}",
string.Format("GetTableMessageById(rootNodeTag={0}, tableName={1}, " +
"keyColumn={2}, keyValue={3})", rootNodeTag ?? "", tableName ?? "",
keyColumn ?? "", keyValue ?? ""), ex);

return message;

The SubSonic Select() query statement I use ends with ExecuteXDoc() which is a .NET 3.5 extension that I added to perform an IDataReader -> XDoc mapping.

I've also added some of pluming that's specific to my needs. The NotFoundException() will cause a DreamMessage with a 404 (not found) HTTP status code to be created and return to the client; the title and message of the response will be localized to match the client's language specification. The HandleException() method takes care of DreamMessage creation, and it writes a log entry using the log4net logger.

Here's an example GET method declaration that uses this generic mapping pattern.

[DreamFeature("GET:{lang}/modules/{id}", "Get modules.")]
[DreamFeatureParam("{lang}", "string", "The requestors preferred language")]
[DreamFeatureParam("{id}", "int", "The id of the module to get")]
public Yield GetModuleById(
DreamContext context,
DreamMessage request,
Result<DreamMessage> response)
string lang = context.GetParam<string>("lang");
int id = context.GetParam<int>("id");

response.Return(GetTableAsMessageByKey("Module", "core_module", "id", id));
catch (Exception ex)
string.Format("GetModuleById(lang={0}, moduleId={1}",
lang, id), ex));

yield break;

And a response generated from a GET to '/en-US/modules/2'

<Module xml:id="2">

This access pattern provides several benefits.
  • Performance - Because I am not using any intermediate DTO classes or business objects, I don't have to go through the entire "deserialize request -> retrieve data -> map data to object -> serialize object as response" lifecycle. this solution is very performant.
  • Database Agnostic - SubSonic allows me to talk to just about any database I would be interested in supporting, and the query format will not change based on the database I am using.
  • Customizable - I'm in control of any special processing I want to do. I'm already returning application-specific localized messages and doing custom logging on the server-side. In the future I want to be able to perform authorization based restrictions on select operations. This solution allows me to do all of that and more.
  • Easy - with a full set of CRUD operations already in place, I'm able to crank out access to my database-centric resources in minutes.
  • Consistent - all of the resources I expose using this pattern will be formatted in the same, predictable format, which makes it easier for clients to consume. For example, I'm using the 'xml:id' attribute to identify the object's key field. Because I'm also enforcing consistency in my database schema (naming conventions, common set of required fields, etc) a certain level of consistency trickles back to the client by default.
Obviously there are several limitations. This implementation forces you to expose your database schema as your payload, which is not desirable in many situations. Additionally, the ability to perform complex validation or business logic spanning more than one resource type is difficult.

But even with these limitations in mind, I know this pattern is going to help me ease the pain of implementing the 20% or more CRUD-only operations I run into every day when creating enterprise-level applications.