Crate sql-macros

sql-macros - it's simple lib for generate sql query for select, select_many, select_all, insert, update, delete

0.2 changes

The attribute parser was rewritten from a hand-rolled TokenTree walker onto darling, on syn 3.0. This fixes a couple of real bugs (field types with 0 or 2+ generic arguments used to be silently dropped during codegen; SqlInsertMany didn't actually batch-insert anything) and replaces panic!s with span-accurate compile errors.

Breaking changes:

#[table(name = users)] (bare identifier) is no longer accepted — write #[table(name = "users")] instead.
SqlUpdate no longer generates a single default update() combining every #[table(update)] field into one WHERE clause. Each #[table(update)] field now generates its own update_by_<field> method instead — same shape as select_by_<field>/delete_by_<field> — so data.update(conn) becomes data.update_by_id(conn) (or whichever field you marked). Two different #[table(update)] fields never combine into one method anymore.

Everything else — return_type = User, select = method(field1, field2), as_type = "...", spec_columns = "...", return_fields = "..." — is unchanged.

New in 0.2:

SqlInsertMany now does a real multi-row INSERT ... VALUES (...), (...), ... via sqlx::QueryBuilder, instead of silently discarding the query result — see Insert many.
Upsert — #[table(upsert)] on a field generates ON CONFLICT (...) DO UPDATE/DO NOTHING for SqlInsert/SqlInsertMany; a struct-level #[table(upsert = method_name(field1, field2, ...))] generates an additional method with a composite conflict target — see Upsert.
Comparison operators — #[table(op = "gt"|"lt"|"gte"|"lte"|"like"|"ilike"| "in"|"not_in")] on a filter field, single value or a list (one method variant per operator) — see Comparison operators.
Custom multi-field methods (#[table(select = name(fields))] and the same for select_many/update/delete) can now also take a raw "$field"-templated string instead of a plain field list, for OR/NOT/ anything beyond a plain AND of equalities — see Custom multi-field methods and Generate methods with many fields.

0.3.0 changes

Breaking change:

Marking two or more fields bare #[table(upsert)] used to silently combine them into one composite ON CONFLICT (...) target. That's now a compile error — at most one field may carry the bare flag. For a composite target spanning several columns, use the new struct-level #[table(upsert = method_name(field1, field2, ...))] attribute instead, which generates an additional, separately named method alongside the always-present insert()/insert_many() — see Upsert.

Also new in 0.3.0:

SqlSelect/SqlSelectAll/SqlSelectMany methods take impl sqlx::PgExecutor<'_> instead of a fixed &sqlx::PgPool. Not breaking — every existing call site passing &pool keeps working — but now &mut *tx (inside a sqlx::Transaction) or &mut conn (a bare PgConnection, e.g. from pool.acquire()) works too.

0.4.0 changes

Breaking change:

Bare #[table(upsert)] no longer modifies the shared insert()/ insert_many() method. Each marked field now generates its own independent method instead — upsert_by_<field> for SqlInsert, upsert_many_by_<field> for SqlInsertMany — same shape as select_by_<field>/update_by_<field>/delete_by_<field>: a marked field is never silently combined with another one. Marking two (or more) fields bare is no longer a compile error either — each just gets its own method. insert()/insert_many() themselves are now always a plain INSERT, never ON CONFLICT, regardless of how many fields are marked. This supersedes 0.3.0's single-bare-field behavior (insert() itself carrying ON CONFLICT) and its "2+ bare fields is a compile error" rule — see Upsert.
Fixed: a struct-level #[table(upsert = method_name(...))] on a struct deriving both SqlInsert and SqlInsertMany used to generate the same method name for both (method_name), which fails to compile (duplicate definitions with name ...) the moment both derives are actually present together. SqlInsertMany now names its copy method_name_many instead — same insert/insert_many split used everywhere else — so the two coexist.

Also new in 0.4.0:

SqlInsert/SqlInsertMany/SqlUpdate/SqlDelete methods now also take impl sqlx::PgExecutor<'_>, same as the select-side derives got in 0.3.0. Not breaking on its own — &mut conn/&mut *tx call sites keep working — but these write-side methods, which used to require &mut sqlx::PgConnection specifically, now also accept &pool directly, without pre-acquiring a connection first.

0.6.0 changes

Breaking change:

#[table(insert_only)] (added in 0.5.0) is renamed to #[table(skip_update)] — consistent with every other field-level flag in this crate (select, select_many, update, delete, upsert), all named after the operation/clause they affect, not a descriptive adjective about the field. No behavior change: still excluded from every SqlUpdate/SqlUpdateMany/SqlUpdateManyColumns-generated SET clause and from SqlInsert/SqlInsertMany/ SqlInsertManyColumns's upsert ON CONFLICT ... DO UPDATE SET clause. 0.5.0's insert_only name no longer exists — update any struct that used it. See Update excludes a skip-update column.

0.5.0 changes

Breaking change:

SqlInsertMany's insert_many (and its upsert_many_by_<field>/ composite-upsert variants) now build INSERT ... SELECT * FROM UNNEST($1, $2, ...) instead of a sqlx::QueryBuilder-built VALUES (...), (...), .... The SQL text stays static regardless of batch size (only the arrays' runtime length varies), so it's now checked against your live database at compile time, same as every other generated method — see Attention, which used to call out insert_many as the one exception to that. The breaking part: Postgres can't infer an UNNEST parameter's array-element type from context, so every column needs a known Postgres type name. Common scalar types (String, bool, i16/i32/i64, f32/f64, uuid::Uuid, serde_json::Value, chrono::NaiveDateTime/ time::PrimitiveDateTime — see the full table in Insert many) are detected automatically, including through Option<T>/Vec<T>/Vec<Option<T>> wrappers and any macro-hygiene Type::Group a field-transposing macro_rules! might introduce; anything else — most notably a custom enum/domain column via as_type, or sqlx::types::Json<T> — now needs #[table(pg_type = "...")] alongside it (e.g. pg_type = "role"), or SqlInsertMany fails to compile with a message telling you to add it.

Also new in 0.5.0:

SqlUpdateMany — a new derive, batch UPDATE symmetric to SqlInsertMany: one update_many_by_<field>(items, executor) method per #[table(update)] field, each row in items setting its own values on its own matching row (not one shared value for the whole batch). Also built on UNNEST, so it's compile-time checked the same way. See Update many.
SqlInsertManyColumns/SqlUpdateManyColumns — for callers who already have the batch built column-wise (one Vec<T> per field) instead of row-wise. Skip SqlInsertMany/SqlUpdateMany's own transpose-and-clone step entirely: each field binds straight into UNNEST(...) with no copying. The column Vecs don't need to be the same length — there's no check, UNNEST just zips them and pads the short ones with NULL up to the longest, so a mismatch surfaces as a database error only if it lands on a NOT NULL column. See Insert many columns and Update many columns.
#[table(skip_update)] — a skip hook: a field stays in every INSERT's column list but is never mentioned in any SET clause this crate generates for it — every SqlUpdate/SqlUpdateMany/ SqlUpdateManyColumns method (including a struct-level custom update method) and SqlInsert/SqlInsertMany/SqlInsertManyColumns's own ON CONFLICT ... DO UPDATE SET upsert clause — for a column set once at creation and never reassigned. See Update excludes a skip-update column.

Install

# Cargo.toml
[dependencies]
sql-macros = { version = "0.5" }

Usage

Table name

use sql_macros::SqlSelect;

#[derive(SqlSelect)]
pub struct User {
    #[table(select)]
    pub id: i32,
    pub email: String,
}

Table name will be generated as users

If you need use special name use #[table(name = "users")]

Select one

Every SqlSelect/SqlSelectAll/SqlSelectMany method takes its first parameter as impl sqlx::PgExecutor<'_>, not a fixed &sqlx::PgPool — a plain &pool still works everywhere below, but so does &mut *tx inside a transaction, or &mut conn for a single connection.

use sql_macros::SqlSelect;

#[derive(SqlSelect)]
pub struct User {
    #[table(select)]
    pub id: i32,
    pub email: String,
}

pub async fn get_by_id(pool: &sqlx::PgPool, id: i32) -> Result<Option<User>, sqlx::Error> {
    let user = User::select_by_id(pool, id).await?;
    Ok(user)
}

View generated code

impl User {
    #[doc = "SELECT id, email FROM users WHERE id=$1"]
    pub async fn select_by_id<'e, E>(executor: E, id: i32) -> Result<Option<User>, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        let object = sqlx::query_as!(User, "SELECT id, email FROM users WHERE id=$1", id)
            .fetch_optional(executor)
            .await?;
        Ok(object)
    }
}

