2 replies

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

accountNumber, beneficiaryName, bankId

. I have overridden the

toDriver

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

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()

allowedBeneficiaries: beneficiaryAccount('allowed_beneficiaries').array()

which means the column type is

payment_config.beneficiary_account[]

payment_config.beneficiary_account[]

.

Now when I try to insert using drizzle and an array of

BeneficiaryAccount

BeneficiaryAccount

, the data does not serialise correctly and the resultant error is

malformed record literal: "[object Object]"

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[]

allowedBeneficiaries: BeneficiaryAccount[]

is not being converted correctly.

If I change my

toDriver

toDriver

implementation to return a string (e.g.

(${accountNumber},${beneficiaryName},${bankId})

(${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?

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

accountNumber, beneficiaryName, bankId

. I have overridden the

toDriver

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

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()

allowedBeneficiaries: beneficiaryAccount('allowed_beneficiaries').array()

which means the column type is

payment_config.beneficiary_account[]

payment_config.beneficiary_account[]

.

Now when I try to insert using drizzle and an array of

BeneficiaryAccount

BeneficiaryAccount

, the data does not serialise correctly and the resultant error is

malformed record literal: "[object Object]"

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[]

allowedBeneficiaries: BeneficiaryAccount[]

is not being converted correctly.

If I change my

toDriver

toDriver

implementation to return a string (e.g.

(${accountNumber},${beneficiaryName},${bankId})

(${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?

Converting drizzle custom type to Postgres composite type fails for arrays

Converting drizzle custom type to Postgres composite type fails for arrays

Similar Threads

Similar Threads

Similar Threads