Introduction

Dynamic queries are a pretty common usecase, for example when filtering through records. Here are some workarounds which have been used so far:

1. checking for `NULL` first

SELECT * FROM table
   WHERE ($1::type IS NULL OR $1 = column)
with this, the WHERE clause is always present and can be checked during compile-time, but it only does something when $1 is Some. Obviously, this only works in very specific cases.

2. writing the query out multiple times

I've seen people write out each permutation of the query by hand. This preserves compile-time checking, but results in a lot of repetition. Personally, I've written declarative macros which factor out common parts, avoiding repetition. But that's not great to maintain, either.

3. ditching compile-time checking

The obvious solution is to just not use compile-time checking and go for the query functions instead of the macros. Apart from losing the compile-time checks, this requires adding a lot of FromRow implementations in a codebase which would otherwise not need them.

Proposal

I propose the addition of a new macro, which automates workaround 2.
The macro would accept a much richer syntax compared to query! or query_as!, allowing the use of if and match.
A call would then expand to query!/query_as! calls for every permutation of the query, allowing all of them to be checked against the database at compile-time.

I attempted to implement this before here [example] [implementation] using macro_rules!.
While it does work, it has some heavy limitations, which often came up in the #ormx channel on discord.

A new implementation I started working uses a procedural macro and doesn't suffer from these limitations.
Here is an example of how using it currently looks like. The exact syntax is of course up for debate.

conditional_query_as!(
    User,
    "SELECT *"
    "FROM users"
    "WHERE"
        "email =" ?query
        "OR first_name LIKE" ?query
        "OR last_name LIKE" ?query
    "ORDER BY"
    match order_by {
        OrderBy::Name => "first_name",
        OrderBy::UserId => "id"
    }
    match order {
        Order::Ascending => "ASC",
        Order::Descending => "DESC"
    }
    if let Some(limit) = limit {
        "LIMIT" ?limit
    }
).fetch_all(&db).await?;

This currently expands to this:

if let Some(limit) = limit {
    match order {
        Order::Ascending => match order_by {
            OrderBy::Name => ConditionalMap8::_1(sqlx::query_as!(User, "SELECT * FROM users WHERE email = $1 OR first_name LIKE $2 OR last_name LIKE $3 ORDER BY first_name ASC LIMIT $4", query, query, query, limit)),
            OrderBy::UserId => ConditionalMap8::_2(sqlx::query_as!(User, "SELECT * FROM users WHERE email = $1 OR first_name LIKE $2 OR last_name LIKE $3 ORDER BY id ASC LIMIT $4", query, query, query, limit)),
        },
        Order::Descending => match order_by {
            OrderBy::Name => ConditionalMap8::_3(sqlx::query_as!(User, "SELECT * FROM users WHERE email = $1 OR first_name LIKE $2 OR last_name LIKE $3 ORDER BY first_name DESC LIMIT $4", query, query, query, limit)),
            OrderBy::UserId => ConditionalMap8::_4(sqlx::query_as!(User, "SELECT * FROM users WHERE email = $1 OR first_name LIKE $2 OR last_name LIKE $3 ORDER BY id DESC LIMIT $4", query, query, query, limit)),
        },
    }
} else {
    match order {
        Order::Ascending => match order_by {
            OrderBy::Name => ConditionalMap8::_5(sqlx::query_as!(User, "SELECT * FROM users WHERE email = $1 OR first_name LIKE $2 OR last_name LIKE $3 ORDER BY first_name ASC", query, query, query)),
            OrderBy::UserId => ConditionalMap8::_6(sqlx::query_as!(User, "SELECT * FROM users WHERE email = $1 OR first_name LIKE $2 OR last_name LIKE $3 ORDER BY id ASC", query, query, query)),
        },
        Order::Descending => match order_by {
            OrderBy::Name => ConditionalMap8::_7(sqlx::query_as!(User, "SELECT * FROM users WHERE email = $1 OR first_name LIKE $2 OR last_name LIKE $3 ORDER BY first_name DESC", query, query, query)),
            OrderBy::UserId => ConditionalMap8::_8(sqlx::query_as!(User, "SELECT * FROM users WHERE email = $1 OR first_name LIKE $2 OR last_name LIKE $3 ORDER BY id DESC", query, query, query)),
        },
    }
}

