Why does `sum` return `SQL<string | null>`?

I was trying to use the sum aggregate function and I noticed that it returns a string. Why is that?

/**
 * Returns the sum of all non-null values in `expression`.
 *
 * ## Examples
 *
 * \`\`\`ts
 * // Sum of every employee's salary
 * db.select({ value: sum(employees.salary) }).from(employees)
 * \`\`\`
 *
 * @see sumDistinct to get the sum of all non-null and non-duplicate values in `expression`
 */
export declare function sum(expression: SQLWrapper): SQL<string | null>;

/**
 * Returns the sum of all non-null values in `expression`.
 *
 * ## Examples
 *
 * \`\`\`ts
 * // Sum of every employee's salary
 * db.select({ value: sum(employees.salary) }).from(employees)
 * \`\`\`
 *
 * @see sumDistinct to get the sum of all non-null and non-duplicate values in `expression`
 */
export declare function sum(expression: SQLWrapper): SQL<string | null>;

6 Replies

Mykhailo•9mo ago

@AstroBear If you are using drizzle function sum(), it is designed this way. If you are using sql, drizzle cannot accurately determine the type without user input. So the best way is to determine type by yourself

sql`sum(${expression})`.mapWith(Number);

sql`sum(${expression})`.mapWith(Number);

AstroBear•9mo ago

Thanks, I was using the drizzle sum agg helper. I ended up just using Number() at the end, but I was wondering why it was decided it had to return a string

Mykhailo•9mo ago

This is drizzle sum agg helper, so it is just designed this way

function sum(expression) {
  return sql`sum(${expression})`.mapWith(String);
}

function sum(expression) {
  return sql`sum(${expression})`.mapWith(String);
}

AstroBear•9mo ago

Got it, ty. I guess it is mapped to String for some sort of compatibility.

Trey•9mo ago

"Just designed this way", is not a great answer. I will also use this workaround but it would be great if someone who knows the history of sum can answer why it returns string | null.

Angelelz•9mo ago

Because the sum function can theoretically return null If no columns are returned by your query, sum will return null A string was used because drizzle cannot warrantee that the number returned by the database will be representable in a JS number It could be a bigint

Gaming

Programming

Why does `sum` return `SQL<string | null>`?