Subscription errors when using column transforms

[mariandaniel24]

Hello, first of all, thank you for this awesome library.

I've recently started to implement postgres.js real-time subscriptions and I've come across syntax error when using the toCamel transform when initializing the database connection.

Prior to running the code below, the database was initialized using wal_level=logical and with migration CREATE PUBLICATION alltables FOR ALL TABLES;

import postgres from "postgres";

async function main() {
  const DB_URI = "postgresql://postgres:postgres@localhost:5432/postgres";

  const sql = postgres(DB_URI, {
    transform: {
      column: {
        from: postgres.toCamel, // if I comment this out, the code works just fine, the problem is that the column names are not `camelCased`
        to: postgres.fromCamel,
      },
    },
    publications: "alltables",
  });

  // I've also tried to subscribe on "insert:company" and similar, but the error is still thrown. 
  await sql.subscribe("*", (row) => {
    console.log(row);
  });
}
main();

The code above throws a syntax error:

PostgresError: syntax error
    at ErrorResponse (/home/dani/dev/********/node_modules/postgres/cjs/src/connection.js:761:26)
    at handle (/home/dani/dev/********/node_modules/postgres/cjs/src/connection.js:468:6)
    at Socket.data (/home/dani/dev/********/node_modules/postgres/cjs/src/connection.js:312:9)
    at Socket.emit (events.js:400:28)
    at addChunk (internal/streams/readable.js:293:12)
    at readableAddChunk (internal/streams/readable.js:267:9)
    at Socket.Readable.push (internal/streams/readable.js:206:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)

The following is logged by the database when running the code above:

2022-09-03 16:04:46.820 UTC [192] LOG:  logical decoding found consistent point at 0/F294CC00
2022-09-03 16:04:46.820 UTC [192] DETAIL:  There are no running transactions.
2022-09-03 16:04:46.820 UTC [192] STATEMENT:  CREATE_REPLICATION_SLOT postgresjs_lkcvuiml6w9 TEMPORARY LOGICAL pgoutput NOEXPORT_SNAPSHOT
2022-09-03 16:04:46.824 UTC [192] ERROR:  syntax error

Let me know if you need any additional information.

[porsager]

You're welcome - and thanks :)

What an odd bug, but luckily easily reproducible and fixed - Thanks for the nice description

[mariandaniel24]

I appreciate the quick response/fix on this issue.

Looking at the changes, it seems to me that transforms will not be supported by subscriptions. Is there any possibility of having this feature enabled? This will help with consistent camelCased|...etc columns across both subscriptions and custom SQL queries.

As a hacky workaround, it should be possible to loop over the keys of a row and call postgres.toCamel, however, it would make more sense to have it follow the transform functions if possible.

[porsager]

Right, of course - that makes perfect sense 😅

I'll get that added.