EntityManager -- Writes & Transactions

This page covers batch write operations, upserts, transactions, and raw SQL execution.

For basic single-entity CRUD, see CRUD Basics. For read-oriented features, see Querying & Pagination.

---

Batch Insert -- insertMany()

Why insertMany() exists

Imagine you need to create 1,000 users. You could call save() in a loop, but that means 1,000 separate INSERT statements, 1,000 network round-trips, and 1,000 transaction commits. That is painfully slow.

insertMany() solves this by packing all rows into a single INSERT INTO ... VALUES (...), (...), (...) statement. One round-trip, one parse, one commit. On real-world benchmarks, this is typically 10-50x faster than individual inserts.

await em.insertMany(User, [
  { name: "Alice", email: "alice@example.com" },
  { name: "Bob", email: "bob@example.com" },
  { name: "Charlie", email: "charlie@example.com" },
]);

The ORM generates a single SQL statement:

-- PostgreSQL
INSERT INTO "user" ("name", "email")
VALUES ($1, $2), ($3, $4), ($5, $6)
-- Parameters: ['Alice', 'alice@example.com', 'Bob', 'bob@example.com', 'Charlie', 'charlie@example.com']

-- MySQL
INSERT INTO `user` (`name`, `email`)
VALUES (?, ?), (?, ?), (?, ?)
-- Parameters: ['Alice', 'alice@example.com', 'Bob', 'bob@example.com', 'Charlie', 'charlie@example.com']

Key characteristics:

• Executes as a single SQL statement -- far more efficient than calling save() in a loop.
• @CreateTimestamp and @UpdateTimestamp columns a row leaves unset get the current time, whatever their type (timestamptz included). Other date/time columns are not touched.
• @Version columns are initialized to 1 for each row, and client-generated keys (@PrimaryGeneratedColumn("uuid") / "uuid-v7") are generated for rows that leave them unset. These generated values are written back onto the objects you passed.
• A column no row provides is left out of the statement, so its database DEFAULT applies -- the same rule as saveMany() (see undefined vs null on INSERT).
• Returns { affected: number } -- if you need the generated PKs back, use insertManyAndReturn() (PostgreSQL / SQLite) or saveMany() (all dialects) instead.

---

Batch Insert and Return -- insertManyAndReturn()

Why insertManyAndReturn() exists

insertMany() is fast, but it returns only { affected: number }. When you need the generated primary keys, timestamps, and database defaults populated on the inserted rows -- without sacrificing the single-statement efficiency of a multi-row insert -- use insertManyAndReturn().

Internally the ORM issues one INSERT INTO ... VALUES (...), (...), (...) RETURNING * statement and maps the result rows back through the ResultTransformer, so column aliases and NamingStrategy mappings are applied on the way out.

const users = await em.insertManyAndReturn(User, [
  { name: "Alice", email: "alice@example.com" },
  { name: "Bob", email: "bob@example.com" },
]);
// users[0].id => 1  (generated by the database)
// users[1].id => 2

The ORM generates:

-- PostgreSQL
INSERT INTO "user" ("name", "email")
VALUES ($1, $2), ($3, $4)
RETURNING *
-- Parameters: ['Alice', 'alice@example.com', 'Bob', 'bob@example.com']

-- SQLite 3.35+
INSERT INTO "user" ("name", "email")
VALUES (?, ?), (?, ?)
RETURNING *
-- Parameters: ['Alice', 'alice@example.com', 'Bob', 'bob@example.com']

Key characteristics:

• Executes as a single SQL statement -- same efficiency as insertMany().
• Returns fully-hydrated entity instances in input order.
• @CreateTimestamp, @UpdateTimestamp, @Version and client-generated UUID keys are filled in before the insert, and a column no row provides is left to its database DEFAULT -- the same rules as insertMany().
• Empty items returns [] immediately without touching the database.

Dialect support. insertManyAndReturn() requires INSERT ... RETURNING, available on PostgreSQL and SQLite 3.35+. Calling it on MySQL throws OrmError with code UNSUPPORTED_DATABASE; use saveMany() there instead.

// On MySQL -- throws OrmError (UNSUPPORTED_DATABASE)
// await em.insertManyAndReturn(User, [{ name: "Alice" }]); // do not use on MySQL

// Use saveMany() for MySQL:
const users = await em.saveMany(User, [{ name: "Alice" }]);

Choosing the right batch insert

Method	Dialects	SQL statements	Returns
insertMany()	All	1	{ affected: number }
insertManyAndReturn()	PostgreSQL, SQLite 3.35+	1	Entity instances
saveMany()	All	N (one per row)	Entity instances

Use insertMany() when you do not need the rows back and must support MySQL. Use insertManyAndReturn() when you need the rows back and target PostgreSQL or SQLite. Use saveMany() when you need rows back on MySQL, or when the batch contains a mix of new and existing entities (INSERT vs UPDATE per row).

---

Batch Save -- saveMany()

Why saveMany() when insertMany() exists?

insertMany() is fast but limited: it only inserts new rows. saveMany() is flexible: it inspects each item's primary key and decides INSERT vs UPDATE individually. Use it when you have a mix of new and existing entities.

const users = await em.saveMany(User, [
  { name: "New User", email: "new@example.com" },           // No PK -> INSERT
  { id: 2, name: "Updated User", email: "upd@example.com" }, // Has PK -> UPDATE
]);
// Returns the saved entities with generated PKs

Under the hood, saveMany() wraps all operations in a single transaction and processes each item one by one. For the example above, the SQL timeline looks like:

-- Step 1: BEGIN transaction
BEGIN

-- Step 2: First item has no PK -> INSERT
-- PostgreSQL
INSERT INTO "user" ("name", "email") VALUES ($1, $2) RETURNING *
-- Parameters: ['New User', 'new@example.com']

-- Step 3: Second item has PK -> UPDATE
-- PostgreSQL
UPDATE "user" SET "name" = $1, "email" = $2 WHERE "id" = $3 RETURNING *
-- Parameters: ['Updated User', 'upd@example.com', 2]

-- Step 4: COMMIT
COMMIT

The tradeoff is clear: saveMany() sends one query per item (slower for pure inserts), but it handles mixed create/update scenarios that insertMany() cannot.

