CODEWITHSUNDEEP
typescriptdrizzle
Alan Sikora
Alan Sikora

Reputation: 557

Drizzle ORM - decimal MySQL is a string?

The schema:

export const myTable = mysqlTable(
  "MyTable",
  {
    id: varchar("id", { length: 191 }).notNull(),
    value: decimal("value", { precision: 7, scale: 4 }).notNull(),
    createdAt: datetime("createdAt", { mode: "date" })
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
  },
  (table) => {
    return {
      myTableId: primaryKey(table.id),
    };
  }
);

The code:

type MyTable = InferModel<typeof myTable, "select">;

const values: MyTable[] = await db.select().from(myTable);

The type of values[0].value is string, and I figure it should be a number.

I could not find anything related to this on Drizzle docs, Github Issues or StackOverflow, and I would like to understand why this happens, or, if I'm making any mistakes.

EDIT: I added an answer that "fixes" the type, but does not answer why double becomes a number and decimal becomes a string, which is enough for me.

EDIT 2: Thanks @ColouredPanda and @andrew-allen: https://github.com/drizzle-team/drizzle-orm/issues/570#issuecomment-1646033240

Upvotes: 5

Views: 6700

Answers (5)

Bernardo Gomes
Bernardo Gomes

Reputation: 21

I used this information, but I made a small change when I needed to use notNull.

The notNull method didn't work in this case:

price: 
  decimal("price", {
    precision: 10,
    scale: 2,
  }).notNull() as unknown as PgDoublePrecisionBuilderInitial<"price">

So I changed it to:

price: (
  decimal("price", {
    precision: 10,
    scale: 2,
  }) as unknown as PgDoublePrecisionBuilderInitial<"price">
).notNull()

Upvotes: 0

Hồ Thiện Lạc
Hồ Thiện Lạc

Reputation: 99

The fact that decimal stored in the Database (numeric in equivalent in Postgres) by store digit by digit, not as floating point. So the ORM parse decimal to string actually a good point to keep original idea of storing its decimal.

Floating point may cause incorrect value, for example 3.6 in C is 3.60000000000000008882

Decimal in the other hand store just store number 3 and 6, so it is 100% exact. Decimal is used in storing money to avoid those error. (some people store as int, in cent unit also)

Upvotes: 0

wobsoriano
wobsoriano

Reputation: 13462

You can try any of the ff.:

1. Customize column data type. Converts the typescript data type into a number, but you'll still get a string as a result:

{
  value: decimal('value', { precision: 7, scale: 4 }).$type<number>().notNull()
}

2. Custom types to make the data type a number, and also accept/return a number:

import { customType, mysqlTable, int } from 'drizzle-orm/mysql-core';

const decimalNumber = customType<{ data: number }>({
  dataType() {
    return 'decimal(7, 4)';
  },
  fromDriver(value) {
    return Number(value);
  },
});

export const myTable = mysqlTable('MyTable', {
  id: varchar('id', { length: 191 }).notNull(),
  value: decimalNumber('value').notNull(),
});

3. Use double.

Be aware that JavaScript number is a 64-bit floating point value, so at some point, you wind up losing precision (why Drizzle end up using strings for decimals).

Upvotes: 8

Pedro Monteagudo
Pedro Monteagudo

Reputation: 416

What I've done to workaround this is adding type assertion like this:

export const db = mysqlTable("someDb", {
  weight: decimal("weight", {
    precision: 6, scale: 2
  }) as unknown as MySqlDoubleBuilderInitial<"weight">
});

In this way, I still can use DECIMAL SQL type (which is what I really want) and typescript types works correctly in all my code base (weight is number). I think is safe to do this type assertion because when I check typeof weight it returns "number" and (I believe) that javascript number supports my specified precision

Upvotes: 2

Alan Sikora
Alan Sikora

Reputation: 557

This might not be an answer to the "why decimals become strings", but it answers the typing problem, which was my main concern.

Instead of decimal, use double. Drizzle will automatically infer double to number.

The updated schema:

export const myTable = mysqlTable(
  "MyTable",
  {
    id: varchar("id", { length: 191 }).notNull(),
    value: double("value", { precision: 7, scale: 4 }).notNull(),
    createdAt: datetime("createdAt", { mode: "date" })
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
  },
  (table) => {
    return {
      myTableId: primaryKey(table.id),
    };
  }
);

Upvotes: 2

Related Questions