Craig Posts - Answer Overflow

Craig

•Created by Craig on 10/6/2023 in #help

Insert Schemas with onConflictDoUpdate

I'm trying to leverage the onConflictDoUpdate method to update a record if it already exists. The problem is that I create an insert schema with drizzle-zod that enforces the non nullable fields are set. In the case where the record already exists, it is valid to pass in partial data to my function, but that then fails the insert schema validation. Is there any way to parse the insert schema only if there is no conflict? My current workaround is to separate the insert and update logic.

2 replies

DTDrizzle Team

•Created by Craig on 8/25/2023 in #help

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?

3 replies

Gaming

Programming