My goal is to utilize Drizzle for inserting data into a table and updating it if the key already exists. In MySQL, the code would look like this:
INSERT INTO myTable1(field1,field2,field3,field4)
SELECT fieldOne,fieldTwo,fieldThree,fieldFour
FROM myTable2
ON DUPLICATE KEY UPDATE
field3 = VALUES(field3),
field4 = VALUES(field4);
For Drizzle-ORM, I expected something similar:
const insertUpdateTable = async (
db: DBType,
sourceTable: number,
targetTable: number,
) => {
const sourceRecords = db.select({
fieldOne: myTable2.fieldOne,
fieldTwo: myTable2.fieldTwo,
fieldThree: myTable2.fieldThree,
fieldFour: myTable2.fieldFour
})
.from(myTable2);
return await db.insert(myTable1)
.values(sourceRecords)
.onDuplicateKeyUpdate({
set: {
field1: sourceRecords.fieldOne,
field2: sourceRecords.fieldTwo
}
})
}
However, WebStorm is indicating errors with my values(sourceRecords) statement and the onDuplicateKeyUpdate fields. My aim is to maintain type safety by using Drizzle rather than resorting to the Magic SQL operator. Any suggestions or advice?