How to perform conditional update operation? (Alternately, updating row with single function when received data have missing fields)

[mukundshah]

Say, for example, I have a function, which on called, updates the record of a row in a table. It takes two argument : id and object. The object has optional fields.

interface IEvent {
  id?: number;
  createdAt: Date;
  updatedAt: Date;

  title: string;
  description: string;
  startDateTime: Date;
  endDateTime: Date;
}

interface IEventUpdate {
  title?: string;
  description?: string;
  startDateTime?: Date;
  endDateTime?: Date;
}

async function updateEvent(id: number, res: IEventUpdate) {
    return await connection.update({
      in: 'Events',
      set: {
        title: res.title,
        description: res.description,
        startDateTime: res.startDateTime,
        endDateTime: res.endDateTime,
        updatedAt: new Date()
      },
      where: {
        id: id,
      },
    });
  }

Now, the issue I have is all the columns in Event table are nonNullable. I won't always have all the fields available from response in the update function, as fields are optional. And, inside the object of connection.update(), conditionals can't be performed, as we can not magically add key-value pair in any object. Or, can we? Let me know, if it's possible.

A while back when I used SQLAlchemy and FastAPI, the update operation was like this:

async def update_user(self, request: UserUpdateSchema):
  query = update(User).where(User.uuid == request.uuid)
  if request.first_name:
      query = query.values(first_name=request.first_name)
  if request.last_name:
      query = query.values(last_name=request.last_name)
  if request.email:
      query = query.values(email=request.email)
  if request.password:
      hashed_password = Hash.bcrypt(password=request.password)
      query = query.values(password=hashed_password)
  if request.is_active:
      query = query.values(is_active=request.is_active)
  query = query.values(updated_at=datetime.datetime.now())
  await self.db_session.execute(query)

[mukundshah]

Okay, I found the solution.

async function updateEvent(id: number, res: IEventUpdate) {
  let query = {
    in: 'Events',
    where: { id: id },
    set: { updatedAt: new Date() } as IEventUpdate,
  };
  if (res.title) query.set.title = res.title;
  if (res.description) query.set.description = res.description;
  if (res.startDateTime) query.set.startDateTime = res.startDateTime;
  if (res.endDateTime) query.set.endDateTime = res.endDateTime;
  return await connection.update(query);
}

[mukundshah]

Another possible solution is to use spread operator, but there is one edge case.

async function updateEvent(id: number, res: IEventUpdate) {
  let query = {
    in: 'Events',
    where: { id: id },
    set: {...res, updatedAt: new Date() }
  };
  return await connection.update(query);
}

for this to work, you can only do this:

let data: IEventUpdate = { title: 'New Title' };
updateEvent(1, data);

If you do it like this:

let data: IEventUpdate = { title: 'New Title', description: undefined };
updateEvent(1, data);

It won't work.

However, the first/primary solution works for all cases.