Before I learned about the use of an ORM (Object-Relational Mapping), I handled all interactions with the database through either stored procedures or building a query and setting up parameters. It was not the most exciting part of development. When I was introduced to NHibernate, an ORM for .net based on Hibernate, an ORM for java, I jumped right in and started bringing it into my projects.
There are hundreds of different ORMs. The goal of most ORMs is to abstract interactions with the database, support multiple databases, and handle mapping. Some implementations also extend the base database functionality by adding caching, lazy loading, and bulk operations, to name a few. The most common denominator of these ORMs is that they generate SQL statements for you. Some do this through their own query language which could be string-based (HQL), or through methods that build the request internally.
In the past few years, I have completely moved away from using ORMs in my personal projects and when I have to work with ORMs, I am very cautious. I know SQL well enough to know what good SQL looks like. I’m also always having to work extra hard to ensure that the generated SQL performs well. ORMs can generate beautiful SQL, but it can also generate statements that are overly verbose and slow.
I’m not here to say that ORMs are evil and you must always stay away from them. I’m here to show the issues that using an ORM can bring to light. I want to start with the abstraction most ORMs provide. Abstraction is an amazing thing. ORMs use abstraction to provide a common API that can speak to different database flavors. The abstraction is what excited me at first. I thought being able to swap out the database with minimal changes to the code was amazing. I’ve used the ability to swap out a database, my use-case being that I wanted the ability to do unit testing against an in-memory database. It was pretty great, at least that was my opinion at the time.
Abstraction is not a bad thing. It can speed up development by dealing with most of the inner workings of database interactions. Where abstraction becomes a problem is that it is generating SQL for you. It is true that this opens the door for developers that are not comfortable with SQL, it forces developers to learn the ORM. Each ORM has a learning curve and each has its own API to interact with the database. This can cause developers to focus on the ORM, not the SQL it is generating. To be good at an ORM, I believe you not only need to understand the SQL it is producing, but you also must know how to use the API to get it to build the SQL you need it to produce. Most ORMs do allow you to write your own SQL, but that is typically not recommended, especially for the more opinionated ORM frameworks. There have been too many times that I have struggled to get ORMs to build SQL the way I need it. I typically find myself falling back to straight SQL for complex queries. ORMs are usually great at creating simple queries but when you need something more advanced, it can be tough to get what you need out of it. End of the day, my issue with the abstraction that ORMs provide is that they hide SQL. This can become a crutch for some people as they put off learning SQL and instead learn the ORM. If you need to switch to another language or system that uses a different ORM, you now need to learn the new ORM.
In my experience, to properly use most ORMs, you have to adapt your system to use it, which can become very invasive, requiring you to have to follow specific patterns to even use it. Some require you to use their own query language, others require you to build your objects with metadata. which is used to handle mappings. These are not bad things, but the more code you have to tailor to the ORM, the harder it is to replace it if you need to. Of course, you can abstract an ORM by isolating it and putting as much as you can behind interfaces but in my opinion, external dependencies such as a database should almost always be abstracted, especially if you want to bring in unit testing that does not actually hit the database.
I am a fan of almost anything that simplifies and speeds up development. Yes, ORMs do exactly that, but sometimes the way they do it can cause pain in the long run. I believe that anyone who uses an ORM should understand SQL well enough to understand the SQL that is generated. I also want to caution when using more advanced ORM features such as lazy-loading. These features can provide a lot of value but they can also cause unintended side-effects. I’ll never forget a bug that took too much time to track down. I was handling an object returned from NHibernate that had quite a few other table dependencies that were set up for lazy-loading. You could force the loading of the data at the time of the queries execution or you can let the ORM query the extra data when you need to use it. An example could be a User table. A user might have one or more roles. You would get a user object from the ORM and when you access that objects roles property, the ORM will execute an additional query to load that uses roles. This can be an awesome feature that can provide huge performance boosts. That said, it can also cause issues if your lazy-loading loads other lazy-loaded data. My bug was just that. Because of all of the lazy-loading, the state of my object and its properties were hard to debug. My issue ended up being that the state of the object didn’t always reflect the state of the database, especially as other processes were interacting with the same underlying data, changing the state of my dependencies as I accessed the lazy-loaded properties.
I’m taking my stab at creating an ORM for Typescript that is extremely lite and using its helpers is completely optional. I would say I’m about 60% done and I hope to finish it by October. I’m naming it nORM which stands for no ORM. when I’m ready, I’ll publish it to GitHub and create an NPM package.
Latest comments (8)
ORM works for simple examples, but not for more complex ones. I am the creator of a new DBMS called JayaDB that fully resolves the object-relational impedance mismatch problem. It is based on a remarkable paradigm shift. As it turns out "object" structures can automatically be generated for any query without requiring any ORM tools. In fact, you don't even need to define classes. You just define one "high level model". All past approaches to ORM and pure object-oriented databases were based on flawed and limiting assumptions. JayaDB is based on the remarkable insight that object structures are equivalent to "compact" response structures that represent a solution to a query. In JayaDB you focus on writing a query according to functional requirements. Then call the method query.displayStructure() which will display the object oriented structure for you. The structure can be accessed by "getter" methods just as if a class were defined.
JayaDB has several other innovations that reduce the time to code very substantially (20+ times faster). It is also a unifying database that subsumes relational, graph, and object-oriented dbs.
There is no website as of yet, but a whitepaper is available upon request at doi.org/10.5281/zenodo.4041283.
I would love to hear more about this. What language is this being built for.
Currently it's a java implementation, but eventually will provide other language bindings. There is a paper you can request. I wouldn't spend too much time on writing an ORM in any language. Once you see the JayaDB implementation you would be better off building something on top of that.
I didn't read all the way through before posting this comment - I only got to the part where you wrote about the problem of having to fix the SQL generated by the ORM - and since you've mentioned .Net (which is the world I've been living in the past decade or so) - I've stopped and searched your post for one word that I couldn't find in it - that word is Dapper.
If you're not familiar with Dapper yet - this is a micro-orm which doesn't generate the SQL for you - but does abstract away a lot of the plumbing code you have to write with your basic IDbCommand interface - and it maps the result set you get from the database to your .Net types (which is, BTW, what defines an ORM - Object-Relational-Mapper).
I strongly recommend giving it a go - the learning curve is very easy (At least for simple uses) and it's fast and lean.
good arricle. totally agree with your point of view. I myself have moved away from rdbms/sql about a year ago and started using mongodb and I'm having a blast so far. don't miss sql at all.
Hey Tyler great write-up!
I mostly agree with your sentiment, that relational databases were created and optimized for a data first approach and SQL was made specifically to interact with it. Whereas ORMs attempt to mold it into a more familiar object oriented approach which can be finicky sometimes.
I think that's where NoSQL came in as a more lenient and object oriented approach to storing data.
That being said, I would recommend new developers to learn SQL and database normalization before using an ORM.
Thank you! I wish there were more of you.
Haha I'm still a beginner at SQL but thanks!
I wrote an article as well about designing a database from a data first perspective and I can understand why people would rather do it a more familiar way with ORM.