Any feedback for my sql params implementation?

Hey everyone,

I am building a json api and obviously want to be able to filter

I use sql_builder to build my query, but it does not have any way for sql parameters.

So I build that myself, I don't like it very much tho

for example, I needed to put the id before the if statement because of lifetime issues.
Is there a better way to do that?
in

let id: Ulid;
if filters.id.is_some() {
    id = filters.id.unwrap();
    sql_builder.and_where(format!("id = {}", params.add(&id)));
}

Any other recommendations?

pub struct SqlParams<'a> {
    values: Vec<&'a (dyn ToSql + Sync)>,
    counter: usize,
}

impl<'a> SqlParams<'a> {
    pub fn new() -> Self {
        SqlParams {
            values: vec![],
            counter: 0,
        }
    }
    pub fn add(&mut self, param: &'a (dyn ToSql + Sync)) -> String {
        self.values.push(param);

        self.counter = self.counter + 1;
        format!("${}", self.counter)
    }

    pub fn values(&self) -> &Vec<&(dyn ToSql + Sync)> {
        &self.values
    }
}
use deadpool_postgres::Client;
use postgres_types::ToSql;
use sql_builder::SqlBuilder;
use tokio_pg_mapper::FromTokioPostgresRow;
use ulid::Ulid;

#[tracing::instrument(skip(client))]
pub async fn find_items(
    client: Client,
    filters: Filters,
) -> Result<Vec<Item>, DbError> {
    let mut params = SqlParams::new();
    let mut sql_builder = SqlBuilder::select_from("items");
    sql_builder.field("*");

    let id: Ulid;
    if filters.id.is_some() {
        id = filters.id.unwrap();
        sql_builder.and_where(format!("id = {}", params.add(&id)));
    }

    let sql = sql_builder.sql().map_err(DbError::SqlError)?;

    let stmt = client.prepare(&sql).await.map_err(|e| {
        tracing::error!(error = ?e, sql=sql, "Failed to prepare statement");
        DbError::Error(e)
    })?;

    let result = client
        .query(&stmt, &params.values())
        .await
        .expect("could not execute stmt")
        .iter()
        .map(|r| Item::from_row_ref(r).map_err(DbError::ConversionFailed))
        .collect::<Result<Vec<Item>, DbError>>();
    result
}

Thanks for the help!

Hmm, IDK Filter etc., but maybe:

if let Some(id) = filter.id.as_ref() {
    sql_builder.and_where(format!("id = {}", params.add(id)));
}

If your posted code is complete, you can omit counter in SqlParams:

impl<'a> SqlParams<'a> {
    pub fn add(&mut self, param: &'a (dyn ToSql + Sync)) -> String {
        self.values.push(param);
        format!("${}", self.values.len())
    }
}
1 Like

thanks a lot!

I didn't know you could do Option<T>.as_ref() but that solves my lifetime issue.

now the only thing that bothers me is the expect() but that's easy :slight_smile: