[BUG]: Relational queries break customTypes with underlying DECIMAL dataTypes

b0o commented 1 year ago

What version of `drizzle-orm` are you using?

v0.27.0

What version of `drizzle-kit` are you using?

v0.19.2

Describe the Bug

Here's a minimal reproduction of the issue:

import { relations, eq } from 'drizzle-orm'
import { bigint, customType, mysqlTable, serial, varchar } from 'drizzle-orm/mysql-core'
import { drizzle } from 'drizzle-orm/planetscale-serverless'
import { cast, connect } from '@planetscale/database'

import { env } from './env'

// fixed-precision monetary type
// both amount and scale are integers
// scale is the number of decimal places
// amount is the number of subunits
// e.g. $34,567.890123 is represented as { scale: 6, amount: 34567890123 }
interface Monetary {
  scale: number
  amount: number
}

const monetary = customType<{
  data: Monetary
  driverData: string
  config: { precision: number; scale: number }
  configRequired: true
}>({
  dataType(config) {
    return `DECIMAL(${config.precision}, ${config.scale})`
  },
  fromDriver(value: string): Monetary {
    const [lhs, rhs] = value.split('.')
    const amount = parseInt(`${lhs}${rhs}`, 10)
    const scale = rhs?.length ?? 0
    return { amount, scale }
  },
  toDriver(value) {
    const { amount, scale } = value
    const lhs = amount.toString().slice(0, -scale)
    const rhs = amount.toString().slice(-scale).padStart(scale, '0')
    return `${lhs || '0'}.${rhs || '0'}`
  },
})

export const transfers = mysqlTable(
  'transfers',
  {
    id: serial('id').primaryKey(),
    fromAccount: bigint('from_account', { mode: 'number' }).notNull(),
    toAccount: bigint('to_account', { mode: 'number' }).notNull(),
    amount: monetary('amount', { precision: 20, scale: 6 }).notNull(),
    description: varchar('description', { length: 255 }).notNull(),
  },
)
export const accounts = mysqlTable(
  'accounts',
  {
    id: serial('id').primaryKey(),
    balance: monetary('balance', { precision: 20, scale: 6 }).notNull(),
    name: varchar('name', { length: 255 }).notNull(),
  },
)

export const transferAccounts = relations(transfers, ({ one }) => ({
  toAccount: one(accounts, {
    fields: [transfers.toAccount],
    references: [accounts.id],
  }),
  fromAccount: one(accounts, {
    fields: [transfers.fromAccount],
    references: [accounts.id],
  }),
}))

const connection = connect({
  host: env.DATABASE_HOST,
  username: env.DATABASE_USERNAME,
  password: env.DATABASE_PASSWORD,
  cast: (field, value) => {
    // Log the cast calls so we can see what's happening under the hood
    console.log('cast called:', { field, value })
    return cast(field, value)
  },
})

export const db = drizzle(connection, {
  logger: true,
  schema: {
    accounts,
    transfers,
    transferAccounts,
  },
})

async function test() {
  const { insertId: bobId } = await db.insert(accounts)
    .values({
      balance: { amount: 123, scale: 2 },
      name: 'Bob',
    })
    .execute()
  const bobAccount = await db.select()
    .from(accounts)
    .where(eq(accounts.id, parseInt(bobId)))
    .execute()
  console.log({ bobAccount })

  const { insertId: aliceId } = await db.insert(accounts)
    .values({
      balance: { amount: 456, scale: 2 },
      name: 'Alice',
    })
    .execute()
  const aliceAccount = await db.select()
    .from(accounts)
    .where(eq(accounts.id, parseInt(aliceId)))
    .execute()
  console.log({ aliceAccount })

  const { insertId: bobToAliceId } = await db.insert(transfers)
    .values({
      fromAccount: parseInt(bobId),
      toAccount: parseInt(aliceId),
      amount: { amount: 100, scale: 2 },
      description: 'Bob to Alice',
    })
    .execute()
  const bobToAliceTransfer = await db.select()
    .from(transfers)
    .where(eq(transfers.id, parseInt(bobToAliceId)))
    .execute()
  console.log({ bobToAliceTransfer })

  const { insertId: aliceToBobId } = await db.insert(transfers)
    .values({
      fromAccount: parseInt(aliceId),
      toAccount: parseInt(bobId),
      amount: { amount: 50, scale: 2 },
      description: 'Alice to Bob',
    })
    .execute()
  const aliceToBobTransfer = await db.select()
    .from(transfers)
    .where(eq(transfers.id, parseInt(aliceToBobId)))
    .execute()
  console.log({ aliceToBobTransfer })

  const bobToAliceWithRelations = await db.query.transfers.findFirst({
    where: eq(transfers.id, parseInt(bobToAliceId)),
    with: {
      fromAccount: true,
      toAccount: true,
    },
  })
  console.log({ bobToAliceWithRelations })
}