Issues

With this approach, each generated call to query_as! must result in the same type.
Currently, this is solved by generating an enum, ConditionalMap8 in this case, which has 8 variants. Each of those variants wraps a sqlx::query::Map. Calling a function like fetch_all on ConditionalMap8 then does a match and redirects the call to the appropriate Map.

For something like conditional_query! (a conditional verison of query!), this approach is currently not possible since each call to query! returns a Map with an ad-hoc generated anonymous type. For this to work with query!, it would be necessarry that sqlx only ever generated one struct for a given set of columns such that the different calls to query! with slightly different queries would return Map with the same generated type.

@mehcode suggested a different approach - sqlx could just expose a macro to generate a struct for a given query. Then, conditional_query! could just be implemented as a wrapper around this macro and conditional_query_as!.

This is something that's been hotly requested for a long time. I'm interested, but I have a couple of comments:

If we can, I'd like to avoid introducing yet another series of macros and just work this into the existing ones.
To that end, I wonder if we can integrate this with #875:

sqlx::query_as!(
    User,
    r#"
        SELECT *
        FROM users
        WHERE 
            email = {query}
                OR first_name LIKE {query}
                OR last_name LIKE {query}
        ORDER BY
            { 
                 match order_by {
                     OrderBy::Name => "first_name",
                     OrderBy::UserId => "id",
                 }
            }
            {
                if let Some(limit) = limit {
                    "LIMIT {limit}" 
                }
            }
    "#
)

I think this way, we at least have a chance of retaining some syntax highlighting in IDEs like CLion as we can make {.*} get treated as a placeholder. It might not sophisticated enough to parse nested pairs of brackets though as I think it's just regex.

Syntax for branches and parameters

I think we need a slightly different syntax for branches (if/match).

ORDER BY
{ 
    match order_by {
        OrderBy::Name => "first_name",
        OrderBy::UserId => "id",
    }
}
This could either be interpreted as a branch or as just a &str argument for the query. Maybe !match .. and !if .. for branches, idk, but there needs to be some way of differentiating them.

Syntax highlighting

I'm not what the current state of this is, but with my current setup, clion does give me some rudimentary highlighting with the first approach, while none with the 2nd. I haven't spent too much time thinking about this, but I would think seperating SQL string literals and arguments would make syntax highlighting easier, not harder.
Having branches & arguments inline in the string definetely requires special treatment by the IDE, while seperate arguments of a macro could be highlighted correctly by just properly supporting macros.

Here is what the first approach currently feels like in CLion: first

And here is the 2nd: second

I think I'd prefer the 1st one, but maybe that's just because Im used to it.

How about something like

sqlx::query_as!(
    User,
    r#"
        SELECT *
        FROM users
        WHERE 
            email = {query}
                OR first_name LIKE {query}
                OR last_name LIKE {query}
        ORDER BY {#order_col} {#limit}
    "#,
    #order_col = match order_by {
        OrderBy::Name => "first_name",
        OrderBy::UserId => "id",
    }
    #limit = match limit {
        Some(limit) => "LIMIT {limit}",
        None => "",
    }
)

using a special sigil like # that's not valid in identifiers to denote that this isn't a regular bound parameter but rather an injection-prone part of the query string (and can't come from the surrounding scope like the inline parameters suggested in #875 will allow).