Select all

use sql_macros::SqlSelectAll;

#[derive(SqlSelectAll)]
pub struct User {
    pub id: i32,
    pub email: String,
}
pub async fn get_all_users(pool: &sqlx::PgPool) -> Result<Vec<User>, sqlx::Error> {
    let users = User::select_all(pool).await?;
    Ok(users)
}

View generated code

impl User {
    #[doc = "SELECT id, email FROM users"]
    pub async fn select_all<'e, E>(executor: E) -> Result<Vec<User>, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        let object = sqlx::query_as!(User, "SELECT id, email FROM users")
            .fetch_all(executor)
            .await?;
        Ok(object)
    }
}

Select many

use sql_macros::SqlSelectMany;

#[derive(SqlSelectMany)]
pub struct User {
    pub id: i32,
    pub email: String,
    #[table(select_many)]
    pub is_removed: bool,
}
pub async fn get_by_removed(pool: &sqlx::PgPool, is_removed: bool) -> Result<Vec<User>, sqlx::Error> {
    let users = User::select_many(pool, is_removed).await?;
    Ok(users)
}

View generated code

impl User {
    #[doc = "SELECT id, email, is_removed FROM users WHERE is_removed=$1"]
    pub async fn select_many_by_is_removed<'e, E>(
        executor: E,
        is_removed: bool,
    ) -> Result<Vec<User>, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        let object = sqlx::query_as!(
            User,
            "SELECT id, email, is_removed FROM users WHERE is_removed=$1",
            is_removed
        )
        .fetch_all(executor)
        .await?;
        Ok(object)
    }
}

Insert

Insert without returning

use sql_macros::SqlInsert;

#[derive(Debug, SqlInsert)]
#[table(name = "users")]
pub struct CreateUser {
    pub email: String,
}

pub async fn create(pool: &sqlx::PgPool, data: &CreateUser) -> Result<u64, sqlx::Error> {
    let query_result = data.insert(pool).await?;
    Ok(query_result.rows_affected())
}

View generated code

impl CreateUser {
    #[doc = "INSERT INTO users (email) VALUES ($1)"]
    pub async fn insert<'e, E>(
        &self,
        executor: E,
    ) -> Result<sqlx::any::AnyQueryResult, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        let query_result = sqlx::query!("INSERT INTO users (email) VALUES ($1)", &self.email)
            .execute(executor)
            .await?;
        Ok(query_result.into())
    }
}

Insert with returning

use sql_macros::SqlInsert;

#[derive(Debug, SqlInsert)]
#[table(name = "users", return_type = User)]
pub struct CreateUser {
    pub email: String,
}

pub async fn create(pool: &sqlx::PgPool, data: &CreateUser) -> Result<User, sqlx::Error> {
    let user = data.insert(pool).await?;
    Ok(user)
}

View generated code

impl CreateUser {
    #[doc = "INSERT INTO users (email) VALUES ($1) RETURNING *"]
    pub async fn insert<'e, E>(&self, executor: E) -> Result<User, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        let object = sqlx::query_as!(
            User,
            "INSERT INTO users (email) VALUES ($1) RETURNING *",
            &self.email
        )
        .fetch_one(executor)
        .await?;
        Ok(object)
    }
}