test()

This is the console output (note the cast called messages, produced by the custom cast function we passed to Planetscale's database-js, which allows us to see the underlying values before they're cast in JS land):

Query: insert into `accounts` (`balance`, `name`) values (?, ?) -- params: ["1.23", "Bob"]
Query: select `id`, `balance`, `name` from `accounts` where `accounts`.`id` = ? -- params: [9]
cast called: {
  field: {
    name: 'id',
    type: 'UINT64',
    table: 'accounts',
    orgTable: 'accounts',
    database: 'test',
    orgName: 'id',
    columnLength: 20,
    charset: 63,
    flags: 49703
  },
  value: '9'
}
cast called: {
  field: {
    name: 'balance',
    type: 'DECIMAL',
    table: 'accounts',
    orgTable: 'accounts',
    database: 'test',
    orgName: 'balance',
    columnLength: 22,
    charset: 63,
    decimals: 6,
    flags: 36865
  },
  value: '1.230000'
}
cast called: {
  field: {
    name: 'name',
    type: 'VARCHAR',
    table: 'accounts',
    orgTable: 'accounts',
    database: 'test',
    orgName: 'name',
    columnLength: 1020,
    charset: 255,
    flags: 4097
  },
  value: 'Bob'
}
{ bobAccount: [ { id: 9, balance: [Object], name: 'Bob' } ] }
Query: insert into `accounts` (`balance`, `name`) values (?, ?) -- params: ["4.56", "Alice"]
Query: select `id`, `balance`, `name` from `accounts` where `accounts`.`id` = ? -- params: [10]
cast called: {
  field: {
    name: 'id',
    type: 'UINT64',
    table: 'accounts',
    orgTable: 'accounts',
    database: 'test',
    orgName: 'id',
    columnLength: 20,
    charset: 63,
    flags: 49703
  },
  value: '10'
}
cast called: {
  field: {
    name: 'balance',
    type: 'DECIMAL',
    table: 'accounts',
    orgTable: 'accounts',
    database: 'test',
    orgName: 'balance',
    columnLength: 22,
    charset: 63,
    decimals: 6,
    flags: 36865
  },
  value: '4.560000'
}
cast called: {
  field: {
    name: 'name',
    type: 'VARCHAR',
    table: 'accounts',
    orgTable: 'accounts',
    database: 'test',
    orgName: 'name',
    columnLength: 1020,
    charset: 255,
    flags: 4097
  },
  value: 'Alice'
}
{ aliceAccount: [ { id: 10, balance: [Object], name: 'Alice' } ] }
Query: insert into `transfers` (`from_account`, `to_account`, `amount`, `description`) values (?, ?, ?, ?) -- params: [9, 10, "1.00", "Bob to Alice"]
Query: select `id`, `from_account`, `to_account`, `amount`, `description` from `transfers` where `transfers`.`id` = ? -- params: [7]
cast called: {
  field: {
    name: 'id',
    type: 'UINT64',
    table: 'transfers',
    orgTable: 'transfers',
    database: 'test',
    orgName: 'id',
    columnLength: 20,
    charset: 63,
    flags: 49703
  },
  value: '7'
}
cast called: {
  field: {
    name: 'from_account',
    type: 'INT64',
    table: 'transfers',
    orgTable: 'transfers',
    database: 'test',
    orgName: 'from_account',
    columnLength: 20,
    charset: 63,
    flags: 36865
  },
  value: '9'
}
cast called: {
  field: {
    name: 'to_account',
    type: 'INT64',
    table: 'transfers',
    orgTable: 'transfers',
    database: 'test',
    orgName: 'to_account',
    columnLength: 20,
    charset: 63,
    flags: 36865
  },
  value: '10'
}
cast called: {
  field: {
    name: 'amount',
    type: 'DECIMAL',
    table: 'transfers',
    orgTable: 'transfers',
    database: 'test',
    orgName: 'amount',
    columnLength: 22,
    charset: 63,
    decimals: 6,
    flags: 36865
  },
  value: '1.000000'
}
cast called: {
  field: {
    name: 'description',
    type: 'VARCHAR',
    table: 'transfers',
    orgTable: 'transfers',
    database: 'test',
    orgName: 'description',
    columnLength: 1020,
    charset: 255,
    flags: 4097
  },
  value: 'Bob to Alice'
}
{
  bobToAliceTransfer: [
    {
      id: 7,
      fromAccount: 9,
      toAccount: 10,
      amount: [Object],
      description: 'Bob to Alice'
    }
  ]
}
Query: insert into `transfers` (`from_account`, `to_account`, `amount`, `description`) values (?, ?, ?, ?) -- params: [10, 9, "0.50", "Alice to Bob"]
Query: select `id`, `from_account`, `to_account`, `amount`, `description` from `transfers` where `transfers`.`id` = ? -- params: [8]
cast called: {
  field: {
    name: 'id',
    type: 'UINT64',
    table: 'transfers',
    orgTable: 'transfers',
    database: 'test',
    orgName: 'id',
    columnLength: 20,
    charset: 63,
    flags: 49703
  },
  value: '8'
}
cast called: {
  field: {
    name: 'from_account',
    type: 'INT64',
    table: 'transfers',
    orgTable: 'transfers',
    database: 'test',
    orgName: 'from_account',
    columnLength: 20,
    charset: 63,
    flags: 36865
  },
  value: '10'
}
cast called: {
  field: {
    name: 'to_account',
    type: 'INT64',
    table: 'transfers',
    orgTable: 'transfers',
    database: 'test',
    orgName: 'to_account',
    columnLength: 20,
    charset: 63,
    flags: 36865
  },
  value: '9'
}
cast called: {
  field: {
    name: 'amount',
    type: 'DECIMAL',
    table: 'transfers',
    orgTable: 'transfers',
    database: 'test',
    orgName: 'amount',
    columnLength: 22,
    charset: 63,
    decimals: 6,
    flags: 36865
  },
  value: '0.500000'
}
cast called: {
  field: {
    name: 'description',
    type: 'VARCHAR',
    table: 'transfers',
    orgTable: 'transfers',
    database: 'test',
    orgName: 'description',
    columnLength: 1020,
    charset: 255,
    flags: 4097
  },
  value: 'Alice to Bob'
}
{
  aliceToBobTransfer: [
    {
      id: 8,
      fromAccount: 10,
      toAccount: 9,
      amount: [Object],
      description: 'Alice to Bob'
    }
  ]
}
Query: select `id`, `from_account`, `to_account`, `amount`, `description`, cast(`fromAccount` as json), cast(`toAccount` as json) from (select `transfers`.`id`, `transfers`.`from_account`, `transfers`.`to_account`, `transfers`.`amount`, `transfers`.`description`, `transfers`.`fromAccount`, if(count(`transfers_toAccount`.`id`) = 0, '[]', json_arrayagg(json_array(`transfers_toAccount`.`id`, `transfers_toAccount`.`balance`, `transfers_toAccount`.`name`))) as `toAccount` from (select `transfers`.`id`, `transfers`.`from_account`, `transfers`.`to_account`, `transfers`.`amount`, `transfers`.`description`, if(count(`transfers_fromAccount`.`id`) = 0, '[]', json_arrayagg(json_array(`transfers_fromAccount`.`id`, `transfers_fromAccount`.`balance`, `transfers_fromAccount`.`name`))) as `fromAccount` from `transfers` left join `accounts` `transfers_fromAccount` on `transfers`.`from_account` = `transfers_fromAccount`.`id` group by `transfers`.`id`) `transfers` left join `accounts` `transfers_toAccount` on `transfers`.`to_account` = `transfers_toAccount`.`id` group by `transfers`.`id`) `transfers` where `transfers`.`id` = ? limit ? -- params: [7, 1]
cast called: {
  field: {
    name: 'id',
    type: 'UINT64',
    table: 'transfers',
    orgTable: 'transfers',
    database: 'test',
    orgName: 'id',
    columnLength: 20,
    charset: 63,
    flags: 32801
  },
  value: '7'
}
cast called: {
  field: {
    name: 'from_account',
    type: 'INT64',
    table: 'transfers',
    orgTable: 'transfers',
    database: 'test',
    orgName: 'from_account',
    columnLength: 20,
    charset: 63,
    flags: 36865
  },
  value: '9'
}
cast called: {
  field: {
    name: 'to_account',
    type: 'INT64',
    table: 'transfers',
    orgTable: 'transfers',
    database: 'test',
    orgName: 'to_account',
    columnLength: 20,
    charset: 63,
    flags: 36865
  },
  value: '10'
}
cast called: {
  field: {
    name: 'amount',
    type: 'DECIMAL',
    table: 'transfers',
    orgTable: 'transfers',
    database: 'test',
    orgName: 'amount',
    columnLength: 22,
    charset: 63,
    decimals: 6,
    flags: 36865
  },
  value: '1.000000'
}
cast called: {
  field: {
    name: 'description',
    type: 'VARCHAR',
    table: 'transfers',
    orgTable: 'transfers',
    database: 'test',
    orgName: 'description',
    columnLength: 1020,
    charset: 255,
    flags: 4097
  },
  value: 'Bob to Alice'
}
cast called: {
  field: {
    name: 'cast(fromAccount as json)',
    type: 'JSON',
    columnLength: 4294967292,
    charset: 255,
    decimals: 31,
    flags: 128
  },
  value: '[[9, 1.23, "Bob"]]'
}
cast called: {
  field: {
    name: 'cast(toAccount as json)',
    type: 'JSON',
    columnLength: 4294967292,
    charset: 255,
    decimals: 31,
    flags: 128
  },
  value: '[[10, 4.56, "Alice"]]'
}
TypeError: value.split is not a function or its return value is not iterable
    at MySqlCustomColumn.fromDriver (**/proj/src/db.ts:28:30)
    at MySqlCustomColumn.mapFromDriverValue (**/proj/node_modules/src/mysql-core/columns/custom.ts:89:52)
    at mapRelationalRow (**/proj/node_modules/src/relations.ts:556:66)
    at mapRelationalRow (**/proj/node_modules/src/relations.ts:526:8)
    at <anonymous> (**/proj/node_modules/src/mysql-core/query-builders/query.ts:111:39)
    at Array.map (<anonymous>)
    at <anonymous> (**/proj/node_modules/src/mysql-core/query-builders/query.ts:111:26)
    at PlanetScalePreparedQuery.execute (**/proj/node_modules/src/planetscale-serverless/session.ts:51:11)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at test (**/proj/src/db.ts:143:35)

