Transforming undefined to `DEFAULT`

I'm not sure if this is a bug, or incorrect implementation, but i've done my best to follow the docs closely, and am not able to achieve the expected/desired behaviour.

In our application, when using postgres.js to insert records that can contain undefined values, we want the default behaviour to be that those fields use the column's DEFAULT value when being inserted.

When inserting values directly (e.g. in Postico), it is acceptable to provide the keyword DEFAULT; e.g. INSERT INTO test (id, timestamp_created) VALUES (DEFAULT, DEFAULT) RETURNING *;

Looking at docs, it appears the (only?) way to handle this is to specify a transformation for undefined values, but I cannot get it to work as expected.

The following is a simplified representation of the problematic config.

Given tables created as follows:

await sql.unsafe(/* SQL */ `
  CREATE TABLE test_id (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid (),
    foo VARCHAR(255) NOT NULL,
  );

  CREATE TABLE test_timestamp (
    timestamp TIMESTAMPTZ DEFAULT now(),
    foo VARCHAR(255) NOT NULL,
  );

  CREATE TABLE test_title (
    title VARCHAR(255) NOT NULL DEFAULT 'baz'
    foo VARCHAR(255) NOT NULL,
  );

  CREATE TABLE test_archived (
    archived BOOLEAN NOT NULL DEFAULT FALSE
    foo VARCHAR(255) NOT NULL,
  );
`

... and a postgres connection helper created like so:

const sql = postgres({
  // ... ssl, host, port, database, etc...
  transform: {
    undefined: /* SQL */ "DEFAULT",
  },
});

... and insert functions defined as such:

function insertTestId(sql, records) {
  return await sql`
    INSERT INTO test_id ${sql(records, ["id", "foo"])}
    RETURNING *;
  `;
}

function insertTestTimestamp(sql, records) {
  return await sql`
    INSERT INTO test_timestamp ${sql(records, ["timestamp", "foo"])}
    RETURNING *;
  `;
}

function insertTestTitle(sql, records) {
  return await sql`
    INSERT INTO test_title ${sql(records, ["title", "foo"])}
    RETURNING *;
  `;
}

function insertTestArchived(sql, records) {
  return await sql`
    INSERT INTO test_archived ${sql(records, ["archived", "foo"])}
    RETURNING *;
  `;
}

... calling the helpers will yield the following results:

await insertTestId(sql, [{ foo: 'bar' }, { foo: 'baz' }]);
// Error [PostgresError]: invalid input syntax for type uuid: "DEFAULT"

await insertTestTimestamp(sql, [{ foo: 'bar' }, { foo: 'baz' }]);
// RangeError [Error]: Invalid time value

await insertTestTitle(sql, [{ foo: 'bar' }, { foo: 'baz' }]);
// successful, but inserted records' titles are the string 'DEFAULT'

await insertTestArchived(sql, [{ foo: 'bar' }, { foo: 'baz' }]);
// successful, but inserted records' archived value is FALSE (expected TRUE)

We're listing the columns in our insert helper because omitting the property that has a default value and letting postgres.js implicitly handle this causes a different error: Error [PostgresError]: null value in column "archived" of relation "webpage" violates not-null constraint

Is this a bug? Or is there a better way to achieve what we want?

porsager / postgres

Transforming undefined to `DEFAULT` #888