What is Object-Relational Mapping (ORM)?

Introduction

In this article, we will examine the working principles and main components of Object-Relational Mapping ("ORM") tools, their pros and cons, as well as example libraries.

ORM

There are two ways to connect a Python application to a relational database: the low-level approach and the high-level approach. The low-level approach involves setting up and configuring a relational database management system on your local machine and writing actual SQL commands to perform operations on the database. The alternative is using an Object-Relational Mapper (ORM).

Object-Relational Mapping (ORM) is a approach that helps you work with databases using code instead of writing SQL queries. It automatically links database tables to program classes, simplifying data manipulation. This means developers do not need to manually write SQL queries to create, read, update, or delete data (CRUD operations). ORM makes this process simpler and more understandable, especially for those who work with object-oriented programming.

How does ORM work?

ORM acts as a "translator" between object-oriented code and relational databases. Its main task is to establish a connection between program classes and database tables. Each class corresponds to a table, and each class attribute corresponds to a column in that table.

When ORM is initialized, it automatically creates and maintains this connection using metadata. For instance, the ORM reads class descriptions and builds database queries based on them. Here’s how it works at an operational level:

1. Mapping objects and tables: Python classes (or classes in another object-oriented language) are mapped to database tables. For example, a User class with id and name attributes will be mapped to the users table, where id is the primary key and name is a column.
2. Working with data as objects: Instead of writing SQL queries directly, a developer works with program objects. For example, to create a new database record, you simply create an object of the corresponding class (like User) and call the save() method. ORM will convert this into an INSERT INTO SQL query.
3. Tracking changes: ORM can monitor object states in the code and automatically update the database when objects are modified. If an object’s field (e.g., name) is changed, ORM "understands" the change and generates an UPDATE SQL query to apply the corresponding updates to the database.
4. Generating SQL queries: ORM handles the task of generating SQL queries. For example, if you need to retrieve all User objects from the database, you might call User.query.all() (depending on the ORM), and the ORM will generate a SELECT * FROM users; query.
5. Handling complex queries and relationships: ORM supports complex relationships between tables, such as one-to-many or many-to-many. For instance, if each user has several articles (a one-to-many relationship), ORM allows you to easily retrieve all articles for a specific user by creating the corresponding SQL JOIN query.
6. Lazy loading: Some ORMs support "lazy loading," meaning related data is only loaded from the database when necessary. For example, if you have a User object and you need to fetch its related articles, they won’t be loaded until you explicitly request them. This helps optimize queries and avoid unnecessary database calls.
7. Data validation: ORM can also perform automatic data validation before saving it to the database, ensuring, for instance, that values don’t exceed allowed ranges or that a string is not empty.

All of this significantly simplifies database interactions, as ORM not only translates operations on objects into SQL, but also manages relationships between tables and ensures data integrity. This frees developers from having to manually write and optimize SQL queries, making the data handling process more flexible and reliable.

ORM vs SQL

Object-Relational Mapping (ORM) and Structured Query Language (SQL) are two completely different methods used to understand and manage databases.

The main difference between them is that ORM uses a much higher level of abstraction than SQL for modeling the database structure. As a result, ORM requires less coding, while SQL is more hands-on. However, this same difference also makes SQL allows developers to work directly with their database, whereas ORM creates a layer between the developer and the database, making it harder to see what’s happening at the database level.

ORM is a convenient alternative to SQL, but the decision of which to use often depends on programming experience and the nature of the data-related task at hand.

Issues with working directly with SQL

Using raw SQL to work with different databases can lead to the following challenges:

1. Difficulty transferring between databases: If a project uses one DBMS and later needs to switch to another, all SQL queries would need to be adapted to the new syntax, which can be time-consuming and resource-intensive.
2. Performance differences: Query optimization may differ across DBMSs, requiring knowledge of each database and performance testing.
3. Support for specific features: Some functions, such as handling spatial data or indexing, may only be supported by specific databases.

How ORM solves these problems

ORM tools automatically abstract away these differences. Here’s how they work:

1. Unified syntax: ORM allows you to write queries using methods and objects in Python (or another programming language). These queries look the same for all databases. For instance, to fetch all users in Python code with ORM, you might call User.query.all(), and ORM will generate the correct SQL query for the specific database.
2. Abstraction of differences: ORM translates your actions into standard SQL queries suited to the specific DBMS. For example, when using AutoField for a primary key, ORM decides whether to use SERIAL for PostgreSQL or AUTO_INCREMENT for MySQL.
3. Automatic schema creation: ORM automatically generates and updates database schemas based on the data models you define in code. This makes migrations and schema management more universal and independent of the database.

Benefits of ORM

Here are some key advantages of using ORM:

