Crate sqlx_askama_template

SQLx Askama Template

A SQLx query builder based on the Askama template engine, providing type-safe SQL templates and parameter binding.

Features

• 🚀 Zero-Cost Abstraction - Compile-time optimized SQL generation
• 🔒 Type Safety - Automatic validation of SQL parameter types
• 📦 Multi-Database Support - PostgreSQL/MySQL/SQLite/Any
• 💡 Smart Parameter Binding - Auto-expansion for list parameters
• 🎨 Template Syntax - Full Askama templating capabilities

Installation

Add to Cargo.toml:

[dependencies]  
sqlx-askama-template = "0.3.1"
sqlx = { version = "0.8", features = ["all-databases", "runtime-tokio"] }
tokio = { version = "1.0", features = ["full"] }

Quick Start

Basic Usage

use sqlx::postgres::PgListener;
use sqlx::{AnyPool, Error, any::install_default_drivers};
use sqlx::{MySqlPool, PgPool, SqlitePool};

use sqlx_askama_template::{BackendDB, DatabaseDialect, SqlTemplate};

use sqlx_askama_template::DBType;
#[derive(sqlx::prelude::FromRow, PartialEq, Eq, Debug)]
struct User {
    id: i64,
    name: String,
}
#[derive(SqlTemplate)]
#[template(source = r#"
    select {{e(user_id)}} as id,{{e(user_name)}} as name
    union all 
    {%- let id=99999_i64 %}
    {%- let name="super man" %}
    select {{et(id)}} as id,{{et(name)}} as name
"#)]
#[add_type(&'q str)]
pub struct UserQuery {
    pub user_id: i64,
    pub user_name: String,
}

async fn test_backend(urls: Vec<(DBType, &str)>) -> Result<(), Error> {
    install_default_drivers();
    for (db_type, url) in urls {
        let pool = AnyPool::connect(url).await?;
        // pool
        let (get_db_type, _get_conn) = pool.backend_db().await?;
        assert_eq!(db_type.backend_name(), get_db_type.backend_name());

        // connection
        let mut conn = pool.acquire().await?;
        let (get_db_type, _get_conn) = conn.backend_db().await?;
        assert_eq!(db_type.backend_name(), get_db_type.backend_name());

        // tx
        let mut conn = pool.begin().await?;
        let (get_db_type, _get_conn) = conn.backend_db().await?;
        assert_eq!(db_type.backend_name(), get_db_type.backend_name());

        match db_type {
            DBType::MySQL => {
                //mysql  DBType::MySQL, "mysql://root:root@localhost/mysql"
                let pool = MySqlPool::connect(url).await?;
                // pool
                let (get_db_type, _get_conn) = pool.backend_db().await?;
                assert_eq!(DBType::MySQL.backend_name(), get_db_type.backend_name());

                // connection
                let mut conn = pool.acquire().await?;
                let (get_db_type, _get_conn) = conn.backend_db().await?;
                assert_eq!(DBType::MySQL.backend_name(), get_db_type.backend_name());

                //
                let mut conn = pool.begin().await?;
                let (get_db_type, _get_conn) = conn.backend_db().await?;
                assert_eq!(DBType::MySQL.backend_name(), get_db_type.backend_name());
            }
            DBType::PostgreSQL => {
                //pg
                let pool = PgPool::connect(url).await?;
                // pool
                let (get_db_type, _get_conn) = pool.backend_db().await?;
                assert_eq!(
                    DBType::PostgreSQL.backend_name(),
                    get_db_type.backend_name()
                );

                // connection
                let mut conn = pool.acquire().await?;
                let (get_db_type, _get_conn) = conn.backend_db().await?;
                assert_eq!(
                    DBType::PostgreSQL.backend_name(),
                    get_db_type.backend_name()
                );

                // tx
                let mut conn = pool.begin().await?;
                let (get_db_type, _get_conn) = conn.backend_db().await?;
                assert_eq!(
                    DBType::PostgreSQL.backend_name(),
                    get_db_type.backend_name()
                );

                // listener
                let mut listener = PgListener::connect(url).await?;
                let (get_db_type, _get_conn) = listener.backend_db().await?;
                assert_eq!(
                    DBType::PostgreSQL.backend_name(),
                    get_db_type.backend_name()
                );
            }
            DBType::SQLite => {
                //sqlite DBType::SQLite, "sqlite://db.file?mode=memory"
                let pool = SqlitePool::connect(url).await?;
                // pool
                let (get_db_type, _get_conn) = pool.backend_db().await?;
                assert_eq!(DBType::SQLite.backend_name(), get_db_type.backend_name());

                // connection
                let mut conn = pool.acquire().await?;
                let (get_db_type, _get_conn) = conn.backend_db().await?;
                assert_eq!(DBType::SQLite.backend_name(), get_db_type.backend_name());

                // tx
                let mut conn = pool.begin().await?;
                let (get_db_type, _get_conn) = conn.backend_db().await?;
                assert_eq!(DBType::SQLite.backend_name(), get_db_type.backend_name());
            }
        }
        test_adapter_query(url).await?;
    }

    Ok(())
}

