pg-upsert

A Rust library for PostgreSQL UPSERT operations using sqlx.

Features

Simple macro-based API for defining fields
Support for ON CONFLICT DO UPDATE and ON CONFLICT DO NOTHING
Optimistic locking with version field support
Batch upsert for multiple rows
Runtime-agnostic (you choose: tokio, async-std, etc.)

Installation

Add to your Cargo.toml:

[dependencies]
pg-upsert = "0.1"
sqlx = { version = "0.8", features = ["runtime-tokio", "postgres"] }
chrono = "0.4"  # Required for date/time field types

Usage

Basic Upsert

use pg_upsert::{upsert, fields, UpsertOptions};
use sqlx::PgPool;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let pool = PgPool::connect("postgres://localhost/mydb").await?;

    let rows_affected = upsert(
        &pool,
        "users",
        &["id"],  // conflict fields
        vec![
            fields!["id" => 1, "name" => "Alice", "email" => "alice@example.com"],
            fields!["id" => 2, "name" => "Bob", "email" => "bob@example.com"],
        ],
        UpsertOptions::default(),
    ).await?;

    println!("Affected rows: {}", rows_affected);
    Ok(())
}

With Version Field (Optimistic Locking)

Only updates if incoming version > existing version:

let rows_affected = upsert(
    &pool,
    "products",
    &["sku"],
    vec![
        fields!["sku" => "ABC123", "price" => 29.99, "version" => 5],
    ],
    UpsertOptions {
        version_field: Some("version".into()),
        ..Default::default()
    },
).await?;

Do Nothing on Conflict

Insert only, skip if row exists:

let rows_affected = upsert(
    &pool,
    "events",
    &["event_id"],
    vec![
        fields!["event_id" => "evt_001", "data" => "payload"],
    ],
    UpsertOptions {
        do_nothing_on_conflict: true,
        ..Default::default()
    },
).await?;

Multiple Conflict Fields

let rows_affected = upsert(
    &pool,
    "inventory",
    &["warehouse_id", "product_id"],  // composite key
    vec![
        fields!["warehouse_id" => 1, "product_id" => 100, "quantity" => 50],
    ],
    UpsertOptions::default(),
).await?;

Supported Field Types

The fields! macro supports:

Integers: i32, i64
Floats: f32, f64
Strings: String, &str
Booleans: bool
Bytes: Vec<u8>
Date/Time: chrono::NaiveDate, chrono::NaiveTime, chrono::NaiveDateTime, chrono::DateTime<Utc>
Optional: Option<T> (becomes NULL when None)

use chrono::{NaiveDate, Utc};

fields![
    "id" => 1_i64,
    "name" => "test",
    "price" => 19.99_f64,
    "active" => true,
    "created_at" => Utc::now(),
    "birth_date" => NaiveDate::from_ymd_opt(1990, 1, 15).unwrap(),
    "description" => None::<String>,  // NULL value
]

Builder Pattern for Options

let options = UpsertOptions::new()
    .with_version_field("version")
    .with_do_nothing_on_conflict(false);

License

MIT

pg-upsert 0.1.0