chain-builder 2.0.0

A typed, dialect-aware SQL query builder for Rust (PostgreSQL/MySQL/SQLite).
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123

use chain_builder::{MySql, Postgres, QueryBuilder, Sqlite, Value};

#[test]
fn postgres_cte_main_union_placeholder_ordering() {
    // THE CRUX: $1 (cte body) -> $2 (main where) -> $3 (union arm).
    let cte = QueryBuilder::<Postgres>::table("logs")
        .select(["n"])
        .where_gt("n", 1i64);
    let arm = QueryBuilder::<Postgres>::table("recent")
        .select(["n"])
        .where_lt("n", 99i64);

    let (sql, binds) = QueryBuilder::<Postgres>::table("recent")
        .with("recent", cte)
        .select(["*"])
        .where_gt("n", 5i64)
        .union(arm)
        .to_sql();

    assert_eq!(
        sql,
        r#"WITH "recent" AS (SELECT "n" FROM "logs" WHERE "n" > $1) SELECT * FROM "recent" WHERE "n" > $2 UNION SELECT "n" FROM "recent" WHERE "n" < $3"#
    );
    assert_eq!(binds, vec![Value::I64(1), Value::I64(5), Value::I64(99)]);
}

#[test]
fn postgres_with_recursive() {
    let cte = QueryBuilder::<Postgres>::table("t").select(["n"]);
    let (sql, _binds) = QueryBuilder::<Postgres>::table("t")
        .with_recursive("t", cte)
        .select(["*"])
        .to_sql();

    assert_eq!(
        sql,
        r#"WITH RECURSIVE "t" AS (SELECT "n" FROM "t") SELECT * FROM "t""#
    );
}

#[test]
fn postgres_union_all() {
    let arm = QueryBuilder::<Postgres>::table("b").select(["id"]);
    let (sql, _binds) = QueryBuilder::<Postgres>::table("a")
        .select(["id"])
        .union_all(arm)
        .to_sql();

    assert_eq!(
        sql,
        r#"SELECT "id" FROM "a" UNION ALL SELECT "id" FROM "b""#
    );
}

#[test]
fn postgres_two_binds_in_cte_and_main() {
    let cte = QueryBuilder::<Postgres>::table("logs")
        .select(["n"])
        .where_gt("n", 1i64)
        .where_lt("n", 10i64);

    let (sql, binds) = QueryBuilder::<Postgres>::table("recent")
        .with("recent", cte)
        .select(["*"])
        .where_gte("n", 5i64)
        .where_lte("n", 8i64)
        .to_sql();

    assert_eq!(
        sql,
        r#"WITH "recent" AS (SELECT "n" FROM "logs" WHERE "n" > $1 AND "n" < $2) SELECT * FROM "recent" WHERE "n" >= $3 AND "n" <= $4"#
    );
    assert_eq!(
        binds,
        vec![Value::I64(1), Value::I64(10), Value::I64(5), Value::I64(8)]
    );
}

#[test]
fn mysql_cte_union() {
    let cte = QueryBuilder::<MySql>::table("logs")
        .select(["n"])
        .where_gt("n", 1i64);
    let arm = QueryBuilder::<MySql>::table("recent")
        .select(["n"])
        .where_lt("n", 99i64);

    let (sql, binds) = QueryBuilder::<MySql>::table("recent")
        .with("recent", cte)
        .select(["*"])
        .where_gt("n", 5i64)
        .union(arm)
        .to_sql();

    assert_eq!(
        sql,
        "WITH `recent` AS (SELECT `n` FROM `logs` WHERE `n` > ?) SELECT * FROM `recent` WHERE `n` > ? UNION SELECT `n` FROM `recent` WHERE `n` < ?"
    );
    assert_eq!(binds, vec![Value::I64(1), Value::I64(5), Value::I64(99)]);
}

#[test]
fn sqlite_cte_union() {
    let cte = QueryBuilder::<Sqlite>::table("logs")
        .select(["n"])
        .where_gt("n", 1i64);
    let arm = QueryBuilder::<Sqlite>::table("recent")
        .select(["n"])
        .where_lt("n", 99i64);

    let (sql, binds) = QueryBuilder::<Sqlite>::table("recent")
        .with("recent", cte)
        .select(["*"])
        .where_gt("n", 5i64)
        .union(arm)
        .to_sql();

    assert_eq!(
        sql,
        r#"WITH "recent" AS (SELECT "n" FROM "logs" WHERE "n" > ?) SELECT * FROM "recent" WHERE "n" > ? UNION SELECT "n" FROM "recent" WHERE "n" < ?"#
    );
    assert_eq!(binds, vec![Value::I64(1), Value::I64(5), Value::I64(99)]);
}