Bulk operations

Bulk operations Guide to inserting, updating, deleting, and upserting many rows at once with prorm models. Source: model · Options: types Overview prorm exposes bulk work through a small set of static model methods: bulkCreate(records, options) — insert (or upsert) an array of records. update(values, { where }) — set-based update of every row matching a filter. destroy({ where }) — set-based delete of every row matching a filter. upsert(values, options) — insert-or-update a single row using the database's native conflict handling (INSERT ... ON CONFLICT / ON DUPLICATE KEY UPDATE). Two shapes are worth keeping straight: bulkCreate is row-oriented — it iterates the array and issues one statement per record, so it is the tool for writing distinct rows. update and destroy are set-oriented — a single SQL statement changes every matching row, so they are the tool for applying the same change to many rows. There is no separate bulkUpdate / bulkDelete method; those operations are just update and destroy with a where clause. Likewise there is no standalone bulkUpsert — bulk upserting is bulkCreate with upsert: true. bulkCreate Pass an array of partial records. Each is inserted via the same path as create, and the created ModelInstances are returned in order. Options bulkCreate accepts BulkCreateOptions (an extension of CreateOptions): Validation By default every record is validated before it is written, using the model's validateOnInsert configuration. A single failing record throws a PrormValidationError (its .errors array holds the individual ValidationErrorItems). Pass validate: false to skip validation when you trust the input: Returning columns Use returning to pull database-generated values (defaults, sequences, computed columns) back into the returned instances. Pass true for all columns or an array to name specific ones: Hooks bulkCreate runs the beforeBulkCreate and afterBulkCreate hooks once around the whole batch, receiving the records / resulting instances. Set hooks: false to skip them entirely, or individualHooks: true to additionally fire per-record beforeCreate / afterCreate hooks (slower, since each record is treated individually): update (bulk update) update applies one SET clause to every row matching where in a single statement. It returns a tuple of [affectedCount, instances]. A where clause is required — calling update without one throws Missing where option in update. Relevant UpdateOptions: When timestamps are enabled the updatedAt column is set automatically. With individualHooks: true, prorm loads the affected instances so it can fire beforeUpdate / afterUpdate per row and include the updated instances in the returned tuple. destroy (bulk delete) destroy deletes every row matching where and returns the affected row count. A where clause is required (Missing where option in destroy). For paranoid (soft-delete) models, destroy performs a soft delete by setting the deletedAt column instead of removing rows. Pass force: true to hard-delete: Relevant DestroyOptions: upsert upsert inserts a row or updates it if it collides with an existing one, using the database's native conflict syntax. It returns [instance, created], where created reports whether a new row was inserted. Conflict targets On PostgreSQL (and SQLite) the conflict target must name the columns of a unique or exclusion constraint via conflictFields — omitting it raises an error, because ON CONFLICT requires explicit columns. MySQL uses ON DUPLICATE KEY UPDATE and infers the conflict from the table's unique keys. If updateOnDuplicate is omitted, all supplied fields are updated on conflict. Relevant UpsertOptions: When timestamps are enabled, createdAt (if absent) and updatedAt are set automatically. Bulk upsert To upsert many rows, call bulkCreate with upsert: true. prorm upserts each record using the shared conflictFields / updateOnDuplicate settings and returns the resulting instances: Rows that match an existing email are updated; the rest are inserted. Performance considerations Wrap bulkCreate in a transaction. It issues one statement per record rather than a single multi-row INSERT, so batching the whole array inside a transaction avoids per-statement commit overhead and gives you all-or-nothing semantics: Prefer set-based update / destroy for uniform changes. Applying the same change across many rows with a single where-based statement is dramatically cheaper than loading rows and saving them one by one. Skip work you do not need. validate: false avoids per-record validation and hooks: false avoids hook dispatch — both meaningful on large batches. Avoid individualHooks: true unless you truly need per-row hook side effects, since it forces per-record processing and extra findAll round-trips. Only request returning when you use it. Returning columns adds work on the database and marshalling on the client; omit it for fire-and-forget writes. Chunk very large arrays. For tens of thousands of records, split the input into batches (e.g. 500–1000 rows) so a single call does not hold a long transaction or build an oversized statement. Related reading Transactions — making a bulk write atomic Streams — when the set is too large to hold in memory