• Speeds up development for teams: ORM simplifies interactions with the database, making it part of the object-oriented model and reducing the time needed to write and debug SQL queries.
• Reduces development costs: By automatically generating SQL queries and abstracting data operations, developers can focus on application logic, reducing the likelihood of errors and lowering testing and bug-fixing costs.
• Handles the logic needed to interact with databases: ORM takes care of creating, reading, updating, and deleting data (CRUD operations) through convenient methods, eliminating the need to manually write SQL queries.
• Increases security: ORM helps prevent attacks like SQL injection by automatically escaping query parameters and securely handling data transmission in SQL queries.
• Simplifies migrations and ensures portability across DBMS: ORM makes it easy to switch between different databases (e.g., from PostgreSQL to MySQL) without significant changes to the code.
• Improves code readability and maintainability: Since database queries look like class or object methods, the code is more readable compared to using raw SQL, simplifying application maintenance.
• Integrates with object-oriented principles: ORM naturally fits into object-oriented programming languages, making it easier to connect objects in code with data in the database.

Drawbacks of ORM

• Learning to use ORM tools can take time: Mastering ORM syntax and features takes time, especially for developers who previously worked with raw SQL. Understanding how ORM generates SQL queries is also important for optimizing application performance.
• Not suitable for complex queries: ORMs are often inefficient when dealing with very complex SQL queries, such as advanced aggregate functions, analytical queries, or custom table joins. In such cases, writing SQL manually may be necessary.
• ORM is generally slower than SQL: Since ORM adds an additional layer of abstraction and wraps SQL queries in objects, it can lead to reduced performance, especially in high-load applications.

Popular Python ORM

Here are some popular Python ORM libraries that we will briefly review:

• SQLAlchemy
• Django ORM
• Tortoise ORM

SQLAlchemy

SQLAlchemy uses a two-layered internal architecture consisting of Core and ORM. The Core is intended for interacting with the database API and rendering SQL expressions, while the ORM is the abstraction layer providing an object model for your databases. This decoupled architecture means SQLAlchemy can theoretically use any number or variety of abstraction layers, though there is some performance overhead.

SQLAlchemy allows you to describe database schemas in two ways, so you can choose what fits your application best. You can use a declarative system where you create Table() objects and specify field names and types as arguments, or you can declare classes using a system similar to dataclasses.

Queries in SQLAlchemy use a syntax reminiscent of actual SQL queries, such as select(User).where(User.name == "Davis"). SQLAlchemy queries can also be rendered as raw SQL for verification, with any necessary adjustments for the specific SQL dialect being supported (e.g., PostgreSQL vs. MySQL). The expression-building tools can also be used independently to render SQL operators for use elsewhere, not just as part of the ORM.

Django ORM

Models for the database managed by Django follow a pattern similar to other Python ORMs. Tables are described with Python classes, and Django’s custom field types are used to describe fields and their behavior. This includes things like one-to-many or many-to-many relationships with other tables, as well as types commonly found in web applications like uploaded files. Custom field types can also be created by subclassing existing ones and using Django’s field class methods to alter their behavior.

Django’s command-line toolkit for working with sites includes powerful tools for managing a project’s data layer. The most useful of these automatically generate migration scripts for your data when you want to change your models and migrate the underlying data to use new models. Each set of changes is preserved as its own migration script, so all migrations for the database are tracked throughout the project’s lifecycle. This simplifies supporting data-driven applications where the schema may change over time.

Tortoise ORM

Creating models with Tortoise follows roughly the same scheme as other Python ORMs. You subclass the Model class and use field classes like IntField, ForeignKeyField, or ManyToManyField to define fields and their relationships.

Queries in Tortoise don’t stick as closely to SQL syntax as some other ORMs. For example, User.filter(rank="Admin") is used to express a SELECT/WHERE query. The .exclude() clause can be used to further refine results, for example, User.filter(rank="Admin").exclude(status="Disabled"). This approach provides a slightly more compact way of expressing common queries than the .select().where() approach used elsewhere.

Async is still relatively new in the Python ecosystem. To explain how to use Tortoise with async web frameworks, the documentation includes examples for FastAPI, Quart, Sanic, Starlette, aiohttp, and others. For those wanting to use type annotations (also relatively new for Python), the Pydantic plugin can generate Pydantic models from Tortoise models, although it supports only serialization, not deserialization. The external tool Aerich generates migration scripts and supports both migrating to newer and reverting to older versions of the schema.

Using ORM provides developers with powerful tools to abstract database interactions, significantly simplifying work with data through object-oriented models. ORM allows you to work with relational databases without the need to write complex SQL queries, which speeds up development and makes code more portable and maintainable. Thanks to ORM, developers can easily migrate between different DBMS and ensure application security by reducing the risk of SQL injection attacks.

Ultimately, the choice between using ORM or raw SQL depends on the project’s specifics, its scale, and performance requirements. ORM is well-suited for most common tasks, but for more complex and specialized queries, a hybrid approach combining ORM and raw SQL may be needed.

Let’s imagine that you have an application that needs to work with both relational and document-oriented databases. Using the standard approach, you would use a tool that is suitable for each database. You would need extensive knowledge of these tools. But what if I told you that you could use one tool to work with both types of data?

This tool is called Amsdal Glue.