Important things to note in the output:

The monetary custom type's toDriver/fromDriver work fine when used in normal queries
When executing the relational query for bobToAliceWithRelations, drizzle seems to use cast(toAccount as json) to get the related record
- The amount in the resulting JSON object has been implicitly cast to a floating point JSON number, which defeats the purpose of using a DECIMAL
- fromDriver is called on the amount from the JSON-encoded version of toAccount, which is now a number
- This violates the type that fromDriver(value: string) expects, based on the monetary customType's driverData being a string
- The fromDriver tries and fails to call split() on value, which is actually a number

Expected behavior

Custom types should behave the same in both normal and relational queries.

Environment & setup

No response

ngregrichardson commented 1 year ago

I just ran into the problem with a bit of a simpler setup:

export const addresses = pgTable('addresses', {
  id: uuid('id').defaultRandom().primaryKey(),
  latitude: decimal('latitude', {
    precision: 8,
    scale: 6,
  }).notNull(),
  longitude: decimal('longitude', {
    precision: 9,
    scale: 6,
  }).notNull()
});

export const userAddresses = pgTable(
  'user_addresses',
  {
    addressId: uuid('address_id')
      .notNull()
      .references(() => addresses.id, {
        onDelete: 'cascade',
      }),
    name: text('name'),
  },
  (table) => ({
    addressIdUserIdNamePk: primaryKey(table.addressId)
  }),
);

