[BUG]: Prepared statement with placeholder error: could not determine data type of parameter

[johnpgr]

What version of drizzle-orm are you using?

0.26.5

What version of drizzle-kit are you using?

0.18.1

Describe the Bug

Description: The problem arises when attempting to use execute prepared() queries with null placeholder() values. The error message displayed is as follows:

Query: select "id", "author_name", "slug", "title", "description", "body", "created_at", "updated_at" from "article" where ($1 IS NULL OR "article".
"author_name" = $2) limit $3 offset $4 -- params: [null, null, 20, 0]

"error: could not determine data type of parameter $1."

const articlePreparedQuery = db
        .select()
        .from(article)
        .where(
            sql`(${placeholder("author")} IS NULL OR ${
                article.authorName
            } = ${placeholder("author")})`
        )
        .limit(placeholder("limit"))
        .offset(placeholder("offset"))
        .prepare("article_prepared");
        
const author = (req.query.author as string) || null;
        const limit = parseInt(req.query.limit as string) || 20;
        const offset = parseInt(req.query.offset as string) || 0;
        const article = await articlePreparedQuery.execute({
            author,
            limit,
            offset,
        });

minimal repo to reproduce the bug

Steps to Reproduce:

• Setup a node-postgres connection with drizzle
• Create a prepared query with placeholder values that can be NULL
• Execute this query with a null value

Expected behavior

The query should execute and evaluate the NULL parameter correctly and return the values

Environment & setup

• Node.js v20.2.0
• Node-postgres v8.11.0
• Drizzle-Orm v0.26.5

[johnpgr]

Update: this bug happens when executing with string parameters as well, and also happens in postgres-js.

const articlesWithAuthor = await articlePreparedQueryNodePg.execute({
            author: "John Doe",
            limit: 10,
            offset: 0,
        });

const allArticles = await articlePreparedQueryNodePg.execute({
            author: null,
            limit: 10,
            offset: 0,
        });
assert.ok(articlesWithAuthor); //fails
assert.ok(allArticles); //fails

Added a docker compose and tests to the example repo

[mirorauhala]

I've run into the same issue when concatenating strings.

Postgres: 16.1 Drizzle-orm: 0.29.1 Drizzle-kit: 0.20.6 postgres.js: 3.4.3

const prefix = "PREFIX-"; // changes dynamically
await db.execute(
  sql`SELECT concat(${prefix}, ${table.name}) AS name FROM ${table}`
);

Error: could not determine data type of parameter $1

I was able to temporarily fix this with:

const prefix = "PREFIX-"; // changes dynamically
await db.execute(
  sql`SELECT ${prefix} || ${table.name} AS name FROM ${table}`
);

[Angelelz]

There are some cases where you need to tell postgres what is the type of your parameter. See this SO answer for a nice explanation. For both null and dynamic test string this is how you can make it work:

const prefix = "PREFIX-"; // changes dynamically
await db.execute(
  sql`SELECT concat(${prefix}::text, ${table.name}) AS name FROM ${table}`
);

Even if prefix is null. I just tested this manually.