Top three reasons why ORM (Object-Relational Mapping) is essential in software development,
Here are the top three reasons why ORM (Object-Relational Mapping) is essential in software development, explained with a simple example:
1. Abstraction of Database Operations
Example: Suppose you have a User
class in your application that needs to interact with a users
table in a relational database.
Without ORM:
# Without ORM
import sqlite3
# Connecting to SQLite database
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# Fetching users
cursor.execute('SELECT * FROM users')
users = cursor.fetchall()
# Working with fetched data
for user in users:
print(f"User: {user[1]} - Email: {user[2]}")
# Closing database connection
conn.close()
With ORM (using SQLAlchemy as an example):
# With ORM (using SQLAlchemy)
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# Setting up ORM
engine = create_engine('sqlite:///example.db')
Base = declarative_base()
# Defining ORM model
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
email = Column(String)
# Creating tables (if not exists)
Base.metadata.create_all(engine)
# Creating a session
Session = sessionmaker(bind=engine)
session = Session()
# Querying users
users = session.query(User).all()
# Working with queried data
for user in users:
print(f"User: {user.name} - Email: {user.email}")
# Closing session
session.close()
Explanation:
-
Without ORM: You manually write SQL queries (
SELECT * FROM users
) and handle database connections and cursor operations. This approach involves more boilerplate code and mixes SQL with application logic. -
With ORM: You define a
User
class that represents theusers
table in the database. ORM (like SQLAlchemy) handles SQL generation, database connections, and object mapping behind the scenes. Developers interact with Python objects (User
instances) rather than SQL queries directly, simplifying code and improving readability.
2. Portability and Database Independence
Example: Imagine you want to switch from SQLite to PostgreSQL without changing your application logic.
Without ORM:
# Without ORM (SQLite)
import sqlite3
# Connecting to SQLite database
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# Fetching users
cursor.execute('SELECT * FROM users')
users = cursor.fetchall()
# Closing database connection
conn.close()
Changing to PostgreSQL (without ORM):
# Without ORM (PostgreSQL)
import psycopg2
# Connecting to PostgreSQL database
conn = psycopg2.connect(database="example", user="user", password="password", host="localhost", port="5432")
cursor = conn.cursor()
# Fetching users (different SQL syntax for PostgreSQL)
cursor.execute('SELECT * FROM users')
users = cursor.fetchall()
# Closing database connection
conn.close()
With ORM (using SQLAlchemy):
# With ORM (using SQLAlchemy)
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# Setting up ORM (works with both SQLite and PostgreSQL)
engine = create_engine('postgresql://user:password@localhost:5432/example')
# engine = create_engine('sqlite:///example.db') # Uncomment for SQLite
Base = declarative_base()
# Defining ORM model (same for both databases)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
email = Column(String)
# Creating tables (if not exists)
Base.metadata.create_all(engine)
# Creating a session
Session = sessionmaker(bind=engine)
session = Session()
# Querying users (same code for both databases)
users = session.query(User).all()
# Working with queried data
for user in users:
print(f"User: {user.name} - Email: {user.email}")
# Closing session
session.close()
Explanation:
-
Without ORM: Switching databases requires rewriting SQL queries and adapting to database-specific APIs (like
sqlite3
vs.psycopg2
). -
With ORM: You change the database connection URL (
create_engine
) and SQLAlchemy handles the differences in SQL dialects and database-specific features internally. Your application code (User
class and query logic) remains unchanged, promoting database portability and minimizing code changes.
3. Object-Oriented Approach
Example: Working with relational data in an object-oriented manner.
Without ORM:
# Without ORM
import sqlite3
# Connecting to SQLite database
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# Defining a function to fetch users
def get_users():
cursor.execute('SELECT * FROM users')
return cursor.fetchall()
# Fetching and printing users
users = get_users()
for user in users:
print(f"User: {user[1]} - Email: {user[2]}")
# Closing database connection
conn.close()
With ORM (using SQLAlchemy):
# With ORM (using SQLAlchemy)
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# Setting up ORM
engine = create_engine('sqlite:///example.db')
Base = declarative_base()
# Defining ORM model
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
email = Column(String)
# Creating tables (if not exists)
Base.metadata.create_all(engine)
# Creating a session
Session = sessionmaker(bind=engine)
session = Session()
# Defining a method to fetch users
def get_users():
return session.query(User).all()
# Fetching and printing users
users = get_users()
for user in users:
print(f"User: {user.name} - Email: {user.email}")
# Closing session
session.close()
Explanation:
-
Without ORM: You manually write functions (
get_users()
) to fetch data from the database and handle result processing. This approach mixes database access code with application logic. -
With ORM: You define a
User
class that abstracts theusers
table. ORM (like SQLAlchemy) allows you to queryUser
objects directly (session.query(User).all()
), promoting a more object-oriented approach. Data from the database is mapped to Python objects (User
instances), facilitating cleaner, more maintainable code that aligns with object-oriented principles.