select with sql<number> returns string

Hi everyone! I am new to drizzle and i have been trying to convert one of my sql queries to it

SELECT 
  customer,
  name,
  SUM(CASE WHEN age >= 0   AND age < 30  THEN amount ELSE 0 END)::NUMERIC AS amount0_30,
  SUM(CASE WHEN age >= 30  AND age < 60  THEN amount ELSE 0 END)::NUMERIC AS amount30_60,
  SUM(CASE WHEN age >= 60  AND age < 90  THEN amount ELSE 0 END)::NUMERIC AS amount60_90,
  SUM(CASE WHEN age >= 90  AND age < 120 THEN amount ELSE 0 END)::NUMERIC AS amount90_120,
  SUM(CASE WHEN age >= 120               THEN amount ELSE 0 END)::NUMERIC AS amount120
FROM (
  SELECT 
    "customer", 
    "name", 
    "amount", 
    (current_timestamp::date - "dueDate"::date) AS age
  FROM "Invoices"
) sub
GROUP BY customer, name ORDER BY customer ASC;

SELECT 
  customer,
  name,
  SUM(CASE WHEN age >= 0   AND age < 30  THEN amount ELSE 0 END)::NUMERIC AS amount0_30,
  SUM(CASE WHEN age >= 30  AND age < 60  THEN amount ELSE 0 END)::NUMERIC AS amount30_60,
  SUM(CASE WHEN age >= 60  AND age < 90  THEN amount ELSE 0 END)::NUMERIC AS amount60_90,
  SUM(CASE WHEN age >= 90  AND age < 120 THEN amount ELSE 0 END)::NUMERIC AS amount90_120,
  SUM(CASE WHEN age >= 120               THEN amount ELSE 0 END)::NUMERIC AS amount120
FROM (
  SELECT 
    "customer", 
    "name", 
    "amount", 
    (current_timestamp::date - "dueDate"::date) AS age
  FROM "Invoices"
) sub
GROUP BY customer, name ORDER BY customer ASC;

this is what i have been doing on the ts side

export interface AgingCustomer {
  customer: string;
  name: string;
  amount0_30: number;
  amount30_60: number;
  amount60_90: number;
  amount90_120: number;
  amount120: number;
}

 async getAgingList(): Promise<AgingCustomer[]> {
    const res = await this.db
      .select({
        customer: sql<string>`customer`,
        name: sql<string>`name`,
        amount0_30:
          sql<number>`SUM(CASE WHEN age >= 0   AND age < 30  THEN amount ELSE 0 END)`.as(
            'amount0_30',
          ),
        amount30_60:
          sql<number>`SUM(CASE WHEN age >= 30  AND age < 60  THEN amount ELSE 0 END)`.as(
            'amount30_60',
          ),
        amount60_90:
          sql<number>`SUM(CASE WHEN age >= 60  AND age < 90  THEN amount ELSE 0 END)`.as(
            'amount60_90',
          ),
        amount90_120:
          sql<number>`SUM(CASE WHEN age >= 90  AND age < 120 THEN amount ELSE 0 END)`.as(
            'amount90_120',
          ),
        amount120:
          sql<number>`SUM(CASE WHEN age >= 120               THEN amount ELSE 0 END)`.as(
            'amount120',
          ),
      })
      .from(
        sql`
          (
            SELECT 
              "customer", 
              "name", 
              "amount", 
              (current_timestamp::date - "dueDate"::date) AS age
            FROM "Invoices"
          ) sub
        `,
      )
      .groupBy(sql`customer, name`)
      .orderBy(sql`customer ASC`);
    console.log(res);
    typia.assert<AgingCustomer[]>(res);
    return res;
  }

export interface AgingCustomer {
  customer: string;
  name: string;
  amount0_30: number;
  amount30_60: number;
  amount60_90: number;
  amount90_120: number;
  amount120: number;
}

 async getAgingList(): Promise<AgingCustomer[]> {
    const res = await this.db
      .select({
        customer: sql<string>`customer`,
        name: sql<string>`name`,
        amount0_30:
          sql<number>`SUM(CASE WHEN age >= 0   AND age < 30  THEN amount ELSE 0 END)`.as(
            'amount0_30',
          ),
        amount30_60:
          sql<number>`SUM(CASE WHEN age >= 30  AND age < 60  THEN amount ELSE 0 END)`.as(
            'amount30_60',
          ),
        amount60_90:
          sql<number>`SUM(CASE WHEN age >= 60  AND age < 90  THEN amount ELSE 0 END)`.as(
            'amount60_90',
          ),
        amount90_120:
          sql<number>`SUM(CASE WHEN age >= 90  AND age < 120 THEN amount ELSE 0 END)`.as(
            'amount90_120',
          ),
        amount120:
          sql<number>`SUM(CASE WHEN age >= 120               THEN amount ELSE 0 END)`.as(
            'amount120',
          ),
      })
      .from(
        sql`
          (
            SELECT 
              "customer", 
              "name", 
              "amount", 
              (current_timestamp::date - "dueDate"::date) AS age
            FROM "Invoices"
          ) sub
        `,
      )
      .groupBy(sql`customer, name`)
      .orderBy(sql`customer ASC`);
    console.log(res);
    typia.assert<AgingCustomer[]>(res);
    return res;
  }