Insert with returning fields

use sql_macros::SqlInsert;

#[derive(sqlx::FromRow)]
struct CreateUserResponse {
    pub id: i32,
}

#[derive(Debug, SqlInsert)]
#[table(name = "users", return_type = CreateUserResponse, return_fields = "id")]
pub struct CreateUser {
    pub email: String,
}

pub async fn create(pool: &sqlx::PgPool, data: &CreateUser) -> Result<CreateUserResponse, sqlx::Error> {
    let user = data.insert(pool).await?;
    Ok(user)
}

View generated code

impl CreateUser {
    #[doc = "INSERT INTO users (email) VALUES ($1) RETURNING id"]
    pub async fn insert<'e, E>(&self, executor: E) -> Result<CreateUserResponse, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        let object = sqlx::query_as!(
            CreateUserResponse,
            "INSERT INTO users (email) VALUES ($1) RETURNING id",
            &self.email
        )
        .fetch_one(executor)
        .await?;
        Ok(object)
    }
}

Insert many

SqlInsert only inserts a single row. For inserting a batch of rows in one round-trip, derive SqlInsertMany as well. Because the number of rows isn't known at compile time, a plain VALUES ($1,$2), ($3,$4), ... can't be a static SQL string — so this is built on INSERT ... SELECT * FROM UNNEST($1, $2, ...) instead: each field is transposed from items: &[Self] into its own array parameter, so the SQL text stays fixed no matter how many rows are in the batch. That means sqlx::query!/query_as! can check it against your live database at compile time, same as every other generated method in this crate.

use sql_macros::SqlInsertMany;

#[derive(Debug, SqlInsertMany)]
#[table(name = "users", return_type = User)]
pub struct CreateUser {
    pub email: String,
}

pub async fn create_many(pool: &sqlx::PgPool, data: &[CreateUser]) -> Result<Vec<User>, sqlx::Error> {
    let users = CreateUser::insert_many(data, pool).await?;
    Ok(users)
}

View generated code

impl CreateUser {
    #[doc = "INSERT INTO users (email) SELECT * FROM UNNEST($1::text[]) RETURNING *"]
    pub async fn insert_many<'e, E>(
        items: &[Self],
        executor: E,
    ) -> Result<Vec<User>, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        if items.is_empty() {
            return Ok(Vec::new());
        }

        let email: Vec<String> = items.iter().map(|item| item.email.clone()).collect();

        let objects = sqlx::query_as!(
            User,
            "INSERT INTO users (email) SELECT * FROM UNNEST($1::text[]) RETURNING *",
            &email
        )
        .fetch_all(executor)
        .await?;
        Ok(objects)
    }
}

Without return_type, insert_many returns Result<sqlx::any::AnyQueryResult, sqlx::Error> instead, same as insert.

Why the ::text[] cast: Postgres can't infer an UNNEST parameter's array-element type from where its output eventually lands, the way it infers a plain scalar bind's type from its target column — resolving which overload of unnest() to call happens before that context is available, so an uncast parameter is a hard function unnest(unknown) is not unique error, not just a missed optimization.

These Rust types get their Postgres array cast auto-detected — no pg_type needed:

Rust type	Cast
`String`	`text[]`
`bool`	`bool[]`
`i16`	`int2[]`
`i32`	`int4[]`
`i64`	`int8[]`
`f32`	`float4[]`
`f64`	`float8[]`
`uuid::Uuid`	`uuid[]`
`serde_json::Value`	`jsonb[]`
`chrono::NaiveDateTime`	`timestamp[]`
`time::PrimitiveDateTime`	`timestamp[]`

VARCHAR/TEXT need no distinction — String always casts to text[], and Postgres's implicit assignment cast from text to varchar(n) means it inserts into either column type fine. Likewise serde_json::Value casts to jsonb[] but works into a plain json column too, via Postgres's implicit json/jsonb assignment cast.

This detection also sees through Option<T>/Vec<T>/Vec<Option<T>> wrappers (see below) and any macro-hygiene Type::Group a macro_rules! that transposes your field type might introduce (e.g. a Vec<$field_ty> batch-struct generator) — it unwraps those first, then matches on the bare T.

Not auto-detected — these need an explicit #[table(pg_type = "...")], even though sqlx supports them as plain scalar columns:

sqlx::types::Json<T> (any T) — pg_type = "jsonb".
A custom enum/domain column via as_type (see below) — pg_type set to that type's own Postgres name.
std::net::IpAddr — deliberately never auto-detected, even with the ipnetwork/ipnet sqlx feature enabled: which Rust type sqlx expects for an inet[] array depends on which of those two features is enabled project-wide, so hardcoding a cast for IpAddr here would still need a feature-dependent as _ and silently break depending on unrelated feature flags elsewhere in your project. Use pg_type = "inet" and switch the field to ipnet::IpNet or ipnetwork::IpNetwork instead — those name the feature you picked explicitly, so they're safe to rely on (ask if you want these added to the auto-detected list).

A nullable column — Option<T> here, or Vec<Option<T>> for SqlInsertManyColumns/SqlUpdateManyColumns — detects T's type the same way (NULL doesn't change which Postgres type an array holds). It also needs its bind wrapped in as _ to compile, which this crate adds automatically whenever a column is Option-wrapped: unlike a plain scalar bind (where the compile-time check infers nullability from the real target column), an array parameter's type has no per-element nullability of its own, so the check always expects the bare, non-Option slice type and rejects an Option-wrapped one without that cast — confirmed live that the actual encoding (NULL elements in a Postgres array) works fine once past it.

#[derive(Debug, SqlInsertMany)]
#[table(name = "users", return_type = User)]
pub struct CreateUser {
    pub email: String,
    #[table(as_type = "role!: Role", pg_type = "role")]
    pub role: Role,
}

