Upsert & Conflict Resolution

Insert or update records atomically with conflict handling across all databases.

Overview

Upsert operations insert a new record or update an existing one based on conflict detection. Each database has its own syntax, but Prax provides a unified API.

Database	Syntax	Features
PostgreSQL	`ON CONFLICT DO UPDATE/NOTHING`	Column/constraint targets, WHERE, EXCLUDED
MySQL	`ON DUPLICATE KEY UPDATE`	VALUES(), INSERT IGNORE
SQLite	`ON CONFLICT DO UPDATE`	Column targets, excluded.column
MSSQL	`MERGE INTO...WHEN MATCHED`	Update/delete/insert, OUTPUT
MongoDB	`updateOne({ upsert: true })`	$setOnInsert, bulkWrite

Basic Upsert

Insert a record, or update if a conflict occurs on the specified columns.

Insert or Ignore

Skip insertion if a conflict occurs without throwing an error.

Conflict Targets

Specify what constitutes a conflict: columns, constraints, or expressions.

Conditional Updates

Only update when certain conditions are met, or use custom expressions.

MySQL ON DUPLICATE KEY

MySQL's upsert syntax uses ON DUPLICATE KEY UPDATE.

MSSQL MERGE Statement

MSSQL uses the powerful MERGE statement for upsert operations.

MongoDB Upsert

MongoDB's native upsert with updateOne and bulkWrite.

Bulk Upsert

Upsert multiple records in a single query.

Prax Fluent API

Use Prax's type-safe upsert methods on model clients.

Best Practices

Use Unique Constraints

Upsert relies on unique constraints or indexes. Ensure your conflict target columns have proper constraints defined.

Consider Race Conditions

Upsert is atomic, but concurrent upserts on different columns may still conflict. Design your conflict targets carefully.

Avoid Upsert for Large Batches

For very large batches, consider staging tables with a separate merge step for better performance and control.

Use RETURNING/OUTPUT

Get back the inserted/updated rows to know which operation occurred and to retrieve generated values like IDs.