sql-macros-derive 0.5.0

Auto generate sql queries for CRUD on sqlx
Documentation
//! `SqlUpdateMany`: one `update_many_by_<field>(items, executor)` method
//! per `#[table(update)]` field — same shape as `SqlUpdate`'s
//! `update_by_<field>`, just batched. Each marked field is independent and
//! gets its own method, never silently combined with another marked
//! field, same as every other per-field derive in this crate.
//!
//! Built on `UPDATE ... SET ... FROM UNNEST(...) AS data(...) WHERE
//! t.<field> = data.<field>` — a *static* SQL string regardless of how
//! many items are in the batch (only the arrays' runtime length varies),
//! so it's checked by `sqlx::query!`/`query_as!` against the live database
//! at compile time, same as every other generated method. See
//! `insert_many.rs`'s module doc for why a plain `sqlx::query!` can't
//! express "however many rows" directly, and `model::unnest_array_type`
//! for why `UNNEST`'s parameters need an explicit Postgres type cast.

use quote::quote;

use crate::model::{self, TableModel};

fn generate_method(
    method_name: &syn::Ident,
    table_name: &str,
    spec_columns: Option<&str>,
    return_type: Option<&syn::Type>,
    return_fields: Option<&str>,
    key: &model::Column,
    set_columns: &[&model::Column],
) -> darling::Result<proc_macro2::TokenStream> {
    // The key column has to be part of the `UNNEST(...)` arrays too (it's
    // what joins `data` back to the real table), so every column — key
    // first, then the `SET` columns — gets transposed into one array.
    let ordered: Vec<&model::Column> = std::iter::once(key).chain(set_columns.iter().copied()).collect();
    let placeholders = model::unnest_placeholders(&ordered)?;
    let collect_stmts = model::unnest_collect_tokens(&ordered);
    let bind_args = model::unnest_bind_tokens(&ordered);
    let data_columns = ordered
        .iter()
        .map(|c| c.ident.to_string())
        .collect::<Vec<_>>()
        .join(", ");

    let sql_set = model::sql_set_clause_from(set_columns, "data");
    let spec_columns_clause = spec_columns.map(|s| format!(", {s}")).unwrap_or_default();
    let key_ident = &key.ident;

    // `AS t` on the target table, and `t.*`/`t.<field>` in `RETURNING`,
    // matter here: a bare `RETURNING *` in an `UPDATE ... FROM ...`
    // context returns columns from *both* the target table and the
    // `UNNEST`-sourced `data` table combined, not just the real row.
    let query = format!(
        "UPDATE {table_name} AS t\nSET {sql_set}{spec_columns_clause}\nFROM UNNEST({placeholders}) AS data({data_columns})\nWHERE t.{key_ident} = data.{key_ident}"
    );
    let returning = return_fields.unwrap_or("t.*");

    let tokens = if let Some(return_type) = return_type {
        let query = format!("{query}\nRETURNING {returning}");
        let doc = model::sql_doc(&query);
        quote! {
            #[doc = #doc]
            pub async fn #method_name<'e, E>(items: &[Self], executor: E) -> Result<Vec<#return_type>, sqlx::Error>
            where
                E: sqlx::PgExecutor<'e>,
            {
                if items.is_empty() {
                    return Ok(Vec::new());
                }

                #collect_stmts
                let objects = sqlx::query_as!(#return_type, #query, #bind_args)
                    .fetch_all(executor)
                    .await?;
                Ok(objects)
            }
        }
    } else {
        let doc = model::sql_doc(&query);
        quote! {
            #[doc = #doc]
            pub async fn #method_name<'e, E>(items: &[Self], executor: E) -> Result<sqlx::any::AnyQueryResult, sqlx::Error>
            where
                E: sqlx::PgExecutor<'e>,
            {
                if items.is_empty() {
                    return Ok(Default::default());
                }

                #collect_stmts
                let result = sqlx::query!(#query, #bind_args).execute(executor).await?;
                Ok(result.into())
            }
        }
    };

    Ok(tokens)
}

pub fn expand(model: &TableModel) -> darling::Result<proc_macro2::TokenStream> {
    let struct_name = &model.struct_name;
    let mut errors = darling::Error::accumulator();
    let mut methods = Vec::new();

    // One method per `#[table(update)]` field — `op` doesn't apply here
    // (unlike `SqlUpdate`'s own `update_by_<field>`): the key is always an
    // equality join against the batch's own array of keys, there's no
    // sensible `>`/`<`/etc. reading for "match this batch of rows".
    for column in model.columns.iter().filter(|c| c.is_update_filter) {
        let method_name =
            syn::Ident::new(&format!("update_many_by_{}", column.ident), column.ident.span());

        let set_columns: Vec<&model::Column> = model
            .columns
            .iter()
            .filter(|c| c.ident != column.ident && !c.is_insert_only)
            .collect();

        if set_columns.is_empty() {
            errors.push(
                darling::Error::custom(format!(
                    "`{method_name}` has no columns left to SET: every field is the key"
                ))
                .with_span(&method_name),
            );
            continue;
        }

        if let Some(tokens) = errors.handle(generate_method(
            &method_name,
            &model.table_name,
            model.spec_columns.as_deref(),
            model.return_type.as_ref(),
            model.return_fields.as_deref(),
            column,
            &set_columns,
        )) {
            methods.push(tokens);
        }
    }

    errors.finish()?;

    Ok(quote! {
        impl #struct_name {
            #(#methods)*
        }
    })
}

#[cfg(test)]
mod tests {
    use super::*;
    use quote::quote as q;

    fn parse(tokens: proc_macro2::TokenStream) -> syn::DeriveInput {
        syn::parse2(tokens).expect("failed to parse test struct")
    }

    #[test]
    fn no_update_filter_marked_generates_no_method() {
        let input = parse(q! {
            struct User { id: i32, email: String }
        });
        let model = TableModel::from_derive_input(&input).unwrap();
        let tokens = expand(&model).unwrap().to_string();
        assert!(!tokens.contains("fn update_many"));
    }

    #[test]
    fn update_many_builds_a_static_query_via_unnest() {
        let input = parse(q! {
            struct User {
                #[table(update)]
                id: i32,
                email: String,
                is_active: bool,
            }
        });
        let model = TableModel::from_derive_input(&input).unwrap();
        let tokens = expand(&model).unwrap().to_string();
        assert!(!tokens.contains("QueryBuilder"));
        assert!(tokens.contains("fn update_many_by_id"));
        assert!(tokens.contains(
            "UPDATE users AS t\\nSET email=data.email, is_active=data.is_active\\n\
             FROM UNNEST($1::int4[],$2::text[],$3::bool[]) AS data(id, email, is_active)\\n\
             WHERE t.id = data.id"
        ));
        assert!(tokens.contains("let id : Vec < i32 >"));
        assert!(tokens.contains("let email : Vec < String >"));
        assert!(tokens.contains("let is_active : Vec < bool >"));
    }

    #[test]
    fn two_update_filter_fields_generate_two_independent_methods() {
        let input = parse(q! {
            struct User {
                #[table(update)]
                id: i32,
                #[table(update)]
                email: String,
                is_active: bool,
            }
        });
        let model = TableModel::from_derive_input(&input).unwrap();
        let tokens = expand(&model).unwrap().to_string();
        assert!(tokens.contains("fn update_many_by_id"));
        assert!(tokens.contains("fn update_many_by_email"));
    }

    #[test]
    fn op_on_an_update_filter_field_is_ignored() {
        // Unlike `SqlUpdate`'s own `update_by_<field>`, `op` doesn't apply
        // to a batch key column — there's only ever one method per field.
        let input = parse(q! {
            struct User {
                #[table(update, op = ["gt", "lt"])]
                id: i32,
                email: String,
            }
        });
        let model = TableModel::from_derive_input(&input).unwrap();
        let tokens = expand(&model).unwrap().to_string();
        assert!(tokens.contains("fn update_many_by_id"));
        assert!(!tokens.contains("fn update_many_by_id_gt"));
        assert!(!tokens.contains("fn update_many_by_id_lt"));
    }

    #[test]
    fn with_return_type_uses_fetch_all_not_fetch_one() {
        // A batch update can affect several rows, unlike `SqlUpdate`'s
        // single-row `update_by_<field>` (which uses `fetch_one`).
        let input = parse(q! {
            #[table(return_type = User)]
            struct User {
                #[table(update)]
                id: i32,
                email: String,
            }
        });
        let model = TableModel::from_derive_input(&input).unwrap();
        let tokens = expand(&model).unwrap().to_string();
        assert!(tokens.contains("fetch_all"));
        assert!(!tokens.contains("fetch_one"));
        assert!(tokens.contains("RETURNING t.*"));
    }

    #[test]
    fn spec_columns_appended_to_set_clause() {
        let input = parse(q! {
            #[table(spec_columns = "updated_at=NOW()")]
            struct User {
                #[table(update)]
                id: i32,
                email: String,
            }
        });
        let model = TableModel::from_derive_input(&input).unwrap();
        let tokens = expand(&model).unwrap().to_string();
        assert!(tokens.contains("SET email=data.email, updated_at=NOW()"));
    }

    #[test]
    fn nullable_field_detects_its_type_and_binds_with_as_cast() {
        // Full expand(), not just `unnest_array_type` in isolation —
        // same reasoning as `insert_many.rs`'s version of this test.
        let input = parse(q! {
            struct User {
                #[table(update)]
                id: i32,
                priority: Option<i32>,
            }
        });
        let model = TableModel::from_derive_input(&input).unwrap();
        let tokens = expand(&model).unwrap().to_string();
        assert!(tokens.contains("UNNEST($1::int4[],$2::int4[])"));
        assert!(tokens.contains("let priority : Vec < Option < i32 > >"));
        assert!(tokens.contains("& priority as _"));
    }

    #[test]
    fn every_field_as_key_is_an_error() {
        let input = parse(q! {
            struct User {
                #[table(update)]
                id: i32,
            }
        });
        let model = TableModel::from_derive_input(&input).unwrap();
        let err = expand(&model).unwrap_err();
        assert!(err.to_string().contains("no columns left to SET"));
    }

    #[test]
    fn insert_only_field_is_excluded_from_the_set_clause() {
        let input = parse(q! {
            struct User {
                #[table(update)]
                id: i32,
                #[table(insert_only)]
                created_by: i32,
                email: String,
            }
        });
        let model = TableModel::from_derive_input(&input).unwrap();
        let tokens = expand(&model).unwrap().to_string();
        assert!(tokens.contains("SET email=data.email"));
        assert!(!tokens.contains("created_by"));
    }
}