sqlx-record/.claude/skills/sqlx-record/sqlx-update-expr.md

sqlx-record UpdateExpr Skill

Guide to advanced update operations with eval_* methods.

Triggers

• "update expression", "update expr"
• "increment field", "decrement field"
• "case when update", "conditional update"
• "arithmetic update", "column arithmetic"

Overview

UpdateExpr enables complex update operations beyond simple value assignment:

• Column arithmetic (count = count + 1)
• CASE/WHEN conditional updates
• Conditional increments/decrements
• Raw SQL escape hatch

UpdateExpr Enum

pub enum UpdateExpr {
    Set(Value),                    // column = value
    Add(Value),                    // column = column + value
    Sub(Value),                    // column = column - value
    Mul(Value),                    // column = column * value
    Div(Value),                    // column = column / value
    Mod(Value),                    // column = column % value
    Case {
        branches: Vec<(Filter<'static>, Value)>,
        default: Value,
    },
    AddIf { condition: Filter<'static>, value: Value },
    SubIf { condition: Filter<'static>, value: Value },
    Coalesce(Value),              // COALESCE(column, value)
    Greatest(Value),              // GREATEST(column, value)
    Least(Value),                 // LEAST(column, value)
    Raw { sql: String, values: Vec<Value> },
}

Generated Methods

For each non-binary field, an eval_{field} method is generated:

// Generated for: count: i32
pub fn eval_count(mut self, expr: UpdateExpr) -> Self

// Generated for: score: i64
pub fn eval_score(mut self, expr: UpdateExpr) -> Self

// Generated for: status: String
pub fn eval_status(mut self, expr: UpdateExpr) -> Self

Binary fields (Vec<u8>) do not get eval_* methods.

Precedence

eval_* methods take precedence over with_* if both are set for the same field:

let form = User::update_form()
    .with_count(100)                        // This is ignored
    .eval_count(UpdateExpr::Add(1.into())); // This is used

Usage Examples

Simple Arithmetic

// Increment
let form = User::update_form()
    .eval_count(UpdateExpr::Add(1.into()));  // count = count + 1

// Decrement
let form = User::update_form()
    .eval_balance(UpdateExpr::Sub(50.into()));  // balance = balance - 50

// Multiply
let form = User::update_form()
    .eval_score(UpdateExpr::Mul(2.into()));  // score = score * 2

CASE/WHEN Conditional

// Update status based on score
let form = User::update_form()
    .eval_tier(UpdateExpr::Case {
        branches: vec![
            ("score".gt(1000), "platinum".into()),
            ("score".gt(500), "gold".into()),
            ("score".gt(100), "silver".into()),
        ],
        default: "bronze".into(),
    });
// SQL: tier = CASE
//        WHEN score > ? THEN ?
//        WHEN score > ? THEN ?
//        WHEN score > ? THEN ?
//        ELSE ? END

Conditional Increment

// Add bonus only for premium users
let form = User::update_form()
    .eval_balance(UpdateExpr::AddIf {
        condition: "is_premium".eq(true),
        value: 100.into(),
    });
// SQL: balance = CASE WHEN is_premium = ? THEN balance + ? ELSE balance END

Using Filters with Case

use sqlx_record::prelude::*;

// Complex condition with AND
let form = User::update_form()
    .eval_discount(UpdateExpr::Case {
        branches: vec![
            (Filter::And(vec![
                "orders".gt(10),
                "is_vip".eq(true),
            ]), 20.into()),
            ("orders".gt(5), 10.into()),
        ],
        default: 0.into(),
    });

Coalesce (NULL handling)

// Set to value if NULL
let form = User::update_form()
    .eval_nickname(UpdateExpr::Coalesce("Anonymous".into()));
// SQL: nickname = COALESCE(nickname, ?)

Greatest/Least

// Ensure minimum value (clamp)
let form = User::update_form()
    .eval_balance(UpdateExpr::Greatest(0.into()));  // balance = GREATEST(balance, 0)

// Ensure maximum value (cap)
let form = User::update_form()
    .eval_score(UpdateExpr::Least(100.into()));  // score = LEAST(score, 100)

Raw SQL Escape Hatch

// Simple expression without parameters
let form = User::update_form()
    .raw("computed", "COALESCE(a, 0) + COALESCE(b, 0)");

// Expression with bind parameters
let form = User::update_form()
    .raw_with_values("adjusted", "ROUND(price * ? * (1 - discount / 100))", values![1.1]);

// Multiple placeholders
let form = User::update_form()
    .raw_with_values("stats", "JSON_SET(stats, '$.views', JSON_EXTRACT(stats, '$.views') + ?)", values![1]);

Combining with Simple Updates

let form = User::update_form()
    .with_name("Alice")                           // Simple value update
    .with_email("alice@example.com")              // Simple value update
    .eval_login_count(UpdateExpr::Add(1.into())) // Arithmetic
    .eval_last_login(UpdateExpr::Set(            // Expression set
        Value::NaiveDateTime(Utc::now().naive_utc())
    ));

User::update_by_id(&pool, &user_id, form).await?;

Full Update Flow

use sqlx_record::prelude::*;

#[derive(Entity, FromRow)]
#[table_name = "game_scores"]
struct GameScore {
    #[primary_key]
    id: Uuid,
    player_id: Uuid,
    score: i64,
    high_score: i64,
    games_played: i32,
    tier: String,
}

async fn record_game(pool: &Pool, id: &Uuid, new_score: i64) -> Result<(), Error> {
    let form = GameScore::update_form()
        // Increment games played
        .eval_games_played(UpdateExpr::Add(1.into()))
        // Update high score if this score is higher
        .eval_high_score(UpdateExpr::Greatest(new_score.into()))
        // Set current score
        .with_score(new_score)
        // Update tier based on high score
        .eval_tier(UpdateExpr::Case {
            branches: vec![
                ("high_score".gt(10000), "master".into()),
                ("high_score".gt(5000), "expert".into()),
                ("high_score".gt(1000), "advanced".into()),
            ],
            default: "beginner".into(),
        });

    GameScore::update_by_id(pool, id, form).await
}

SQL Generation

The update_stmt_with_values() method generates SQL and collects bind values:

let form = User::update_form()
    .with_name("Alice")
    .eval_count(UpdateExpr::Add(5.into()));

let (sql, values) = form.update_stmt_with_values();
// sql: "name = ?, count = count + ?"
// values: [Value::String("Alice"), Value::Int32(5)]

Database Compatibility

All UpdateExpr variants generate standard SQL that works across:

• MySQL
• PostgreSQL
• SQLite

Note: Greatest and Least use SQL functions that are available in all three databases.