sql-macros-derive 0.5.0

Auto generate sql queries for CRUD on sqlx
Documentation
use quote::quote;

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

/// Everything about a single `SqlInsertMany` derive that's the same across
/// every generated method (the default `insert_many()` and any
/// composite-upsert methods) — only `method_name` and `targets` vary per
/// method.
struct InsertManyContext<'a> {
    table_name: &'a str,
    column_names: String,
    placeholders: String,
    all: Vec<&'a model::Column>,
    return_type: Option<&'a syn::Type>,
    return_fields: Option<&'a str>,
}

fn generate_method(
    ctx: &InsertManyContext<'_>,
    method_name: &syn::Ident,
    targets: &[&model::Column],
) -> proc_macro2::TokenStream {
    let InsertManyContext {
        table_name,
        column_names,
        placeholders,
        all,
        return_type,
        return_fields,
    } = ctx;
    // `INSERT ... SELECT * FROM UNNEST($1, $2, ...)` instead of a
    // `VALUES (...), (...), ...` list: the SQL text stays *static*
    // regardless of how many rows are in `items` (only the arrays' runtime
    // length varies), so this is checkable by `sqlx::query!`/`query_as!`
    // against the live database at compile time — same as every other
    // generated method, unlike the old `sqlx::QueryBuilder`-based version.
    let collect_stmts = model::unnest_collect_tokens(all);
    let bind_args = model::unnest_bind_tokens(all);

    let query = format!("INSERT INTO {table_name} ({column_names})\nSELECT * FROM UNNEST({placeholders})");
    let query = match model::upsert_clause(all, targets) {
        Some(clause) => format!("{query}{clause}"),
        None => query,
    };
    let returning = return_fields.unwrap_or("*");

    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())
            }
        }
    }
}

