---
import DocsLayout from '../../layouts/DocsLayout.astro';
import CodeBlock from '../../components/CodeBlock.astro';
const basicUpsert = `use prax_query::upsert::{Upsert, ConflictTarget};
// PostgreSQL: ON CONFLICT DO UPDATE
let sql = Upsert::new("users")
.columns(["email", "name", "updated_at"])
.values(["$1", "$2", "$3"])
.on_conflict(ConflictTarget::columns(["email"]))
.do_update(["name", "updated_at"])
.to_postgres_sql();
// INSERT INTO users (email, name, updated_at)
// VALUES ($1, $2, $3)
// ON CONFLICT (email)
// DO UPDATE SET name = EXCLUDED.name, updated_at = EXCLUDED.updated_at`;
const doNothing = `use prax_query::upsert::{Upsert, ConflictTarget};
// INSERT or ignore on conflict
let sql = Upsert::new("users")
.columns(["email", "name"])
.values(["$1", "$2"])
.on_conflict(ConflictTarget::columns(["email"]))
.do_nothing()
.to_postgres_sql();
// INSERT INTO users (email, name) VALUES ($1, $2) ON CONFLICT (email) DO NOTHING
// MySQL equivalent: the same .do_nothing() renders as INSERT IGNORE
let sql = Upsert::new("users")
.columns(["email", "name"])
.values(["?", "?"])
.do_nothing()
.to_mysql_sql();
// INSERT IGNORE INTO users (email, name) VALUES (?, ?)`;
const conflictTargets = `use prax_query::upsert::ConflictTarget;
// Conflict on specific columns
let by_columns = ConflictTarget::columns(["tenant_id", "email"]);
// Conflict on constraint name (renders ON CONSTRAINT <name> on Postgres)
let by_constraint = ConflictTarget::constraint("users_email_unique");
// Conflict on index expression (PostgreSQL)
let by_expression = ConflictTarget::index_expression("LOWER(email)");
// No explicit target (MySQL ON DUPLICATE KEY)
let implicit = ConflictTarget::Implicit;`;
const conditionalUpdate = `use prax_query::upsert::{Assignment, AssignmentValue, ConflictTarget, Upsert};
// Conditional update with WHERE — .where_clause() goes on the Upsert
let sql = Upsert::new("products")
.columns(["sku", "price", "stock", "updated_at"])
.values(["$1", "$2", "$3", "$4"])
.on_conflict(ConflictTarget::columns(["sku"]))
.do_update(["price", "stock", "updated_at"])
.where_clause("products.updated_at < EXCLUDED.updated_at") // Only update if newer
.to_postgres_sql();
// Custom update expressions via Assignment values
let sql = Upsert::new("counters")
.columns(["key", "value"])
.values(["$1", "$2"])
.on_conflict(ConflictTarget::columns(["key"]))
.do_update_set(vec![
Assignment {
column: "value".into(),
value: AssignmentValue::Expression("counters.value + EXCLUDED.value".into()),
}, // Increment
Assignment {
column: "updated_at".into(),
value: AssignmentValue::Expression("NOW()".into()),
},
])
.to_postgres_sql();`;
const mysqlUpsert = `use prax_query::upsert::{Assignment, AssignmentValue, Upsert};
// MySQL: ON DUPLICATE KEY UPDATE — the same .do_update() builder,
// rendered with MySQL syntax by .to_mysql_sql()
let sql = Upsert::new("users")
.columns(["email", "name", "login_count"])
.values(["?", "?", "?"])
.do_update(["name", "login_count"])
.to_mysql_sql();
// INSERT INTO users (email, name, login_count)
// VALUES (?, ?, ?)
// ON DUPLICATE KEY UPDATE
// name = VALUES(name),
// login_count = VALUES(login_count)
// With custom expressions
let sql = Upsert::new("counters")
.columns(["key", "value"])
.values(["?", "?"])
.do_update_set(vec![
Assignment {
column: "value".into(),
value: AssignmentValue::Expression("value + 1".into()),
},
Assignment {
column: "updated_at".into(),
value: AssignmentValue::Expression("NOW()".into()),
},
])
.to_mysql_sql();`;
const mssqlMerge = `use prax_query::upsert::{ConflictTarget, Upsert};
// MSSQL: MERGE statement — generated from the same Upsert struct
let sql = Upsert::new("users")
.columns(["email", "name", "updated_at"])
.values(["@P1", "@P2", "@P3"])
.on_conflict(ConflictTarget::columns(["email"]))
.do_update(["name", "updated_at"])
.to_mssql_sql();
// MERGE INTO users AS target
// USING (SELECT @P1 AS email, @P2 AS name, @P3 AS updated_at) AS source
// ON target.email = source.email
// WHEN MATCHED THEN
// UPDATE SET target.name = source.name, target.updated_at = source.updated_at
// WHEN NOT MATCHED THEN
// INSERT (email, name, updated_at)
// VALUES (source.email, source.name, source.updated_at);`;
const mongoUpsert = `use prax_query::upsert::mongodb::{self, BulkUpsert};
// Build a MongoDB upsert payload (no client needed — the builder
// produces the filter/update document you hand to the driver)
let upsert = mongodb::upsert()
.filter_eq("email", "alice@example.com")
.set("name", "Alice")
.set("updatedAt", now)
.set_on_insert("createdAt", now) // Only on insert
.inc("loginCount", 1)
.build();
// updateOne with upsert: true
let op = upsert.to_update_one();
// { "filter": ..., "update": ..., "options": { "upsert": true } }
// findOneAndUpdate returning the post-update document
let op = upsert.to_find_one_and_update(true);
// Replace semantics (full document replacement)
let op = upsert.to_replace_one(replacement);
// Bulk upsert with bulkWrite
let mut alice = serde_json::Map::new();
alice.insert("email".to_string(), serde_json::json!("alice@example.com"));
let mut bob = serde_json::Map::new();
bob.insert("email".to_string(), serde_json::json!("bob@example.com"));
let bulk = BulkUpsert::new()
.ordered(false)
.add(alice, serde_json::json!({ "$set": { "name": "Alice" } }))
.add(bob, serde_json::json!({ "$set": { "name": "Bob" } }));
let doc = bulk.to_bulk_write();
// { "operations": [
// { "updateOne": { "filter": ..., "update": ..., "upsert": true } }, ... ],
// "options": { "ordered": false } }`;
const bulkUpsert = `use prax_query::upsert::{ConflictTarget, Upsert};
// BulkUpsert is MongoDB-only — there is no multi-row SQL bulk upsert
// type. For SQL backends, build one Upsert per row and execute the
// statements inside a transaction.
for (sku, name, price) in rows {
let sql = Upsert::new("products")
.columns(["sku", "name", "price"])
.values(["$1", "$2", "$3"]) // bind (sku, name, price) as params
.on_conflict(ConflictTarget::columns(["sku"]))
.do_update(["name", "price"])
.returning(["id", "sku", "created_at"])
.to_postgres_sql();
// Execute sql with the row's params inside the transaction...
}`;
const praxUpsert = `// Using Prax's fluent API
let user = client
.user()
.upsert()
.where(user::email::equals("alice@example.com"))
.create_set("email", "alice@example.com")
.create_set("name", "Alice")
.update_set("name", "Alice Updated")
.exec()
.await?;
// Or apply the generated typed inputs (UserCreateInput / UserUpdateInput)
let user = client
.user()
.upsert()
.where(user::email::equals("alice@example.com"))
.with_create_input(UserCreateInput {
email: "alice@example.com".into(),
name: Some("Alice".into()),
..Default::default()
})
.with_update_input(UserUpdateInput {
name: Some("Alice Updated".into()),
..Default::default()
})
.exec()
.await?;
// create_many with skip_duplicates — ON CONFLICT DO NOTHING on
// Postgres/SQLite, INSERT IGNORE on MySQL
let count = client
.user()
.create_many()
.with_create_inputs([
UserCreateInput { email: "a@example.com".into(), ..Default::default() },
UserCreateInput { email: "b@example.com".into(), ..Default::default() },
UserCreateInput { email: "c@example.com".into(), ..Default::default() },
])
.skip_duplicates()
.exec()
.await?;`;
---
<DocsLayout title="Upsert & Conflict Resolution - Prax ORM">
<article class="max-w-4xl mx-auto px-6 py-12">
<header class="mb-12">
<h1 class="text-4xl font-bold mb-4">Upsert & Conflict Resolution</h1>
<p class="text-xl text-muted">
Insert or update records atomically with conflict handling across all databases.
</p>
</header>
<div class="space-y-12">
<!-- Introduction -->
<section>
<h2 class="text-2xl font-semibold mb-4">Overview</h2>
<p class="text-muted mb-4">
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.
</p>
<div class="overflow-x-auto">
<table class="w-full text-sm">
<thead>
<tr class="border-b border-border">
<th class="text-left py-3 px-4 font-semibold">Database</th>
<th class="text-left py-3 px-4 font-semibold">Syntax</th>
<th class="text-left py-3 px-4 font-semibold">Features</th>
</tr>
</thead>
<tbody class="text-muted">
<tr class="border-b border-border">
<td class="py-3 px-4">PostgreSQL</td>
<td class="py-3 px-4"><code>ON CONFLICT DO UPDATE/NOTHING</code></td>
<td class="py-3 px-4">Column/constraint targets, WHERE, EXCLUDED</td>
</tr>
<tr class="border-b border-border">
<td class="py-3 px-4">MySQL</td>
<td class="py-3 px-4"><code>ON DUPLICATE KEY UPDATE</code></td>
<td class="py-3 px-4">VALUES(), INSERT IGNORE</td>
</tr>
<tr class="border-b border-border">
<td class="py-3 px-4">SQLite</td>
<td class="py-3 px-4"><code>ON CONFLICT DO UPDATE</code></td>
<td class="py-3 px-4">Column targets, excluded.column</td>
</tr>
<tr class="border-b border-border">
<td class="py-3 px-4">MSSQL</td>
<td class="py-3 px-4"><code>MERGE INTO...WHEN MATCHED</code></td>
<td class="py-3 px-4">Update/delete/insert, OUTPUT</td>
</tr>
<tr class="border-b border-border">
<td class="py-3 px-4">MongoDB</td>
<td class="py-3 px-4"><code>updateOne({ upsert: true })</code></td>
<td class="py-3 px-4">$setOnInsert, bulkWrite</td>
</tr>
</tbody>
</table>
</div>
</section>
<!-- Basic Upsert -->
<section>
<h2 class="text-2xl font-semibold mb-4">Basic Upsert</h2>
<p class="text-muted mb-4">
Insert a record, or update if a conflict occurs on the specified columns.
</p>
<CodeBlock code={basicUpsert} lang="rust" filename="src/main.rs" />
</section>
<!-- Do Nothing -->
<section>
<h2 class="text-2xl font-semibold mb-4">Insert or Ignore</h2>
<p class="text-muted mb-4">
Skip insertion if a conflict occurs without throwing an error.
</p>
<CodeBlock code={doNothing} lang="rust" filename="src/main.rs" />
</section>
<!-- Conflict Targets -->
<section>
<h2 class="text-2xl font-semibold mb-4">Conflict Targets</h2>
<p class="text-muted mb-4">
Specify what constitutes a conflict: columns, constraints, or expressions.
</p>
<CodeBlock code={conflictTargets} lang="rust" filename="src/main.rs" />
</section>
<!-- Conditional Update -->
<section>
<h2 class="text-2xl font-semibold mb-4">Conditional Updates</h2>
<p class="text-muted mb-4">
Only update when certain conditions are met, or use custom expressions.
</p>
<CodeBlock code={conditionalUpdate} lang="rust" filename="src/main.rs" />
</section>
<!-- MySQL -->
<section>
<h2 class="text-2xl font-semibold mb-4">MySQL ON DUPLICATE KEY</h2>
<p class="text-muted mb-4">
MySQL's upsert syntax uses <code class="px-2 py-1 bg-surface-elevated rounded">ON DUPLICATE KEY UPDATE</code>.
</p>
<CodeBlock code={mysqlUpsert} lang="rust" filename="src/main.rs" />
</section>
<!-- MSSQL -->
<section>
<h2 class="text-2xl font-semibold mb-4">MSSQL MERGE Statement</h2>
<p class="text-muted mb-4">
MSSQL uses the powerful MERGE statement for upsert operations.
</p>
<CodeBlock code={mssqlMerge} lang="rust" filename="src/main.rs" />
</section>
<!-- MongoDB -->
<section>
<h2 class="text-2xl font-semibold mb-4">MongoDB Upsert</h2>
<p class="text-muted mb-4">
MongoDB's native upsert with updateOne and bulkWrite.
</p>
<CodeBlock code={mongoUpsert} lang="rust" filename="src/main.rs" />
</section>
<!-- Bulk Upsert -->
<section>
<h2 class="text-2xl font-semibold mb-4">Bulk Upsert</h2>
<p class="text-muted mb-4">
<code class="px-2 py-1 bg-surface-elevated rounded">BulkUpsert</code> is MongoDB-only;
for SQL backends, build one <code class="px-2 py-1 bg-surface-elevated rounded">Upsert</code>
per row and execute the statements in a transaction.
</p>
<CodeBlock code={bulkUpsert} lang="rust" filename="src/main.rs" />
</section>
<!-- Prax API -->
<section>
<h2 class="text-2xl font-semibold mb-4">Prax Fluent API</h2>
<p class="text-muted mb-4">
Use Prax's type-safe upsert methods on model clients.
</p>
<CodeBlock code={praxUpsert} lang="rust" filename="src/main.rs" />
</section>
<!-- Best Practices -->
<section>
<h2 class="text-2xl font-semibold mb-4">Best Practices</h2>
<div class="grid gap-4">
<div class="p-4 rounded-xl bg-surface border border-border">
<h4 class="font-semibold mb-2 text-success-400">Use Unique Constraints</h4>
<p class="text-muted text-sm">
Upsert relies on unique constraints or indexes. Ensure your conflict target
columns have proper constraints defined.
</p>
</div>
<div class="p-4 rounded-xl bg-surface border border-border">
<h4 class="font-semibold mb-2 text-success-400">Consider Race Conditions</h4>
<p class="text-muted text-sm">
Upsert is atomic, but concurrent upserts on different columns may still
conflict. Design your conflict targets carefully.
</p>
</div>
<div class="p-4 rounded-xl bg-surface border border-border">
<h4 class="font-semibold mb-2 text-warning-400">Avoid Upsert for Large Batches</h4>
<p class="text-muted text-sm">
For very large batches, consider staging tables with a separate merge step
for better performance and control.
</p>
</div>
<div class="p-4 rounded-xl bg-surface border border-border">
<h4 class="font-semibold mb-2 text-info-400">Use RETURNING/OUTPUT</h4>
<p class="text-muted text-sm">
Get back the inserted/updated rows to know which operation occurred
and to retrieve generated values like IDs.
</p>
</div>
</div>
</section>
</div>
</article>
</DocsLayout>