Why are specific values specified in the query builder?

[nu11ptr]

A two part question, first a design question and the second specific to my situation:

First, why are specific parameter values specified to the query builder in things like the WHERE clause? I don't see how it can statically type check these (or can it and I'm missing that?), and I would expect you would want to cache/prepare the query either in the database directly or at least cache the string itself (to avoid rebuilding it every query), however, your values would be different on every query, so why build them together?

In summary, the design seems to be:

1. SeaQuery (every time): build query -> add param values -> (string, values)
2. Database (every time): execute query and values

What I would have expected:

1. SeaQuery (first time): build query -> add params as placeholders ($1, ?, etc.) -> (string, statement)
2. Database (first time): prepare query/statement (optional)
3. SeaQuery OR Database (every time): add values to statement -> values
4. Database (every time): execute query and values

By making it two step build instead of one you don't generate the query every time you execute, you prepare/cache it. Am I missing something? This seems like the obvious design, but I'm assuming you had your reasons.

Second, I would like to prepare the SQL statement, not always rebuild it, but apply different values every time. I am planning to just discard the generated Values and keep the String, so I want that part as cheap as possible on the first hit. But what values do I then supply to things like .eq()? Bogus values of any primitive type? ""? or 0? or? I just need a query with the resulting text like: "WHERE 'my_col' = $1", etc. (but of course DB specific). Or is there a "special" value to signal I just want a variable as the target?

Thanks

[tyt2y3]

Why SeaQuery does “string + values” every time

• String building is cheap: Constructing the SQL string is not the bottleneck. Database drivers already have prepared statement caches internally. So even if SeaQuery hands over a fresh (string, values) each time, the driver can reuse the prepared plan under the hood. You don’t pay the full parse cost every time.

• AST structural difference: SeaQuery builds queries dynamically. The AST can differ between calls depending on conditions (e.g. optional filters, dynamic joins, conditional WHERE clauses). That makes it tricky to pre‑prepare a single statement and just swap values - because the shape of the query itself may change. Example from readme:

fn query(a: Option<i32>, b: Option<char>) -> SelectStatement {
    Query::select()
        .column("id")
        .from("character")
        .apply_if(a, |q, v| {
            q.and_where(Expr::col("font_id").eq(v));
        })
        .apply_if(b, |q, v| {
            q.and_where(Expr::col("ascii").like(v));
        })
        .take()
}

assert_eq!(
    query(Some(5), Some('A')).to_string(MysqlQueryBuilder),
    "SELECT `id` FROM `character` WHERE `font_id` = 5 AND `ascii` LIKE 'A'"
);
assert_eq!(
    query(Some(5), None).to_string(MysqlQueryBuilder),
    "SELECT `id` FROM `character` WHERE `font_id` = 5"
);
assert_eq!(
    query(None, None).to_string(MysqlQueryBuilder),
    "SELECT `id` FROM `character`"
);

Why you can’t just skip values

• In SeaQuery, values are used to infer the type of each parameter. The builder needs to know whether $1 is an i32, a TEXT, a UUID, etc
• PostgreSQL’s wire protocol is very strict about parameter typing. You can’t just throw in a generic NULL placeholder - the driver needs to know which type of NULL it is. so it has to be Option<i32>::None
• In practice, you can just use i32::default() or 0i32 as dummy values

[nu11ptr]

Thanks for this full answer. A few thoughts:

• The string itself is cheap to build, but I wasn't sure about the statement struct that leads up to that was. I see a ton of Vecs in there so that is a lot of allocations. I do agree that I/O is likely to make this a very small part of the cost, but this is still Rust, and we often try to minimize anything we can. Most of the DB crates use generics over trait objects, so while it may not matter, all other design decisions in the ecosystem still gravitate towards bare metal (right or wrong).

• I admit I've never built a dynamic query like that, but I just started using it. This makes sense to me, but I wonder how common this is in practice?

• Skipping values: I would argue this is the difference between a type and a value. I can understand why you would need the type, but the value seems entirely unnecessary to me (with the exception of the above dynamic query), so my expectation is you would focus on the type in the query construction and then use that to validate values added later are correct in amount and type.