async fn test_adapter_query(url: &str) -> Result<(), Error> {
    //  test count
    let user_query = UserQuery {
        user_id: 1,
        user_name: "admin".to_string(),
    };
    let mut sql_buff = String::new();
    let db_adatper = user_query.render_db_adapter_manager(&mut sql_buff);
    let pool = AnyPool::connect(url).await?;

    let count = db_adatper.count(&pool).await?;
    assert_eq!(2, count);
    println!("{}", sql_buff);

    // test page
    let db_adatper = user_query.render_db_adapter_manager(&mut sql_buff);
    let user: Option<User> = db_adatper.set_page(1, 1).fetch_optional_as(&pool).await?;

    println!("{:?}", user);

    let pool = AnyPool::connect(url).await?;
    let user: Vec<User> = user_query
        .render_db_adapter_manager(&mut sql_buff)
        .set_page(1, 2)
        .fetch_all_as(&pool)
        .await?;
    println!("{}", sql_buff);
    println!("{:?}", user);

    let page_info = user_query
        .render_db_adapter_manager(&mut sql_buff)
        .count_page(1, &pool)
        .await?;
    println!("{}", sql_buff);
    println!("{:?}", page_info);
    //fecth
    let user: Vec<User> = user_query
        .render_db_adapter_manager(&mut sql_buff)
        .fetch_all_as(&pool)
        .await?;
    println!("{}", sql_buff);
    println!("{:?}", user);
    Ok(())
}
#[tokio::main]
async fn main() -> Result<(), Error> {
    let urls = vec![
        (
            DBType::PostgreSQL,
            "postgres://postgres:postgres@localhost/postgres",
        ),
        (DBType::SQLite, "sqlite://db.file?mode=memory"),
        // (DBType::MySQL, "mysql://root:root@localhost/mysql"),
    ];
    test_backend(urls).await?;

    Ok(())
}

Core Features

Template Syntax

Syntax	Example	Description
Single Parameter	{{e(user_id)}}	Binds a single parameter
List Expansion	{{el(ids)}}	Expands to IN (?, ?)
Temporary Variables	{% let limit = 100 %}	Defines template-local variables
Conditional Logic	{% if active %}...{% endif %}	Dynamic SQL conditions

Parameter Encoding Methods

Method	Description	Example
e()	Encodes a single value	{{e(user_id)}}
el()	Encodes a list ($1, $2...)	{{el(ids)}}
et()	Encodes a template-local value	{{et(limit)}}
etl()	Encodes a template-local list	{{etl(filters)}}

Multi-Database Support

Database	Parameter Style	Example
PostgreSQL	$1, $2	WHERE id = $1
MySQL	?	WHERE id = ?
SQLite	?	WHERE id = ?

Macro Attributes

#[template] - Core Template Attribute

#[derive(SqlTemplate)]  
#[template(  
    source = "SQL template content",  // Required  
    ext = "txt",                      // Askama file extension  
    print = "all",                    // Optional debug output (none, ast, code, all)  
    config = "path"                   // Optional custom Askama config path  
)]  

Parameters:

• source: Inline SQL template content (supports Askama syntax)
• ext: Askama file extension
• print: Debug mode for Askama
• config: Path to a custom Askama configuration file

#[add_type] - Add Additional Type Constraints

Used to add Encode + Type constraints for non-field types in templates (e.g., Vec<T>, HashMap<K, V>).

#[derive(SqlTemplate)]  
#[add_type(chrono::NaiveDate, Option<&'a str>)]  // Add extra type support  

Features:

• Adds type constraints for template-local variables
• Supports comma-separated types

#[ignore_type] - Skip Field Type Constraints

#[derive(SqlTemplate)]  
struct Query {  
    #[ignore_type]  // Skip type checks for this field  
    metadata: JsonValue  
}  

Use Cases:

• Skip fields that do not require SQLx parameter binding
• Avoid unnecessary trait constraints for complex types

Full Example

use std::collections::HashMap;  

use sqlx_askama_template::SqlTemplate;  

