DEV Community

Cover image for Did you know you can optimize database access with ORM and raw SQL?
Negitama
Negitama

Posted on

Did you know you can optimize database access with ORM and raw SQL?

Did you know that you can choose between using SQLAlchemy ORM and raw SQL queries for database interactions in Python? Let's explore how each approach could benefit your development.

Setup: Define Models with SQLAlchemy ORM

First, we create our ORM models. This file (e.g., orm_models.py) defines the schema in code using SQLAlchemy’s declarative base.

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

Base = declarative_base()

class JobDescription(Base):
    __tablename__ = 'job_description'
    id = Column(Integer, primary_key=True)
    company_id = Column(String, nullable=False)
    company_name = Column(Text, nullable=False)
    job_description = Column(Text, nullable=False)

class CandidateResume(Base):
    __tablename__ = 'candidate_resume'
    id = Column(Integer, primary_key=True)
    candidate_id = Column(String, nullable=False)
    candidate_name = Column(Text, nullable=False)
    resume = Column(Text, nullable=False)

engine = create_engine('postgresql://user:password@localhost/dbname')

Session = sessionmaker(bind=engine)

if __name__ == '__main__':
    Base.metadata.create_all(engine)
Enter fullscreen mode Exit fullscreen mode

Bit-ORM Layer: Repository Using the ORM

This repository function uses the ORM—meaning we work with Python objects. The ORM layer automatically converts our model objects into the appropriate SQL queries. Save this in (e.g.) repository_orm.py.

from orm_models import JobDescription, Session

def get_job_descriptions_by_company_orm(company_id: str):
    session = Session()
    try:
        results = session.query(JobDescription)\
                         .filter(JobDescription.company_id == company_id)\
                         .all()
        return results
    finally:
        session.close()

if __name__ == '__main__':
    jobs = get_job_descriptions_by_company_orm('COMPANY_123')
    for job in jobs:
        print(f"{job.company_name}: {job.job_description}")
Enter fullscreen mode Exit fullscreen mode

Advantages of the ORM Approach

  • Abstraction & Simplicity: You deal with Python objects; the ORM hides the SQL details.
  • Maintainability: Changes to models update all the underlying queries.
  • Safety & Consistency: Automatic parameter binding prevents SQL injection.
  • Relationship Handling: ORMs make it easier to navigate relationships among models.

Disadvantages of the ORM Approach

  • Abstraction Overhead: Sometimes it can be less efficient if you need very fine-tuned queries.
  • Complexity for Advanced Queries: Very complex queries or performance optimizations may require custom SQL.

Bit-Out-ORM Layer: Repository Using Raw SQL

In contrast, this repository function uses raw SQL queries via SQLAlchemy’s connection API. Save this as repository_raw.py.

from orm_models import engine

def get_job_descriptions_by_company_raw(company_id: str):
    with engine.connect() as connection:
        result = connection.execute(
            "SELECT id, company_id, company_name, job_description FROM job_description WHERE company_id = :company_id",
            {"company_id": company_id}
        )
        return result.fetchall()

if __name__ == '__main__':
    rows = get_job_descriptions_by_company_raw('COMPANY_123')
    for row in rows:
        print(f"{row['company_name']}: {row['job_description']}")
Enter fullscreen mode Exit fullscreen mode

Advantages of the Raw SQL Approach

  • Fine-Grained Control: Directly write and optimize SQL queries for complex or performance-critical tasks.
  • Transparency: You see exactly what SQL is sent to the database.
  • Flexibility: Use database-specific features or functions not directly exposed by the ORM.

Disadvantages of the Raw SQL Approach

  • Manual Mapping: You must manually convert results to Python objects if needed.
  • Error-Prone: Writing raw SQL can lead to mistakes, and maintaining it over time may be more challenging.
  • Repetition: Common CRUD operations might require writing repetitive SQL queries when an ORM could generate them automatically.

Summary and Comparison

In a full-scale system, it’s common to use an ORM for most database operations while falling back on raw SQL for advanced use cases that require fine-tuning. This hybrid approach allows you to benefit from high productivity when possible while retaining control when needed.

Top comments (0)