Summary

I have some data in my postgres database and when I run a delete with import { sql } from "@vercel/postgres"; statement it does not work. I am trying to build a Vercel cron job, so I created an api route in app/api/cron/route.ts with a DELETE endpoint.

Here is an example of the data that I am attempting to delete

I am doing a query above to get the encounterIds I want to delete (and that works great!). Then I'm filtering out the ids that have indicatorIds that I don't want to delete by storing it in a map like so

  deleteTheseObjects.forEach((values, key, thisMap) => {
    if (
      values.includes(DONT_DELETE_ME_ID_ONE) ||
      values.includes(DONT_DELETE_ME_ID_TWO)
    ) {
      thisMap.delete(key);
    }
  });

This also works as expected:

Map(2) {
  '6ee787ad-3851-4ebb-8d2b-16ca2f8c211c' => [ 8 ],
  'b73a4db4-a25e-4dc9-accd-d97bbb521c0a' => [ 1, 4, 15, 19 ]
}

Then I'm taking the keys from the map, putting them into an array and putting them in this delete statement:

  const result =
    await sql`DELETE FROM visitindicatorsencounter WHERE encounterId IN (${idsToString(
      Array.from(deleteTheseObjects.keys())
    )}) AND indicatorId IN (1, 2, 3, 4, 5, 6, 14, 17, 19)`;

With one item in the deleteTheseObjects map, it works great. This is what is generated from the code above

DELETE FROM visitindicatorsencounter WHERE encounterId IN (6ee787ad-3851-4ebb-8d2b-16ca2f8c211c) AND indicatorId IN (1, 2, 3, 4, 5, 6, 14, 17, 19)

But the result with more than one encounterId does not work :(

Here is the raw command text

DELETE FROM visitindicatorsencounter WHERE encounterId IN (6ee787ad-3851-4ebb-8d2b-16ca2f8c211c,b73a4db4-a25e-4dc9-accd-d97bbb521c0a) AND indicatorId IN (1, 2, 3, 4, 5, 6, 14, 17, 19)

Approaches

Adding quotes: I added quotes around each of the uuids but got variants of this error:

⨯ error: column "$1" does not exist
at sn.parseErrorMessage (webpack-internal:///(rsc)/./node_modules/@neondatabase/serverless/index.mjs:1191:6)
at sn.handlePacket (webpack-internal:///(rsc)/./node_modules/@neondatabase/serverless/index.mjs:1152:48)
at sn.parse (webpack-internal:///(rsc)/./node_modules/@neondatabase/serverless/index.mjs:1136:63)
at x.eval (webpack-internal:///(rsc)/./node_modules/@neondatabase/serverless/index.mjs:1198:74)
at x.emit (webpack-internal:///(rsc)/./node_modules/@neondatabase/serverless/index.mjs:409:63)
at WebSocket.eval (webpack-internal:///(rsc)/./node_modules/@neondatabase/serverless/index.mjs:997:80)
at callListener (webpack-internal:///(rsc)/./node_modules/ws/lib/event-target.js:290:14)
at WebSocket.onMessage (webpack-internal:///(rsc)/./node_modules/ws/lib/event-target.js:209:9)
at WebSocket.emit (node:events:514:28)
at Receiver.receiverOnMessage (webpack-internal:///(rsc)/./node_modules/ws/lib/websocket.js:1192:20)
at Receiver.emit (node:events:514:28)
at Receiver.dataMessage (webpack-internal:///(rsc)/./node_modules/ws/lib/receiver.js:545:14)
at Receiver.getData (webpack-internal:///(rsc)/./node_modules/ws/lib/receiver.js:478:17)
at Receiver.startLoop (webpack-internal:///(rsc)/./node_modules/ws/lib/receiver.js:167:22)
at Receiver._write (webpack-internal:///(rsc)/./node_modules/ws/lib/receiver.js:93:10)
at writeOrBuffer (node:internal/streams/writable:392:12)
at _write (node:internal/streams/writable:333:10)
at Writable.write (node:internal/streams/writable:337:10)
at TLSSocket.socketOnData (webpack-internal:///(rsc)/./node_modules/ws/lib/websocket.js:1286:35)
at TLSSocket.emit (node:events:514:28)
at addChunk (node:internal/streams/readable:324:12)
at readableAddChunk (node:internal/streams/readable:297:9)
at Readable.push (node:internal/streams/readable:234:10)
at TLSWrap.onStreamRead (node:internal/stream_base_commons:190:23)
at TLSWrap.callbackTrampoline (node:internal/async_hooks:130:17) {
length: 102,
severity: 'ERROR',
code: '42703',
detail: undefined,
hint: undefined,
position: '56',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'parse_relation.c',
line: '3722',
routine: 'errorMissingColumn'
}

Individual requests: In the code above I am building one request (which is preferable) but tried adding all of the promises to an array and waiting for all them to finish with Promise.all(...). So I added const client = db.connect() to the top of my function and built a list of requests like so
```
await Promise.all(
Array.from(deleteTheseObjects.keys()).map((encounterId) => {
  console.log(
    `DELETE FROM visitindicatorsencounter WHERE encounterId="${encounterId}" AND indicatorId IN (1, 2, 3, 4, 5, 6, 14, 17, 19)`
  );
  return client.sql`DELETE FROM visitindicatorsencounter WHERE encounterId="${encounterId}" AND indicatorId IN (1, 2, 3, 4, 5, 6, 14, 17, 19)`;
})
);
```
This did not error, but like the above examples, did not accomplish what I wanted it to do either. No rows are affected.

So I'm kinda stuck. I've made sure I was using the correct method to avoid the cache. With sql command working in the vercel dashboard, I'm not sure what the issue is.

Related Issues

I didn't look super hard, but this is the closest issue I could find and it seems that the issue was magically fixed (seems to be cache related)... so not much help for me :( https://github.com/vercel/storage/issues/229

vercel / storage

@vercel/postgres DELETE not working as expected #694

Summary

Approaches

Related Issues