[BUG]: JSONB select behaviour difference between postgres drivers

[LeonAlvarez]

What version of drizzle-orm are you using?

0.26.5

What version of drizzle-kit are you using?

No response

Describe the Bug

JSONB queries have different behavior on pg and postgres.js drivers. On postgres.js it works correctly (If change to jsonb from #666 is present), but it gives an error on pg (without any change to internals)

test.serial('json object insert', async (t) => {
	const { db } = t.context;

	const bar = 33
	const foo = 'bar';

	await db.insert(metaDataTable).values({ data: {foo, bar} });
	const result = await db.select({
		id: metaDataTable.id,
		data: metaDataTable.data,
	}).from(metaDataTable).where(and(sql`data->'foo' = ${foo}`, sql`data->'bar' = ${bar}`));
	
	t.deepEqual(result, [{ id: 1, data: {foo: 'bar', bar: 33}}]);
});

Query produced is the same in both cases.

  {
     params: [
       'bar',
       33,
     ],
     sql: 'select "id", "data" from "meta_data" where (data->\'foo\' = $1 and data->\'bar\' = $2)',
   }

pg error:

son object insert

 Rejected promise returned by test. Reason:

 error (DatabaseError) @Error {
   code: '22P02',
   column: undefined,
   constraint: undefined,
   dataType: undefined,
   detail: 'Token "bar" is invalid.',
   file: 'jsonfuncs.c',
   hint: undefined,
   internalPosition: undefined,
   internalQuery: undefined,
   length: 185,
   line: '627',
   position: undefined,
   routine: 'json_ereport_error',
   schema: undefined,
   severity: 'ERROR',
   table: undefined,
   where: `JSON data, line 1: bar␊
   unnamed portal parameter $1 = '...'`,
   message: 'invalid input syntax for type json',
 }

 › Parser.parseErrorMessage (/Users/leon/code/drizzle-orm/node_modules/.pnpm/pg-protocol@1.6.0/node_modules/pg-protocol/dist/parser.js:287:98)
 › Parser.handlePacket (/Users/leon/code/drizzle-orm/node_modules/.pnpm/pg-protocol@1.6.0/node_modules/pg-protocol/dist/parser.js:126:29)
 › Parser.parse (/Users/leon/code/drizzle-orm/node_modules/.pnpm/pg-protocol@1.6.0/node_modules/pg-protocol/dist/parser.js:39:38)
 › Socket.<anonymous> (/Users/leon/code/drizzle-orm/node_modules/.pnpm/pg-protocol@1.6.0/node_modules/pg-protocol/dist/index.js:11:42)

 ─

Expected behavior

When using pg driver select should work as it works on postgres.js

Environment & setup

Can use the following table to execute the above test

type MetaData = {
	foo: string;
	bar: number;
}
const metaDataTable = pgTable('meta_data', {
	id: serial('id').primaryKey(),
	data: jsonb('data').$type<MetaData>(),
});


await ctx.db.execute(
        sql`
	        create table meta_data (
		        id serial primary key,
		        data jsonb
	        )
        `,
);

[matthewwong525]

Potential related Issues / PRs:

• BUG: jsonb always inserted as a json string when using postgres-js: This issue reports a bug where inserting an object into a postgres jsonb field with db.insert only inserts a string when using the postgres-js adapter. The link is relevant because it is a similar bug related to JSONB behavior in drizzle-orm and may provide insights or potential solutions for the issue at hand.

This message was generated by AI from https://www.triagefeedback.com

[AndriiSherman]

Duplicates #724