Omitting pg_type there is a compile error telling you to add it, rather than a confusing error from Postgres itself.

Insert many columns

If you already have the batch built column-wise — one Vec<T> per field — rather than row-wise, derive SqlInsertManyColumns instead of SqlInsertMany. It skips the transpose-and-clone step above entirely: each field's own Vec binds straight into UNNEST(...), no copying. Same return_type/return_fields/pg_type rules as SqlInsertMany.

use sql_macros::SqlInsertManyColumns;

#[derive(Debug, SqlInsertManyColumns)]
#[table(name = "users", return_type = User)]
pub struct CreateUserColumns {
    pub email: Vec<String>,
}

pub async fn create_many(pool: &sqlx::PgPool, data: CreateUserColumns) -> Result<Vec<User>, sqlx::Error> {
    let users = data.insert_many(pool).await?;
    Ok(users)
}

View generated code

impl CreateUserColumns {
    #[doc = "INSERT INTO users (email) SELECT * FROM UNNEST($1::text[]) RETURNING *"]
    pub async fn insert_many<'e, E>(&self, executor: E) -> Result<Vec<User>, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        if self.email.is_empty() {
            return Ok(Vec::new());
        }

        let objects = sqlx::query_as!(
            User,
            "INSERT INTO users (email) SELECT * FROM UNNEST($1::text[]) RETURNING *",
            &self.email
        )
        .fetch_all(executor)
        .await?;
        Ok(objects)
    }
}

With two or more columns, every field's Vec is expected to be the same length — this isn't checked. If they aren't, UNNEST pads the short one(s) with NULL up to the longest; a NOT NULL column turns that into a real database error, a nullable one won't, so getting the lengths right is on you.

SqlInsertManyColumns supports the same upsert design as SqlInsertMany (see Upsert): a bare #[table(upsert)] field gets its own upsert_by_<field> method, and a struct-level #[table(upsert = method_name(field1, field2, ...))] gets an additional method under that exact name:

#[derive(Debug, SqlInsertManyColumns)]
#[table(name = "users", return_type = User)]
pub struct UpsertUserColumns {
    #[table(upsert)]
    pub email: Vec<String>,
    pub is_active: Vec<bool>,
}

View generated code

impl UpsertUserColumns {
    #[doc = "INSERT INTO users (email, is_active) SELECT * FROM UNNEST($1::text[],$2::bool[]) RETURNING *"]
    pub async fn insert_many<'e, E>(&self, executor: E) -> Result<Vec<User>, sqlx::Error> {
        // ... no `ON CONFLICT` ...
    }

    #[doc = "INSERT INTO users (email, is_active) SELECT * FROM UNNEST($1::text[],$2::bool[]) ON CONFLICT (email) DO UPDATE SET is_active=EXCLUDED.is_active RETURNING *"]
    pub async fn upsert_by_email<'e, E>(&self, executor: E) -> Result<Vec<User>, sqlx::Error> {
        // ...
    }
}

Unlike SqlInsertMany, the per-field method here is named bare upsert_by_<field>, not upsert_many_by_<field> — no _many suffix needed, since this derive can't coexist with SqlInsert/SqlInsertMany on the same struct (their field types are incompatible: scalar vs. Vec<T>), so there's no name to collide with.

Upsert

Mark a field #[table(upsert)] to generate its own upsert_by_<field> method on SqlInsert — same shape as select_by_<field>/ update_by_<field>/delete_by_<field>: that field becomes the ON CONFLICT (...) target, every other field becomes a DO UPDATE SET col = EXCLUDED.col assignment (or DO NOTHING if it's the only field, since an empty SET is invalid SQL). The plain insert() method is unaffected — always a plain INSERT, never ON CONFLICT, whether or not any field is marked.

use sql_macros::SqlInsert;

#[derive(Debug, SqlInsert)]
#[table(name = "users", return_type = User)]
pub struct UpsertUser {
    #[table(upsert)]
    pub email: String,
    pub is_active: bool,
}

pub async fn upsert(pool: &sqlx::PgPool, data: &UpsertUser) -> Result<User, sqlx::Error> {
    let user = data.upsert_by_email(pool).await?;
    Ok(user)
}

View generated code

impl UpsertUser {
    #[doc = "INSERT INTO users (email, is_active) VALUES ($1,$2)"]
    pub async fn insert<'e, E>(&self, executor: E) -> Result<User, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        // ... no `ON CONFLICT` ...
    }

    #[doc = "INSERT INTO users (email, is_active) VALUES ($1,$2) ON CONFLICT (email) DO UPDATE SET is_active=EXCLUDED.is_active RETURNING *"]
    pub async fn upsert_by_email<'e, E>(&self, executor: E) -> Result<User, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        let object = sqlx::query_as!(
            User,
            "INSERT INTO users (email, is_active) VALUES ($1,$2) ON CONFLICT (email) DO UPDATE SET is_active=EXCLUDED.is_active RETURNING *",
            &self.email as _,
            &self.is_active as _
        )
        .fetch_one(executor)
        .await?;
        Ok(object)
    }
}

SqlInsertMany supports the same #[table(upsert)] attribute, naming its method upsert_many_by_<field> instead (not upsert_by_<field>, so deriving both SqlInsert and SqlInsertMany on one struct doesn't collide) — the ON CONFLICT clause is appended once, after the batch VALUES (...), (...), ... list, and applies to every row.

Marking two (or more) fields bare #[table(upsert)] is not an error — each marked field is independent and gets its own method (upsert_by_org_id, upsert_by_user_id, ...), never silently combined with another marked field — same rule as two #[table(select)] fields never combining into one select_by_.... For a single ON CONFLICT target spanning more than one column, use a struct-level #[table(upsert = method_name(field1, field2, ...))] attribute instead. It generates an additional, separately named method — the plain insert() (or insert_many()) is still generated alongside it, since that method is never affected by #[table(upsert)] in any form. If the struct derives both SqlInsert and SqlInsertMany, SqlInsertMany's copy of the method is named method_name_many (not bare method_name), same insert/ insert_many split as everywhere else — so the two don't collide:

use sql_macros::SqlInsert;

#[derive(Debug, SqlInsert)]
#[table(name = "user_todos", upsert = mark_done(user_id, todo_id))]
pub struct MarkTodoDone {
    pub user_id: i32,
    pub todo_id: i32,
    pub done: bool,
}

View generated code

impl MarkTodoDone {
    #[doc = "INSERT INTO user_todos (user_id, todo_id, done) VALUES ($1,$2,$3)"]
    pub async fn insert<'e, E>(&self, executor: E) -> Result<sqlx::any::AnyQueryResult, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        // ...
    }

    #[doc = "INSERT INTO user_todos (user_id, todo_id, done) VALUES ($1,$2,$3) ON CONFLICT (user_id, todo_id) DO UPDATE SET done=EXCLUDED.done"]
    pub async fn mark_done<'e, E>(&self, executor: E) -> Result<sqlx::any::AnyQueryResult, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        // ...
    }
}

Update

Update without returning

It just return query result (see sqlx::any::AnyQueryResult)

use sql_macros::SqlUpdate;

#[derive(SqlUpdate)]
#[table(name = "users")]
pub struct UpdateUser {
    #[table(update)]
    pub id: i32,
    pub email: String,
}

pub async fn update(pool: &sqlx::PgPool, data: &UpdateUser) -> Result<u64, sqlx::Error> {
    let query_result = data.update_by_id(pool).await?;
    Ok(query_result.rows_affected())
}

View generated code

impl UpdateUser {
    #[doc = "UPDATE users SET email=$1 WHERE id=$2"]
    pub async fn update_by_id<'e, E>(
        &self,
        executor: E,
    ) -> Result<sqlx::any::AnyQueryResult, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        let result = sqlx::query!(
            "UPDATE users SET email=$1 WHERE id=$2",
           &self.email,
           &self.id
       )
       .execute(executor)
       .await?;
       Ok(result.into())
   }
}

Update with returning type

use sql_macros::SqlUpdate;

#[derive(SqlUpdate)]
#[table(name = "users", return_type = User)]
pub struct UpdateUser {
    #[table(update)]
    pub id: i32,
    pub email: String,
}

pub async fn update(pool: &sqlx::PgPool, data: &UpdateUser) -> Result<User, sqlx::Error> {
    let user = data.update_by_id(pool).await?;
    Ok(user)
}

View generated code

impl UpdateUser {
   #[doc = "UPDATE users SET email=$1 WHERE id=$2 RETURNING *"]
   pub async fn update_by_id<'e, E>(&self, executor: E) -> Result<User, sqlx::Error>
   where
       E: sqlx::PgExecutor<'e>,
   {
       let object = sqlx::query_as!(
           User,
           "UPDATE users SET email=$1 WHERE id=$2 RETURNING *",
            &self.email,
            &self.id
        )
        .fetch_one(executor)
        .await?;
        Ok(object)
    }
}

Update with returning fields

use sql_macros::SqlUpdate;

#[derive(sqlx::FromRow)]
struct UpdateUserResponse {
    pub id: i32,
}

#[derive(SqlUpdate)]
#[table(name = "users", return_type = UpdateUserResponse, return_fields = "id")]
pub struct UpdateUser {
    #[table(update)]
    pub id: i32,
    pub email: String,
}

pub async fn update(pool: &sqlx::PgPool, data: &UpdateUser) -> Result<UpdateUserResponse, sqlx::Error> {
    let res = data.update_by_id(pool).await?;
    Ok(res)
}

View generated code

impl UpdateUser {
   #[doc = "UPDATE users SET email=$1 WHERE id=$2 RETURNING id"]
   pub async fn update_by_id<'e, E>(&self, executor: E) -> Result<UpdateUserResponse, sqlx::Error>
   where
       E: sqlx::PgExecutor<'e>,
   {
       let object = sqlx::query_as!(
           UpdateUserResponse,
           "UPDATE users SET email=$1 WHERE id=$2 RETURNING id",
            &self.email,
            &self.id
        )
        .fetch_one(executor)
        .await?;
        Ok(object)
    }
}

Update with special columns

use sql_macros::SqlUpdate;

#[derive(SqlUpdate)]
#[table(name = "users", spec_columns = "updated_at=NOW()")]
pub struct UpdateUser {
    #[table(update)]
    pub id: i32,
    pub email: String,
}

pub async fn update(pool: &sqlx::PgPool, data: &UpdateUser) -> Result<u64, sqlx::Error> {
    let query_result = data.update_by_id(pool).await?;
    Ok(query_result.rows_affected())
}

View generated code

impl UpdateUser {
   #[doc = "UPDATE users SET email=$1, updated_at=NOW() WHERE id=$2"]
   pub async fn update_by_id<'e, E>(
       &self,
       executor: E,
   ) -> Result<sqlx::any::AnyQueryResult, sqlx::Error>
   where
       E: sqlx::PgExecutor<'e>,
   {
       let result = sqlx::query!(
            "UPDATE users SET email=$1, updated_at=NOW() WHERE id=$2",
            &self.email,
            &self.id
        )
        .execute(executor)
        .await?;
        Ok(result.into())
    }
}

Update excludes a skip-update column

#[table(skip_update)] is a skip hook, not a database-level guard: the marked field stays in SqlInsert/SqlInsertMany/ SqlInsertManyColumns's column list as usual, but every SET clause this crate generates for it simply never mentions the column at all — no bind, no col=..., nothing. That covers:

