Upsert and ON CONFLICT

Relevant source files

The following files were used as context for generating this wiki page:

• src/backend/query_builder.rs
• src/backend/mysql/query.rs
• README.md
• src/lib.rs
• tests/mysql/query.rs
• tests/postgres/query.rs
• tests/sqlite/query.rs
• examples/sqlx_mysql/src/main.rs

Upsert functionality in SeaQuery is implemented through the ON CONFLICT (Postgres/SQLite) and ON DUPLICATE KEY UPDATE (MySQL) clauses. This feature allows an INSERT statement to handle row collisions—where a record with the same unique identifier already exists—by either performing an UPDATE on specific columns or simply doing nothing. This abstraction provides a unified API to construct these complex queries across different database backends.

Sources: README.md:183-195, src/backend/query_builder.rs:104, src/backend/mysql/query.rs:242

Core Architecture and Data Structures

The Upsert logic is centered around the OnConflict structure, which is integrated into the InsertStatement. When a query builder processes an InsertStatement, it checks for an optional OnConflict definition to append the appropriate conflict-handling clause to the SQL string.

OnConflict Components

The OnConflict mechanism consists of several key structural elements that define the target of the conflict and the resulting action:

• Targets: Specifies what triggers the conflict. This can be column identifiers, expressions, or a specific constraint name.
• Actions: Defines the behavior upon conflict. The primary actions are DoNothing or Update.
• Update Strategies: When an update action is chosen, the system defines how columns should be updated, such as using values from the "excluded" row or specific expressions.

Sources: src/backend/query_builder.rs:963-965, src/backend/query_builder.rs:977-985, src/backend/query_builder.rs:1022-1033

Logic Flow for Query Generation

The following diagram illustrates how the QueryBuilder processes the OnConflict portion of an InsertStatement.

The query generation flow involves conditional hooks based on the database backend to handle syntax variations between MySQL, Postgres, and SQLite.
Sources: src/backend/query_builder.rs:963-973

Backend Specific Implementations

SeaQuery translates the abstract OnConflict definition into backend-specific SQL. There are significant differences between how MySQL and Postgres/SQLite handle these conflicts.

Postgres and SQLite (ON CONFLICT)

Postgres and SQLite share a similar syntax. They support defining conflict targets (columns or constraints) and use the excluded table alias to reference values that were originally intended for insertion.

• Conflict Targets: Written as a parenthesized list of columns or ON CONSTRAINT "name".
• Excluded Values: Referenced via "excluded"."column_name".

Sources: src/backend/query_builder.rs:1008-1017, src/backend/query_builder.rs:1062-1067, tests/postgres/query.rs:1059-1065

MySQL (ON DUPLICATE KEY UPDATE)

MySQL uses a different keyword and does not require a conflict target specification. Instead, it applies to any duplicate key collision.

• Syntax: Uses ON DUPLICATE KEY UPDATE.
• Excluded Values: Referenced using the VALUES(column_name) function.
• Do Nothing Hack: MySQL does not have a native DO NOTHING. SeaQuery implements this by generating an update that sets a column to its existing value (e.g., id = id) or using the IGNORE keyword if no columns are specified.

Sources: src/backend/mysql/query.rs:242-255, src/backend/mysql/query.rs:211-235, tests/mysql/query.rs:829-840

Syntax Summary Table

Feature	Postgres / SQLite	MySQL
Primary Keyword	ON CONFLICT	ON DUPLICATE KEY
Action Keyword	DO UPDATE SET / DO NOTHING	UPDATE / IGNORE
Target Selection	Required (Columns/Constraints)	Not used
Excluded Ref	"excluded"."col"	VALUES("col")
Constraint Target	ON CONSTRAINT "name"	Not supported

Sources: src/backend/query_builder.rs:963-1090, src/backend/mysql/query.rs:211-255

Component Deep Dive

Conflict Targets (OnConflictTarget)

Targets define the scope of the collision detection.

This relationship allows developers to target specific unique indexes or named constraints during an upsert.
Sources: src/backend/query_builder.rs:977-985, src/backend/query_builder.rs:991-1006

Update Strategies (OnConflictUpdate)

When performing an update, there are two primary strategies:

1. Column: Automatically takes the value from the "excluded" row.
2. Expression: Uses a custom expression to determine the new value (e.g., count = count + 1).

Sources: src/backend/query_builder.rs:1041-1053

Code Example: Cross-Backend Upsert

The same SeaQuery code generates appropriate SQL for different engines.

let query = Query::insert()
    .into_table(Glyph::Table)
    .columns([Glyph::Aspect, Glyph::Image])
    .values_panic([2.into(), 3.into()])
    .on_conflict(
        OnConflict::column(Glyph::Id)
            .update_columns([Glyph::Aspect, Glyph::Image])
            .to_owned(),
    )
    .to_owned();

// Postgres Result:
// INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) 
// ON CONFLICT ("id") DO UPDATE SET "aspect" = "excluded"."aspect", "image" = "excluded"."image"

// MySQL Result:
// INSERT INTO `glyph` (`aspect`, `image`) VALUES (2, 3) 
// ON DUPLICATE KEY UPDATE `aspect` = VALUES(`aspect`), `image` = VALUES(`image`)

Sources: README.md:195-207, examples/sqlx_mysql/src/main.rs:183-195

Conclusion

The Upsert and ON CONFLICT system in SeaQuery provides a robust abstraction for handling data collisions during insertion. By encapsulating backend differences into a unified OnConflict structure, it allows developers to write database-agnostic code while still leveraging advanced features like expression-based updates and constraint-specific conflict targets.

Sources: src/lib.rs:242-255, src/backend/query_builder.rs:963-973