We have many clients that have chosen to use Object-Relational Mapping, or ORM, to abstract the database and subsequent writing of SQL statements from engineers. We advise our clients to understand the impact of using ORMs and their impact on scalability. ORMs are notoriously bad at writing SQL with the most common violation being the use of select * from a table and then eliminating unnecessary data from the results set. Here, we will look at when it is appropriate to use and ORM and when it is not.
The purpose of ORM is to address the object-relational impedance mismatch between object oriented and relational databases. ORM frameworks abstract the developer from the complexities of serializing an object into a database. For an object-oriented programmer to use a relational database, they must understand and be able to code in the SQL of the database. However, writing raw SQL statements can be time consuming. Using an ORM frees the programmer from having to understand complex SQL statements. This improves development time, and subsequently, time to market.
ORMs map the object-oriented programs to an underlying relational data structure. They create a structured map to help engineers understand the underlying structure. The ORM uses the map to explain how objects relate to tables, to convert data between objects and tables, and generates the underlying SQL code. Once the ORM generates the SQL statement, the Engineer does not need to worry about the code.
When Should I use an ORM?
There are several reasons to use an ORM.
- Productivity – Since engineers do not need to spend time creating SQL statements, they can focus more on their code. This reduces the overall time to market by shortening development time.
- Better Designed Code – A well written ORM can force design patterns that use best practices for application design.
- Testing – Since the ORM generates the SQL code, once the data access code is tested, it doesn’t need to be tested again unless it is changed. Engineers can focus on business logic and their code.
If we look at the reasons for using an ORM, we see it helps with time to market by abstracting the database and SQL from engineers. This makes it a good fit for products that are new to the market. Teams can get their product to market quicker and see what gains traction.
When Do We Get Rid of an ORM?
There is a point where an ORM will become a hinderance. This occurs as the product scales and the number of customers grow. The poor SQL code written early by the ORM begins to degrade in performance. ORMs tend to create more code than an Engineer would writing SQL directly. This extra code slows performance.
ORMs do not generate the best SQL statements and typically use multiple anti-patterns like select * from table. This results in a large data set that then needs to be filtered to get the data needed. This extraneous work slows the system. To properly tune the SQL, the engineer must understand the code, the underlying data structure, and the best way to access the data. With this knowledge, the engineer is best suited to write the best SQL to access the data. Most of our high transaction clients have had to rip out their ORMs or aggressively shard their databases to offset the poor SQL.
Developers get lazy when using an ORM. They rely on the ORM schema generators to create migration scripts which will alter the schema structures to represent what they have implemented in the object within code. These migration scripts require version tables be maintained in the database so, the ORM framework knows which database update scripts to execute to get the database schemas in sync with the code. Mixing ORM and SQL can break this linking and it is important that this is accounted for as you migrate away from the ORM framework.
Selecting an ORM early in a product’s lifecycle makes sense for time to market. However, the ORM will require the team to consider Y-axis (service splits) and Z-axis (sharding) splits much earlier. It will also be more complicated to make those splits as the data is abstracted from the team. Once the product has hit a certain level of use, the team will need to begin removing the ORM. Planning for this eventuality early will reduce the cost of the transition.
The growth of the system will begin to slow time to market if the ORM is not removed. Changes to the application and testing become more complex. It will also be more difficult to troubleshoot problems in the system. Complex SQL will reduce performance of the database servers, typically the most expensive part of the system.
Using an ORM can help teams deliver new products quicker. However, the team must plan for the eventuality of when they need to remove the ORM and begin writing SQL directly. For high transaction systems, the ORM will begin to degrade performance. Understanding hen to make the switch is critical to maintaining scalability.
AKF has helped many clients address their scale issues. If you need help removing an ORM or with scale issues in general, give us a call. We can help.