Software Development – ORMs vs SQLReading Time: 3 minutes
Behind every mature software language, there’s an ORM. A few years ago, no one questioned the use of these industry standard libraries in software development. They help reduce your code complexity and hand off that functionality to a battle worn ORM. The programmers building these really know what they are doing with relational databases.
#1 SQL is Great but…
Now as someone who has used: MySQL, Postgres, Oracle, Snowflake and MSSQL. I love SQL! It is a fantastic base language to get any relational data you need from a particular data source. Each sub SQL language has their own interesting ways of managing security and data integrity. I once managed to turn a previously manual task that took 2 months to do into a single SQL query. That query also had no human errors and took 15 seconds to run. So don’t misunderstand my point of view when I say implementing large sets of basic SQL is inherently BORING. It can also make it harder to read when there is a particularly large query with no documentation.
#2 The Simplicity of ORMs
ORMs are a great way for a programmer to get a lot of backend work done very quickly. For prototyping an application or even an MVP. The speed of software development allows for initial business needs to be met in a timely manner. No one is going to worry about how an application is coded if it’s not already deployed. There are many small to medium business use cases where ORMs just outshine plain SQL because it can sometimes just feel like you’re reinventing the wheel.
Let us consider an example. An API with an endpoint of ‘/products’. In this example we are using a simple application running an ORM to get this information. Effectively, we have just abstracted a select statement with a data type transformer to make it easier to use. In this case it works well and when search functions come into play such as pagination and column searches ORM implementation can take a lot of the headaches away.
#3 The ORM Tax
Now I’m sure in quite a few business cases this would suffice but if we had multiple linked entities such as: category, linked products and delivery types. This data is transformed into a data type ready for manipulation and then has to be mapped appropriately to then be served to the client, this is the ORM tax. If your application was to come under any heavy traffic then the application nodes would need to scale or your response times would suffer.
Some ORMs do transactions you may not even consider, such as the doctrine standard of counting the rows for pagination. When you count 1 million+ rows on every API call alongside your query, the database comes under massive strain. Even when the application scales, all it does is make the problem worse. Adding more strain can even in some cases take down your database. Now there is an obvious solution, just use cursor based navigation but the point is that isn’t the default so knowing how the ORM works is a very important factor in whether you should decide to use it.
#4 The Hybrid Approach
The best rule is moderation in both for software development. Assess the business cases for your database transactions and maybe even run benchmarks if you’re not sure. If you are going to use an ORM then make sure that you and your team have a good understanding of its inner workings so that when issues arise you don’t have to trawl stackoverflow questions just to tell you something that’s already in the documentation.
In development we shouldn’t consider the quickest route to accomplishing something but the best route. Consider the long term implications on your applications. How many requests will you expect this end point to receive? Is your end point even that complicated? If not, why not go back to basics and just make an SQL request getting the specific information you need to then output the data. Using an ORM application does also have the advantage of cutting down potential code bloat which can make your application read better to other developers.
If you want to see more discussion on this then here’s a couple of Reddit threads:
I hope this has helped and as always if you have any additions or anything you feel I left out please leave a comment below.