drizzle-team / drizzle-team/drizzle-orm

[BUG]: Incorrect JSONB parameter binding in prepared statements using @> operator fails JSON cast

Open
#4,935 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

When using Drizzle ORM to perform a prepared query (.prepare().execute()) on a PostgreSQL jsonb column, parameter binding for JSON values using the @> operator fails. The driver incorrectly binds the JSON value as a native array rather than a formatted JSON string, which causes a PostgreSQL cast error. This issue occurs specifically when attempting to bind a JavaScript array that is first stringified and then cast to jsonb within the SQL template.

Reproduction:
Generic Schema Definition
```typescript
import { pgTable, serial, text, jsonb } from "drizzle-orm/pg-core";

export const genericTable = pgTable("generic_table", {
id: serial("id").primaryKey(),
name: text("name").notNull().unique(),
data: jsonb("data").$type(),
});
```

Generic Reproduction Code
```typescript
import { sql, or, eq } from "drizzle-orm";

// Assuming a database connection 'db' and 'genericTable' exists.
const queryFunction = async (nameOrData: string) => {
const aliasesJsonPlaceholder = sql.placeholder('data_json');

const preparedQuery = db
.select()
.from(genericTable)
.where(
or(
eq(genericTable.name, nameOrData),
sql`${genericTable.data} @> ${aliasesJsonPlaceholder}::jsonb`
)
)
.limit(1)
.prepare();

const result = await preparedQuery.execute({
data_json: JSON.stringify([nameOrData]),
});

return result;
};
```

Command Execution
```typescript
// Assuming a record exists with 'name' = 'test_name' and 'data' = '["test_data"]'
await queryFunction('test_data');
```

Expected Behavior:
The query should execute successfully, binding the data_json parameter as a single JSON string (e.g., "[\"test_data\"]"). The @> operator should then correctly use this to check for containment in the jsonb column.
Actual Behavior:
The query fails with the following error, showing that the driver is attempting to cast a native array to jsonb:
Failed query: ...where ("generic_table"."name" = $1 or "generic_table"."data" @> $2::jsonb) limit $3
params: test_data,["test_data"],1

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.