Raw SQL vs ORMs: Pros and Cons

Structured Query Language (SQL) is a powerful tool developed in the 1970s for managing relational databases like PostgreSQL and MySQL. SQL efficiently retrieves, updates, and deletes structured data by working directly with tables and defined relationships. By contrast, non-relational databases like MongoDB organize data as documents or key-value pairs, useful for flexible data structures but differing significantly from SQL.

Object-Relational Mappings (ORMs) and Object-Document Mappings (ODMs) were developed to simplify database interactions for developers. Tools like Prisma and Sequelize (ORMs) and Mongoose (ODM for MongoDB) allow code to interact with data as objects, making complex operations more accessible without writing raw SQL. This abstraction speeds up development and enhances code readability, though it may add processing layers that impact performance.

Recently, while working on dashboard stats, my supervisor suggested I switch from Prisma to raw SQL. Our project stack is with NestJS, Prisma, and PostgreSQL, initially, I used JavaScript and Prisma for daily visitor counts, turnaround times, and processing metrics. However, my original approach involved multiple loops to compute stats, which wasn’t as efficient.

Switching to raw SQL, I restructured my queries to calculate everything—sums, counts, averages—directly in SQL, which cut down loops and simplified the code. The database handled the heavy lifting, resulting in faster execution and lower memory usage, showing how SQL can be optimal for complex, data-heavy tasks.

While ORMs are excellent tools that make development faster and more accessible, raw SQL often offers significant speed advantages. Backend developers should consider raw SQL for data-intensive operations, as it allows for streamlined, high-performance queries that ORMs sometimes can't match.
What is your experience with ORMs, which is your favorite and why?