Bulk update via raw query in TypeORM

Question

How can I do bulk update via raw query in TypeORM?
For example we have model User with property name
How can I change names of few users in one transaction?

typeorm version: 0.2.7
database: postgress

Answer 1

To bulk update, you can use update with set method, it is always recommended to not use raw queries when you can use orm functions.

import {getConnection, In} from "typeorm";
const userIds = [1,2,3];

await getConnection()
  .createQueryBuilder()
  .update(User)
  .set({ isaSeniorCitizen: true })
  .where({ id: In(userIds) })
  .execute();

Answer 2

Also you can update several rows with repository api this way:

await repository.update(
  {
    id: In([1,2,3,4,5]),
  },
  { selected: true },
);

Answer 3

Updating multiple columns with different values is not supported. There is an open issue about this in the Typeorm repo: https://github.com/typeorm/typeorm/issues/7326

How to perform this using raw psql is shown in @Roman-Pekar's answer to Update multiple rows in same query using PostgreSQL.

Another "solution" would be to create a stored function in the postgres db and call that. In one of the comments to another similar question I show an example on how to do that: typeorm efficient bulk update

Answer 4

You can either use QueryBuilder:

import {getConnection} from "typeorm";

await getConnection()
  .createQueryBuilder()
  .update(User)
  .set({ firstName: "Timber", lastName: "Saw" })
  .where("id = :id", { id: 1 })
  .execute();

or query() method:

getRepository(User).query('UPDATE `users` SET firstName = 'Timber', lastName = 'Saw' WHERE id = 1')

For bulk updating obviously you can do .where({ id: In(userIds) }) with QueryBuilder or WHERE id IN (${userIds.join(',')}) with raw query.

Answer 5

You can do this way

import {getConnection } from "typeorm";
const userIds = [1,2,3];

await getConnection()
  .createQueryBuilder()
  .update(User)
  .set({ isaSeniorCitizen: true })
  .whereInIds(userIds)
  .execute();