Sqlx: [Discussion] Dynamic Query Builder Design

[abonander]

We have received a lot of requests, and have a significant need ourselves, for a dynamic query builder in SQLx that can generate and execute valid SQL with the appropriate bind parameters for a given database backend at runtime.

This is to do things like:

• Dynamically add columns from the query based on what data is needed
• Dynamically add joins to the query from other tables
• Dynamically add where clauses based on user input
• Generate INSERT INTO foo(...) VALUES(...) statements with a variable number of records in the VALUES expression, taking into account the SQL flavor's max number of bind parameters per query (which we can build a more specialized API for on top of this)

MVP

I'm thinking the minimum viable product for this would be something like the following:

pub struct QueryBuilder<DB> { ... }

impl<DB> QueryBuilder<DB> {
    /// Create a builder with the initial SQL literal to append to
    pub fn new(init: impl Into<String>) -> Self { ... }

    /// Push more SQL code
    pub fn push(&mut self, sql: impl Display) -> Self { ... }

    /// Push a bind parameter to the SQL and add encode its value
    /// RFC: should we be taking values here, or bind once the query is built?
    pub fn push_bind(&mut self, value: impl Encode<DB>) -> Self { ... }

    /// Append a comma separated list with the closure being repeatedly called to add the values between commas until it returns `false`.
    ///
    /// This seems a bit higher level but it would be really useful for generating `VALUES` lists with binds
    pub fn comma_sep(&mut self, impl FnMut(&mut QueryBuilder<DB>) -> bool) -> Self { ... }

    /// Finalize the query so it can be executed
    /// 
    /// I'm thinking this clones the query so the builder can be reused
    pub fn build(&mut self) -> sqlx::Query<DB, 'static> { ... }

    /// Alternatively, we have `.execute()`, `.fetch()`, `.fetch_all()`, etc. on this struct
}

Thoughts to the Future

• Some sort of macro that can do interpolations and dynamic repetitions like quote::quote!()
  
  
  
  • Perhaps still somehow checking the query at compile time
  
  
  • The catch here is that the syntax would have to be tokenizable by Rust's lexer and I think there's a few things about SQL which violate that (single-quote strings, for one)
  
  
• A dedicated API for generating efficient INSERT INTO statements
  
  
  
  • Perhaps a proc-macro that generates an insertion builder for a given table with specific columns and return values
  
  

Non-Goals

• We don't want to go overboard and implement a fully fledged query DSL yet, that's a discussion for another day

cc @mehcode @izik1 @danielakhterov

[connec]

I guess that would result in usage like the following to perform a multi-row insert:

// E.g.
let records: Vec<(Uuid, i32)> = vec![...];

let records_iter = records.iter();
let query = QueryBuilder::new("INSERT INTO users VALUES ")
    .comma_sep(move |builder| {
        if let Some(record) = records_iter.next() {
            builder.push("(");
            builder.push_bind(record.0);
            builder.push(", ");
            builder.push_bind(record.1);
            builder.push(")");
            true
        } else {
            false
        }
    })
    .build();


let result = query.execute(&conn).await?;
assert_eq!(result, 2);

This would be a decent starting point, though the inner building would be a bit painful for large structs.

[foldu]

Prisma has a working SQL builder: https://github.com/prisma/quaint

[mehcode]

Note that this interface would be aimed at being used _by_ quaint. Not at replacing Quaint.

---

We want to reach for low-level while still doing what we can with our db-specific knowledge ( placeholders, ID quoting, etc. ).

[abonander]

This would be a decent starting point, though the inner building would be a bit painful for large structs.

Maybe instead we have .separated(char) -> Separated where Separated also has .push() and .push_bind() but appends the separator between calls or something.

[elertan]

Any progress on this? I feel like this is very much a necessary feature for real world applications.
I am currently really in need of some dynamic query building system, specifically dynamically building up a where clause based on user input.
I don't know how to do this in a temporary fashion either, that does not fall back to copy pasting logic with different parameters...

[elertan]

Any update on this?

[nkconnor]

/// I'm thinking this clones the query so the builder can be reused
pub fn build(&mut self) -> sqlx::Query<DB, 'static> { ... }

Wouldn't that by virtue of the builder also clone the arguments? It seems like that would be a bit heavy. Alternatively, if the design had a placeholder type param, we could clone the query "plan" and later bind the args.

[abonander]

Wouldn't that by virtue of the builder also clone the arguments?

Hmm, no, the arguments would be wrapped in Option internally and it would probably panic if called more than once. Really though, the only reason to take &mut self here is so that it can be called at the end of a method chain.

[nkconnor]

do you use push_bind to repopulate the builder args?

On Fri, Sep 11, 2020, 6:03 PM Austin Bonander [e-mail]
wrote:

Wouldn't that by virtue of the builder also clone the arguments?

Hmm, no, the arguments would be wrapped in Option internally and it would
probably panic if called more than once. Really though, the only reason to
take &mut self here is so that it can be called at the end of a method
chain.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/launchbadge/sqlx/issues/291#issuecomment-691331451,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/ABRMQUOK7SFG4UQF7UBXABLSFKNETANCNFSM4MUDDMWA
.

[elertan]

I wouldn't mind trying to implement a basic query builder similar to the one you had suggested @abonander . Should I give it a shot?

[abonander]

@nkconnor .push_bind() both pushes a bind parameter expression to the query string and also adds the bind value to the arguments, so it recreating the arguments doesn't make much sense. I'm thinking the query builder is going to be a one-and-done kinda thing. Although maybe instead of building the Arguments concurrently to the query string, we just have the user call .bind() on the Query returned by build(), so theoretically it could be used more than once, but I think that's going to be very error-prone.

@elertan please do! We unfortunately don't have much time to dedicate to SQLx these days but we're trying to get back on it.

[shiftrtech]

Hi, anyone can advise me about how to make a massive database ingestion (> 200k rows) with SQLX, there is a bulk operation builder or somethig like that?, thanks

[imbolc]

@shiftrtech from my experience wrapping bunch of separate inserts into a transaction gives roughly the same speed

[shiftrtech]

inserts

Can you give me some basic example?

[imbolc]

let mut tx = db.begin().await?;
for row in your_data {
    sqlx::query!("INSERT ...").execute(&mut tx).await?;
}
tx.commit().await?;