[BUG]: Custom types not working in `with` queries

[roman910dev]

What version of drizzle-orm are you using?

0.29.0

What version of drizzle-kit are you using?

0.20.6

Describe the Bug

I have the following custom type defined to store hex colors:

const color = customType<{ data: string; driverData: Buffer }>({
    dataType: () => 'binary(3)',
    fromDriver(value: Buffer) {
        return '#' + value.toString('hex')
    },
    toDriver(value: string) {
        return sql`UNHEX(${value.replace('#', '')})`
    },
})

It works fine when I directly query the table:

 {
    id: '4uUw3v2NwaSiNbFSEYDCJu32YvG2',
    name: 'Exclusiverse',
    slug: 'exclusiverse',
    logo: 'https://www.exclusiverse.club/_next/image?url=%2F_next%2Fstatic%2Fmedia%2Flogo-exclusiverse.f5e6e6f1.webp&w=640&q=75',
    website: 'https://www.exclusiverse.club',
    icon: 'https://www.exclusiverse.club/favicon.ico',
    color: '#ff2773',       // <------------------- proper result
}

However, when I get the table through a with query from another table:

{
    id: '4uUw3v2NwaSiNbFSEYDCJu32YvG2',
    wallet: '0x9a62d71703a68388e2b9e1309b7ea3cf4692f0b8',
    username: 'bellc_emp',
    fullName: "Bellcaire d'Empordà",
    email: 'bellcairemoto@gmail.com',
    role: 'enterprise',
    bio: '',
    enterprise: {
        id: '4uUw3v2NwaSiNbFSEYDCJu32YvG2',
        name: 'Exclusiverse',
        slug: 'exclusiverse',
        logo: 'https://www.exclusiverse.club/_next/image?url=%2F_next%2Fstatic%2Fmedia%2Flogo-exclusiverse.f5e6e6f1.webp&w=640&q=75',
        website: 'https://www.exclusiverse.club',
        icon: 'https://www.exclusiverse.club/favicon.ico',
        color: '#base64:type254:/ydz',      // <---------- unexpected behavior
    },
}

I have observed this behavior with other custom types too. In my case all of them are from binary columns.

Expected behavior

The expected behavior would be to get something like:

{
    id: '4uUw3v2NwaSiNbFSEYDCJu32YvG2',
    wallet: '0x9a62d71703a68388e2b9e1309b7ea3cf4692f0b8',
    username: 'bellc_emp',
    fullName: "Bellcaire d'Empordà",
    email: 'bellcairemoto@gmail.com',
    role: 'enterprise',
    bio: '',
    enterprise: {
        id: '4uUw3v2NwaSiNbFSEYDCJu32YvG2',
        name: 'Exclusiverse',
        slug: 'exclusiverse',
        logo: 'https://www.exclusiverse.club/_next/image?url=%2F_next%2Fstatic%2Fmedia%2Flogo-exclusiverse.f5e6e6f1.webp&w=640&q=75',
        website: 'https://www.exclusiverse.club',
        icon: 'https://www.exclusiverse.club/favicon.ico',
        color: '#ff2773',       // <---------- expected behavior
    },
}

Environment & setup

No response

[jlgalarza3]

Same issue here! Did you find a hack to solve it?

[jlgalarza3]

Same issue here! Did you find a hack to solve it?

It is definitely a bug. This problem is happening because the queries with with fail to do the fromDriver mapping of the custom types.

I solved this problem by doing the mapping myself in the customType definition. In my case, it was a bytea type, so for this type, I did it like this

export const bytea = customType<PgBytea>({
  dataType: () => "bytea",
  fromDriver: value =>
    typeof value === "string"
      ? Buffer.from(value.slice(2), "hex")
      : value,
});

[roman910dev]

I don't really understand what you are trying to do there. It does not work for me at least.

In my case, it looks like the fromDriver call is being skipped for fields nested in withs.

What I do as workaround is not select the column directly but instead include it in the extras like this:

await db.query.users.findFirst({
    with: {
        enterprise: {
            columns: {color: false},
            extras: {color: sql<string>`CONCAT('#', LOWER(HEX(color)))`.as('color')}
        }
    }
})