---

undefined vs null on INSERT -- Letting DB Defaults Apply

Why the distinction matters

save() and saveMany() treat undefined as "not provided" (the same semantics as TypeORM and knex): a column whose entity value is undefined is omitted from the INSERT column list, so the database-side DEFAULT clause -- including @Column({ default }) -- applies. An explicit null is different: the column is included and writes NULL.

@Entity()
class Article {
  @PrimaryGeneratedColumn() id!: number;
  @Column() title!: string;
  @Column({ default: "draft" }) status!: string;
  @Column({ nullable: true }) summary?: string;
}

const a = await em.save(Article, { title: "Hello" });
// INSERT INTO "article" ("title") VALUES ($1)
// -> "status" omitted, DB DEFAULT 'draft' applies
a.status; // "draft"

await em.save(Article, { title: "Hello", summary: null });
// INSERT INTO "article" ("title", "summary") VALUES ($1, $2)
// -> "summary" is included and explicitly writes NULL

Auto-populated columns are the exception: @CreateTimestamp, @UpdateTimestamp, @Version, and client-side UUID generation strategies are still included and injected even when their entity value is undefined.

If every column ends up omitted, the ORM emits the dialect's all-defaults INSERT form:

-- MySQL / MariaDB
INSERT INTO `t` () VALUES ()

-- PostgreSQL / SQLite
INSERT INTO "t" DEFAULT VALUES

