In this lesson, you will learn how to perform calculations like sums, counts, and averages directly on your database records using your ORM, rather than fetching all rows into memory.
Why Aggregate in the Database?
As your application grows, you might find yourself needing to know the "total sales," "average order value," or "number of users" in your system. A common beginner mistake is to fetch every single record into your Node.js application, store them in an array, and iterate through them to calculate these values. This is incredibly inefficient and can crash your server if you have millions of rows.
Database aggregations allow you to offload this math to the database engine. The database is highly optimized for scanning rows and calculating these values instantly, sending only the final result back to your application.
Performing Aggregations
Most ORMs provide aggregate methods on the model interface that mirror standard SQL functions (SUM, COUNT, AVG). Here is how you might calculate basic metrics for an Order model:
// Count the total number of orders
const totalOrders = await Order.count();
// Calculate the sum of all order totals
const totalRevenue = await Order.sum('total_price');
// Calculate the average price per order
const averageOrderValue = await Order.avg('total_price');
You can also combine these operations with the Query Scopes or filters you learned previously. For example, if you want the average price of only "completed" orders, you simply chain the filter before the aggregation:
const avgCompleted = await Order.where({ status: 'completed' }).avg('total_price');
By keeping these operations within the ORM layer, you keep your code clean and leverage the database's ability to handle complex data sets. This ensures your Node.js application remains performant and responsive, regardless of how many records are stored in your database tables.
Key Takeaway: Always perform calculations like counts, sums, and averages at the database level using ORM aggregate methods to avoid unnecessary data transfer and improve application memory usage.