Derive Macro UpsertTemplate 

#[derive(UpsertTemplate)]
{
    // Attributes available to this derive:
    #[table]
    #[tp_upsert]
    #[debug_slow]
    #[db]
}

UpsertTemplate is a derive macro designed to automatically generate upsert (INSERT … ON CONFLICT) functions based on sqlx. This macro creates upsert methods for the struct it is applied to, which can either insert a new record or update an existing one based on conflict resolution. It assumes that the columns in the database correspond to the fields in the struct.

Attributes

UpsertTemplate accepts the following attributes:

• table: Specifies the name of the table in the database (mandatory).
• debug_slow: Configures debug logs for the executed query:
  • If set to 0: Only logs the executed query.
  • If set to a value greater than 0: Only logs the query if the execution time exceeds the configured value (in milliseconds).
  • If not configured, no debug logs will be generated.
• tp_upsert: The main configuration for generating the upsert function, with the following sub-attributes:
  • conflict: Specifies the columns that define the conflict condition (mandatory).
  • update: List of columns that will be updated on conflict. If empty, all non-conflict columns will be updated.
  • where: Additional WHERE clause for the ON CONFLICT DO UPDATE with placeholder support (see Placeholder Mapping in SelectTemplate).
  • fn_name: The name of the generated function. If empty, the library will automatically generate a function name.
  • returning: If set to true, the generated function will return the upserted record (PostgreSQL only).
  • debug_slow: Configures debug logs for the executed query (overrides struct-level setting).

Database Support

Upsert functionality is supported in:

• PostgreSQL: Uses INSERT ... ON CONFLICT ... DO UPDATE syntax with full RETURNING support
• SQLite: Uses INSERT ... ON CONFLICT ... DO UPDATE syntax with RETURNING support (SQLite 3.35.0+)
• MySQL: Uses INSERT ... ON DUPLICATE KEY UPDATE syntax (RETURNING not supported)

Example

use sqlx_template::UpsertTemplate;
use sqlx::Pool;

// PostgreSQL example
#[derive(UpsertTemplate, sqlx::FromRow)]
#[table("users")]
#[tp_upsert(by = "email", update = "password, updated_at", fn_name = "upsert_user")]
#[tp_upsert(by = "id", fn_name = "upsert_by_id", returning = true)]
#[debug_slow = 1000]
#[db("postgres")]
pub struct UserPg {
    pub id: i32,
    pub email: String,
    pub password: String,
    pub updated_at: Option<chrono::DateTime<chrono::Utc>>,
}

// SQLite example
#[derive(UpsertTemplate, sqlx::FromRow)]
#[table("users")]
#[tp_upsert(by = "email", update = "password")]
#[db("sqlite")]
pub struct UserSqlite {
    pub id: i32,
    pub email: String,
    pub password: String,
}

// MySQL example
#[derive(UpsertTemplate, sqlx::FromRow)]
#[table("users")]
#[tp_upsert(by = "email", update = "password")]
#[db("mysql")]
pub struct UserMysql {
    pub id: i32,
    pub email: String,
    pub password: String,
}

// Usage:
let user = UserPg { id: 1, email: "john@example.com".to_string(), password: "newpass".to_string(), updated_at: None };
let rows_affected = UserPg::upsert_user(&user, &pool).await?;

// With returning (PostgreSQL and SQLite)
let upserted_user = UserPg::upsert_by_id(&user, &pool).await?;

Note

This macro relies on sqlx and database-specific upsert syntax. Make sure your target database supports the generated upsert statements.