SqlUpdate/SqlUpdateMany/SqlUpdateManyColumns's update_by_<field> methods, and a struct-level #[table(update = method_name(...))] custom method.
SqlInsert/SqlInsertMany/SqlInsertManyColumns's own ON CONFLICT (...) DO UPDATE SET upsert clause — no EXCLUDED.<col> either, so a conflicting row's insert-time value survives an upsert instead of being silently overwritten.

For a column set once at creation and never reassigned (created_by, an initial invitation_token, ...), so deriving SqlInsert's upsert_by_<field> and/or SqlUpdate's update_by_id on the same struct doesn't let either one overwrite it. The column isn't locked by a trigger or constraint — it's just absent from these particular generated queries, same as a field without #[table(select)] is simply absent from select_by_<field>'s WHERE clause. Any other query you write by hand can still change it.

use sql_macros::{SqlInsert, SqlUpdate};

#[derive(SqlInsert, SqlUpdate)]
#[table(name = "users")]
pub struct UpdateUser {
    #[table(update, upsert)]
    pub id: i32,
    #[table(skip_update)]
    pub created_by: i32,
    pub email: String,
}

View generated code

impl UpdateUser {
    // `insert()` still includes `created_by` — unaffected.
    #[doc = "INSERT INTO users (id, created_by, email) VALUES ($1,$2,$3) RETURNING *"]
    pub async fn insert<'e, E>(&self, executor: E) -> Result<Self, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    { /* ... */ }

    // `upsert_by_id()` drops `created_by` from the `DO UPDATE SET` list
    // too — no `EXCLUDED.created_by`.
    #[doc = "INSERT INTO users (id, created_by, email) VALUES ($1,$2,$3) ON CONFLICT (id) DO UPDATE SET email=EXCLUDED.email RETURNING *"]
    pub async fn upsert_by_id<'e, E>(&self, executor: E) -> Result<Self, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    { /* ... */ }

    // `update_by_id()` drops `created_by` from SET.
    #[doc = "UPDATE users SET email=$1 WHERE id=$2"]
    pub async fn update_by_id<'e, E>(
        &self,
        executor: E,
    ) -> Result<sqlx::any::AnyQueryResult, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    { /* ... */ }
}

Update many

SqlUpdate only updates a single row at a time. For updating a batch of rows in one round-trip — each row setting its own values, not one shared value for the whole batch — derive SqlUpdateMany instead. One update_many_by_<field> method is generated per #[table(update)] field, same shape as SqlInsertMany: built on UPDATE ... SET ... FROM UNNEST(...) AS data(...) WHERE t.<field> = data.<field>, a static SQL string regardless of batch size, so it's checked at compile time the same way. op/spec_columns/return_type/return_fields work the same as SqlUpdate, except a batch can affect several rows, so return_type here uses fetch_all (Vec<T>), not SqlUpdate's single-row fetch_one.

use sql_macros::SqlUpdateMany;

#[derive(SqlUpdateMany)]
#[table(name = "users", return_type = User)]
pub struct UpdateUser {
    #[table(update)]
    pub id: i32,
    pub email: String,
}

pub async fn rename_many(pool: &sqlx::PgPool, data: &[UpdateUser]) -> Result<Vec<User>, sqlx::Error> {
    let users = UpdateUser::update_many_by_id(data, pool).await?;
    Ok(users)
}

View generated code

impl UpdateUser {
    #[doc = "UPDATE users AS t SET email=data.email FROM UNNEST($1::int4[],$2::text[]) AS data(id, email) WHERE t.id = data.id RETURNING t.*"]
    pub async fn update_many_by_id<'e, E>(items: &[Self], executor: E) -> Result<Vec<User>, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        if items.is_empty() {
            return Ok(Vec::new());
        }

        let id: Vec<i32> = items.iter().map(|item| item.id.clone()).collect();
        let email: Vec<String> = items.iter().map(|item| item.email.clone()).collect();

        let objects = sqlx::query_as!(
            User,
            "UPDATE users AS t SET email=data.email FROM UNNEST($1::int4[],$2::text[]) AS data(id, email) WHERE t.id = data.id RETURNING t.*",
            &id,
            &email
        )
        .fetch_all(executor)
        .await?;
        Ok(objects)
    }
}

Note the target table is aliased AS t and the default RETURNING is t.*, not a bare * — in an UPDATE ... FROM ... query, a bare * returns columns from both the target table and the UNNEST-sourced data table combined, not just the real row. A custom return_fields needs the same qualification if a column name would otherwise be ambiguous between the two (e.g. return_fields = "t.id, t.email").

Without return_type, update_many_by_id returns Result<sqlx::any::AnyQueryResult, sqlx::Error> instead, same as update_by_id. op on a #[table(update, op = "...")] field is ignored here (unlike SqlUpdate's own update_by_<field>) — there's only ever one update_many_by_<field> method, since a batch's key column is always an equality join against the batch's own array of keys; >/</etc. don't have a sensible reading for "match this batch of rows". Marking two (or more) fields #[table(update)] generates two independent update_many_by_<field> methods, same as SqlUpdate.

See Insert many for why UNNEST's parameters need an explicit Postgres type cast (#[table(pg_type = "...")] for anything SqlUpdateMany can't detect automatically) — the same rule applies here, for every column, not just the key.

Update many columns

Same relationship as Insert many columns: if you already have the batch built column-wise instead of row-wise, derive SqlUpdateManyColumns instead of SqlUpdateMany — it skips the transpose-and-clone step, binding each field's own Vec straight into UNNEST(...).

use sql_macros::SqlUpdateManyColumns;

#[derive(SqlUpdateManyColumns)]
#[table(name = "users", return_type = User)]
pub struct UpdateUserColumns {
    #[table(update)]
    pub id: Vec<i32>,
    pub email: Vec<String>,
}

pub async fn rename_many(pool: &sqlx::PgPool, data: &UpdateUserColumns) -> Result<Vec<User>, sqlx::Error> {
    let users = data.update_many_by_id(pool).await?;
    Ok(users)
}

View generated code