the type of the const res does match the type i want as output but the actual data i get from it gives me string values for all the amounts. I am really confused on why this happens.

5 Replies

loucass003OP•4mo ago

typing and runtime output does not match

TOSL•4mo ago

Drizzle does not (and can't) enforce sql<T> typing. It's just helps inference on the field. You need to use sql``.mapWith() https://orm.drizzle.team/docs/sql#sqlmapwith The issue is (probably) coming from the database driver converting the result of sql SUM() to a string to avoid losing precision which is common practice.

loucass003OP•4mo ago

thanks for the reply 🙏 i am looking into sql mapWith https://orm.drizzle.team/docs/sql#sql-select and trying to do the same as this

 count: sql<number>`count(*)`.mapWith(Number)

 count: sql<number>`count(*)`.mapWith(Number)

But i dont have access to the mapWith function in my code. I am on drizzle "drizzle-orm": "^0.44.2" is the doc up to date?

Drizzle ORM - Magic sql`` operator

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

loucass003OP•4mo ago

Nvm found it cant use it afer .as it solves my issue thank you ❤️

.select({
  customer: sql<string>`customer`,
  name: sql<string>`name`,
  amount0_30:
    sql<number>`SUM(CASE WHEN age >= 0   AND age < 30  THEN amount ELSE 0 END)`.mapWith(
      Number,
    ),
  amount30_60:
    sql<number>`SUM(CASE WHEN age >= 30  AND age < 60  THEN amount ELSE 0 END)`.mapWith(
      Number,
    ),
  amount60_90:
    sql<number>`SUM(CASE WHEN age >= 60  AND age < 90  THEN amount ELSE 0 END)`.mapWith(
      Number,
    ),
  amount90_120:
    sql<number>`SUM(CASE WHEN age >= 90  AND age < 120 THEN amount ELSE 0 END)`.mapWith(
      Number,
    ),
  amount120:
    sql<number>`SUM(CASE WHEN age >= 120               THEN amount ELSE 0 END)`.mapWith(
      Number,
    ),
})

.select({
  customer: sql<string>`customer`,
  name: sql<string>`name`,
  amount0_30:
    sql<number>`SUM(CASE WHEN age >= 0   AND age < 30  THEN amount ELSE 0 END)`.mapWith(
      Number,
    ),
  amount30_60:
    sql<number>`SUM(CASE WHEN age >= 30  AND age < 60  THEN amount ELSE 0 END)`.mapWith(
      Number,
    ),
  amount60_90:
    sql<number>`SUM(CASE WHEN age >= 60  AND age < 90  THEN amount ELSE 0 END)`.mapWith(
      Number,
    ),
  amount90_120:
    sql<number>`SUM(CASE WHEN age >= 90  AND age < 120 THEN amount ELSE 0 END)`.mapWith(
      Number,
    ),
  amount120:
    sql<number>`SUM(CASE WHEN age >= 120               THEN amount ELSE 0 END)`.mapWith(
      Number,
    ),
})

Sillvva•4mo ago

As TOSL stated it's a data type issue. NUMERIC and DECIMAL data types have exact precision rather than floating point decimals. Database drivers treat these types as strings. https://www.postgresql.org/docs/current/datatype-numeric.html https://github.com/porsager/postgres?tab=readme-ov-file#numbers-bigint-numeric

PostgreSQL Documentation

8.1. Numeric Types

8.1. Numeric Types # 8.1.1. Integer Types 8.1.2. Arbitrary Precision Numbers 8.1.3. Floating-Point Types 8.1.4. Serial Types Numeric types consist of …

GitHub

GitHub - porsager/postgres: Postgres.js - The Fastest full featured...

Postgres.js - The Fastest full featured PostgreSQL client for Node.js, Deno, Bun and CloudFlare - porsager/postgres

Gaming

Programming

select with sql<number> returns string

Did you find this page helpful?