pg-upsert 0.1.1

# 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`:

```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

```rust
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:

```rust
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:

```rust
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

```rust
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:

```rust
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)

```rust
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

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

## License

MIT