Assigning a customType and flushing to database does not escape returning values

[xmajzel]

Describe the bug

In the reproduction bellow I've created Entity A, which has property end of type IntervalType. Running the test fails on the last line, when flushing to database (await em.flush();) with following error:

update "a" set "end" = (500 || 'milliseconds')::interval where "id" = 1 returning (extract (epoch from end::interval) * 1000)::int as "end" - syntax error at or near "end"
SyntaxErrorException: update "a" set "end" = (500 || 'milliseconds')::interval where "id" = 1 returning (extract (epoch from end::interval) * 1000)::int as "end" - syntax error at or near "end"
    at PostgreSqlExceptionConverter.convertException (/Users/jozefmajzel/Workspace/Other/mikro-orm/packages/postgresql/src/PostgreSqlExceptionConverter.ts:44:16)
    at PostgreSqlDriver.convertException (/Users/jozefmajzel/Workspace/Other/mikro-orm/packages/core/src/drivers/DatabaseDriver.ts:465:50)
    at /Users/jozefmajzel/Workspace/Other/mikro-orm/packages/core/src/drivers/DatabaseDriver.ts:470:18
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at PostgreSqlDriver.nativeUpdate (/Users/jozefmajzel/Workspace/Other/mikro-orm/packages/knex/src/AbstractSqlDriver.ts:630:13)
    at ChangeSetPersister.persistManagedEntity (/Users/jozefmajzel/Workspace/Other/mikro-orm/packages/core/src/unit-of-work/ChangeSetPersister.ts:184:17)
    at ChangeSetPersister.executeUpdates (/Users/jozefmajzel/Workspace/Other/mikro-orm/packages/core/src/unit-of-work/ChangeSetPersister.ts:59:7)
    at ChangeSetPersister.runForEachSchema (/Users/jozefmajzel/Workspace/Other/mikro-orm/packages/core/src/unit-of-work/ChangeSetPersister.ts:96:7)
    at UnitOfWork.commitUpdateChangeSets (/Users/jozefmajzel/Workspace/Other/mikro-orm/packages/core/src/unit-of-work/UnitOfWork.ts:1005:5)
    at UnitOfWork.persistToDatabase (/Users/jozefmajzel/Workspace/Other/mikro-orm/packages/core/src/unit-of-work/UnitOfWork.ts:900:7)
    at PostgreSqlConnection.transactional (/Users/jozefmajzel/Workspace/Other/mikro-orm/packages/knex/src/AbstractSqlConnection.ts:85:19)
    at UnitOfWork.doCommit (/Users/jozefmajzel/Workspace/Other/mikro-orm/packages/core/src/unit-of-work/UnitOfWork.ts:398:9)
    at UnitOfWork.commit (/Users/jozefmajzel/Workspace/Other/mikro-orm/packages/core/src/unit-of-work/UnitOfWork.ts:364:7)
    at SqlEntityManager.flush (/Users/jozefmajzel/Workspace/Other/mikro-orm/packages/core/src/EntityManager.ts:1753:5)
    at Object.<anonymous> (/Users/jozefmajzel/Workspace/Other/mikro-orm/tests/issues/GHXXXX.test.ts:73:5)

    at Parser.parseErrorMessage (/Users/jozefmajzel/Workspace/Other/mikro-orm/node_modules/pg-protocol/src/parser.ts:369:69)
    at Parser.handlePacket (/Users/jozefmajzel/Workspace/Other/mikro-orm/node_modules/pg-protocol/src/parser.ts:188:21)
    at Parser.parse (/Users/jozefmajzel/Workspace/Other/mikro-orm/node_modules/pg-protocol/src/parser.ts:103:30)
    at Socket.<anonymous> (/Users/jozefmajzel/Workspace/Other/mikro-orm/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (node:events:518:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Socket.Readable.push (node:internal/streams/readable:390:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:130:17)

Problem:

update "a" set "end" = (500 || 'milliseconds')::interval where "id" = 1 returning (extract (epoch from end::interval) * 1000)::int as "end" - syntax error at or near "end"

• The main cause is that end is a reserved keyword in PostgrerSQL and returning statement doesn't have escaped property name
• Key parameter in function IntervalType.convertToJSValueSQL is not escaped

Reproduction

import {
  Entity,
  EntityManager,
  MikroORM,
  PrimaryKey,
  Property,
  Type,
  PostgreSqlDriver,
  BaseEntity,
} from '@mikro-orm/postgresql';

class IntervalType extends Type<number, number | null | undefined> {

  getColumnType() {
    return `interval`;
  }

  override get runtimeType(): string {
    return 'number';
  }

  compareAsType(): string {
    return 'number';
  }

  convertToJSValueSQL(key: string) {
    return `(extract (epoch from ${key}::interval) * 1000)::int`;
  }

  convertToDatabaseValueSQL(key: string) {
    return `(${key} || 'milliseconds')::interval`;
  }

}

@Entity()
export class A extends BaseEntity {

  @PrimaryKey()
  id!: number;

  @Property({ type: IntervalType })
  end!: number;

}


describe('GH issue XXXX', () => {
  let orm: MikroORM;
  let em: EntityManager;

  beforeAll(async () => {
    orm = await MikroORM.init<PostgreSqlDriver>({
      entities: [A],
      dbName: `mikro_orm_test_em`,
      driver: PostgreSqlDriver,
    });
  });

  beforeEach(async () => {
    await orm.schema.refreshDatabase();
    em = orm.em.fork();
  });

  afterAll(() => orm.close(true));

  test('update customType', async () => {
    const bk = em.create(A, { end: 10 });
    await em.flush();
    em.clear();
    const entityA = await em.findOneOrFail(A, { id: bk.id });
    entityA.assign({ end: 500 });
    await em.flush();
  });
});

What driver are you using?

@mikro-orm/postgresql

MikroORM version

6.2.5

Node.js version

20.11.1

Operating system

Mac OS Sonoma 14.4.1 (23E224)

Validations

• Read the Contributing Guidelines.
• Read the docs.
• Check that there isn't already an issue that reports the same bug to avoid creating a duplicate.
• Check that this is a concrete bug. For Q&A open a GitHub Discussion or join our Slack.
• The provided reproduction is a minimal reproducible example of the bug.

[xmajzel]

Thanks ♥️