With this setup, if I just query addresses like

const regular = (
      await this.drizzleService.db.select().from(addresses).limit(1)
    )[0];

I get

{
      "id": "21c0ff1e-3065-4457-bd48-ab5ad05afc55",
      "latitude": "30.241401",
      "longitude": "-97.752413"
}

Using the RQB like

const rqb = await this.drizzleService.db.query.addresses.findFirst();

also gives the same response. However, when you query using the RQB relation, like

const rqb = await this.drizzleService.db.query.userAddresses.findFirst({
      with: {
        address: true,
      },
    });

you get

{
      "addressId": "97ee01f0-2ab1-479b-9dc9-bdfeba2607d8",
      "address": {
        "id": "97ee01f0-2ab1-479b-9dc9-bdfeba2607d8",
        "latitude": 39.952406,
        "longitude": -75.187326
       }
}

Angelelz commented 10 months ago

This issue is very interesting. The main problem is that mysql will encode decimal as regular numbers when serializing to json. Since the RQB relies on json aggregation to be fast, it ends up receiving a number instead of a string. I don't think there's a workaround right now, other that just using the regular crud API.

I just opened a PR that fixes this issue for decimal in #1688, in your case my fix won't work because your column is not exactly decimal. My fix is going to look specifically for decimal columns and will cast it to char at the database, that way it's serialized to json properly.

In your case you are going to have to wait for #1694. That's PR that fixes another issue when using the extras columns in RQB. When that PR is merged, you'll have to manually cast your column to char and user mapWith to parse it with you column decoder accordingly. Like this:

const bobToAliceWithRelations = await db.query.transfers.findFirst({
    where: eq(transfers.id, parseInt(bobToAliceId)),
    with: {
      fromAccount: {
        columns: { balance: false },
        extras: { balance: sql`cast(${account.balance} to char)`.mapWith(account.balance).as('balance') }
      },
      toAccount: {
        columns: { balance: false },
        extras: { balance: sql`cast(${account.balance} to char)`.mapWith(account.balance).as('balance') }
      },
    },
  })

drizzle-team / drizzle-orm