Derive Macro SqliteTemplate

#[derive(SqliteTemplate)]
{
    // Attributes available to this derive:
    #[table]
    #[tp_upsert]
    #[tp_select_all]
    #[tp_select_one]
    #[tp_select_page]
    #[tp_select_stream]
    #[tp_select_count]
    #[tp_update]
    #[tp_delete]
    #[auto]
    #[debug_slow]
    #[tp_select_builder]
    #[tp_update_builder]
    #[tp_delete_builder]
}

SqliteTemplate is a database-specific version of SqlxTemplate optimized for SQLite. This macro generates all database operation functions specifically targeting SQLite features and syntax. It combines insert, update, select, delete, and upsert operations with SQLite-specific optimizations and syntax compatibility.

Attributes

SqliteTemplate accepts the same attributes as SqlxTemplate:

• table: Specifies the name of the table in the database (mandatory).
• debug_slow: Global debug configuration for all generated functions.
• auto: Applied to fields that should be excluded from insert statements.
• tp_select_all, tp_select_one, tp_select_page, tp_select_stream, tp_select_count: Select operation configurations.
• tp_update: Update operation configurations.
• tp_delete: Delete operation configurations.
• tp_upsert: Upsert operation configurations.
• tp_select_builder: Builder pattern configuration for SELECT operations.
• tp_update_builder: Builder pattern configuration for UPDATE operations.
• tp_delete_builder: Builder pattern configuration for DELETE operations.

Builder Pattern Support

The macro supports fluent builder patterns for query construction with custom WHERE conditions.

Builder Attributes

• tp_select_builder: Builder pattern configuration for SELECT operations
• tp_update_builder: Builder pattern configuration for UPDATE operations
• tp_delete_builder: Builder pattern configuration for DELETE operations

Custom Condition Syntax

Custom conditions are defined using the following syntax:

#[tp_select_builder(
    method_name = "SQL_expression_with_placeholders"
)]

Parameter Types

• Auto-mapping: :field_name automatically maps to struct field types
• Explicit types: :param$Type for custom parameter types
• Multiple parameters: Single condition can accept multiple parameters

Examples

#[tp_select_builder(
    with_email_domain = "email LIKE :domain$String",
    with_score_range = "score BETWEEN :min$i32 AND :max$i32",
    with_active_status = "active = :active"  // Auto-mapped to bool
)]

Generated Methods

For each field, the builder generates:

Field-based Methods

• Equality: .field_name(value), .field_name_not(value)
• Comparison: .field_name_gt(value), .field_name_gte(value), .field_name_lt(value), .field_name_lte(value)
• String operations: .field_name_like(pattern), .field_name_start_with(prefix), .field_name_end_with(suffix)
• Ordering: .order_by_field_asc(), .order_by_field_desc()

Builder-specific Methods

SELECT Builder

• Query execution: .find_all(), .find_one(), .count(), .find_page(), .stream()
• SQL generation: .build_sql()

UPDATE Builder

• SET clauses: .on_field_name(value) - specify which fields to update
• WHERE clauses: .by_field_name(value) - specify which records to update
• Execution: .execute() - returns number of affected rows

DELETE Builder

• WHERE clauses: .field_name(value) - specify which records to delete
• Execution: .execute() - returns number of deleted rows

Usage Examples

SELECT Builder

let users = User::builder_select()
    .email("john@example.com")?           // Field-based condition
    .score_gte(&75)?                      // Generated comparison method
    .with_email_domain("%@company.com")?  // Custom condition
    .with_score_range(60, 90)?            // Custom condition with multiple params
    .order_by_score_desc()?               // Generated ORDER BY method
    .find_all(&pool)
    .await?;

UPDATE Builder

let affected = User::builder_update()
    .on_email("newemail@example.com")?    // SET email = ?
    .on_active(&true)?                    // SET active = ?
    .by_id(&user_id)?                     // WHERE id = ?
    .with_high_score(80)?                 // Custom WHERE condition
    .execute(&pool)
    .await?;

DELETE Builder

let deleted = User::builder_delete()
    .active(&false)?                      // WHERE active = false
    .with_old_accounts("2023-01-01")?     // Custom WHERE condition
    .execute(&pool)
    .await?;

Validation

• Table alias validation: Prevents use of table aliases (e.g., u.field) in custom conditions
• Column validation: Ensures referenced columns exist in the struct
• Type safety: Compile-time parameter type checking
• SQL injection protection: Uses parameterized queries

Performance Features

• Compile-time optimization: SQL templates pre-generated at compile time
• Minimal runtime overhead: Reduced format! calls and string allocations
• Efficient parameter binding: Direct parameter binding without intermediate formatting

Document generated by LLMs

SQLite-Specific Features

• Optimized query generation for SQLite syntax
• Support for SQLite-specific data types and functions
• Upsert operations using SQLite’s INSERT … ON CONFLICT syntax
• Proper handling of SQLite’s ROWID and auto-increment columns
• SQLite-compatible LIMIT and OFFSET syntax for pagination
• Builder pattern with compile-time optimized SQL generation

Example

use sqlx_template::SqliteTemplate;
use sqlx::Pool;

#[derive(SqliteTemplate, sqlx::FromRow)]
#[table("users")]
#[tp_select_builder(
    with_email_domain = "email LIKE :domain$String",
    with_score_range = "score BETWEEN :min$i32 AND :max$i32"
)]
#[tp_update(by = "id")]
#[tp_delete(by = "id")]
#[tp_upsert(by = "email", update = "password")]
pub struct User {
    #[auto]
    pub id: i32,
    pub email: String,
    pub password: String,
    pub score: i32,
    pub active: bool,
}

let user = User {
    id: 1,
    email: "john@example.com".to_string(),
    password: "password".to_string(),
    score: 85,
    active: true
};

// Traditional operations:
// User::insert(&user, &pool).await?;
// User::update_by_id(&user, &pool).await?;
// User::delete_by_id(&1, &pool).await?;
// User::upsert_by_email(&user, &pool).await?;

// Builder pattern operations:
let users = User::builder_select()
    .email("john@example.com")?
    .active(&true)?
    .with_email_domain("%@company.com")?
    .order_by_score_desc()?
    .find_all(&pool)
    .await?;

Note

This macro is specifically designed for SQLite and generates SQLite-compatible SQL syntax. Use SqlxTemplate for database-agnostic code or other database-specific templates for other databases.