For saveMany() batch inserts, all rows share one column set in the multi-row VALUES list. A column is omitted only when no item in the batch provides it; in mixed batches (some items provide the column, some don't), the column stays in the list and missing rows bind NULL.

insertMany(), insertManyAndReturn(), createInsertBuilder() and batchUpsert() follow the same batch rule, and upsert() / insertIgnore() the single-row one. When no row of an insertMany() provides any column at all, the full column list is kept and binds NULL -- a multi-row INSERT has no portable all-defaults form. Before 2.1, insertMany(), insertManyAndReturn() and createInsertBuilder() named every declared column and bound NULL for the ones you left out, so a DEFAULT never applied there.

---

Unknown Keys in Write Payloads

What counts as a known key

save(), saveMany(), insertMany(), insertManyAndReturn(), upsert(), insertIgnore() and batchUpsert() read their values by property key. The keys a payload may carry are:

• @Column property names (firstName, not the DB column first_name);
• relation properties of any kind (team, posts, tags) -- cascades and hydrated instances;
• @ManyToOne / @OneToOne FK shadow properties (teamId, or the fkProperty you declared) and the join column itself;
• @ComputedColumn properties -- never written, but present on every instance read back;
• in a single-table hierarchy, the discriminator column and the columns of the sibling classes.

Anything else -- a typo, a DTO field that never became a column, a DB column name typed instead of the property -- is not written. Before 2.1 that was silent; updateMany() and every read already rejected the same key with a Did you mean suggestion.

The unknownWriteKeys policy

The unknownWriteKeys connection option decides what happens:

Policy	Behavior
"warn" (default)	The write runs. The key is logged once per entity and key for the lifetime of the EntityManager, naming the method and the closest accepted key.
"throw"	The write is rejected before any SQL with InvalidQueryError -- the same error a typo in a read where raises. Recommended once the warnings are clean.
"ignore"	The previous behavior: unknown keys are dropped silently.

await DatabaseClient.getInstance().connect({
  type: "postgres",
  // ...
  unknownWriteKeys: "throw",
});

await em.save(User, { firstNam: "kim" } as any);
// InvalidQueryError: Unknown column "firstNam" in "data" for entity "User". Did you mean "firstName"?

Under the default policy the same call succeeds and logs:

[WriteInput] Unknown key "firstNam" in the data passed to save() for entity "User" — it matches no column, relation or FK property and was not written. Did you mean "firstName"? Set unknownWriteKeys: "throw" to reject such writes, or "ignore" to silence this warning.

A DB column name is reported too, because the INSERT never reads it: save(Team, { team_name: "x" }) warns with Did you mean "teamName"? where a read where: { team_name } would have resolved the column. Write by property name.

Values that are never written are never reported: an undefined field (see the section above) and a function-valued member (a method on an entity instance). The check runs before hooks, cascades and tenant-column injection, so it sees exactly the payload you passed.

update() / updateMany() keep their existing contract and throw on an unknown key in data or where regardless of the policy. create(), merge() and preload() never persist, so they keep every key on the instance -- the write that follows reports it.

---

RETURNING Rows Map Back to Property Names

On RETURNING-capable drivers (PostgreSQL, MariaDB 10.5+), the entity returned by save() is built from the RETURNING * row. That row is now routed through the ResultTransformer, so DB column names are mapped back to entity property keys -- covering @Column({ name }) and NamingStrategy mappings like SnakeNamingStrategy -- and column transformer from functions are applied on the way out.

@Entity()
class Category {
  @PrimaryGeneratedColumn() id!: number;
  @Column({ name: "LFT_NO" }) left!: number;
}

const saved = await em.save(Category, { left: 1 });
saved.left;             // 1 -- property key, as declared on the class
(saved as any).LFT_NO;  // undefined -- raw DB key no longer leaks through

Previously the returned object exposed raw DB keys (e.g. LFT_NO instead of left). The mapping applies to INSERT RETURNING, UPDATE RETURNING, and saveMany() batch RETURNING alike.

---

Batch Delete -- deleteMany()

Why deleteMany() over multiple delete() calls?

For the same reason insertMany() is faster than looping save(): one SQL statement is better than many. deleteMany() takes an array of primary key values and generates a single DELETE ... WHERE id IN (...) statement.

const result = await em.deleteMany(User, [1, 2, 3]);
console.log(result.affected); // 3

-- PostgreSQL
DELETE FROM "user" WHERE "id" IN ($1, $2, $3)
-- Parameters: [1, 2, 3]

-- MySQL
DELETE FROM `user` WHERE `id` IN (?, ?, ?)
-- Parameters: [1, 2, 3]

TIP

If you need to delete by a condition rather than by PKs, use delete() with a WHERE clause:

await em.delete(User, { isActive: false });

Operator criteria in delete() / softDelete() / restore()

The criteria object is not limited to equality. delete(), softDelete(), and restore() accept the same find-style operator objects as where in reads -- { between: [a, b] }, { gt }, { gte }, { lt }, { lte }, { in }, { like }, and the rest -- plus null for IS NULL. They are resolved by the same WhereResolver as the read paths (updateMany() already supported this).

// Delete an entire nested-set subtree in one statement
await em.delete(Category, { lft: { between: [node.lft, node.rgt] } });

// Soft-delete stale drafts
await em.softDelete(Post, { status: "draft", updatedAt: { lt: cutoff } });

// null -> IS NULL
await em.delete(Session, { userId: null });

Empty criteria still throw DeleteWithoutConditionsError -- the table-wide guard is unchanged.

Logical combinators in bulk criteria -- AND / OR / NOT

The criteria of delete(), softDelete(), restore() and the where of updateMany() / update() accept the same AND / OR / NOT keys as a read where, nested to any depth. The identifier check that runs before these writes walks the combinators exactly like the read paths do, so a typo inside an OR branch is still caught before any SQL runs, with the same Did you mean suggestion.

// Delete everything that is either archived or stale
await em.delete(Post, {
  OR: [{ status: "archived" }, { updatedAt: { lt: cutoff } }],
});

// Close the open tickets that DO have an assignee
await em.updateMany(Ticket, { status: "closed" }, {
  where: {
    AND: [{ status: "open" }, { NOT: { assigneeId: null } }],
  },
});

// A combinator next to a plain column is AND-ed with it, as in find()
await em.softDelete(Draft, { authorId: 7, OR: [{ title: null }, { body: "" }] });

Criteria that resolve to no predicate at all are treated like empty criteria: the call throws DeleteWithoutConditionsError instead of falling through to a table-wide statement. That covers { OR: [] }, { AND: [] }, criteria whose every value is undefined ({ status: undefined }), and a NOT whose inner object resolves to nothing. It holds for updateMany() as well, including the case where the SET payload is empty too — that combination used to answer { affected: 0 } without complaining about the criteria.

An OR branch (or an element of the array form) that resolves to nothing is a different error, because it would widen the statement rather than remove its filter: an empty branch is TRUE, so { OR: [{ id: undefined }, { id: 2 }] } would match every row. It throws InvalidQueryError naming the branch. Inside AND an empty branch is the identity, so it is skipped and the rest of the group still applies. The full rule for undefined in a where or criteria object is in undefined values.

The criteria check now runs before anything else the write would do. delete() with criteria that resolve to nothing no longer emits beforeDelete, and no longer lets the one-to-many cascade read the parents and delete their children first — the transaction used to roll those rows back, but the listeners had already run. softDelete() and restore() are guarded the same way, before their beforeSoftDelete / beforeRestore events.

---

Targeted Update -- update()

Why update() exists

updateMany() is powerful but verbose for the common case: change the rows matching a filter. It nests the filter under an options object ({ where: ... }), which reads differently from delete(entity, criteria) where the filter is just the second argument. update() is the filter-first sugar that closes that gap:

// update(entity, where, data) -- the filter is the 2nd arg, just like delete()
await em.update(User, { id: 1 }, { name: "Alice" });

// raw SQL expressions work too (forwarded straight to the SET clause)
await em.update(Post, { id: 1 }, { viewCount: sql`view_count + 1` });

It delegates to updateMany(), so it inherits every safeguard: the empty-WHERE guard (a table-wide update is rejected), tenant scoping, @UpdateTimestamp auto-injection, NamingStrategy column mapping, and Sql expression support. The return shape is identical -- { affected }.

Reach for updateMany() directly only when you need an ordered/capped update (orderBy + limit). For everything else, update() is the shorter, more consistent call. It is available on repositories as well: repo.update(where, data).

---

Bulk Update -- updateMany()

Why updateMany() exists

Sometimes you need to update thousands of rows with the same change -- deactivating all expired accounts, bumping a price for a category, or resetting a flag. Doing this with save() would mean fetching each row, modifying it, and saving it back. updateMany() does it in one shot with a single UPDATE ... SET ... WHERE ....

const result = await em.updateMany(User,
  { isActive: false },            // SET -- the data to apply
  { where: { lastLoginAt: null } }, // WHERE -- the condition to match
);
console.log(result.affected); // number of updated rows

-- PostgreSQL
UPDATE "user"
SET "isActive" = $1
WHERE "lastLoginAt" IS NULL
-- Parameters: [false]

-- MySQL
UPDATE `user`
SET `isActive` = ?
WHERE `lastLoginAt` IS NULL
-- Parameters: [false]

The where accepts everything a read where does, including AND / OR / NOT (see Logical combinators in bulk criteria above). The data argument is the SET clause: it maps columns to values, so a combinator key there is rejected with Logical combinator "OR" is not allowed in the update data rather than being mistaken for a column -- move it into where.

A more realistic example -- deactivating users who haven't logged in recently:

const result = await em.updateMany(User,
  { isActive: false, deactivatedAt: new Date() },
  { where: { isActive: true } },
);
console.log(`Deactivated ${result.affected} users`);

-- PostgreSQL
UPDATE "user"
SET "isActive" = $1, "deactivatedAt" = $2, "updatedAt" = $3
WHERE "isActive" = $4
-- Parameters: [false, '2026-03-22 12:00:00', '2026-03-22 12:00:00', true]

Notice the "updatedAt" column in the SET clause -- the ORM automatically injects @UpdateTimestamp columns, even in bulk updates.

Soft-deleted rows are skipped by default

When the entity has a @DeletedAt column, updateMany() (and the update() / increment() / decrement() helpers that delegate to it) only touches live rows -- it appends "deletedAt" IS NULL to the WHERE, exactly like find(). A bulk update never silently rewrites data on a logically-deleted row.

Pass withDeleted: true to include trashed rows:

// Default: trashed rows are left untouched
await em.updateMany(User, { plan: "free" }, { where: { plan: "pro" } });

// Opt in: also update soft-deleted rows
await em.updateMany(
  User,
  { plan: "free" },
  { where: { plan: "pro" }, withDeleted: true },
);

-- Default -- PostgreSQL
UPDATE "user" SET "plan" = $1 WHERE "plan" = $2 AND "deletedAt" IS NULL

-- withDeleted: true -- the soft-delete predicate is omitted
UPDATE "user" SET "plan" = $1 WHERE "plan" = $2

For Single-Table-Inheritance child classes, updateMany() also appends the discriminator predicate, so updating updateMany(CreditCardPayment, …) never touches sibling subtypes sharing the table -- the same rule find() and delete() follow.

SQL expressions in updateMany

Sometimes you need computed updates -- incrementing a counter, appending to a string, or using database functions. updateMany accepts raw SQL expressions via sql-template-tag as column values:

import sql from "sql-template-tag";

// Increment view count
await em.updateMany(Post,
  { viewCount: sql`"viewCount" + 1` },
  { where: { id: 1 } },
);

-- PostgreSQL
UPDATE "post"
SET "viewCount" = "viewCount" + 1
WHERE "id" = $1
-- Parameters: [1]

You can mix literal values and SQL expressions in the same update:

await em.updateMany(Product,
  {
    price: sql`"price" * 1.1`,         // 10% price increase
    lastUpdatedBy: "admin",             // Literal value
  },
  { where: { category: "electronics" } },
);

Key characteristics:

• @UpdateTimestamp columns are automatically injected into the SET clause.
• An empty WHERE condition throws DeleteWithoutConditionsError (safety guard).
• Unlike save(), this does not fire entity lifecycle hooks or events -- it is a raw bulk operation.

DANGER

The parameter order is (Entity, setData, { where }) -- not (Entity, where, setData). The data you want to set comes first.

---

Atomic Increment and Decrement -- increment() / decrement()

Why these exist

Both update() and updateMany() can increment a counter via a raw SQL expression:

import sql from "sql-template-tag";
await em.update(Post, { id: 1 }, { viewCount: sql`view_count + 1` });

That works, but it requires importing sql-template-tag, knowing the actual DB column name, and writing the correct SQL fragment. increment() is the safe shorthand: it resolves the entity property to the correctly escaped column, binds by as a parameter (no string concatenation), and delegates to update() so every safeguard comes along for free.

Usage

// Add 1 to viewCount for the matching row (by defaults to 1)
await em.increment(Post, { id: 1 }, "viewCount");

// Add 50 to balance
await em.increment(Wallet, { userId: 7 }, "balance", 50);

// Subtract 1 from stock
await em.decrement(Product, { id: 9 }, "stock");

// Subtract 100 from balance
await em.decrement(Wallet, { userId: 7 }, "balance", 100);

Each call emits a single UPDATE statement:

-- PostgreSQL (viewCount += 1)
UPDATE "post"
SET "viewCount" = "viewCount" + $1, "updatedAt" = $2, "version" = "version" + 1
WHERE "id" = $3
-- Parameters: [1, '2026-06-13 10:00:00', 1]

-- MySQL (stock -= 1)
UPDATE `product`
SET `stock` = `stock` - ?, `updatedAt` = ?
WHERE `id` = ?
-- Parameters: [1, '2026-06-13 10:00:00', 9]

Signatures

// EntityManager
em.increment<T>(entity: Class<T>, where: WhereClause<T>, column: keyof T & string, by?: number): Promise<{ affected: number }>
em.decrement<T>(entity: Class<T>, where: WhereClause<T>, column: keyof T & string, by?: number): Promise<{ affected: number }>

// BaseRepository (entity already bound -- no first argument)
repo.increment(where: WhereClause<T>, column: keyof T & string, by?: number): Promise<{ affected: number }>
repo.decrement(where: WhereClause<T>, column: keyof T & string, by?: number): Promise<{ affected: number }>

Behavior

• Atomic -- the delta is applied as SET col = col + ? in the database. Two concurrent callers produce the correct combined result; there is no read-modify-write race.
• Delegates to update() -- inherits the empty-WHERE guard (throws DeleteWithoutConditionsError when where is empty or matches zero columns), tenant scoping, NamingStrategy column mapping, and @UpdateTimestamp auto-injection. The @Version optimistic-lock column is also bumped in the same statement when present.
• by defaults to 1 -- passing 0, NaN, Infinity, or a non-finite number throws InvalidQueryError.
• Returns { affected } -- the row count reported by the database driver.

Repository shorthand

const postRepo = em.getRepository(Post);

// Equivalent to em.increment(Post, { id: 1 }, "viewCount")
await postRepo.increment({ id: 1 }, "viewCount");

const productRepo = em.getRepository(Product);
// Equivalent to em.decrement(Product, { id: 9 }, "stock")
await productRepo.decrement({ id: 9 }, "stock");

---

Upsert -- Insert or Update

Why upsert?

Consider a "login tracking" feature: every time a user logs in, you want to either create a new record or update the existing one. Without upsert, you would need to:

1. findOne() -- Check if the record exists
2. If yes: save() with the PK to UPDATE
3. If no: save() without PK to INSERT

That is three round-trips and a race condition (two requests could both see "not found" and both try to INSERT, causing a duplicate key error). Upsert solves both problems with a single atomic statement.

By primary key

await em.upsert(User, {
  id: 1,
  name: "Alice",
  email: "alice@example.com",
});
// If id=1 exists -> UPDATE name and email
// If id=1 doesn't exist -> INSERT new row

-- PostgreSQL
INSERT INTO "user" ("id", "name", "email")
VALUES ($1, $2, $3)
ON CONFLICT ("id") DO UPDATE SET "name" = EXCLUDED."name", "email" = EXCLUDED."email"
-- Parameters: [1, 'Alice', 'alice@example.com']

-- MySQL
INSERT INTO `user` (`id`, `name`, `email`)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `email` = VALUES(`email`)
-- Parameters: [1, 'Alice', 'alice@example.com']

The key phrase is ON CONFLICT ... DO UPDATE (PostgreSQL) or ON DUPLICATE KEY UPDATE (MySQL). Both mean: "try to insert, but if there is a conflict on the specified column(s), update the existing row instead."

By unique column

Pass an array of column names as the third argument to specify the conflict target:

await em.upsert(User, {
  email: "alice@example.com",
  name: "Alice",
  lastLoginAt: new Date(),
}, ["email"]);
// If a row with this email exists -> UPDATE name and lastLoginAt
// If no row with this email -> INSERT

-- PostgreSQL
INSERT INTO "user" ("email", "name", "lastLoginAt")
VALUES ($1, $2, $3)
ON CONFLICT ("email") DO UPDATE SET "name" = EXCLUDED."name", "lastLoginAt" = EXCLUDED."lastLoginAt"
-- Parameters: ['alice@example.com', 'Alice', '2026-03-22 12:00:00']

-- MySQL
INSERT INTO `user` (`email`, `name`, `lastLoginAt`)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `lastLoginAt` = VALUES(`lastLoginAt`)
-- Parameters: ['alice@example.com', 'Alice', '2026-03-22 12:00:00']

How conflict detection works

The "conflict columns" tell the database which uniqueness constraint to check. If you specify ["email"], the database looks for an existing row where email matches the value you are inserting. If it finds one, it updates that row. If it does not, it inserts a new row.

INFO

The conflict columns (third argument) must have a unique constraint or be the primary key. Otherwise, the database will reject the query. In PostgreSQL, you will get: there is no unique or exclusion constraint matching the ON CONFLICT specification.

Batch Upsert -- batchUpsert()

When you need to upsert hundreds or thousands of rows at once, batchUpsert() is significantly faster than calling upsert() in a loop. It packs all rows into a single multi-row INSERT ... ON CONFLICT statement.

await em.batchUpsert(User, [
  { email: "alice@example.com", name: "Alice", loginCount: 1 },
  { email: "bob@example.com", name: "Bob", loginCount: 1 },
  { email: "charlie@example.com", name: "Charlie", loginCount: 1 },
], ["email"]);

-- PostgreSQL
INSERT INTO "user" ("email", "name", "loginCount")
VALUES ($1, $2, $3), ($4, $5, $6), ($7, $8, $9)
ON CONFLICT ("email") DO UPDATE SET "name" = EXCLUDED."name", "loginCount" = EXCLUDED."loginCount"

-- MySQL
INSERT INTO `user` (`email`, `name`, `loginCount`)
VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `loginCount` = VALUES(`loginCount`)

The optional third argument specifies the conflict columns. If omitted, the primary key is used.

Managed columns on conflict

upsert(), insertIgnore() and batchUpsert() fill in the columns the ORM owns on a copy of your payload -- the objects you pass are not modified (the tenant column included), so read the row back if you need the generated key, version or timestamps. The primary key and the managed columns never take the payload's value on the conflict branch:

Column	Row inserted	Row conflicts
Primary key	yours; "uuid" / "uuid-v7" keys are generated, auto-increment keys come from the database	not written -- the stored key stays
Other "uuid" / "uuid-v7" columns	yours, or generated	written only when the payload states one
@Version	yours, or 1	stored value + 1 (a stored NULL counts as 0)
@CreateTimestamp	yours, or the current time	not written
@UpdateTimestamp	yours, or the current time	the inserted row's value -- the current time unless you passed one; a payload of just the key and updatedAt still updates it
@DeletedAt	yours, or NULL	NULL unless the payload sets it -- a soft-deleted row is restored
Tenant column (tenant_column)	the active tenant	not written

For an Order with @Version, @UpdateTimestamp and @DeletedAt:

await em.upsert(Order, { slug: "a-1", amount: 42 }, ["slug"]);

-- PostgreSQL (SQLite is identical with lowercase `excluded`)
INSERT INTO "order" ("slug", "amount", "version", "createdAt", "updatedAt")
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT ("slug") DO UPDATE SET "amount" = EXCLUDED."amount",
  "updatedAt" = EXCLUDED."updatedAt",
  "version" = COALESCE("order"."version", 0) + 1,
  "deletedAt" = NULL

-- MySQL / MariaDB
INSERT INTO `order` (`slug`, `amount`, `version`, `createdAt`, `updatedAt`)
VALUES (?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE `amount` = VALUES(`amount`),
  `updatedAt` = VALUES(`updatedAt`),
  `version` = COALESCE(`order`.`version`, 0) + 1,
  `deletedAt` = NULL

Things to know:

• The version is bumped, not checked. An upsert is last-write-wins and never throws OptimisticLockError; it only makes sure a save() holding the pre-upsert version is rejected afterwards. A key-only upsert that merely restores a soft-deleted row keeps its version, as restore() does. A @Version value in an upsert() / batchUpsert() payload is used when the row is inserted and ignored on conflict, with a warning logged once per entity class. Use save() when a stale write must fail.
• Managed columns never cause an update on their own. When nothing of yours is left to update besides the conflict target, the statement degrades to DO NOTHING on PostgreSQL/SQLite and to a no-op ON DUPLICATE KEY UPDATE <col> = <col> on MySQL/MariaDB: a missing row is inserted, a live conflicting row -- version and timestamps included -- is left alone. The one exception is a soft-deleted conflicting row, which is still restored (DO UPDATE SET "deletedAt" = NULL WHERE "order"."deletedAt" IS NOT NULL). Before 2.1 such a call returned { affected: 0 } without sending any SQL, so the missing row was not inserted either.
• Soft delete and unique keys. With an ordinary unique index a trashed row still conflicts, and the upsert restores it with your values -- unlike updateMany(), which skips soft-deleted rows. If you want a new row instead, use a partial unique index (WHERE "deletedAt" IS NULL, PostgreSQL/SQLite) and target it with createInsertBuilder().onConflict(cols, { where }); upsert() cannot name a partial index.
• The INSERT is always attempted. The database checks NOT NULL before it looks for a conflict, so every NOT NULL column without a default must be in the payload even when the row exists. A payload that states no column and no relation sends nothing and returns { affected: 0 }.
• Relations are written as their foreign key. A @ManyToOne or the owner side of a @OneToOne can be given as a related instance, a bare key or the ${property}Id shadow property, as with save() and insertMany(). The key is part of the INSERT and, unless it belongs to the conflict target, is assigned on conflict like any other column; a relation the payload leaves out keeps the stored key. The conflict target takes DB column names, so a unique pair of foreign keys is targeted by its join columns -- em.upsert(Like, { user, post, weight: 3 }, ["user_id", "post_id"]). There is no cascade: a related instance must already carry its primary key. Before 2.1 the key was dropped and the row stored NULL; with a conflict target over foreign keys, every call inserted another row.
• JOINED children are rejected. upsert(), insertIgnore(), batchUpsert(), insertMany(), insertManyAndReturn() and createInsertBuilder() write one table, and a table-per-type child spans two, so they throw UNSUPPORTED_OPERATION; use save() (saveMany() falls back to it for such children).
• insertIgnore() seeds the same values for the row it inserts and never writes a conflicting row, soft-deleted or not.
• createInsertBuilder() seeds inserted rows the same way, but its doUpdate() assigns exactly the columns you list -- no version bump, timestamp refresh or @DeletedAt reset is added.

Return value — { affected: number }

Both upsert() and batchUpsert() return Promise<{ affected: number }>.

const result = await em.upsert(User, { id: 1, name: "Alice" });
console.log(result.affected); // 1 (insert) or 2 (update) on MySQL, 1 on PostgreSQL/SQLite

The affected count is driver-reported as-is — not normalized:

Driver	INSERT	UPDATE	Unchanged row	Conflicting row skipped
MySQL	1	2	1	1
PostgreSQL	1	1	1	0
SQLite	1	1	1	0

MySQL uses affectedRows from ON DUPLICATE KEY UPDATE, which counts an insert as 1 and an update as 2. An existing row set to its current values counts as 1 here rather than the 0 the MySQL manual documents, because mysql2 connects with CLIENT_FOUND_ROWS (matched rows, not changed rows). An entity with @Version has no unchanged row: the conflict branch bumps the version, so MySQL reports 2 even when every value you passed matches the stored row. PostgreSQL and SQLite report 1 for every row they write and 0 for a conflicting row they skip -- because nothing of yours was left to update (see Managed columns on conflict) or, under tenant_column, because another tenant owns it.

batchUpsert() returns { affected: 0 } when the items array is empty.

The repository equivalent is userRepo.batchUpsert(items, conflictColumns).

Under tenantStrategy: "tenant_column"

The conflict branch only ever writes rows belonging to the active tenant. PostgreSQL and SQLite take the predicate as a DO UPDATE ... WHERE:

INSERT INTO "user" ("email", "name", "tenant_id") VALUES ($1, $2, $3)
ON CONFLICT ("email") DO UPDATE SET "name" = EXCLUDED."name"
WHERE "user"."tenant_id" = $4

MySQL/MariaDB has no WHERE on ON DUPLICATE KEY UPDATE, so each assignment carries the guard instead:

INSERT INTO `user` (`email`, `name`, `tenant_id`) VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE `name` = IF(`user`.`tenant_id` = ?, VALUES(`name`), `user`.`name`)

Three consequences worth knowing:

• The tenant column itself is never in the update list, so a conflicting row cannot change owner.
• A row skipped because another tenant owns it is reported as not affected on PostgreSQL and SQLite, and the ORM logs a warning once per entity class. MySQL/MariaDB reports 1 for it (mysql2 connects with CLIENT_FOUND_ROWS, so a matched-but-unchanged row counts) — the same number an insert reports, so read the rows back there if you need certainty.
• When the tenant column was the only column of yours left to update, the statement degrades like any upsert with nothing to update: DO NOTHING on PostgreSQL/SQLite, a no-op ON DUPLICATE KEY UPDATE on MySQL/MariaDB. The insert still happens and the conflicting row is left alone.
• The managed assignments sit under the same guard -- on MySQL/MariaDB the version bump is `version` = IF(`user`.`tenant_id` = ?, COALESCE(`user`.`version`, 0) + 1, `user`.`version`), so another tenant's row is never bumped or restored.

insertIgnore() needs no guard — it never writes an existing row. When another tenant owns the conflicting key, your insert is the one that is dropped.

---

Expression-based Upsert -- createInsertBuilder()

The limit of upsert()

upsert() and batchUpsert() can do exactly one thing with the columns you pass: overwrite the stored value with the proposed one. That is col = EXCLUDED.col, and it is enough for "last write wins". The only stored-row arithmetic they perform is the ORM's own bookkeeping -- the @Version bump (see Managed columns on conflict) -- and it is not available for your columns.

It is not enough the moment the new value depends on the stored value:

records   = stored.records + proposed.records     -- accumulate
last_time = MAX(stored.last_time, proposed.last_time)  -- high-water mark

The obvious workaround -- find() the row, compute in TypeScript, save() it back -- has a race: two concurrent writers both read records = 10, both write 12, and one increment is lost. Making it correct needs SELECT … FOR UPDATE and a held row lock. Doing it in one statement needs no lock at all, because the database evaluates the expression while it holds the row.

createInsertBuilder() is that statement.

Accumulating counters

import { greatest, sql } from "@stingerloom/orm";

await em.createInsertBuilder(SyncMarker)
  .values(buckets)
  .onConflict(["mac", "bucketStart"])
  .doUpdate((t, ex) => ({
    records:  t.records.add(ex.records),
    lastTime: greatest(t.lastTime, ex.lastTime),
    syncedAt: sql`NOW()`,
  }))
  .execute();

doUpdate()'s callback receives two references:

• t -- the row already stored. Renders qualified by the table name ("sync_markers"."records"). PostgreSQL requires that: inside DO UPDATE SET and its WHERE, both the target table and EXCLUDED are in scope, so a bare column name is rejected as ambiguous. MySQL and SQLite accept the same spelling.
• ex -- the row this INSERT proposed. Renders as EXCLUDED."col" (PostgreSQL), excluded."col" (SQLite) or VALUES(`col`) (MySQL).

Both are ordinary qAlias references, so the whole expression vocabulary composes -- .add(), .mul(), coalesce(), greatest(), CASE, JSON paths.

-- PostgreSQL
INSERT INTO "sync_markers" ("mac", "bucket_start", "records", "last_time", "synced_at")
VALUES ($1, $2, $3, $4, $5), ($6, $7, $8, $9, $10)
ON CONFLICT ("mac", "bucket_start") DO UPDATE
   SET "records"   = ("sync_markers"."records" + EXCLUDED."records"),
       "last_time" = GREATEST("sync_markers"."last_time", EXCLUDED."last_time"),
       "synced_at" = NOW()

Building the VALUES list

values() takes one row or an array, and repeated calls accumulate -- convenient when the rows are assembled in a loop:

const builder = em.createInsertBuilder(SyncMarker);
for (const batch of batches) builder.values(batch.rows);

A cell can also hold a raw sql fragment. It is spliced into the tuple as written instead of being bound, so the database evaluates it -- NOW(), a sequence call:

builder.values({ mac, bucketStart, records, syncedAt: sql`NOW()` });
// VALUES ($1, $2, $3, NOW())

Plain values go through the column's write transformer as usual; fragments are the caller's responsibility.

The three forms of doUpdate()

// 1. Overwrite the listed columns with the proposed values -- upsert()'s form,
//    without its @Version / @UpdateTimestamp / @DeletedAt handling
.doUpdate(["name", "email"])

// 2. Literal values and raw SQL
.doUpdate({ status: "seen", seenAt: sql`NOW()` })

// 3. Expressions over both rows
.doUpdate((t, ex) => ({ hits: t.hits.add(ex.hits) }))

Literal values in form 2 go through the column's write transformer, exactly as insertMany() values do.

Skipping conflicts -- doNothing()

await em.createInsertBuilder(AuditLog)
  .values(entries)
  .onConflict(["requestId"])
  .doNothing()
  .execute();

On MySQL this becomes INSERT IGNORE, which downgrades every error in the statement to a warning, not just the duplicate key. That is the same tradeoff insertIgnore() already makes.

Filtering the update -- doUpdateWhere()

Only advance a row when the proposed reading is actually newer. The condition can compare the stored row against the proposed one -- unqualified references read the stored row, qExcluded references the proposed one, exactly as in doUpdate():

import { qAlias, qExcluded } from "@stingerloom/orm";

const m  = qAlias(Reading, "m");
const ex = qExcluded(Reading);

await em.createInsertBuilder(Reading)
  .values(rows)
  .onConflict(["sensorId"])
  .doUpdate((t, x) => ({ value: x.value, takenAt: x.takenAt }))
  .doUpdateWhere(m.takenAt.lt(ex.takenAt))
  .execute();

-- PostgreSQL
… DO UPDATE SET "value" = EXCLUDED."value", "taken_at" = EXCLUDED."taken_at"
  WHERE "readings"."taken_at" < EXCLUDED."taken_at"

Rows failing the predicate are left untouched, so replaying an old batch can never move data backwards -- the write is idempotent without a read-modify-write round trip.

PostgreSQL and SQLite only -- ON DUPLICATE KEY UPDATE takes no WHERE, so this throws on MySQL rather than quietly dropping the predicate. The MySQL equivalent is to fold the condition into each assigned value with iff(), which renders as a CASE expression and runs on all three dialects:

import { iff } from "@stingerloom/orm";

.doUpdate((t, x) => ({
  value:   iff(x.takenAt.gt(t.takenAt), x.value, t.value),
  takenAt: iff(x.takenAt.gt(t.takenAt), x.takenAt, t.takenAt),
}))
// "value" = CASE WHEN EXCLUDED."taken_at" > "taken_at" THEN EXCLUDED."value" ELSE "value" END, …

Every column falls back to its stored value when the guard fails, which is the same outcome -- at the cost of repeating the condition per column.

Partial unique indexes and named constraints

// ON CONFLICT ("email") WHERE "deleted_at" IS NULL DO UPDATE …
.onConflict(["email"], { where: u.deletedAt.isNull() })

// ON CONFLICT ON CONSTRAINT "user_email_key" DO UPDATE …   (PostgreSQL only)
.onConflictConstraint("user_email_key")

{ where } narrows which index arbitrates the conflict (needed when the unique index itself is partial). doUpdateWhere() narrows which conflicting rows get updated. They are different clauses and can be used together.

Inspecting the SQL

build() returns the Sql fragment and toSql() its text plus bound values, without executing:

const { text, values } = em.createInsertBuilder(SyncMarker)
  .values(rows)
  .onConflict(["mac", "bucketStart"])
  .doUpdate((t, ex) => ({ records: t.records.add(ex.records) }))
  .toSql();

Tenant scoping is applied at execute time, so it does not appear in build() output — that covers both the tenant column fill and, under tenant_column, the guard that keeps doUpdate() off rows owned by another tenant (ANDed with your own doUpdateWhere() predicate).

Behavior notes

• Statement-level, like createUpdateBuilder() -- no beforeInsert / afterInsert events and no entity hooks fire. Tenant columns, @CreateTimestamp / @UpdateTimestamp / @Version defaults, generated UUID keys and column transformers are applied to the inserted rows exactly as insertMany() applies them; the conflict action assigns only the columns you list.
• Duplicate keys inside one statement are not merged for you. PostgreSQL rejects a VALUES list that hits the same conflict target twice (ON CONFLICT DO UPDATE command cannot affect row a second time); SQLite applies the rows sequentially so the accumulation compounds. Merge duplicates in the caller before building the statement.
• affected is driver-reported as-is, with the same MySQL 1-vs-2 caveat as upsert().
• The repository equivalent is markerRepo.createInsertBuilder().

Dialect support

Feature	PostgreSQL	MySQL / MariaDB	SQLite
doUpdate() expressions	yes	yes	yes
excluded reference	EXCLUDED.col	VALUES(col)	excluded.col
onConflict([...]) columns	yes	accepted, not emitted	yes
onConflict(..., { where })	yes	throws	yes
onConflictConstraint()	yes	throws	throws
doNothing()	DO NOTHING	INSERT IGNORE	DO NOTHING
doUpdateWhere()	yes	throws	yes

MySQL arbitrates on every unique key at once, so it has no conflict target to name. Passing .onConflict([...]) there is still worth doing -- it keeps the same call portable to PostgreSQL.

---

Transactions

Why transactions?

Every individual EntityManager operation (save, find, delete, etc.) is automatically wrapped in its own transaction. But what happens when you need two operations that must succeed or fail together?

Consider an e-commerce checkout: you create an order and deduct inventory. If the order creation succeeds but the inventory deduction fails, you have an order for items that are still "available" -- a data inconsistency. Transactions solve this by grouping operations into an atomic unit: either everything commits, or everything rolls back.

Callback API -- transaction()

The simplest way to use transactions. The callback receives this EntityManager, and all operations within share the same transaction.

const order = await em.transaction(async (txEm) => {
  const order = await txEm.save(Order, {
    userId: 1,
    status: "pending",
  });

  await txEm.insertMany(OrderItem, [
    { orderId: order.id, productId: 10, quantity: 2 },
    { orderId: order.id, productId: 20, quantity: 1 },
  ]);

  return order;
  // COMMIT on success
});
// If any operation throws -> ROLLBACK automatically

Here is the exact SQL timeline for this transaction:

-- 1. Open a connection and start the transaction
BEGIN

-- 2. Insert the order
INSERT INTO "order" ("userId", "status") VALUES ($1, $2) RETURNING *
-- Parameters: [1, 'pending']

-- 3. Insert order items (single multi-row statement)
INSERT INTO "order_item" ("orderId", "productId", "quantity")
VALUES ($1, $2, $3), ($4, $5, $6)
-- Parameters: [1, 10, 2, 1, 20, 1]

-- 4a. If everything succeeded:
COMMIT

-- 4b. If any query threw an error:
ROLLBACK

The BEGIN and COMMIT/ROLLBACK are handled automatically. You never write them yourself.

What happens on error

If any operation inside the callback throws an exception, the ORM:

1. Catches the exception
2. Executes ROLLBACK to undo all changes made within the transaction
3. Re-throws the original exception so your application code can handle it

This means the database is never left in a half-finished state. Either all changes are applied, or none are.

Deadlock retry

In high-concurrency scenarios (e.g., multiple users purchasing the same product), deadlocks can occur. A deadlock happens when two transactions are each waiting for the other to release a lock -- neither can proceed. The database detects this and kills one of them.

The transaction() method supports automatic retry:

await em.transaction(async (txEm) => {
  const stock = await txEm.findOne(Inventory, {
    where: { productId: 42 },
    lock: LockMode.PESSIMISTIC_WRITE,
  });

  if (stock.quantity < 1) {
    throw new Error("Out of stock");
  }

  stock.quantity -= 1;
  await txEm.save(Inventory, stock);
}, {
  retryOnDeadlock: true,  // Enable deadlock retry
  maxRetries: 3,          // Maximum attempts (default: 3)
  retryDelayMs: 100,      // Delay between retries in ms (default: 100)
});

The ORM detects deadlock errors per dialect:

• MySQL: errno 1213 (ER_LOCK_DEADLOCK)
• PostgreSQL: code 40P01 (deadlock_detected)
• SQLite: SQLITE_BUSY / "database is locked"

When a deadlock is detected, the entire callback is re-executed from scratch. The callback must be idempotent -- it should not have side effects outside the database (e.g., sending emails) that cannot be safely repeated.

Decorator-based transactions

For NestJS services, you can use the @Transactional() decorator instead of the callback API. See Transactions for details on decorator usage, isolation levels, and savepoints.

---

Raw SQL -- query()

Why raw SQL?

The EntityManager API covers 90% of database interactions. But sometimes you need features it does not expose: window functions, CTEs (Common Table Expressions), database-specific syntax, or complex joins that are more readable as raw SQL.

query() gives you an escape hatch to execute any SQL while still benefiting from the ORM's connection management, transaction handling, and parameter binding.

With sql-template-tag (recommended)

import sql from "sql-template-tag";

const users = await em.query<{ id: number; name: string }>(
  sql`SELECT * FROM "user" WHERE "age" > ${18} AND "city" = ${"Seoul"}`
);

This looks like string interpolation, but it is not. The sql template tag from sql-template-tag separates the query text from the parameter values automatically. Here is what actually gets sent to the database:

-- Query text (sent to database)
SELECT * FROM "user" WHERE "age" > $1 AND "city" = $2
-- Parameters (sent separately): [18, 'Seoul']

The database receives the query structure and the values as separate pieces. This means a malicious value like '; DROP TABLE user; -- is treated as a literal string, never as SQL code. This is called parameterized queries, and it is the primary defense against SQL injection.

With string + parameter array

const posts = await em.query<{ id: number; title: string }>(
  "SELECT id, title FROM post WHERE author_id = $1",
  [42]
);

-- Query text
SELECT id, title FROM post WHERE author_id = $1
-- Parameters: [42]

WARNING

When using raw SQL strings, always use parameter binding ($1, ?, etc.). Never concatenate user input directly into the string.

Return type

query<T>() returns T[]. The generic parameter T lets you type the result rows:

interface MonthlyStats {
  month: string;
  total_orders: number;
  revenue: number;
}

const stats = await em.query<MonthlyStats>(sql`
  SELECT
    TO_CHAR("created_at", 'YYYY-MM') AS month,
    COUNT(*) AS total_orders,
    SUM("amount") AS revenue
  FROM "order"
  WHERE "created_at" >= ${startDate}
  GROUP BY TO_CHAR("created_at", 'YYYY-MM')
  ORDER BY month DESC
`);

-- PostgreSQL
SELECT
  TO_CHAR("created_at", 'YYYY-MM') AS month,
  COUNT(*) AS total_orders,
  SUM("amount") AS revenue
FROM "order"
WHERE "created_at" >= $1
GROUP BY TO_CHAR("created_at", 'YYYY-MM')
ORDER BY month DESC
-- Parameters: [startDate]

The T[] return type does not validate at runtime -- it trusts your type annotation. If the SQL returns columns that do not match your interface, TypeScript will not catch it at compile time. Treat the generic parameter as documentation for your team, not a runtime guarantee.

For a full guide on CTEs, UNION, window functions, and subqueries, see Raw SQL & CTE.

---

Next Steps

• CRUD Basics -- save, find, delete, soft delete
• Querying & Pagination -- SELECT, pagination, streaming, aggregates
• Advanced -- Events, subscribers, multi-tenancy, plugins, FindOption reference