SQL BatchUpdate does not work when different class properties are edited

[timvandam]

Example test case:

import { Database } from '@deepkit/orm';
import { SQLiteDatabaseAdapter } from '@deepkit/sqlite';
import { AutoIncrement, PrimaryKey } from '@deepkit/type';

class MyEntity {
  id: number & PrimaryKey & AutoIncrement = 0;
  constructor(public username: string, public realName: string) {}
}

const db = new Database(new SQLiteDatabaseAdapter(':memory:'), [MyEntity]);

const exampleEntities = [new MyEntity('supertim123', 'tim'), new MyEntity('mrmarc321', 'marc')];

beforeAll(async () => {
  await db.migrate();
  await db.persist(...exampleEntities);
});

// passes!
it('can batch update the same field in all entities', async () => {
  const session = db.createSession();

  for (const entity of await session.query(MyEntity).find()) {
    entity.username += '!';
  }

  await expect(session.commit()).resolves.not.toThrow();
});

// fails! NOT NULL constraint failed: MyEntity.username
it('can batch update different fields in all entities', async () => {
  const session = db.createSession();
  session.logger.enableLogging();

  const [entity1, entity2] = await session.query(MyEntity).find();
  entity1.username += '!';
  entity2.realName += ' turner';

  await expect(session.commit()).resolves.not.toThrow();
});

Batch update does the following in the failing test:

CREATE TEMPORARY TABLE _b AS
  SELECT _.id, _.username, _.realName
  FROM (SELECT column1 as id, column2 as username, column3 as realName FROM (VALUES (?,?,?), (?,?,?))) as _
  INNER JOIN "MyEntity" as _origin ON (_origin."id" = _."id"); -- params: [ 1, 'supertim123!!', 'marc turner', 2, undefined, undefined ]

UPDATE "MyEntity"
  SET "username" = _b."username", "realName" = _b."realName"
  FROM _b
  WHERE "MyEntity"."id" = _b."id"; -- params: []

The first query is created based on the changeset. Among all changesets, both username and realName are updated, however since only one of them changes in each entity, the other one will be undefined in the changeset.

In order to fix this, the temporary _b table can be constructed differently. E.g. the SELECT part could read:

SELECT COALESCE(_."id", _origin."id") AS "id", COALESCE(_."username", _origin."username") AS "username", COALESCE(_."realName", _origin."realName") AS "realName"

This way undefined / null values will simply be replaced by their current values.

This would go wrong when unsetting values though, so it isn't a perfect solution

[timvandam]

It appears that there is more going wrong judging by the parameters of the CREATE TEMPORARY TABLE

[timvandam]

Another fix would be to use changeSet.item to insert the current values. However this assumes that all fields were loaded.

I think the best solution would be to replace ? with _origin.columnName for columns that are not in the changeset $set nor $unset

[marcj]

Fixed in https://github.com/deepkit/deepkit-framework/commit/513a8180a51bb7611d5fae805084c9f85ef06c02