Error: bind message supplies 1 parameters, but prepared statement "" requires 0

[mattiaz9]

Steps to reproduce

Adding one or more parameters to the query cause the error: NeonDbError: db error: ERROR: bind message supplies 1 parameters, but prepared statement "" requires 0 when neonConfig.poolQueryViaFetch = true

neonConfig.poolQueryViaFetch = true
const pool = new Pool({ connectionString: env.DATABASE_URL })

const result = await pool.query(`SELECT * FROM "Client" WHERE id = '$1'`, [
  "xxxxxxxxxxxx",
])

Expected result

The same response as if the parameter were set inline like: const result = await pool.query('SELECT * FROM "Client" WHERE id = "xxxxxxxxxxxxx"')

Actual result

Throws an error

Environment

macOS Ventura 13.4.6 node 18.6 nextjs: 13.4.8-canary.2 @neondatabase/serverless: 0.4.22

Logs, links

• Caused by: ERROR: bind message supplies 1 parameters, but prepared statement "" requires 0 at eval (webpack-internal:///(sc_server)/./node_modules/@neondatabase/serverless/index.js:5886:67) at process.processTicksAndRejections (node:internal/process/task_queues:95:5) { code: '08P01' }

[jawj]

The problem occurs whether or not poolQueryViaFetch is true, and is simply that '$1' should be $1: it should not be quoted.

So you should find this works:

const result = await pool.query('SELECT * FROM "Client" WHERE id = $1', ["xxxxxxxxxxxx"])

[mattiaz9]

@jawj I'm not sure if it's a drizzle-orm problem then. Any idea why using drizzle gives that error only when poolQueryViaFetch is true?

For instance:

neonConfig.poolQueryViaFetch = false
const pool = new Pool({ connectionString: env.DATABASE_URL, ssl: false })
const db = drizzle(pool)
const result = await db.select().from(client).where(eq(client.id, "xxxxxxxxxxxxxx"))

is giving that error, but not when poolQueryViaFetch is false

[jawj]

Thanks for the follow-up. I asked the Drizzle guys, who said:

Drizzle is not covering $1 in quotes. Also, we do nothing with the driver's connection. We just use it to execute statements and get a response back. So any connection issue should be on the driver's side (not always, but 99% of the time). In this case, it should not be related to drizzle.

I can't reproduce your issue with the driver alone, though. Are you able to provide a simple, complete repro we can take a look at?

[mattiaz9]

@jawj here is the repo: https://github.com/mattiaz9/test-drizzle-neon

Just go to http://localhost:3000/api/clients/d7ik35aggjoehkca88am4fjm and it should give you the error.

How can i give you the connection string?

[mattiaz9]

Or maybe if you can use your own test db you can use this SQL:

CREATE TABLE "public"."Client" (
    "id" varchar(32) NOT NULL,
    "createdAt" timestamp(3) NOT NULL DEFAULT now(),
    "updatedAt" timestamp(3) NOT NULL DEFAULT now(),
    "name" varchar(255) NOT NULL DEFAULT ''::character varying,
    PRIMARY KEY ("id")
);

INSERT INTO "public"."Client" ("id", "createdAt", "updatedAt", "name") VALUES
('d7ik35aggjoehkca88am4fjm', '2023-06-26 16:50:56.156', '2023-06-26 16:50:56.156', 'John');

[mattiaz9]

I don't know the implications of doing a regex replace of the query string, but this function should do it:

function parseQuery(query: string) {
  return query.replace(/'\$(\d+)'/g, "$$$1")
}

So basically wrapping the strings variable here should make it work: https://github.com/neondatabase/serverless/blob/main/export/httpQuery.ts#L44

...
query = parseQuery(strings);
...

I only tested it with a direct fetch request to be fair.

[mattiaz9]

Actually the real issue is another. The drizzle query string is correct but the object passed to the query function doesn't include the values field, which is passed instead as the second parameter.

Since this library checks if the typeof config is string, it expects the object to include values.

A quick fix should be something like this:

const queryValues = (typeof config === 'string' ? values : config.values ?? values) ?? [];

[jawj]

Thanks for the repo. I think this is fixed in 0.4.23.

Usually one calls query({ text: 'SELECT ...', values: [...]}) or query('SELECT ...', [...]), but drizzle does an odd hybrid: query({ text: 'SELECT ...' }, [...]). The http driver didn't deal with this appropriately, but now it does.

Let me know how you get on.

EDIT: somehow I missed your last message explaining the problem, but yes, that was exactly it.

[mattiaz9]

Yes, looks like it works in 0.4.23. Thanks!