# 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.