The Dopefly Tech Blog

I wrote my own ORM

posted under category: General on October 16, 2021 by Nathan

A blog series in which I confess to accidentally having written my own poor version of a solved problem

I joined a new project at work. OK, joined is a polite word. A product was thrust into my lap. It has great documentation and lots of clean code written --maybe generated? Nevertheless the generator was missing and so were all the previous developers. One thing it had in spades was a strong MVC N-Tier Architecture. This made it really easy to find things, change things, and understand how the system worked.

By the way - if you do this for your application, you’re doing this for the next dev that maintains your application - and we thank you!

As I maintained this application for a while, I began to notice similarities in parts of the application that really were redundant. Specifically the data access layer. It was split between data access objects (DAOs) and data gateways (DGs). While the DGs had a lot of odds and ends that would return various recordsets, the DAOs had the same system over and over. CRUD. Load a single record and populate a single object. Read a single record and perform an insert or update. Delete a single record from the database.

The only things different were the names of tables and the names of the columns. There were a couple one-off tables without a single PKID column, but those weren’t the meat of the system.

I began to literally sketch out some potential solutions. The end result looked a little bit like this:

I began playing with constructing the SQL statements for each table based on component metadata. Properties in my components would probably need some custom metadata, but that both helps get this job done, and self-document the system a little better. Did I mention I was using ColdFusion for this? It makes things so simple. Watch.

The user class starts off looking like this

component {
  property name="id";
  property name="name";
  property name="role";
}

Thanks to ColdFusion’s custom metadata system, I can throw anything I want on there, then pull it out when I’m building my DAO queries.

component table="user" {
  property name="id" pk="true" required="true" sequence="seq_user_id;
  property name="name" type="string" required="true";
  property name="role" type="string";
  property name="someDynamicProperty" persist="false";
}

So on one end, I used this to build my CRUD queries, then on the other side, I used the metadata to map the recordsets back into the models. It was actually pretty simple, once it all worked.

I tried it out for a few new tables as part of a new feature. That’s how you add your innovations and entertainment, by the way – you make the fun stuff a “critical” part of the less-fun stuff. Once that worked, I spread it across the rest of the system. In one day I reduced the codebase by 3,000 lines!

I took it a little further by auto-generating some basic list functions, like the neat little listByCriteria where you send in an object from the table with the properties you want to find.

var criteria = new User();
criteria.setRole("Admin");
var admins = dgo.listByCriteria(criteria);

What did I learn?

It’s a lot of work up front to generate your own queries, but a lot less work in the long run when you know you’re getting the most optimized experience you can. Sure the ORM here was simplistic, but so were the needs of the application.

When you make something that’s like a framework, but it stays as part of a single system, it tends to integrate tighter than you expect. This ORM became an integral part of the application it grew from. The downsides with that are that it would have been very difficult to replace it with a publicly available ORM, and it became harder and harder to reuse it in another system. In this ORM’s case, it never grew out of this application.

Of course, now that other ORMs exist, I don’t think that I would do this again. However… I have another one coming up that would prove me wrong. Stay tuned.