pub fn expand(model: &TableModel) -> darling::Result<proc_macro2::TokenStream> {
    let struct_name = &model.struct_name;

    let all: Vec<&model::Column> = model.columns.iter().collect();
    let placeholders = model::unnest_placeholders(&all)?;

    let ctx = InsertManyContext {
        table_name: &model.table_name,
        column_names: model.column_names(),
        placeholders,
        all,
        return_type: model.return_type.as_ref(),
        return_fields: model.return_fields.as_deref(),
    };

    let mut errors = darling::Error::accumulator();
    let mut methods = Vec::new();

    // `insert_many()` — always generated, always plain (no `ON CONFLICT`).
    let default_method_name = syn::Ident::new("insert_many", struct_name.span());
    methods.push(generate_method(&ctx, &default_method_name, &[]));

    // One `upsert_many_by_<field>` method per bare-`#[table(upsert)]` field
    // — `_many_by_` (not `_by_`) so this doesn't collide with `SqlInsert`'s
    // `upsert_by_<field>` when a struct derives both.
    for column in model.upsert_targets() {
        let method_name =
            syn::Ident::new(&format!("upsert_many_by_{}", column.ident), column.ident.span());
        methods.push(generate_method(&ctx, &method_name, &[column]));
    }

    // Struct-level composite upsert, e.g. `#[table(upsert =
    // mark_done(user_id, todo_id))]`. Named `<method_name>_many` (not bare
    // `<method_name>`) — same `insert`/`insert_many` split — so this
    // doesn't collide with `SqlInsert`'s own method of that name when a
    // struct derives both.
    if let Some(customs) = errors.handle(model.custom_upsert_methods()) {
        for custom in customs {
            let method_name =
                syn::Ident::new(&format!("{}_many", custom.method_name), custom.method_name.span());
            methods.push(generate_method(&ctx, &method_name, &custom.targets));
        }
    }

    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 plain_insert_many_has_no_on_conflict_clause() {
        let input = parse(q! {
            struct CreateUser { email: String }
        });
        let model = TableModel::from_derive_input(&input).unwrap();
        let tokens = expand(&model).unwrap().to_string();
        assert!(!tokens.contains("ON CONFLICT"));
    }

    #[test]
    fn insert_many_builds_a_static_query_via_unnest() {
        // Regression: this used to be `sqlx::QueryBuilder` building
        // `VALUES (...), (...), ...` at runtime — a static SQL string that
        // `sqlx::query!`/`query_as!` can check against the live database at
        // compile time, unlike the old runtime-built version.
        let input = parse(q! {
            struct CreateUser { 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(
            "INSERT INTO create_users (email, is_active)\\nSELECT * FROM UNNEST($1::text[],$2::bool[])"
        ));
        assert!(tokens.contains("let email : Vec < String >"));
        assert!(tokens.contains("let is_active : Vec < bool >"));
        assert!(tokens.contains("sqlx :: query !"));
    }

    #[test]
    fn nullable_field_detects_its_type_and_binds_with_as_cast() {
        // Full expand(), not just `unnest_array_type` in isolation: type
        // detection alone isn't enough — the bind expression also needs
        // `as _`, or it fails to compile with `expected &[i32], found
        // &Vec<Option<i32>>` (an array parameter has no per-element
        // nullability, unlike a plain scalar bind).
        let input = parse(q! {
            struct CreateUser {
                email: String,
                priority: Option<i32>,
            }
        });
        let model = TableModel::from_derive_input(&input).unwrap();
        let tokens = expand(&model).unwrap().to_string();
        assert!(tokens.contains("UNNEST($1::text[],$2::int4[])"));
        assert!(tokens.contains("let priority : Vec < Option < i32 > >"));
        assert!(tokens.contains("& priority as _"));
    }

    #[test]
    fn unrecognized_column_type_without_pg_type_is_a_spanned_error() {
        let input = parse(q! {
            struct CreateUser { role: Role }
        });
        let model = TableModel::from_derive_input(&input).unwrap();
        let err = expand(&model).unwrap_err();
        assert!(err.to_string().contains("don't know the Postgres type"));
        assert!(err.to_string().contains("pg_type"));
    }

    #[test]
    fn pg_type_override_is_used_for_the_unnest_cast() {
        let input = parse(q! {
            struct CreateUser {
                #[table(as_type = "role!: Role", pg_type = "role")]
                role: Role,
            }
        });
        let model = TableModel::from_derive_input(&input).unwrap();
        let tokens = expand(&model).unwrap().to_string();
        assert!(tokens.contains("UNNEST($1::role[])"));
    }

    #[test]
    fn bare_upsert_field_generates_its_own_upsert_many_by_method() {
        let input = parse(q! {
            struct CreateUser {
                #[table(upsert)]
                email: String,
                is_active: bool,
            }
        });
        let model = TableModel::from_derive_input(&input).unwrap();
        let tokens = expand(&model).unwrap().to_string();
        assert!(tokens.contains("fn insert_many <"));
        assert!(tokens.contains("fn upsert_many_by_email"));
        assert!(tokens.contains("ON CONFLICT (email) DO UPDATE SET is_active=EXCLUDED.is_active"));
    }

    #[test]
    fn insert_only_field_is_excluded_from_the_upsert_set_clause() {
        let input = parse(q! {
            struct CreateUser {
                #[table(upsert)]
                email: String,
                #[table(insert_only)]
                created_by: i32,
                is_active: bool,
            }
        });
        let model = TableModel::from_derive_input(&input).unwrap();
        let tokens = expand(&model).unwrap().to_string();
        assert!(tokens.contains("ON CONFLICT (email) DO UPDATE SET is_active=EXCLUDED.is_active"));
        assert!(!tokens.contains("created_by=EXCLUDED.created_by"));
    }

    #[test]
    fn two_bare_upsert_fields_generate_two_independent_methods() {
        let input = parse(q! {
            struct AddMember {
                #[table(upsert)]
                org_id: i32,
                #[table(upsert)]
                user_id: i32,
                role: String,
            }
        });
        let model = TableModel::from_derive_input(&input).unwrap();
        let tokens = expand(&model).unwrap().to_string();
        assert!(tokens.contains("fn upsert_many_by_org_id"));
        assert!(tokens.contains("ON CONFLICT (org_id) DO UPDATE SET user_id=EXCLUDED.user_id, role=EXCLUDED.role"));
        assert!(tokens.contains("fn upsert_many_by_user_id"));
        assert!(tokens.contains("ON CONFLICT (user_id) DO UPDATE SET org_id=EXCLUDED.org_id, role=EXCLUDED.role"));
    }

    #[test]
    fn composite_upsert_generates_an_additional_named_method_suffixed_many() {
        // Suffixed `_many` (not the bare `method_name` from the attribute)
        // so this doesn't collide with `SqlInsert`'s own method of that
        // name when a struct derives both.
        let input = parse(q! {
            #[table(upsert = insert_or_update_members(org_id, user_id))]
            struct AddMember {
                org_id: i32,
                user_id: i32,
                role: String,
            }
        });
        let model = TableModel::from_derive_input(&input).unwrap();
        let tokens = expand(&model).unwrap().to_string();
        assert!(tokens.contains("fn insert_many <"));
        assert!(tokens.contains("fn insert_or_update_members_many <"));
        assert!(tokens.contains("ON CONFLICT (org_id, user_id) DO UPDATE SET role=EXCLUDED.role"));
    }
}