[BUG]: cannot insert jsonb in PGlite

[jamesarosen]

Report hasn't been filed before.

• I have verified that the bug I'm about to report hasn't been filed before.

What version of drizzle-orm are you using?

0.38.2

What version of drizzle-kit are you using?

0.38.2

Other packages

@electric-sql/[email protected],[email protected]

Describe the Bug

Given a schema:

export const events = sm.table('events', {
  metadata: jsonb('metadata').default({}).notNull(),
});

I can insert rows with jsonb:

await db.insert(events).values([
  { metadata: {} },
  { metadata: { foo: 'foo 1' } },
  { metadata: { foo: 'foo 2' } },
]);

And I can retrieve rows with jsonb:

const events = await db.select().from(events);
// [ { metadata: {} }, { metadata: { "foo": "foo 1" } }, { metadata: { "foo": "foo 2" } } ]

But I can't use the ->> operator to query:

const events = await db.select().from(events).where(sql`metadata->>'foo' = ${'foo 1'}`);
// []

I verified that PGlite itself handles this fine:

const db = new PGlite(...);
await db.exec(`
  create table events(metadata jsonb);
  insert into events(metadata) values
    ('{}'::jsonb)
  , ('{ "foo": "foo 1" }'::jsonb)
  , ('{ "foo": "foo 2" }'::jsonb)
`)
await db.select(`select * from events where metadata->>'foo' is not null`)
// { rows: [ { metadata: '{"foo":"foo 1"}', } { metadata: '{"foo":"foo 2"}', } ], ... }

[jamesarosen]

This was caused by #724

It seems to still be happening when using PGlite on [email protected]

See electric-sql/pglite#315

If I set debug: 1 on the PGlite instance, I get the following logs:

await db.insert(events).values({
  metadata: { via: 'js-object' },
});
/*
runQuery insert into "test"."events" ("metadata") values ($1) [ '{"via":"js-object"}' ] {
  rowMode: 'object',
  parsers: {
    '1082': [Function: 1082],
    '1114': [Function: 1114],
    '1184': [Function: 1184],
    '1186': [Function: 1186]
  }
}
*/

await db.insert(events).values({
  metadata: sql`${{ via: 'sql-jsonb' }}::jsonb`,
});
/*
runQuery insert into "test"."events" ("metadata") values ($1) [ { "via": "sql-jsonb" } ] {
  rowMode: 'object',
  parsers: {
    '1082': [Function: 1082],
    '1114': [Function: 1114],
    '1184': [Function: 1184],
    '1186': [Function: 1186]
  }
}
*/