impl UpdateUserColumns {
    #[doc = "UPDATE users AS t SET email=data.email FROM UNNEST($1::int4[],$2::text[]) AS data(id, email) WHERE t.id = data.id RETURNING t.*"]
    pub async fn update_many_by_id<'e, E>(&self, executor: E) -> Result<Vec<User>, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        if self.id.is_empty() {
            return Ok(Vec::new());
        }

        let objects = sqlx::query_as!(
            User,
            "UPDATE users AS t SET email=data.email FROM UNNEST($1::int4[],$2::text[]) AS data(id, email) WHERE t.id = data.id RETURNING t.*",
            &self.id,
            &self.email
        )
        .fetch_all(executor)
        .await?;
        Ok(objects)
    }
}

Same op-ignored/spec_columns/return_type/return_fields/t.* rules as SqlUpdateMany — see Update many above. Same no-length-check caveat as Insert many columns: mismatched column lengths aren't checked, and UNNEST pads the short one with NULL rather than erroring on its own.

Delete

use sql_macros::SqlDelete;

#[derive(SqlDelete)]
pub struct User {
    #[table(delete)]
    pub id: i32,
}

async fn delete(pool: &sqlx::PgPool, id: i32) -> Result<u64, sqlx::Error> {
    let result = User::delete_by_id(pool, id).await?;
    Ok(result.rows_affected())
}

View generated code

impl User {
    #[doc = "DELETE FROM users WHERE id=$1"]
    pub async fn delete_by_id<'e, E>(
        executor: E,
        id: i32,
    ) -> Result<sqlx::any::AnyQueryResult, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        let result = sqlx::query!("DELETE FROM users WHERE id=$1", id)
            .execute(executor)
            .await?;
        Ok(result.into())
    }
}

Generate methods with many fields

use sql_macros::{SqlDelete, SqlSelect, SqlSelectMany, SqlUpdate};

#[derive(SqlSelect, SqlSelectMany, SqlDelete, SqlUpdate)]
#[table(select = get_active_user(is_active, is_removed))]
#[table(select_many = get_user_by_removed(is_active, is_removed))]
#[table(delete = delete_user(is_active, is_removed))]
#[table(update = update_by_email(email))]
pub struct User {
    pub id: i32,
    pub email: String,
    pub is_active: bool,
    pub is_removed: bool,
}

Works with select, select_many, delete, update.

For update, the listed fields (email above) become the WHERE filter and every other field (id, is_active, is_removed) becomes a SET column - same rule update_by_<field> follows via #[table(update)] on a field, just parameterised so you can generate several differently-filtered update methods on one struct:

View generated code

impl User {
    #[doc = "UPDATE users SET id=$1, is_active=$2, is_removed=$3 WHERE email=$4"]
    pub async fn update_by_email<'e, E>(&self, executor: E) -> Result<sqlx::any::AnyQueryResult, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        let result = sqlx::query!(
            "UPDATE users SET id=$1, is_active=$2, is_removed=$3 WHERE email=$4",
            &self.id,
            &self.is_active,
            &self.is_removed,
            &self.email
        )
        .execute(executor)
        .await?;
        Ok(result.into())
    }
}

Comparison operators

A field's own single-field methods (select_by_<field>, select_many_by_<field>, delete_by_<field>, update_by_<field>) default to =. Add op = "..." to use something else: gt/lt/gte/lte (>, <, >=, <=), like/ilike (LIKE/ILIKE — ilike is Postgres's case-insensitive LIKE), or in/not_in (= ANY($n)/!= ALL($n), which also widen the generated parameter from FieldType to &[FieldType]).

This only applies to a field's own single-field methods — a custom multi-field method (select = name(...)) never reads it; those are always =, AND-joined (see Custom multi-field methods below for how to express something more complex there).

use sql_macros::SqlSelectMany;

#[derive(SqlSelectMany)]
pub struct User {
    pub email: String,
    #[table(select_many, op = "gt")]
    pub id: i32,
}

pub async fn get_newer_than(pool: &sqlx::PgPool, id: i32) -> Result<Vec<User>, sqlx::Error> {
    let users = User::select_many_by_id_gt(pool, id).await?;
    Ok(users)
}

View generated code

impl User {
    #[doc = "SELECT email, id FROM users WHERE id>$1"]
    pub async fn select_many_by_id_gt<'e, E>(executor: E, id: i32) -> Result<Vec<User>, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        let object = sqlx::query_as!(User, "SELECT email, id FROM users WHERE id>$1", id)
            .fetch_all(executor)
            .await?;
        Ok(object)
    }
}

Note the method name: any non-eq operator is suffixed onto the base name (select_many_by_id -> select_many_by_id_gt), so the operator is visible at the call site instead of being hidden behind a plain-looking name.

op also accepts a list — op = ["gt", "lt"] — generating one method variant per operator, instead of one field forcing a single fixed comparison everywhere a field can only have one meaning at a time:

#[derive(SqlSelectMany)]
pub struct User {
    pub email: String,
    #[table(select_many, op = ["gt", "lt"])]
    pub id: i32,
}

View generated code

impl User {
    #[doc = "SELECT email, id FROM users WHERE id>$1"]
    pub async fn select_many_by_id_gt<'e, E>(executor: E, id: i32) -> Result<Vec<User>, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        let object = sqlx::query_as!(User, "SELECT email, id FROM users WHERE id>$1", id)
            .fetch_all(executor)
            .await?;
        Ok(object)
    }

    #[doc = "SELECT email, id FROM users WHERE id<$1"]
    pub async fn select_many_by_id_lt<'e, E>(executor: E, id: i32) -> Result<Vec<User>, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        let object = sqlx::query_as!(User, "SELECT email, id FROM users WHERE id<$1", id)
            .fetch_all(executor)
            .await?;
        Ok(object)
    }
}

