https://mattrighetti.com/2025/01/20/you-dont-need-sql-builders

 
About Projects Resume Series

You Probably Don't Need Query Builders

Jan 20, 2025
Discuss on HN

In a previous [0] post I've discussed why I ditched sea-query and why
sql is almost always the best way to not re-learn something new from
the beginning that will inevitably end up slowing you down or simply
not working at all in the long run.

From time to time, I still stumble upon stackoverflow questions like
this one [1].

OP is basically asking for a way to dynamically build a query based
on a search filter that is declared like the following

struct Search {
    id: i64,
    username: Option<String>,
    min_age: Option<i8>,
    max_age: Option<i8>,
}

You may agree that this is a very common scenario in every single API
that's out there. You have an endpoint that returns some items and
you want to expose a way to filter those items so that the client can
ask only for things it's interested in.

What's even more interesting is that all the answers to OP involve
some kind of complex way to build the query and if and else that, at
least in that case, are most likely unnecessary.

One of the answers suggests the following implementation

fn search_query(search: Search) -> String {
    let mut query = QueryBuilder::new("SELECT * from users where id = ");
    query.push_bind(search.id);

    if let Some(username) = search.username {
        query.push(" AND username = ");
        query.push_bind(username);
    }

    if let Some(min_age) = search.min_age {
        query.push(" AND age > ");
        query.push_bind(min_age);
    }

    if let Some(max_age) = search.max_age {
        query.push(" AND age < ");
        query.push_bind(max_age);
    }

    query.build().sql().into()
}

The above solution works just fine, sure - the fact that the field id
is always present in the Search filter simplifies stuff a bit. What
if id was an Option<i64>? If you want to keep using the query builder
you'd need to introduce another if statement checking if the id is
present or not

fn search_query(search: Search) -> String {
    let mut query = QueryBuilder::new("SELECT * from users");

    if let Some(id) = search.id {
        query.push(" AND id = ");
        query.push_bind(search.id);
    }

    if let Some(username) = search.username {
        query.push(" AND username = ");
        query.push_bind(username);
    }

    if let Some(min_age) = search.min_age {
        query.push(" AND age > ");
        query.push_bind(min_age);
    }

    if let Some(max_age) = search.max_age {
        query.push(" AND age < ");
        query.push_bind(max_age);
    }

    query.build().sql().into()
}

Mmmh, the query seems to be broken now!

If each field is None the query will work just fine, if one of them
is at least Some(_) the query won't work because that will translate
to SELECT * from users AND username = 'testname'. A quick fix would
be adding a WHERE 1 = 1 clause at the beginning of the query

fn search_query(search: Search) -> String {
    let mut query = QueryBuilder::new("SELECT * from users WHERE 1 = 1");

    if let Some(id) = search.id {
        query.push(" AND id = ");
        query.push_bind(search.id);
    }

    if let Some(username) = search.username {
        query.push(" AND username = ");
        query.push_bind(username);
    }

    if let Some(min_age) = search.min_age {
        query.push(" AND age > ");
        query.push_bind(min_age);
    }

    if let Some(max_age) = search.max_age {
        query.push(" AND age < ");
        query.push_bind(max_age);
    }

    query.build().sql().into()
}

Ahhh it's working again now!

Yep, that was an easy fix, but we can do even better

The sqlx crate is capable of handling Option<T> types easily: if the
value is Some(_) that will be used in the binding, otherwise sqlx
will bind NULL for None values. With that in mind we can use SQL to
only apply those WHERE clauses if the value IS NOT NULL.

The function above becomes

async fn search_query(search: Search) -> String {
    let query = r"
    SELECT * from users
    WHERE id = $1
        AND ($2 IS NULL OR username = $2)
        AND ($3 IS NULL or age > $3)
        AND ($4 IS NULL or age < $4)
    ".into()
}

Note that you need to use check for IS NULL to avoid running the
filtering when the value is None

This approach does not let you push bindings one by one as the
previous method, but you don't actually need it here, you can bind
values all at once later. Let's query the data directly