How about something like
sqlx::query_as!(
    User,
    r#"
        SELECT *
        FROM users
        WHERE 
            email = {query}
                OR first_name LIKE {query}
                OR last_name LIKE {query}
        ORDER BY {#order_col} {#limit}
    "#,
    #order_col = match order_by {
        OrderBy::Name => "first_name",
        OrderBy::UserId => "id",
    }
    #limit = match limit {
        Some(limit) => "LIMIT {limit}",
        None => "",
    }
)
using a special sigil like # that's not valid in identifiers to denote that this isn't a regular bound parameter but rather an injection-prone part of the query string (and can't just come from the surrounding scope like the inline parameters suggested in #875 will allow).

I like this a lot! Would also give nice syntax highlighting since not everything is in a big string literal

@jplatte Though I'm not sure how nested branches would look like, maybe something like

#order_col = if let Some((column, direction)) = order {
   "ORDER BY {#column} {#direction}",
    #direction = match direction {
        Order::Ascending => "ASC",
        Order::Descending => "DESC"
    },
    #column = match column {
        Column::Id => "id",
        ...
    }
}

EDIT: Initial idea doesn't work. Let me think more about it...

I'm now at the point where I'm trying to figure out how to integrate this into query_as!. Right now, the macro just calls query_as! itself, so it's basically just a wrapper around that.

My approach to this would just be to keep the old macro under a new name, and in calls to query_as!, figure out if old_query_as! or conditional_query_as! should be called.

This is obviously more like a hack than a proper solution. That being said, I'm not familiar with the rest of the sqlx codebase and not sure how to properly integrate it.
I'd really appreciate if someone could take a look at what I did and how to integrate it into query_as!.

EDIT: I have now made conditional_query_as! expand to expand_query! and (hopefully) fully backwards compatible to query_as!, which I replaced with the new proc macro. Let's see what the tests say.

1491 now passes all tests, so it's backwards-compatible as far as the tests can tell.

While there is still quite some work to do regarding documentation, you can get an idea of how this works here

inline arguments

This currently supports the "basic" inline arguments of #875: Everything within {..} will be interpreted as an argument. Every rust expression can be used here. Array parameters, as outlined in #875, are not included yet.

TODOs:

~~write more tests for the new functionality of the query_as! macro~~
do some benchmarking to determine how using the new functionality impacts compile times

Sorry for bumping, but are there any updates on this?

@TmLev it's been commented on by @abonander, suggesting this won't happen: https://github.com/launchbadge/sqlx/pull/1491#issuecomment-1184924225

Hey!

As this is no longer planned to be included in SQLx upstream we decided to implement it as a separate crate since we have a need for it. sqlx-conditional-queries (github).

I fully support this proposal!

When I use string constants or macros to extract the first half of the query string, the compiler tells me that query_as! is not supported, but if I use query_as, it cannot check SQL statement errors at compile time. No way, I would rather copy the query statement than not have the compile-time check function.

TOO LONG my code:


pub async fn prods(
    State(pool): State<MySqlPool>,
) -> Result<Json<Vec<Product>>, (StatusCode, String)> {
    let prods = sqlx::query_as!(
        FlatProduct,
        r#"
        SELECT 
            p.id,
            p.title,
            p.oem_zh,
            p.oem_en,
            p.model_zh,
            p.model_en,
            p.cover,
            p.images,
            c.slug AS category_slug,
            c.name_en AS category_name_en,
            c.name_zh As category_name_zh,
            GROUP_CONCAT(DISTINCT b.slug) AS brand_slugs,
            GROUP_CONCAT(DISTINCT b.name_en) AS brand_names_en,
            GROUP_CONCAT(DISTINCT b.name_zh) AS brand_names_zh
        FROM products AS p
        JOIN categories AS c ON p.category_id = c.id
        LEFT JOIN top_brand_product AS t ON t.product_id = p.id
        LEFT JOIN brands AS b ON b.id = t.brand_id
        WHERE p.pub = 1
        GROUP BY p.id
        ORDER BY p.gitle ASC
        "#
    )
    .map(organize)
    .fetch_all(&pool)
    .await
    .map_err(internal)?;

    Ok(Json(prods))
}

