Drizzle Team•14mo ago

Converting drizzle custom type to Postgres composite type fails for arrays

I have a postgres composite type in my database consisting of three fields, accountNumber, beneficiaryName, bankId. I have overridden the toDriver implementation to try and handle insertion of this custom type as follows:

type BeneficiaryAccountSqlValue = [
    account_number: string,
    beneficiary_name: string,
    bank_id: BankId,
];
export const beneficiaryAccount = customType<{
    data: BeneficiaryAccount;
    driverData: BeneficiaryAccountSqlValue;
}>({
    dataType() {
        return 'payment_config.beneficiary_account';
    },
    toDriver({
        accountNumber,
        beneficiaryName,
        bankId,
    }: BeneficiaryAccount): SQL<BeneficiaryAccountSqlValue> {
        return sql<BeneficiaryAccountSqlValue>`(${accountNumber},${beneficiaryName},${bankId})`;
    },
});

type BeneficiaryAccountSqlValue = [
    account_number: string,
    beneficiary_name: string,
    bank_id: BankId,
];
export const beneficiaryAccount = customType<{
    data: BeneficiaryAccount;
    driverData: BeneficiaryAccountSqlValue;
}>({
    dataType() {
        return 'payment_config.beneficiary_account';
    },
    toDriver({
        accountNumber,
        beneficiaryName,
        bankId,
    }: BeneficiaryAccount): SQL<BeneficiaryAccountSqlValue> {
        return sql<BeneficiaryAccountSqlValue>`(${accountNumber},${beneficiaryName},${bankId})`;
    },
});

This works fine and as expected when the column I am inserting into is defined as type payment_config.beneficiary_account. The problem occurs when the column is defined as an array of that type, i.e. allowedBeneficiaries: beneficiaryAccount('allowed_beneficiaries').array() which means the column type is payment_config.beneficiary_account[]. Now when I try to insert using drizzle and an array of BeneficiaryAccount, the data does not serialise correctly and the resultant error is malformed record literal: "[object Object]". An example query that is produced is

insert into ___ (..., "allowed_beneficiaries", ...) values (default, $1, $2, $3, $4, default, default, default, default, default, default) -- params: [true, "100.00", "200.00", "{[object Object]}"]

insert into ___ (..., "allowed_beneficiaries", ...) values (default, $1, $2, $3, $4, default, default, default, default, default, default) -- params: [true, "100.00", "200.00", "{[object Object]}"]

So it looks like the parameter value for allowedBeneficiaries: BeneficiaryAccount[] is not being converted correctly. If I change my toDriver implementation to return a string (e.g. (${accountNumber},${beneficiaryName},${bankId}), then it works, except in the cases where there are special characters that need to be escaped (e.g. ").
Any idea if this is an issue on my side or a drizzle bug?

2 Replies

Andrii Sherman•14mo ago

Didn't test with postgres composite types it. I need to check

bluesky•13mo ago

is there any progress on this?

Gaming

Programming

Converting drizzle custom type to Postgres composite type fails for arrays