ORM vs. SQL: When to use each

How does SQL work?

Relational databases define the database with text using Structured Query Language (SQL). With SQL, the programmer uses CREATE TABLE to define the entity, then SELECT to find data, along with UPDATE or DELETE. These commands roughly correspond with create, read, update and delete (CRUD) for applications.

The following example of SQL code creates a table named 'Employees' and performs CRUD functions on that table.

#A Sample Table
CREATE TABLE Employees (
    EmployeeID int NOT NULL PRIMARY KEY,
    LastName varchar2(50),
    FirstName varchar2(50),
    Address varchar2(255),
    City varchar2(255),
    Zip varchar2(10)
);

#Find an employee number
SELECT Lastname, FirstName 
FROM Employees 
WHERE EmployeeID = 387;


#Name change; update
UPDATE Employees
SET Lastname = "Smith"
WHERE EmployeeID = 387;


#They leave the company
DELETE FROM Employees 
WHERE EmployeeID=387;

This code example was sourced from the author's GitHub.

SQL adds another layer to code, because programmers must create a string with the entire SELECT statement, then add in the variables for the specific actions they'd like to perform. Programmers can circumvent this challenge by tying the objects -- like employees -- to the entities -- the employees table -- explicitly using object-relational mapping (ORM).

How does ORM work?

To tie objects to the example table using ORM, first define a simple object that has the same members as the database table has entities. Both have FirstName, LastName and EmployeeID. Then, add a configuration file -- likely XML -- that maps the object onto a table, shows which member variables connect to which table attributes and says where the database is -- swapping it out for production builds. Finally, there might be a bit of magical syntax -- annotations in Java -- which gives special instructions at compile time. These don't really matter to Java but might matter to the database. For example, the framework might need to know to auto-generate EmployeeID when new records are created.

This makes creating new employees in Java easy.

Employee emp = new employee("Jackson","Sarah", "123 Easy St", "Madison", "WI", "53558");

It makes updating them easy well.

emp.LastName = "Brown";

Of course, it is not quite that easy, because most databases exist inside of a transaction. Complex transactions require relational integrity. Employees might, for example, have a pay grade or job code that needs to exist in another table. A transaction can wrap several operations and check to see if the entire change succeeds or fails, giving the programmer the chance to handle exceptions.

Some ORM mechanisms require the programmer to build the transaction themselves through commands, while others can map an entire table into memory, track what changes and automatically save back to the database in the background as needed. This can save database and network trips, especially for desktop applications.

Fundamentally, ORMs make dealing with database objects feel like writing code in Java, C# or Python -- as opposed to adding another text layer of raw SQL to retrieve and change data. Here's a sample of what it might look like to retrieve a record that already exists from an ORM. In this case, it's a Java Hibernate request that retrieves an employee with the "EmployeeID" field equal to 3.

Criteria cr = session.createCriteria(Employee); 
cr.add(Restrictions.eq("EmployeeID", 3)); 
List employee = cr.list();

Comparing ORM vs. SQL

The benefit of object relational mapping is that it abstracts away the database; the programmer doesn't have to fiddle with the details. Once the basics of the application are roughed in, maintenance programming becomes much easier. Depending on the API, the ORM can lazy-load data as needed or it can pre-fetch from the database.

The problem with object relational mapping, as opposed to SQL, is that it abstracts away the database. That means the programmer doesn't have exact control over what is happening. An ORM that pre-fetches might fetch too much data -- especially for a desktop application -- and cause memory problems or delays.

Programming pure SQL is arguably more powerful; at the very least, it is "closer to the metal." Debugging queries that run slow, correcting performance issues with the database and fixing buggy code are easier with SQL, because the programmer can trace any specific call in the database log back to a specific line of SQL. In addition, programmers can take the code out directly and run it in a workbench. On the other hand, that debugging, tuning and query writing is an entirely different skill set the programmer might not have.

Some ORMs offer a hybrid, a query language of their own that may be close -- or even identical to -- SQL. This is mostly helpful for complex joins.

How to choose between ORM and SQL

Simple reports that list a table -- such as the delay-load example -- are easier and faster to create in SQL. Likewise, small CRUD applications can be easier to get started with object relational mapping. These can certainly be scaled to multi-user, but they are more likely to work, for say, a few hundred customer service representatives at web scale. At web scale, REST APIs tend to make each request separate, thus eliminating the possible benefits of prefetch and auto-calculated transaction handling, as each request is a transaction.

ORMs can be easier to get started with for junior programmers. Once they are in place, simple maintenance with an ORM becomes much easier, but there is some "wiring" that must happen when connecting a new object to its related table.

Doing this wiring in the first place -- installing the ORM, connecting the tables, figuring out how to connect to the database -- is likely better done by a programmer experienced with the idiosyncrasies of the particular programming environment, as databases and passwords may need to be stored in a secrets file and which database is called may need to change based on the level of the test environment.

Getting started from nothing is probably easier in SQL, though the programmer should study common data access patterns in SQL. With SQL, it's easier to create a big ball of mud that works and is hard to maintain, while with ORM it's easier to reach a conceptual limit and be unable to proceed or have unintended consequences.

Finally, nearly every programming language has SQL support through some sort of database connector. Insert SQL, and you'll get an array back with a single row or an object that contains many arrays for multiple rows. The programmer will have to convert that into an object or populate the screen. There are essentially no ORMs that have broad cross-language platform support, which means every programming language the team supports will likely need to have a different ORM, with languages on the same platform, such as .NET, as a possible exception.

When to use ORM and SQL together

Larger organizations often have a variety of teams with a variety of contexts; especially companies that grew through acquisition. Instead of forcing a single standard for all applications, it might make sense to come up with broad policies. The organization might continue support for databases in legacy software, use object relational mapping for internal and desktop software and SQL for microservices and APIs, along with a hybrid approach for complex queries.

Start small and experiment with both using proof of concepts with a careful eye to the future and complexity. In the end, the question is: What is working for the organization and what's the next small experiment to try?

Matt Heusser is managing director at Excelon Development, where he recruits, trains and conducts software testing and development. The initial lead organizer of the Great Lakes Software Excellence Conference and lead editor of "How to Reduce the Cost of Software Testing," Matt served a term on the board of directors for the Association for Software Testing.