Rustで使ったsql_query_builderを使いやすくする

目的

RustでSQLビルダーを使ってみたという記事でモデルクラスを作成することで、Typoを減らすことができました。

しかしWHERE句あたりで文字列リテラルが残るのが気になったのでヘルパーを書いて極力リテラルを書かなくても済むようにしてみました。

コード

sql_builder_helper.rs

pub enum Operator {
    Equal,
    Like,
    ILike,
    GreaterThan,
    LessThan,
    GreaterThanEqual,
    LessThanEqual,
}

impl Operator {
    pub fn as_str(&self) -> &'static str {
        match self {
            Operator::Equal => "=",
            Operator::Like => "LIKE",
            Operator::ILike => "ILIKE",
            Operator::GreaterThan => ">",
            Operator::LessThan => "<",
            Operator::GreaterThanEqual => ">=",
            Operator::LessThanEqual => "<=",
        }
    }

    pub fn where_clause(&self, field: &str, index: usize) -> String {
        format!("{} {} ${}", field, self.as_str(), index)
    }
}

pub const ASTERISK: &str = "*";

pub fn like_pattern(value: &str) -> String {
    format!("%{}%", value.replace("%", "\\%").replace("_", "\\_"))
}

pub struct WhereMaker {
    count: usize,
}

impl WhereMaker {
    pub fn new() -> Self {
        Self::new_with_count(1)
    }

    pub fn new_with_count(count: usize) -> Self {
        WhereMaker { count }
    }

    pub fn next_index(&mut self) -> usize {
        let current = self.count;
        self.count += 1;
        current
    }

    pub fn where_clause(&mut self, operator: &Operator, field: &str) -> String {
        operator.where_clause(field, self.next_index())
    }
}

main.rs

use postgresql::table::{companies::Companies, users::Users};
use sql_query_builder as sql;
use sqlx::postgres::PgPoolOptions;
pub mod sql_builder_helper;
use uuid::Uuid;

use sql_builder_helper::{Operator, ASTERISK, like_pattern, WhereMaker};

async fn get_company_by_name(pool: &sqlx::PgPool, name: &str) -> anyhow::Result<Companies> {
    let mut where_maker = WhereMaker::new();
    let query = sql::Select::new()
        .raw(Companies::SELECT_SQL)
        .where_clause(&where_maker.where_clause(&Operator::Like, Companies::FIELDS.company_name));
    let company: Companies = sqlx::query_as(&query.as_string())
        .bind(like_pattern(name))
        .fetch_one(pool)
        .await?;
    Ok(company)
}

async fn get_user_by_name(pool: &sqlx::PgPool, company_uuid: &Uuid, name: &str) -> anyhow::Result<Users> {
    let mut where_maker = WhereMaker::new();
    let query = sql::Select::new()
        .select(ASTERISK)
        .from(Users::TABLE_NAME)
        .where_clause(&where_maker.where_clause(&Operator::Equal, Users::FIELDS.company_uuid))
        .where_clause(&where_maker.where_clause(&Operator::Like, Users::FIELDS.user_name));

    let user: Users = sqlx::query_as(&query.as_string())
        .bind(company_uuid)
        .bind(like_pattern(name))
        .fetch_one(pool)
        .await?;
    Ok(user)
}

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    env_logger::init();

    let pool = PgPoolOptions::new()
        .max_connections(5)
        .connect("postgres://user:pass@postgresql/web")
        .await?;

    let company = get_company_by_name(&pool, "Sample").await?;
    let user = get_user_by_name(&pool, &company.uuid, "Sample").await?;

    println!("{:?}", user);
    Ok(())
}

まとめ

• Operatorを導入して絞り込み条件のenumから選択できるようにしました
• WhereMakerで順番を手で書く必要がなくなりました
• like_patternでLIKEのエスケープを考慮するようになりました