Object-Relational Mapping (ORM) Explained with Examples

Let’s explore object-relational mapping (ORM), how it works, its advantages and drawbacks, plus the most popular ORM tools. We will also compare ORM with its alternatives and explain the ideal ORM application.

What is ORM?

ORM, or object-relational mapping, is a programming method used to bridge the gap between object-oriented programming languages (like Python, Java, Ruby, C++, JavaScript, C#, and many more) and relational databases (like PostgreSQL, MySQL, or SQLite). Simply put, ORM allows developers to manipulate data using Java or Python instead of SQL statements.

To get to the bottom of this, let's clarify what each word in the ORM name means.

In object-oriented programming, an object represents a real-world entity or concept modeled within the program. For example, in a hotel booking application, objects might include a specific hotel, a certain room, a particular guest, a unique reservation, an exact bank transaction, and more. An object encapsulates associated data (attributes) and methods (behavior) that are operations applicable to the object. 

A class is a blueprint for creating objects with similar characteristics and behaviors. Programmers define a class with its inherent attributes and methods and then use it to create individual objects. For instance, a HotelBooking class dictates the attributes and behaviors for all booking objects in a hotel chain application.

Here is an example of a Python object that represents an individual, non-paid booking in the Overlook Hotel belonging to the hotel chain.

Like all objects in the HotelBooking class, the Overlook_booking object has the following attributes: booking_id, hotel_name, location, address, guests, check_in_date, check_out_date, room_type, and amenities. Each attribute has a value specific to a particular booking.

What can you do with this object? Get booking details, add or delete a guest, change amenities, calculate stay duration, get total cost, cancel booking, and more. These real-life actions are reflected by methods in the programming language.

Now, let’s go back to the meaning of the other parts of an ORM abbreviation.

Relational refers to a database that organizes data in tables with rows and columns. This type of database is widely used for business applications, and for good reasons: It is easy to use, ACID compliant (suitable for typical business transactions), flexible enough (allows manipulation of data without changing the table structure), and highly standardized. The problem is that the DB doesn’t know anything about an object; it just faithfully keeps structured data in order in different tables.

To learn more about databases and database management systems, read the comprehensive DBMS comparison in our dedicated article. 

For the system to operate a single object written in Python, developers must incorporate a bunch of raw SQL queries in the code. For example, you must join data from several tables to retrieve all attributes of the object related to the Jack Torrance family room reservation.

Writing SQL queries demands that a programmer switch from an object-oriented to a table-oriented paradigm for any operation on data. This is inconvenient and can lead to error-prone code. That’s what mapping can help with.

Mapping links the objects in code with the data in the database and automates SQL query generation. It allows engineers to work with familiar programming languages without thinking about how data is organized.

Imagine we need to change a reservation since the Overlook Hotel’s guest, Jack Torrance, decided he needed privacy and asked to book a separate room for his son and wife.

In Python with incorporated raw SQL statements, the code will look like this:

Here’s how the code would look if we use one of the popular ORM tools for Python, SQLAlchemy:

As you can see, the program operates with objects rather than tables. SQL queries are sent behind the scenes, making the code easier to write, read, and maintain.

How does ORM work?

The object-oriented and relational concepts are pretty different. While objects contain data and methods, relational databases can only save data. While objects are often composed of other objects, and classes inherit structures from other classes, relational models lack a built-in hierarchical mechanism. They flatten an object’s natural hierarchical structure into separate tables with atomic values (indivisible pieces of data) and unique rows. Maintaining the inheritance or nesting logic requires extra queries.

ORM resolves this contradiction by providing a mapping layer that automatically rebuilds complex object structures from flat tables, retrieving all data and adding methods. This allows developers to work with objects in their native hierarchical form.

ORMs use two main patterns and several strategies (that can be combined with one or both patterns) to map application objects to database structures.

Active record pattern

An active record pattern ties an application to a database schema. It equates tables with classes, rows with objects, and columns with attributes. Foreign keys (attributes that refer to primary keys or unique row IDs in another table)  represent relationships: They serve as an equivalent to references between objects in the code.

Each class provides the basic methods for CRUD (create, read, update, delete) operations, so the class is responsible for updating or deleting itself.

When an application changes an object, the database automatically updates tables corresponding to those changes. And vice versa, the changes in a database are reflected in code. In fact, there’s no additional layer between the logic and the database. However, an active record still abstracts mapping and generates SQL, which are all hallmark features of ORM.

An active record pattern is suitable for simple applications with tight linking between objects and databases.

Datta mapper pattern

A data mapper pattern separates classes and objects from the database structure and doesn’t necessarily follow the rule “one object = one row in the table.” An object can correspond to several tables, while programmers specify how objects are serialized, deserialized, and connected to the database. All database operations are detached from objects. You can work with collections of tables and update them simultaneously. All this gives programmers more control and flexibility but requires more effort. 

Data mappers excel in handling complex queries and are well-suited for scalable, enterprise-level systems.

Additional ORM strategies

Now, let’s break down some of the most popular strategies that can be combined with one or both main patterns and enhance them.

An identity map is a cache mechanism that ensures that each database row is represented by a single object in memory at any time during a session or transaction. When object data is fetched from the database, it is stored in an in-memory map (identity map). Any subsequent query for the same data returns the same object from the map, avoiding the creation of duplicate objects. An identity map helps maintain consistency and improves performance by minimizing interactions with a database. Identity maps can be used with both active record and data mapper patterns.

A table data gateway uses a single object (gateway) as an interface with a particular table in a database. It contains all SQL queries to manipulate data in this table and is the sole point of access for all classes. A table data gateway complements a data mapper pattern to improve the system’s scalability and maintainability, but it can also be applied separately for simpler use cases.

Lazy loading strategy delays the loading of related data until it is explicitly accessed. For example, related guests are not loaded when fetching a booking until their attributes are requested. This technique minimizes initial database queries and memory usage and is supported by most ORM tools, no matter what the pattern.

What are ORM tools?

Every object-oriented programming language offers ORM tools for handling object-relational mapping tasks. These tools provide the sets of APIs that allow developers to interact with databases in their programming language of choice.

Let’s identify some ORM tools.

• Java ORM: Hibernate, EclipseLink, and OpenJPA

• Python ORM: SQLAlchemy, Django ORM, Peewee, and Pony ORM

• ORM for .NET and C#: Entity Framework Core, Dapper, and NHibernate

• Node.js and TypeScript ORM: Sequelize, Prisma, Objections.js

• Ruby: ActiveRecord

• PHP: Eloquent (included in Laravel) and Doctrine.

Read our comprehensive ORM tools overview to learn more about object-relational mapping software and its advantages and drawbacks.

Object-relational mapping vs SQL vs Query builders

While the use of ORM in business applications is now the industry standard, there are alternatives— you can still rely on pure SQL or apply query builders.

Choosing between these three options depends on factors such as the complexity of the data, security requirements, and the overall project size. Let’s explore each approach.

ORM: ideal for business apps

Using ORM obviously saves developers time when writing and maintaining code. However, an even more important advantage is that applications built with an ORM tool can easily switch between database management systems (e.g., MySQL to PostgreSQL). Instead of adjusting the whole app to a new DB, you’ll only have to change an ORM layer. Most ORM frameworks (like Django ORM, Hibernate, SQLAlchemy, and more) are DB-agnostic, i.e., natively support multiple DBMS. Hence, they only require updates to the database connection configurations (e.g., driver, host, port).

Another good thing about ORM tools is that they automatically sanitize and parameterize SQL queries, reducing the risk of vulnerabilities like SQL injection (one of the most common hacking practices). 

On the flip side, there are some performance problems. ORM tools can slow down complex queries. For instance, an ORM layer may execute multiple unnecessary database calls when fetching related sub-objects of a primary object. The phenomenon (known as the "N+1 problem") occurs if ORM is not carefully optimized.

Since ORM focuses on the object-oriented paradigm, it’s ideal for managing straightforward, real-world entities and handling commercial operations.  It works well for applications like content management systems (CMS), eCommerce platforms, online travel agencies, and streaming services, where object structures and operations are relatively simple.

Raw SQL: suitable for complex queries

Writing SQL queries directly allows developers to use specific database features that an ORM layer might not expose. Besides, complex queries and joins are more efficient when written directly, as a developer can precisely control data processing.

On the other hand, not all developers are well-versed in SQL, and writing complex SQL queries can require significant database knowledge. But this is a minor issue that can be resolved, as SQL is a rather easy language to learn. What's worse is a higher risk of human errors compared to ORM. When changes are needed, they may involve rewriting repetitive SQL across different parts of the codebase, which increases the risk of bugs.

You also must consider higher security risks since SQL injection is more probable without ORM.

In general, SQL is a better choice for applications that handle massive data loads, require complex data transformations, or demand detailed performance tuning, such as financial software or supply chain management (SCM) systems that oversee and track shipments. 

Query builders: a middle ground for complex business logic

A query builder is a tool for constructing SQL queries programmatically, offering templates to interact with the database but still requiring knowledge of SQL.

Unlike  ORM, which maps tables to objects and vice versa, a query builder focuses solely on query creation. Some popular query builders include Laravel query builder (part of the Laravel PHP framework), Knex.js for Node.js, SQLAlchemy Core for Python, and Hibernate Criteria for Java.

The main advantage of these tools is greater flexibility compared to ORM for writing complex queries while still automating some repetitive SQL code.

However, the desire to please both sides leads to inherent weaknesses in every middle-ground decision: Abstraction level and database control are both limited.

Query builders are well-suited for applications with complex queries that exceed the capabilities of typical ORM but don’t require full control of raw SQL. For example, an application that generates custom reports might use a query builder to handle the variable query logic effectively. Another use case can be a human resource management system (HRMS).

When deciding between an ORM, raw SQL, or a query builder, consider whether the automation and ease of use of an ORM meet your requirements, or if the control of raw SQL or the flexibility of a query builder is a better fit for your goals. Note that many ORM tools allow writing plain SQL queries, so you can combine both approaches when needed.

Ultimately, the right choice depends on your project's specific demands and the value each tool adds to your development process.