This works the same way for #[table(update)] fields — op = ["gt", "lt"] generates update_by_age_gt and update_by_age_lt, same shape as select_many_by_age_gt/select_many_by_age_lt.

op = "in"/"not_in" change the parameter type, since they filter against a list rather than a single value:

#[derive(SqlSelectMany)]
pub struct User {
    pub email: String,
    #[table(select_many, op = "in")]
    pub id: i32,
}

View generated code

impl User {
    #[doc = "SELECT email, id FROM users WHERE id = ANY($1)"]
    pub async fn select_many_by_id_in<'e, E>(executor: E, id: &[i32]) -> Result<Vec<User>, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        let object = sqlx::query_as!(User, "SELECT email, id FROM users WHERE id = ANY($1)", id)
            .fetch_all(executor)
            .await?;
        Ok(object)
    }
}

Custom multi-field methods

A custom multi-field method (select = name(...), select_many = name(...), update = name(...), delete = name(...)) is always =, AND-joined — #[table(op = "...")] never applies here, on purpose: a field's own comparison operator staying fixed to that field keeps a struct's attributes readable at a glance, instead of the same field silently meaning something different in every method that references it.

If you need something more than a plain AND of equalities — OR, NOT, a different operator, anything — give the method a raw filter template instead of a field list: a single quoted string where $field is only the bound-value placeholder (numbered by first occurrence; the same field referenced twice reuses one number). The column name, =, OR, AND, NOT, parens, anything else in the string is your own SQL, passed through unchanged.

use sql_macros::SqlSelectMany;

#[derive(SqlSelectMany)]
#[table(select_many = search_users(
    "email=$email OR (is_active=$is_active AND NOT is_removed=$is_removed)"
))]
pub struct User {
    pub id: i32,
    pub email: String,
    pub is_active: bool,
    pub is_removed: bool,
}

View generated code

impl User {
    #[doc = "SELECT id, email, is_active, is_removed FROM users WHERE email=$1 OR (is_active=$2 AND NOT is_removed=$3)"]
    pub async fn search_users<'e, E>(
        executor: E,
        email: String,
        is_active: bool,
        is_removed: bool,
    ) -> Result<Vec<User>, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        let object = sqlx::query_as!(
            User,
            "SELECT id, email, is_active, is_removed FROM users WHERE email=$1 OR (is_active=$2 AND NOT is_removed=$3)",
            email,
            is_active,
            is_removed
        )
        .fetch_all(executor)
        .await?;
        Ok(object)
    }
}

This works the same way for update = name("..."), except the placeholders are numbered after the SET columns, same as a plain field list would be.

Select with enum

use sql_macros::SqlSelect;

#[derive(Debug, sqlx::Type)]
#[sqlx(type_name = "role", rename_all = "snake_case")]
pub enum Role {
    Admin,
    User,
    SuperAdmin,
}

#[derive(SqlSelect)]
pub struct User {
    #[table(select)]
    pub id: i32,
    pub email: String,
    #[table(as_type = "role!: Role")]
    pub role: Role,
}

pub async fn get_by_id(pool: &sqlx::PgPool, id: i32) -> Result<Option<User>, sqlx::Error> {
    let user = User::select_by_id(pool, id).await?;
    Ok(user)
}

View generated code

impl User {
    #[doc = "SELECT id, email, role AS \"role!: Role\" FROM users WHERE id=$1"]
    pub async fn select_by_id<'e, E>(executor: E, id: i32) -> Result<Option<User>, sqlx::Error>
    where
        E: sqlx::PgExecutor<'e>,
    {
        let object = sqlx::query_as!(
            User,
            "SELECT id, email, role AS \"role!: Role\" FROM users WHERE id=$1",
            id
        )
        .fetch_optional(executor)
        .await?;
        Ok(object)
    }
}

Attention

return_type without return_fields generates RETURNING *. That's a problem if the return type has a column with a Postgres enum (or other custom) type — sqlx::query_as! needs an AS "col!: Type" annotation to decode it, and * can't carry one, since the macro only ever sees return_type as a type name (a token), not the return type's own field list — it has no way to know that field needs an override.

#[derive(Debug, sqlx::FromRow)]
pub struct User {
    pub id: i32,
    pub email: String,
    pub role: Role,
}

#[derive(Debug, SqlInsert)]
#[table(name = "users", return_type = User)]
pub struct CreateUser {
    pub email: String,
}

Why this fails

error: no built in mapping found for type role of column "role";
a type override may be required, see documentation for details

The fix: write the annotation yourself in return_fields — it's inserted into the query as plain text, so anything valid after RETURNING works, including a type override:

#[table(name = "users", return_type = User, return_fields = "id, email, role AS \"role!: Role\"")]

Name		Name	Last commit message	Last commit date
examples		examples
sql-macros-core		sql-macros-core
sql-macros-derive		sql-macros-derive
sql-macros		sql-macros
.gitignore		.gitignore
CONTRIBUTING.md		CONTRIBUTING.md
Cargo.lock		Cargo.lock
Cargo.toml		Cargo.toml
LICENSE-APACHE		LICENSE-APACHE
LICENSE-MIT		LICENSE-MIT
Makefile		Makefile
ROADMAP.md		ROADMAP.md
Readme.md		Readme.md

Latest commit

History

Folders and files

Repository files navigation

Crate sql-macros

0.2 changes

0.3.0 changes

0.4.0 changes

0.6.0 changes

0.5.0 changes

Install

Usage

Table name

Select one

Select all

Select many

Insert

Insert without returning

Insert with returning

Insert with returning fields

Insert many

Insert many columns

Upsert

Update

Update without returning

Update with returning type

Update with returning fields

Update with special columns

Update excludes a skip-update column

Update many

Update many columns

Delete

Generate methods with many fields

Comparison operators

Custom multi-field methods

Select with enum

Attention

About

Resources

Contributing

Stars

Watchers

Forks

Releases

Packages

Used by

Contributors

Languages