I wish I knew this before: Python's ORM vs Raw SQL in SQLAlchemy Explained!\n\n## Introduction\n\nIf you’ve ever built a Python backend, you’ve likely run into the choice between using an ORM (Object Relational Mapper) or writing raw SQL queries when working with a database. Let’s dive into a hands-on example using SQLAlchemy and unravel the strengths and weaknesses of each approach—so you can pick what’s best for your next project!\n\n---\n\n## 1. Setup: Define Models with SQLAlchemy ORM\n\nFirst, let’s set up our data model using SQLAlchemy’s declarative base.\n\n

python\nfrom sqlalchemy import Column, Integer, String, Text, create_engine\nfrom sqlalchemy.ext.declarative import declarative_base\nfrom sqlalchemy.orm import sessionmaker\n\nBase = declarative_base()\n\nclass JobDescription(Base):\n __tablename__ = 'job_description'\n id = Column(Integer, primary_key=True)\n company_id = Column(String, nullable=False)\n company_name = Column(Text, nullable=False)\n job_description = Column(Text, nullable=False)\n\nclass CandidateResume(Base):\n __tablename__ = 'candidate_resume'\n id = Column(Integer, primary_key=True)\n candidate_id = Column(String, nullable=False)\n candidate_name = Column(Text, nullable=False)\n resume = Column(Text, nullable=False)\n\nengine = create_engine('postgresql://user:password@localhost/dbname')\nSession = sessionmaker(bind=engine)\n\nif __name__ == '__main__':\n Base.metadata.create_all(engine)\n

\n\n---\n\n## 2. Bit-ORM Layer: Repository Using the ORM\n\nUsing the ORM, you’ll interact with Python objects instead of raw SQL.\n\n

python\nfrom orm_models import JobDescription, Session\n\ndef get_job_descriptions_by_company_orm(company_id: str):\n """\n Retrieve JobDescriptions for a given company using ORM.\n Returns a list of JobDescription objects.\n """\n session = Session()\n try:\n results = session.query(JobDescription)\n .filter(JobDescription.company_id == company_id)\n .all()\n return results\n finally:\n session.close()\n\nif __name__ == '__main__':\n jobs = get_job_descriptions_by_company_orm('COMPANY_123')\n for job in jobs:\n print(f"{job.company_name}: {job.job_description}")\n

\n\n### Advantages of the ORM Approach\n- Abstraction & Simplicity: Work directly with Python objects; SQL generation is handled for you.\n- Maintainability: Updates in models propagate everywhere queries are used.\n- Safety & Consistency: Automatic parameter binding reduces SQL injection risks.\n- Relationship Handling: Easier navigation between related models.\n\n### Disadvantages\n- Abstraction Overhead: Less efficient for highly-optimized or complex queries.\n- Difficult Advanced Queries: May require raw SQL for complex performance customizations.\n\n---\n\n## 3. Bit-Out-ORM Layer: Repository Using Raw SQL\n\nFor cases where fine-grained control is needed, writing raw SQL lets you directly manipulate queries.\n\n

python\nfrom orm_models import engine\n\ndef get_job_descriptions_by_company_raw(company_id: str):\n """\n Retrieve job descriptions for a given company using raw SQL.\n Returns a list of RowProxy objects.\n """\n with engine.connect() as connection:\n result = connection.execute(\n "SELECT id, company_id, company_name, job_description FROM job_description WHERE company_id = :company_id",\n {"company_id": company_id}\n )\n return result.fetchall()\n\nif __name__ == '__main__':\n rows = get_job_descriptions_by_company_raw('COMPANY_123')\n for row in rows:\n print(f"{row['company_name']}: {row['job_description']}")\n

\n\n### Advantages of the Raw SQL Approach\n- Fine-Grained Control: Ideal for advanced optimizations and database-specific features.\n- Transparency: Directly see and understand the executed SQL.\n- Flexibility: Access features not surfaced by the ORM.\n\n### Disadvantages\n- Manual Mapping: Responsible for converting rows into Python objects.\n- Error-Prone: Handwriting SQL increases the chance of errors and maintenance headaches.\n- Repetition: CRUD operations may become verbose and repetitive.\n\n---\n\n## 4. Summary & Takeaways\n\n- Use ORM for productivity, maintainability, and safety in most cases.\n- Use Raw SQL for advanced queries or performance-critical areas.\n- Combining both approaches in a hybrid system gives the best of both worlds!\n\n*Did you find this breakdown useful? Drop your favorite approach in the comments below!*\n\nHappy coding! 🚀