Difference between ORM and SQL in Odoo.

Odoo provides an ORM (Object Relational Mapping) layer that allows developers to interact with the database using Python objects instead of writing raw SQL queries. ORM automatically handles:


Access rights & record rules

Multi-company logic

Translations

Cache management

Upgrade compatibility


SQL directly interacts with the PostgreSQL database and bypasses Odoo’s security and business logic layers.


✅ ​ORM Example

partners = self.env['res.partner'].search([('customer_rank', '>', 0)])

for partner in partners:

    partner.write({'comment': 'Customer'})


✅ Secure

✅ Respects access rules

✅ Easy to maintain


✅ ​SQL Example

self.env.cr.execute("""

    SELECT id FROM res_partner WHERE customer_rank > 0

""")

partner_ids = [row[0] for row in self.env.cr.fetchall()]

partners = self.env['res.partner'].browse(partner_ids)



⚠️ Faster for large datasets

⚠️ No access rule checking

⚠️ Manual security handling required


 

When to Use What?


Use Case                                                  ORM           SQL

Business logic                                               ✅                ❌

CRUD operations                                       ✅                ❌

Large reports                                               ❌                ✅

Performance-critical queries               ❌                ✅


Best Practice: Always prefer ORM unless SQL is absolutely necessary.