[BUG]: `Decimal` type gets casted to `number` when queried through `with` (include relations)

[ghost]

What version of drizzle-orm are you using?

0.28.6

What version of drizzle-kit are you using?

0.19.13

Describe the Bug

With those two tables and relations:

export const processes = mysqlTable('processes', {
    id: varchar('id', { length: 128 })
        .primaryKey()
        .$defaultFn(() => createId()),
});

export const processesRelations = relations(processes, ({ one, many }) => ({
    processesInputs: many(processesInputs),
}));

export const processesInputs = mysqlTable('processesInputs', {
    id: varchar('id', { length: 128 })
        .primaryKey()
        .$defaultFn(() => createId()),
    percentage: decimal('percentage', { precision: 20, scale: 10 }).notNull(),
});

export const processesInputsRelations = relations(processesInputs, ({ one }) => ({
    process: one(processes, {
        fields: [processesInputs.processId],
        references: [processes.id],
    }),
}));

If I query the processesInputs table directly, I get percentage as a string:

const inputs = await db.query.processesInputs.findMany({
        where: eq(processesInputs.processId, processId),
    });

// inputs is something like:
[{
  id: 'pm8q3gbyb9uc6kw7klog7zs3',
  percentage: '100.0000000000',
  processId: 'mb9fzdtnhz81bwngwouj0vb1',
}]

But if I query the processes table including the related processesInputs, I get percentage as a number:

const process = await db.query.processes.findFirst({
        where: eq(processes.id, processId),
        with: {
            processesInputs: true,
        },
    });

const inputs = process.processesInputs;

//  inputs is something like:
[{
  id: 'pm8q3gbyb9uc6kw7klog7zs3',
  percentage: 100,
  processId: 'mb9fzdtnhz81bwngwouj0vb1',
}]

Expected behavior

I would expect percentage to be of type string in both cases

Environment & setup

No response

[Christophevvv]

Same problem here. Numeric column value gets somehow transformed to number when accessed through with, but the type still says string (as it should be indeed). This is also problematic when precision is important...

[AChangXD]

+1

[Angelelz]

What driver is giving you this problem?

[Christophevvv]

pg

[ghost]

mysql

[cloleb]

Related to an issue i opened for postgres #1453

[hlspablo]

this also happens in numeric with postgres. Seems to be related how drizzle handle relations using with: in the executed query, it uses JSON_AGG and JSON_BUILD_ARRAY, for example:

COALESCE(JSON_AGG(JSON_BUILD_ARRAY(
   "product_variations"."id", 
   "product_variations"."price", 
   "product_variations"."stock", 
   "product_variations"."weight", 
   "product_variations"."thickness_id", 
   "product_variations"."product_id", 
   "product_variations_thickness"."data"
 )), '[]'::json) AS "data" 
 seems that when it is casted to JSON it is converted to JSON numbers.
 

[adiSuper94]

@Angelelz facing this same issue in mysql. I see that there is a PR that has been open for a while to solve this issue. Any reason why it hasn't been merged ? If there is anything that need to be done before it can be merged, I'd like to help.

[hstevanoski]

+1. This is a bummer with drizzle-zod as Zod schema validation fails against retreived data due to an invalid_type error ("Expected string, received number"); I'm using tRPC and my procedures fail due to this. The only workaround I can think of is .extend()-ing the fields to z.number().or(z.string())[.nullable()]. Drizzle shoots itself in the foot here. Please prioritize this.

[dalechyn]

@Angelelz, spotting the same issue when using numeric(78, 0) with pg driver.

100000000000000000000000000 in the database turns to 100000000000000004764729344 when queried via db.query.

https://github.com/drizzle-team/drizzle-orm/issues/1290#issuecomment-2064307614, @hlspablo's comment seems to point the exact reason why that happens.

[johanneskares]

this also happens in numeric with postgres. Seems to be related how drizzle handle relations using with: in the executed query, it uses JSON_AGG and JSON_BUILD_ARRAY, for example:

COALESCE(JSON_AGG(JSON_BUILD_ARRAY(
   "product_variations"."id", 
   "product_variations"."price", 
   "product_variations"."stock", 
   "product_variations"."weight", 
   "product_variations"."thickness_id", 
   "product_variations"."product_id", 
   "product_variations_thickness"."data"
 )), '[]'::json) AS "data" 
 seems that when it is casted to JSON it is converted to JSON numbers.

I think you are right. But the problem is in Drizzle, not PG. PG is actually returning the full resolution number in json, but drizzle internally uses JSON.parse for subrelations. Json.parse can't handle high precision numbers. My guess is it's happening here:

https://github.com/drizzle-team/drizzle-orm/blob/c8359a16fff4b05aff09445edd63fc65a7430ce9/drizzle-orm/src/relations.ts#L682

[dennismuench]

Is there any known workaround?

[dennismuench]

Overriding the numeric columns inside the extras object and casting everything to text worked for me (pg).

    const invoiceQuery = database.query.invoices.findFirst({
      columns: invoiceParams,
      with: {
        items: {
          columns: invoiceItemParams,
          extras: {
            amount: sql`cast (${invoiceItems.amount} as text)`.as('amount'),
            discount: sql`cast (${invoiceItems.discount} as text)`.as('discount'),
            discountAmount: sql`cast (${invoiceItems.discountAmount} as text)`.as('discountAmount'),
            unitAmount: sql`cast (${invoiceItems.unitAmount} as text)`.as('unitAmount'),
            total: sql`cast (${invoiceItems.total} as text)`.as('total'),
          },
        },
        ...
      },
      where: (t, { and, eq }) => and(
        eq(t.id, sql.placeholder('invoiceId')),
        eq(t.tenantId, sql.placeholder('tenantId')),
      ),
    });