Raw Sql vs SqlAlchemy when Django ORM is not enough

Question

In a django project, if a situation comes that Django ORM is not able to execute some complex queries then there are 2 options:

1. Use Raw Sql queries

2. Use SqlAlchemy

I want to know if there are some rules like some pros and cons of both which can guide me to choose one of the above 2 options. Or is it entirely depends on what we like?

Answer 1

ORM

• Pro Usable on different DBMS without code customization.
• Pro Makes code/DB integration easier.
• Pro Additional type checking from ORM/table definitions.
• Pro Facilitate db migrations.
• Pro Essentially a DSL for DB integration.
• Con Additional component to understand (you should understand how the ORM will create the SQL)
• Con Any SQL customizations eliminate the usability on different DBMS.

Raw SQL

• Pro You know the SQL being used because it's there in the code.
• Pro More control over the SQL and integration.
• Pro Less complexity (no ORM).
• Con Tied to one DBMS flavor of SQL.
• Con Manual code/DB integration.

The issue of complexity

I'm sure I'll get dinged for this, BUT.

Unlike many of the its proponents I do NOT think ORMs free the developers from understanding SQL. You still have to understand SQL because you will end up dealing with the SQL your ORM creates. And here is why:

• SQL statements are typically one of the first things that needs performance tuning in a given project.
• ORMs are still young. None that I know of implement an actual SQL parser which means, outside of trivial statements, the SQL constructed is often inefficient, and in some cases just wrong.

This doesn't mean you shouldn't use an ORM. They can still reduce the amount of code you have to write. But you should understand you may not get all the benefits you think you will.