Object Relational Mapping

1 What is Object Relational Mapping?

Object Relational Mapping (ORM) is a programming paradigm that bridges the gap between object-oriented programming languages and relational databases. It transforms data between compatible systems, allowing developers to handle database transactions using object-oriented techniques.

2 How does Object Relational Mapping work?

2.1 Basic principle of ORM

Object Relational Mapping (ORM) is basically a technique that makes it possible to manipulate data between a relational database and an object-oriented programming system. The database tables are represented as classes, while the rows in these tables are represented as instances of the respective class and the columns as properties (attributes) of these instances.

2.2 Functionality

Object entity: With ORM, each object is assigned directly to a database entity (in most cases a table).
Database abstraction: ORM systems offer a level of abstraction via SQL. Instead of writing SQL statements, you execute CRUD operations (Create, Read, Update, Delete) using object-oriented techniques.
Synchronization: ORM systems automatically synchronize the database status with the current status of the object in the application and vice versa.
Queries: With ORM, you can execute complex queries using your usual programming language without writing direct SQL code.

2.3 An illustrative ORM example

Let’s look at a simple example: a book system with a database that stores books and authors.

In a relational database, we might have two tables: Bücher and Autoren.

Bücher-Table:
- BookID (primary key)
- Book name
- AuthorID (foreign key)
Autoren-Table:
- AuthorID (primary key)
- AuthorName

With an ORM system, we could have two classes in our application, Buch and Autor.

Object Relational Mapping – ORM code – Smart Database Handling

In an object-oriented programming language, this could look like this:

class Author:
def __init__(self, id, name):
self.id = id
self.name = name

class book:
def __init__(self, id, name, author):
self.id = id
self.name = name
self.author = author # This represents a relationship to an author object

3. why use ORM?

Object Relational Mapping, often simply referred to as ORM, serves as a bridge between the object-oriented world of application programming and the relational world of databases. This is a technique that allows database operations to be represented in the form of objects and methods instead of using direct SQL code.

Efficiency: It simplifies database access for developers.
Maintainability: The code becomes clearer and easier to maintain.
Database independence: The same ORM code can work with different databases.

Why ORM? Advantages of Object Relational Mapping

Detailed mode of operation

Object-entity mapping: When using ORM, each entity in the database – usually a table – corresponds to a class in the application. Each row within this table corresponds to an instance of this class, and each column corresponds to an attribute of this instance.
Database abstraction: ORM enables abstraction from the underlying database and the SQL used. Instead of writing SQL queries directly, operations are performed on the objects and the ORM system takes care of converting these operations into SQL commands.
Automatic synchronization: ORM systems track the status of objects. Changes to an object are recognized and can be automatically transferred to the database.
Linguistic consistency: Queries are written in the same programming language as the rest of the application. The need to constantly switch between SQL and the main programming language is eliminated.

Application example

Let’s assume we have a web application for managing books in a library. In a relational database, we have the tables Bücher and Autoren.

With an ORM tool, we could create a corresponding class in our application for each of these tables. When a new book is added to the library, we would simply create a new book object and save it. The ORM tool then takes care of converting this book object into a new data record in the Bücher table.

Example in Python with the ORM tool SQLAlchemy:

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Author(Base):
__tablename__ = ‘authors’

id = Column(Integer, primary_key=True)
name = Column(String)

books = relationship(“book”, back_populates=”author”)

class Book(Base):
__tablename__ = ‘books’

id = Column(Integer, primary_key=True)
title = Column(String)
author_id = Column(Integer, ForeignKey(‘authors.id’))

author = relationship(“Author”, back_populates=”books”)

# Create database and set up session
engine = create_engine(‘sqlite:///library.db’)
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

# Add new author and book
jk_rowling = Author(name=”J.K. Rowling”)
session.add(jk_rowling)

harry_potter = book(title=”Harry Potter and the Philosopher’s Stone”, author=jk_rowling)
session.add(harry_potter)

session.commit()

In this example, we have two classes, Autor and Buch, and use them to store information in the database without writing direct SQL code. ORM thus enables an intuitive and object-oriented approach to database interaction that offers several advantages:

Seamless integration: By working with classes and objects that correspond directly to tables in the database, developers can design their application logic in a way that is more natural and consistent with the rest of their application.
Increased productivity: Developers no longer have to spend time writing and optimizing complex SQL queries. Instead, they can concentrate on the business logic and take advantage of the benefits of object-oriented programming.
Error prevention: Since ORM tools often ensure that the queries created are correct, this reduces the risk of database errors that could result from improperly written SQL.
Flexibility: If required, developers can still work directly at SQL level. This means that they are not completely restricted and can make specific adjustments or optimizations if the situation requires it.
Consistency: By using an ORM system, developers can ensure that database operations are performed in a uniform and consistent manner throughout the project. This also facilitates collaboration in larger teams, as everyone uses the same ORM methodology.

Implementing ORM in projects therefore not only simplifies database interaction, but also improves code quality and maintainability, while at the same time reducing development time. It is an approach that is well suited to both small and large projects and offers developers considerable advantages in terms of efficiency and flexibility.

4. known ORM frameworks

4.1 Hibernate (Java)

One of the best-known ORM frameworks, offers a flexible and powerful abstraction for Java applications.

4.2 Django ORM (Python)

An integral part of the Django web framework, it allows developers to define data models as Python classes.

4.3 Entity Framework (C#)

An ORM for .NET applications that offers closer integration with Microsoft products.

ORM architecture – connection between object-oriented application and relational database

5 Advantages and disadvantages of ORM

5.1 Advantages

Productivity increase: Reduction of boilerplate code.
Maintainability: A more consistent code base.
Security: Avoidance of SQL injection through parameterization.

5.2 Disadvantages

Performance: ORM can be slower than raw SQL in some cases.
Complexity: Some frameworks have a steep learning curve.
Abstraction: Excessive abstraction can lead to a lack of understanding of the underlying database operations.

6. best practices in the use of ORM

5.1 Lazy loading: Loading data only when it is needed.

5.2 Caching: Use of cache mechanisms to minimize repeated database accesses.

5.3 Optimized SQL code: Even if ORM simplifies the handling of SQL, it is important to monitor and optimize the generated SQL code.

7. summary

Object Relational Mapping is a powerful tool that allows developers to focus on the business logic and worry less about the database layer. However, it is important to understand the benefits and limitations of ORM and follow best practices to achieve the best results.