example_marco/
example_marco.rs

1use std::collections::HashMap;
2
3use sqlx::{AnyPool, Arguments, Error, Executor, FromRow, MySqlPool, any::install_default_drivers};
4
5use sqlx_askama_template::SqlTemplate;
6#[derive(sqlx::prelude::FromRow, PartialEq, Eq, Debug)]
7struct User {
8    id: i64,
9    name: String,
10}
11#[derive(SqlTemplate)]
12#[template(ext="html",askama=askama,source = r#"
13    select {{e(user_id)}} as id,{{e(user_name)}} as name
14    union all 
15    {%- let id=99999_i64 %}
16    {%- let name="super man" %}
17    select {{et(id)}} as id,{{et(name)}} as name
18"#)]
19#[add_type(&'q str)]
20pub struct UserQuery {
21    pub user_id: i64,
22    pub user_name: String,
23}
24
25async fn simple_query() -> Result<(), Error> {
26    let users = vec![
27        User {
28            id: 1,
29            name: "admin".to_string(),
30        },
31        User {
32            id: 99999_i64,
33            name: "super man".to_string(),
34        },
35    ];
36
37    let user_query = UserQuery {
38        user_id: 1,
39        user_name: "admin".to_string(),
40    };
41    //pg
42
43    let pool = sqlx::PgPool::connect("postgres://postgres:postgres@localhost/postgres").await?;
44    let mut sql_buff = String::new();
45    let execute = user_query.render_executable(&mut sql_buff)?;
46
47    let rows = pool.fetch_all(execute).await?;
48    let mut db_users = Vec::new();
49    for row in &rows {
50        db_users.push(User::from_row(row)?);
51    }
52    assert_eq!(db_users, users);
53
54    //sqlite+any
55    install_default_drivers();
56    let pool = AnyPool::connect("sqlite://db.file?mode=memory").await?;
57    let mut sql_buff = String::new();
58    let rows = user_query
59        .render_executable(&mut sql_buff)?
60        .fetch_all(&pool)
61        .await?;
62
63    let mut db_users = Vec::new();
64    for row in &rows {
65        db_users.push(User::from_row(row)?);
66    }
67    assert_eq!(db_users, users);
68
69    //mysql
70
71    let pool = MySqlPool::connect("mysql://root:root@localhost/mysql").await?;
72
73    let db_users: Vec<User> = user_query
74        .adapter_render()
75        .set_persistent(false)
76        .fetch_all_as(&pool)
77        .await?;
78
79    assert_eq!(db_users, users);
80    Ok(())
81}
82
83#[derive(SqlTemplate)]
84#[add_type(Option<&'a i64>,bool)]
85#[template(
86    source = r#"
87    {%- let v="abc".to_string() %}
88    SELECT {{et(v)}} as v,t.* FROM table t
89    WHERE arg1 = {{e(arg1)}}
90      AND arg2 = {{e(arg2)}}
91      AND arg3 = {{e(arg3)}}
92      AND arg4 = {{e(arg4.first())}}
93      AND arg5 = {{e(arg5.get(&0))}}
94      {%- let v2=3_i64 %}
95      AND arg6 = {{et(v2)}}
96      {%- let v3="abc".to_string() %}
97      AND arg7 = {{et(v3)}}
98      AND arg_list1 in {{el(arg4)}}
99      {%- let list=["abc".to_string()] %}
100      AND arg_temp_list1 in {{etl(*list)}}
101      AND arg_list2 in {{el(arg5.values())}}
102      {%- if let Some(first) = arg4.first() %}
103        AND arg_option = {{et(**first)}}
104      {%- endif %}
105      {%- if let Some(first) = arg5.get(&0) %}
106        AND arg_option1 = {{et(**first)}}
107      {%- endif %}     
108"#,
109    print = "all"
110)]
111pub struct QueryData<'a, T>
112where
113    T: Sized,
114{
115    arg1: i64,
116    _arg1: i64, //same type
117    arg2: String,
118    arg3: &'a str,
119    #[ignore_type]
120    arg4: Vec<i64>,
121    #[ignore_type]
122    arg5: HashMap<i32, i64>,
123    #[ignore_type]
124    #[allow(unused)]
125    arg6: T,
126}
127
128#[derive(SqlTemplate)]
129#[template(source = r#"
130    {%- let status_list = ["active", "pending"] %}
131    SELECT 
132        u.id,
133        u.name,
134        COUNT(o.id) AS order_count
135    FROM users u
136    LEFT JOIN orders o ON u.id = o.user_id
137    WHERE 1=1
138    {%- if let Some(min_age) = min_age %}
139        AND age >= {{et(min_age)}}
140    {%- endif %}
141    {%- if filter_names.len()>0 %}
142        AND name IN {{el(filter_names)}}
143    {%- endif %}
144    AND status IN {{etl(*status_list)}}
145    GROUP BY u.id
146    ORDER BY {{order_field}}
147    LIMIT {{e(limit)}}
148    "#)]
149#[add_type(i32)]
150pub struct ComplexQuery<'a> {
151    min_age: Option<i32>,
152    #[ignore_type]
153    filter_names: Vec<&'a str>,
154    order_field: &'a str,
155    limit: i64,
156}
157
158fn render_complex_sql() {
159    let data = QueryData {
160        arg1: 42,
161        _arg1: 123,
162        arg2: "value".to_string(),
163        arg3: "reference",
164        arg4: vec![12, 12, 55, 66],
165        arg5: HashMap::from_iter([(0, 2), (1, 2), (2, 3)]),
166        arg6: 1,
167    };
168
169    let (sql, arg) =
170        <&QueryData<'_, i32> as SqlTemplate<'_, sqlx::Postgres>>::render_sql(&data).unwrap();
171
172    assert_eq!(arg.unwrap().len(), 18);
173    println!("----{sql}----");
174
175    let data = ComplexQuery {
176        filter_names: vec!["name1", "name2"],
177        limit: 10,
178        min_age: Some(18),
179        order_field: "id",
180    };
181
182    let (sql, arg) =
183        <&ComplexQuery<'_> as SqlTemplate<'_, sqlx::Postgres>>::render_sql(&data).unwrap();
184
185    assert_eq!(arg.unwrap().len(), 6);
186    println!("----{sql}----");
187}
188#[tokio::main]
189async fn main() -> Result<(), Error> {
190    simple_query().await?;
191    render_complex_sql();
192
193    Ok(())
194}
example_marco/example_marco.rs

example_marco/
example_marco.rs