async fn search_query(search: Search, pg: &PgPool) {
    let query = sqlx::query(r"
    SELECT * FROM users
    WHERE id = $1
        AND ($2 IS NULL OR username = $2)
        AND ($3 IS NULL OR age > $3)
        AND ($4 IS NULL OR age < $4)
    ")
    .bind(search.id)
    .bind(search.username)
    .bind(search.min_age)
    .bind(search.max_age)
    .fetch_all(pg)
    .await
    .expect("failed querying users");
}

This is what I usually prefer, it looks nicer and I don't have to
write more Rust logic that I'd have to test later. The dynamically
built query shown before can end up being 16 different queries, on
the other hand you only have one query if you exclusively use sql.

Another reason why I prefer to do queries this way is that I can copy
and paste the statement in Datagrip and test it directly in the
database, mimicking what sqlx will end up doing.

In the following examples I am using Postgres syntax, it may be
different for other engines like Sqlite or MySql

By now you should have a better idea of how you can work with sql to
reduce the Rust logic that's involved in your queries, but I'd like
to give some other common examples and functions you can work with.

A common type that I encounter pretty frequently is a Vec<T>. Most of
the times I do not want to filter at all if vec.is_empty(). To make
this a bit more complicated let's consider the scenario where I have
an Option<Vec<T>> and I only want to apply the filter if !
vec.is_empty().

async fn filter(nicknames: Option<Vec<i64>>, pg: &PgPool) {
    sqlx::query(
        r"SELECT *
        FROM users
        WHERE 1 = 1
            AND ($1 IS NULL
                 OR CARDINALITY($1::integer[]) = 0
                 OR nickname = $1
            )"
    )
    .bind(nicknames)
    .fetch_all(pg)
    .await
    .unwrap();
}

Let's break it down:

  * $1 IS NULL is satisfied if nicknames.is_none() and won't apply
    the filter

  * CARDINALITY($1::integer[]) stops the filtering if
    nickname.is_some() && nickname.unwrap().len() = 0

  * Finally, if the vector is not None and its length is greater than
    one then nickname = $1 will filter all the users that have
    nickname as nickname

Let's move on to another similar scenario, this time you have a
vector represented as string with comma separated values: you may
have an endpoint that accepts a query parameter with multiple values
separated by a comma (e.g ?ids=11,22,33,44). My naive-self in the
past used to create a fancy custom deserializer function that
transformed 11,22,33,44 from a String into a Vec<i64> and that is
useless work that could have easily been handled by the database.

async fn filter(ids: String, pg: &PgPool) {
    sqlx::query(
        r"SELECT *
        FROM users
        WHERE
            id IN (ARRAY_REMOVE(STRING_TO_ARRAY($1, ','), ''))"
    )
    .bind(ids)
    .fetch_all(pg)
    .await
    .unwrap();
}

The syntax above works for Postgres but may be different for other
database servers. I'm sure such a basic feature is available on most
of them and you can use it.

(ARRAY_REMOVE(STRING_TO_ARRAY($1, ','), '') creates an array of ids
by splitting comma separated values and also removes empty values in
case someone decides to mess with your backend and tries to pass ?ids
=11,,,.

The next feature I'd like to explore is probably the de-facto API
must-have: pagination. Pagination basically lets your client say
"give me page 2 with a maximum of 10 items in it". You can model that
filter with the following struct

struct Filter {
    /// current page
    pub page: Option<i64>,
    /// number of items per page
    pub limit_per_page: Option<i64>
}

I've marked them as Option<i64> because clients are rude and want you
to do the guessing. Most of them won't ask for a specific page but
they really want the fist one. Also, if the client is lazy and does
not provide a limit_per_page you should fallback to a good default
value to not overload your Postgres instance.

Pagination is tricky because a user could pass a ?page=-100 and
negative pages do not exist. You could have avoided that by using an
Option<u64> but then you'd have to cast that value to an i64 whenever
you want to bind that to a query (in Postgres at least). To make
things worse, an hacker could request a limit_per_page=1000000000 to
make your server crash and wake you up while you're sleeping because
your app is dead.

My less sql-centric self would have implemented a filter validation
to check for its correctness

impl Filter {
    fn validate(&self) -> Result<(), String> {
        match self.page {
            Some(page) if page < 0 => {
                return Err("page can't be negative")
            }
            _ => {}
        }

        match self.limit_per_page {
            Some(limit) if limit < 10 && limit > 100 {
                return Err("limit must be between 10 and 100")
            }
            _ => {}
        }

        Ok(())
    }
}

But guess what...  SQL has the solution for you

async fn filter(filter: Filter, pg: &PgPool) {
    sqlx::query(
    r"SELECT *
    FROM users
    LIMIT
        CASE WHEN $2 > 0 THEN $2 END
    OFFSET
        CASE
            WHEN $2 BETWEEN 0 AND 100 AND $1 > 0
                THEN (($1 - 1) * $2)
            ELSE
                50
        END"
    )
    // if page is not provided, fallback to 0
    .bind(filter.page.unwrap_or(0))
    // if limit is not provided, fallback to 50
    .bind(filter.limit_per_page.unwrap_or(50))
    .fetch_all(pg)
    .await
    .unwrap();
}

The LIMIT statement is only applied if the passed value is greater
than zero, on the other hand the OFFSET is applied only if the passed
limit_per_page is between zero and a hundred and the page is not
negative, all other cases are defaulted to OFFSET 50.

Lastly, I'd like to cover an UPDATE statement which I feel is also
quite common in APIs out there. Let's say we have an UpdateForm
struct that models our HTML form.

struct UpdateForm {
    id: Uuid
    name: Option<String>
    surname: Option<String>
}

This form takes the user unique id and, optionally, its name and
surname. I want to provide a way for my client to only update the
value that it passes to my backend with a Some(_) value. Seems like
we can do just that with the use of COALESCE

async fn update_user(UpdateForm { id, name, surname }: UpdateForm, pg: &PgPool) {
    sqlx::query(
        r"UPDATE user
        SET name = COALESCE($2, name),
            surname = COALESCE($3, surname)
        WHERE id = $1"
    )
    .bind(id)
    .bind(name)
    .bind(surname)
    .fetch_one(pg)
    .await
    .unwrap();
}

The query above uses COALESCE to only update those values that are
.is_some(), the others will remain the same and no update will take
place.

Neat! be mindful, `COALESCE` will return the first non-null value in
its statement, so order matters!

As you can see we've got a lot done by employing sql alone, you can
get smart with it and avoid writing over-complicated Rust logic in
your queries, isn't that a neater and better solution overall?

 RSS mattrighetti