#[derive(SqlTemplate)]  
#[template(  
    source = r#"  
    {% let status_list = ["active", "pending"] %}  
    SELECT  
        u.id,  
        u.name,  
        COUNT(o.id) AS order_count  
    FROM users u  
    LEFT JOIN orders o ON u.id = o.user_id  
    WHERE 1=1  
    {% if let Some(min_age) = min_age %}  
        AND age >= {{et(min_age)}}  
    {% endif %}  
    {% if filter_names.len()>0 %}  
        AND name IN {{el(filter_names)}}  
    {% endif %}  
    AND status IN {{etl(*status_list)}}  
    GROUP BY u.id  
    ORDER BY {{order_field}}  
    LIMIT {{e(limit)}}  
    "#,  
    ext = "txt"  
)]  
#[add_type(i32)]  
pub struct ComplexQuery<'a> {  
    min_age: Option<i32>,  
    #[ignore_type]  
    filter_names: Vec<&'a str>,  
    order_field: &'a str,  
    limit: i64,  
}  

#[test]  
fn render_complex_sql() {  
    let data = ComplexQuery {  
        filter_names: vec!["name1", "name2"],  
        limit: 10,  
        min_age: Some(18),  
        order_field: "id",  
    };  

    let (sql, arg) =  
        <&ComplexQuery<'_> as SqlTemplate<'_, sqlx::Postgres>>::render_sql(&data).unwrap();  
    use sqlx::Arguments;  
    assert_eq!(arg.unwrap().len(), 6);  
    println!("----{sql}----");  
}  

#[derive(SqlTemplate)]  
#[add_type(Option<&'a i64>, bool)]  
#[template(  
    ext = "txt",  
    source = r#"  
    {% let v="abc".to_string() %}  
    SELECT {{et(v)}} as v,t.* FROM table t  
    WHERE arg1 = {{e(arg1)}}  
      AND arg2 = {{e(arg2)}}  
      AND arg3 = {{e(arg3)}}  
      AND arg4 = {{e(arg4.first())}}  
      AND arg5 = {{e(arg5.get(&0))}}  
      {% let v2=3_i64 %}  
      AND arg6 = {{et(v2)}}  
      {% let v3="abc".to_string() %}  
      AND arg7 = {{et(v3)}}  
      AND arg_list1 in {{el(arg4)}}  
      {% let list=["abc".to_string()] %}  
      AND arg_temp_list1 in {{etl(*list)}}  
      AND arg_list2 in {{el(arg5.values())}}  
      {% if let Some(first) = arg4.first() %}  
        AND arg_option = {{et(**first)}}  
      {% endif %}  
      {% if let Some(first) = arg5.get(&0) %}  
        AND arg_option1 = {{et(**first)}}  
      {% endif %}  
"#,  
    print = "all"  
)]  
pub struct QueryData<'a, T>  
where  
    T: Sized,  
{  
    arg1: i64,  
    _arg1: i64,  // Same type  
    arg2: String,  
    arg3: &'a str,  
    #[ignore_type]  
    arg4: Vec<i64>,  
    #[ignore_type]  
    arg5: HashMap<i32, i64>,  
    #[ignore_type]  
    #[allow(unused)]  
    arg6: T,  
}  

#[test]  
fn render_query_data_sql() {  
    let data = QueryData {  
        arg1: 42,  
        _arg1: 123,  
        arg2: "value".to_string(),  
        arg3: "reference",  
        arg4: vec![12, 12, 55, 66],  
        arg5: HashMap::from_iter([(0, 2), (1, 2), (2, 3)]),  
        arg6: 1,  
    };  

    let (sql, arg) =  
        <&QueryData<'_, i32> as SqlTemplate<'_, sqlx::Postgres>>::render_sql(&data).unwrap();  
    use sqlx::Arguments;  
    assert_eq!(arg.unwrap().len(), 18);  
    println!("----{sql}----");  
}  

Best Practices

1. Use `{% if %}` blocks for dynamic SQL  
2. Use `add_type` to add type constraints for template-local variables  
3. Use `ignore_type` to skip serialization for specific fields  
4. Set `print = "none"` in production  

License

This project is licensed under the Apache License 2.0.
Copyright © 2025 gouhengheng

Important Notice:
Under the Apache 2.0 License, you may not use this file except in compliance with the License.
You may obtain a copy of the License at:
http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an “AS IS” BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for specific governing permissions and limitations.

Re-exports

pub use askama;

Structs

AdapterExecutor

DBAdapterManager

Database adapter manager handling SQL rendering and execution

PageInfo

Pagination metadata container

SqlTemplateExecute

Internal executor for SQL templates

TemplateArg

SQL template argument processor handling safe parameter encoding and placeholder generation

Enums

DBType

Database type enumeration supporting major database systems

Traits

BackendDB

Trait for database connections/pools that can detect their backend type

DatabaseDialect

Abstracts SQL dialect differences across database systems

SqlTemplate

SQL template trait

Functions

backend_db

Derive Macros

SqlTemplate