#[derive(Debug, Deserialize, Serialize)]
pub struct ProdsPageParams {
    page: u32,
}

pub async fn prods_page(
    params: Path<ProdsPageParams>,
    State(pool): State<MySqlPool>,
) -> Result<Json<Vec<Product>>, (StatusCode, String)> {
    let page = params.0.page;

    let offset = (page - 1) * PER_PAGE_COUNTS;

    let prods = sqlx::query_as!(
        FlatProduct,
        r#"
        SELECT 
            p.id,
            p.title,
            p.oem_zh,
            p.oem_en,
            p.model_zh,
            p.model_en,
            p.cover,
            p.images,
            c.slug AS category_slug,
            c.name_en AS category_name_en,
            c.name_zh As category_name_zh,
            GROUP_CONCAT(DISTINCT b.slug) AS brand_slugs,
            GROUP_CONCAT(DISTINCT b.name_en) AS brand_names_en,
            GROUP_CONCAT(DISTINCT b.name_zh) AS brand_names_zh
        FROM products AS p
        JOIN categories AS c ON p.category_id = c.id
        LEFT JOIN top_brand_product AS t ON t.product_id = p.id
        LEFT JOIN brands AS b ON b.id = t.brand_id
        WHERE p.pub = 1
        GROUP BY p.id
        ORDER BY p.gitle ASC
        LIMIT ? OFFSET ?
        "#,
        PER_PAGE_COUNTS,
        offset,
    )
    .map(organize)
    .fetch_all(&pool)
    .await
    .map_err(internal)?;

    Ok(Json(prods))
}

#[derive(Debug, Deserialize, Serialize)]
pub struct CatProdsParams {
    cat_slug: String,
}

pub async fn cat_prods(
    params: Path<CatProdsParams>,
    State(pool): State<MySqlPool>,
) -> Result<Json<Vec<Product>>, (StatusCode, String)> {
    let cat_slug = params.0.cat_slug;

    let prods = sqlx::query_as!(
        FlatProduct,
        r#"
        SELECT 
            p.id,
            p.title,
            p.oem_zh,
            p.oem_en,
            p.model_zh,
            p.model_en,
            p.cover,
            p.images,
            c.slug AS category_slug,
            c.name_en AS category_name_en,
            c.name_zh As category_name_zh,
            GROUP_CONCAT(DISTINCT b.slug) AS brand_slugs,
            GROUP_CONCAT(DISTINCT b.name_en) AS brand_names_en,
            GROUP_CONCAT(DISTINCT b.name_zh) AS brand_names_zh
        FROM products AS p
        JOIN categories AS c ON p.category_id = c.id
        LEFT JOIN top_brand_product AS t ON t.product_id = p.id
        LEFT JOIN brands AS b ON b.id = t.brand_id
        WHERE p.pub = 1
        AND c.slug = ?
        GROUP BY p.id
        ORDER BY p.gitle ASC
        "#,
        cat_slug,
    )
    .map(organize)
    .fetch_all(&pool)
    .await
    .map_err(internal)?;

    Ok(Json(prods))
}

#[derive(Debug, Deserialize, Serialize)]
pub struct CatProdsPageParams {
    cat_slug: String,
    page: u32,
}

