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:

use pg_upsert::FieldValue;

let rows_affected = upsert(
    &pool,
    "products",
    &["sku"],
    vec![
        fields![
            "sku" => "ABC123",
            "price" => FieldValue::Numeric("29.99".to_string()),
            "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?;

High-Precision Numeric Values

For financial data or any scenario requiring exact decimal precision:

use pg_upsert::FieldValue;

let rows_affected = upsert(
    &pool,
    "transactions",
    &["transaction_id"],
    vec![
        fields![
            "transaction_id" => "TXN-001",
            "amount" => FieldValue::Numeric("1234567.89".to_string()),
            "fee" => FieldValue::Numeric("0.0025".to_string()),
            "balance" => FieldValue::Numeric("99999999.999999".to_string())
        ],
    ],
    UpsertOptions::default(),
).await?;

Supported Field Types

The fields! macro supports:

• Integers: i32, i64
• Floats: f32, f64
• Strings: String, &str
• Numeric: FieldValue::Numeric(String) for high-precision decimal values (PostgreSQL NUMERIC/DECIMAL types)
• 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};
use pg_upsert::FieldValue;

fields![
    "id" => 1_i64,
    "name" => "test",
    "price" => 19.99_f64,
    "balance" => FieldValue::Numeric("99999.999999999999".to_string()),  // High-precision numeric
    "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