sql.json(null) should produce 'null'::jsonb instead of null

[thecodeboss]

Expected Behavior

The query

sql`
  SELECT ${sql.json(null)}
`;

should result in the SQL

SELECT 'null'::jsonb

Current Behavior

The above query actually gets translated to

SELECT NULL

NULL and 'null'::jsonb are different, and this can have a bad side effect such as when using jsonb_set. In the following example, we have a jsonb {"key": "value"}, and try to set the value to null, which should result in a new jsonb {"key": null}

sql`
  SELECT jsonb_set('{"key": "value"}'::jsonb, '{key}', ${sql.json(null)})
`

However, it actually results in NULL instead of {"key": null} since Slonik passes in NULL instead of 'null'::jsonb. Note that you could also simply pass in the string 'null' and it would be implicitly casted to jsonb here.

Possible Solution

I haven't followed the code path for how sql.json gets translated, but I did find a test case that is specifically checking null but expecting what I consider an incorrect result.
The test is:

test('passes null unstringified', (t) => {
  const query = sql`SELECT ${sql.json(null)}`;

  t.deepEqual(query, {
    sql: 'SELECT $1',
    type: SqlToken,
    values: [
      null,
    ],
  });
});

but I believe it should be:

test('passes null unstringified', (t) => {
  const query = sql`SELECT ${sql.json(null)}`;

  t.deepEqual(query, {
    sql: 'SELECT $1',
    type: SqlToken,
    values: [
      'null',
    ],
  });
});

Workaround

I ran into this issue when trying to do something like

sql`SELECT jsonb_set(data, path, ${sql.join(value)}`

where value can sometimes be null. Instead I've rewritten this as:

sql`SELECT jsonb_set(data, path, ${value === null ? sql`'null'::jsonb` : sql.json(value)})`

The ternary works, but it would be simpler if sql.json handled null differently.

[gajus]

@thecodeboss Please review #333

[gajus]

🎉 This issue has been resolved in version 28.0.0 🎉

The release is available on:

• npm package (@latest dist-tag)
• GitHub release

Your semantic-release bot 📦🚀