pub async fn cat_prods_page(
    params: Path<CatProdsPageParams>,
    State(pool): State<MySqlPool>,
) -> Result<Json<Vec<Product>>, (StatusCode, String)> {
    let cat_slug = params.0.cat_slug;
    let page = params.0.page;

    let offset = (page - 1) * PER_PAGE_COUNTS;

    let prods = sqlx::query_as!(
        FlatProduct,
        r#"
        SELECT 
            p.id,
            p.title,
            p.oem_zh,
            p.oem_en,
            p.model_zh,
            p.model_en,
            p.cover,
            p.images,
            c.slug AS category_slug,
            c.name_en AS category_name_en,
            c.name_zh As category_name_zh,
            GROUP_CONCAT(DISTINCT b.slug) AS brand_slugs,
            GROUP_CONCAT(DISTINCT b.name_en) AS brand_names_en,
            GROUP_CONCAT(DISTINCT b.name_zh) AS brand_names_zh
        FROM products AS p
        JOIN categories AS c ON p.category_id = c.id
        LEFT JOIN top_brand_product AS t ON t.product_id = p.id
        LEFT JOIN brands AS b ON b.id = t.brand_id
        WHERE p.pub = 1
        AND c.slug = ?
        GROUP BY p.id
        ORDER BY p.gitle ASC
        LIMIT ? OFFSET ?
        "#,
        cat_slug,
        PER_PAGE_COUNTS,
        offset,
    )
    .map(organize)
    .fetch_all(&pool)
    .await
    .map_err(internal)?;

    Ok(Json(prods))
}

#[derive(Debug, Deserialize, Serialize)]
pub struct BrandProdsParams {
    brand_slug: String,
}

pub async fn brand_prods(
    params: Path<BrandProdsParams>,
    State(pool): State<MySqlPool>,
) -> Result<Json<Vec<Product>>, (StatusCode, String)> {
    let brand_slug = params.0.brand_slug;

    let prods = sqlx::query_as!(
        FlatProduct,
        r#"
        SELECT 
            p.id,
            p.title,
            p.oem_zh,
            p.oem_en,
            p.model_zh,
            p.model_en,
            p.cover,
            p.images,
            c.slug AS category_slug,
            c.name_en AS category_name_en,
            c.name_zh As category_name_zh,
            GROUP_CONCAT(DISTINCT b.slug) AS brand_slugs,
            GROUP_CONCAT(DISTINCT b.name_en) AS brand_names_en,
            GROUP_CONCAT(DISTINCT b.name_zh) AS brand_names_zh
        FROM products AS p
        JOIN categories AS c ON p.category_id = c.id
        LEFT JOIN top_brand_product AS t ON t.product_id = p.id
        LEFT JOIN brands AS b ON b.id = t.brand_id
        WHERE p.pub = 1
        AND b.slug = ?
        GROUP BY p.id
        ORDER BY p.gitle ASC
        "#,
        brand_slug,
    )
    .map(organize)
    .fetch_all(&pool)
    .await
    .map_err(internal)?;

    Ok(Json(prods))
}

#[derive(Debug, Deserialize, Serialize)]
pub struct BrandProdsPageParams {
    brand_slug: String,
    page: u32,
}

pub async fn brand_prods_page(
    params: Path<BrandProdsPageParams>,
    State(pool): State<MySqlPool>,
) -> Result<Json<Vec<Product>>, (StatusCode, String)> {
    let brand_slug = params.0.brand_slug;
    let page = params.0.page;

    let offset = (page - 1) * PER_PAGE_COUNTS;

    let prods = sqlx::query_as!(
        FlatProduct,
        r#"
        SELECT 
            p.id,
            p.title,
            p.oem_zh,
            p.oem_en,
            p.model_zh,
            p.model_en,
            p.cover,
            p.images,
            c.slug AS category_slug,
            c.name_en AS category_name_en,
            c.name_zh As category_name_zh,
            GROUP_CONCAT(DISTINCT b.slug) AS brand_slugs,
            GROUP_CONCAT(DISTINCT b.name_en) AS brand_names_en,
            GROUP_CONCAT(DISTINCT b.name_zh) AS brand_names_zh
        FROM products AS p
        JOIN categories AS c ON p.category_id = c.id
        LEFT JOIN top_brand_product AS t ON t.product_id = p.id
        LEFT JOIN brands AS b ON b.id = t.brand_id
        WHERE p.pub = 1
        AND b.slug = ?
        GROUP BY p.id
        ORDER BY p.gitle ASC
        LIMIT ? OFFSET ?
        "#,
        brand_slug,
        PER_PAGE_COUNTS,
        offset,
    )
    .map(organize)
    .fetch_all(&pool)
    .await
    .map_err(internal)?;

    Ok(Json(prods))
}

