@vercel/postgres DELETE not working as expected

[AndrewOt]

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

1. 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'
}

2. 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 :(
#229