Bulk operations

import { Prorm, DataTypes } from 'ts-prorm-orm';

export const prorm = new Prorm({ dialect: 'postgres', host: 'localhost', database: 'shop' });

export const Product = prorm.define('Product', {
  id:    { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  sku:   { type: DataTypes.STRING(32), unique: true, validate: { len: [3, 32] } },
  name:  { type: DataTypes.STRING(200) },
  price: { type: DataTypes.DECIMAL(10, 2) },
  stock: { type: DataTypes.INTEGER() },
}, { tableName: 'products', timestamps: false });

export const Order = prorm.define('Order', {
  id:     { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  userId: { type: DataTypes.INTEGER() },
  status: { type: DataTypes.STRING(20) },
  total:  { type: DataTypes.DECIMAL(10, 2) },
}, { tableName: 'orders', timestamps: false });

Overview#

bulkCreate#

const created = await Product.bulkCreate([
  { sku: 'HD-01', name: 'Headset', price: 89, stock: 5 },
  { sku: 'CB-01', name: 'USB-C cable', price: 9.5, stock: 120 },
]);

// [{ sku: 'HD-01', name: 'Headset', price: 89, stock: 5, id: 3, isNewRecord: false }, …]

Two paths#

[!WARNING]
The default path does not validate. A record that create() would reject
is inserted as-is:

await Product.create({ sku: 'x', name: 'Bad', price: 1, stock: 1 });
// ValidationError: Validation len on sku failed

await Product.bulkCreate([{ sku: 'x', name: 'Bad', price: 1, stock: 1 }]);
// INSERT INTO "products" (…) VALUES ('x', 'Bad', 1, 1)   -- no error

Pass validate: true to check every record before anything is written: one
invalid record throws a ValidationError naming it (row 1: Validation len on sku failed) and no INSERT is sent. individualHooks: true also
validates, but record by record, so it stops at the first invalid one after
the earlier ones were written.

await Product.bulkCreate(feed, { validate: true });

Options#

update (bulk update)#

import { Op } from 'ts-prorm-orm';

const [cancelled] = await Order.update(
  { status: 'cancelled' },
  { where: { status: 'pending', total: { [Op.lt]: 20 } } },
);

[!IMPORTANT]
update needs a where that selects rows. With no where, or an empty one
(where: {}, for example a filter built from input that came back empty),
it throws before anything is sent:

await Order.update({ status: 'archived' }, { where: {} });
// Error: Missing where option in update: update() without conditions would
// affect every row. Pass a where that selects the rows, or { all: true } to
// update the whole table.

await Order.update({ status: 'archived' }, { all: true });
// UPDATE "orders" SET "status" = 'archived'

This is new in 4.9.2: earlier releases sent the UPDATE with no WHERE
and changed every row. Add all: true to calls that mean it.

await Order.update({ status: 'archived' }, { where: { status: 'paid' }, limit: 100 });

destroy (bulk delete)#

const removed = await Order.destroy({ where: { status: 'cancelled' }, limit: 500 });

[!TIP]
A limit turns one huge delete into a loop of small ones, so each statement
holds its locks briefly and replication does not stall behind it:

while ((await Order.destroy({ where: { status: 'cancelled' }, limit: 500 })) > 0) {
  // repeat until nothing is left
}

await Order.destroy({ where: { status: 'cancelled' } });              // soft delete
await Order.destroy({ where: { status: 'cancelled' }, force: true }); // real DELETE

upsert#

const [row, created] = await Product.upsert(
  { sku: 'MS-01', name: 'Mouse', price: 17.99, stock: 40 },
  { conflictFields: ['sku'], updateOnDuplicate: ['price', 'stock'] },
);

Conflict targets#

[!WARNING]
The conflict columns must be backed by a real unique constraint or primary
key in the database. If they are not, PostgreSQL and SQLite reject the
statement (SQLite: ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint). Declaring unique: true on the attribute helps only if the
table was created with it.

Bulk upsert#

const feed = [
  { sku: 'KB-01', name: 'Keyboard', price: 44.5, stock: 12 },
  { sku: 'WC-01', name: 'Webcam', price: 39, stock: 3 },
];

await prorm.transaction(async (t) => {
  for (const row of feed) {
    await Product.upsert(row, {
      conflictFields: ['sku'],
      updateOnDuplicate: ['price', 'stock'],
      transaction: t,
    });
  }
});

Hooks#

[!IMPORTANT]
An afterUpdate(instance) hook that reads instance.id gets undefined after
a set-based Model.update(), because it receives the values you set, not a
row. Code that must see every write, such as
cache invalidation, belongs in the bulk hooks,
which receive the where for both instance and set-based calls.

Loading a large feed#

Filter what changes#

Use set-based statements for uniform changes#

Chunk the rest#

const BATCH = 500;
for (let i = 0; i < rows.length; i += BATCH) {
  const chunk = rows.slice(i, i + BATCH);
  await prorm.transaction(async (t) => {
    await Product.bulkCreate(chunk, { transaction: t });
  });
}

Skip work you do not need#

[!TIP]
For a feed of tens of thousands of rows, a COPY (PostgreSQL) or
LOAD DATA (MySQL) through raw queries into a staging
table, followed by one set-based INSERT … SELECT, beats any per-row path.

Related reading#