#[derive(Debug, Deserialize, Serialize)]
pub struct CatBrandProdsParams {
    cat_slug: String,
    brand_slug: String,
}

pub async fn cat_brand_prods(
    params: Path<CatBrandProdsParams>,
    State(pool): State<MySqlPool>,
) -> Result<Json<Vec<Product>>, (StatusCode, String)> {
    let cat_slug = params.0.cat_slug;
    let brand_slug = params.0.brand_slug;

    let prods = sqlx::query_as!(
        FlatProduct,
        r#"
        SELECT 
            p.id,
            p.title,
            p.oem_zh,
            p.oem_en,
            p.model_zh,
            p.model_en,
            p.cover,
            p.images,
            c.slug AS category_slug,
            c.name_en AS category_name_en,
            c.name_zh As category_name_zh,
            GROUP_CONCAT(DISTINCT b.slug) AS brand_slugs,
            GROUP_CONCAT(DISTINCT b.name_en) AS brand_names_en,
            GROUP_CONCAT(DISTINCT b.name_zh) AS brand_names_zh
        FROM products AS p
        JOIN categories AS c ON p.category_id = c.id
        LEFT JOIN top_brand_product AS t ON t.product_id = p.id
        LEFT JOIN brands AS b ON b.id = t.brand_id
        WHERE p.pub = 1
        AND c.slug = ?
        AND b.slug = ?
        GROUP BY p.id
        ORDER BY p.gitle ASC
        "#,
        cat_slug,
        brand_slug,
    )
    .map(organize)
    .fetch_all(&pool)
    .await
    .map_err(internal)?;

    Ok(Json(prods))
}

#[derive(Debug, Deserialize, Serialize)]
pub struct CatBrandProdsPageParams {
    cat_slug: String,
    brand_slug: String,
    page: u32,
}

pub async fn cat_brand_prods_page(
    params: Path<CatBrandProdsPageParams>,
    State(pool): State<MySqlPool>,
) -> Result<Json<Vec<Product>>, (StatusCode, String)> {
    let cat_slug = params.0.cat_slug;
    let brand_slug = params.0.brand_slug;
    let page = params.0.page;

    let offset = (page - 1) * PER_PAGE_COUNTS;

    let prods = sqlx::query_as!(
        FlatProduct,
        r#"
        SELECT 
            p.id,
            p.title,
            p.oem_zh,
            p.oem_en,
            p.model_zh,
            p.model_en,
            p.cover,
            p.images,
            c.slug AS category_slug,
            c.name_en AS category_name_en,
            c.name_zh As category_name_zh,
            GROUP_CONCAT(DISTINCT b.slug) AS brand_slugs,
            GROUP_CONCAT(DISTINCT b.name_en) AS brand_names_en,
            GROUP_CONCAT(DISTINCT b.name_zh) AS brand_names_zh
        FROM products AS p
        JOIN categories AS c ON p.category_id = c.id
        LEFT JOIN top_brand_product AS t ON t.product_id = p.id
        LEFT JOIN brands AS b ON b.id = t.brand_id
        WHERE p.pub = 1
        AND c.slug = ?
        AND b.slug = ?
        GROUP BY p.id
        ORDER BY p.gitle ASC
        LIMIT ? OFFSET ?
        "#,
        cat_slug,
        brand_slug,
        PER_PAGE_COUNTS,
        offset,
    )
    .map(organize)
    .fetch_all(&pool)
    .await
    .map_err(internal)?;

    Ok(Json(prods))
}

The argument against this seems to be the combinatorial explosion from checking all known values, which is reasonable. As an alternative, what about partially checked queries? That is, validate a portion of the query at compile time to get type information, but allow editing the query to fill in missing information.

Something like the following could be a possible API:

struct User { id: u32, name: String, birthday: NaiveDateTime }

/// Return users with a name matcher, born after a specific date,
/// ordering by any valid column
fn sort_filter(
    pool: &MySqlPool,
    name_like: &str,
    min_birthday: Option<NaiveDateTime>,
    order_col: Ordering<&str>
) -> sqlx::Result<Vec<Table>> {
    // Initialize the checked portion of the query
    let mut pq: PartialQuery = sqlx::query_as!(
        User,
        "SELECT id, name, birthday
        FROM t1
        WHERE name LIKE ?
        dynamic!("where_addition")
        dynamic!("order_by")
        LIMIT 100",
        name_like
    );

    // `q` is a reference to the query right before `where_clause`
    pq.set("where_addition", |q| {
        if let Some(bday) = min_birthday {
            q.push("AND birthday > ").push_bind(bday);
        }
    })?;

    // Make sure our query will be correct. Bonus if `PartialQuery` could pull
    // a static list of all table columns at compile time, to make this even easier.
    check_column_valid!(User, order_col)?;

    // Set the other dynamic placeholder
    pq.set("order_by", |q| {
        q.push("ORDER_BY");
        match order_col {
            Ordering::Asc(v) => q.push('`').push(v).push("` ").push("ASC");
            Ordering::Desc(v) => q.push('`').push(v).push("` ").push("DESC");
        }
    })?;

    pq.fetch_all(pool).await
}

So, what happens is:

query_as! sees that there are dynamic! segments (syntax like {} would work too). It will delete these placeholders before sending the query to the database.
Instead of returning a Result<Vec<User>> directly, it returns a type that contains: a. The Row -> User mapping function/closure b. The base query string and binds, with dynamic! segments removed c. Something with information about how to add string segments or binds. [(&str, usize, usize); 2] with
```
struct DynamicSegment {
    name: &'static str, // Name of the segment
    str_start: usize, // start position of this segment's string
    str_end: usize, // end position of this segment's string.`.insert_str` at this index on `push`
    bind_start: usize, // start position of this segment's binds
    bind_end: usize, // end position of this segment's binds. `.insert` at this index on `push_bind`.
}
```
sqlx::Arguments probably needs to gain an insert method to make this possible, or another trait added for dynamic arguments
fetch_all makes sure that all dynamic keys were .set (even if null). The statement actually gets prepared here.

The pattern would work well for cases where you don't want to change the columns, only apply refinement operations to the return dataset. This covers the main use cases - ORDER BY sorting, WHERE filtering, LIMIT / OFFSET traditional pagination, WHERE cursor pagination, GROUP BY aggregation.

The full dynamic query cannot be checked at compile time, but that is probably acceptable given the explosion problem mentioned above.

As this is no longer planned to be included in SQLx upstream we decided to implement it as a separate crate since we have a need for it. sqlx-conditional-queries (github).

This is excellent, thank you!

Two small points:

To piggyback on #1014, this would be very helpful to highlight in the readme. I know of several projects where teams have opted for an ORM to bridge the gap between static, compile-time safe queries vs. dynamic based on user input, where _sqlx_conditionalqueries would have been the obvious choice. It's through dumb luck I stumbled on this issue (and read through to the end!) -- so glad I did. This deserves more air time.
If this addresses the OP, can this issue now be closed?

launchbadge / sqlx

Add macro for checking dynamic queries at compile time #1488

Introduction

1. checking for `NULL` first

2. writing the query out multiple times

3. ditching compile-time checking

Proposal

Issues

Syntax for branches and parameters

Syntax highlighting

1491 now passes all tests, so it's backwards-compatible as far as the tests can tell.

inline arguments

launchbadge / sqlx

Add macro for checking dynamic queries at compile time #1488

Introduction

1. checking for NULL first

2. writing the query out multiple times

3. ditching compile-time checking

Proposal

Issues

Syntax for branches and parameters

Syntax highlighting

1491 now passes all tests, so it's backwards-compatible as far as the tests can tell.

inline arguments

1. checking for `NULL` first