25
\$\begingroup\$

I'm looking to add a thin layer of abstraction for database interaction in my application. I'm not really looking for a full blown ORM or advanced query builder.

I am familiar with Diesel, but its current limitations (for my specific use case) require almost as much effort to workaround as using Rust's database adapters directly.

What I have

My repository interface looks like:

pub trait Repository {            

    // I know I could probably add the `Sized` constraint to the trait directly
    // but wasn't sure if I wanted to do that yet
    fn find<T, Find>(conn: &mut T, id: u64) 
        -> ::err::Result<Find> where T: ::db::GenConn, Find: Findable<Self>, Self: Sized {
        let tup: Find::RowType = conn.prep_exec(Find::SQL, (id,))?
            .flat_map(|x| x)
            .flat_map(|x| ::mysql::from_row_opt(x))
            .next()
            .ok_or("No results")?;
        Ok(Find::from_row(tup))
    }

    fn insert<T, Ins>(conn: &mut T, obj: Ins) 
        -> ::err::Result<u64> where T: ::db::GenConn, Ins: Insertable<Self>, Self: Sized {
        let res = conn.prep_exec(Ins::SQL, obj.to_params())?;
        let id = res.last_insert_id();
        Ok(id)
    }

    fn update<T, Up>(conn: &mut T, obj: Up) 
        -> ::err::Result<()> where T: ::db::GenConn, Up: Updatable<Self>, Self: Sized {
        let res = conn.prep_exec(Up::SQL, obj.to_params())?;
        Ok(())
    }

    fn delete<T, Del>(conn: &mut T, obj: Del) 
        -> ::err::Result<()> where T: ::db::GenConn, Del::Deletable<Self>, Self: Sized {
        let res = conn.prep_exec(Del::SQL, obj.to_params())?;
        Ok(())
    }    
}

pub trait Insertable<R: Repository> {
    const SQL: &'static str;
    fn to_params(&self) -> ::mysql::Params;
}

pub trait Updatable<R: Repository> {
    const SQL: &'static str;
    fn to_params(&self) -> ::mysql::Params;
}

pub trait Findable<R: Repository> {
    const SQL: &'static str;
    type RowType: ::mysql::value::FromRow;
    fn from_row(row: Self::RowType) -> Self;
}

pub trait Deletable<R: Repository> {
    const SQL: &'static str;
    fn to_params(&self) -> ::mysql::Params;
}

Which I can implement like:

pub enum ClipRepository {}

impl Repository for ClipRepository {}

impl<'a> Insertable<ClipRepository> for &'a NewClip {
    const SQL: &'static str = 
        "INSERT INTO clips(slug, data, interview_id) VALUES (?, ?, ?)";
    fn to_params(&self) -> ::mysql::Params {
        (&self.slug, &self.data, &self.interview_id).into()
    }
}

impl<'a> Updatable<ClipRepository> for &'a Clip {
    const SQL: &'static str = 
        "UPDATE clips SET slug = ?, data = ?, interview_id = ? WHERE id = ?";
    fn to_params(&self) -> ::mysql::Params {
        (&self.slug, &self.data, &self.interview_id, &self.id).into()
    }
}

impl Findable<ClipRepository> for Clip {
    const SQL: &'static str = 
        "SELECT id, slug, data, interview_id FROM clips WHERE id = ? LIMIT 1";
    type RowType = (u64, String, clips::Data, u64);
    fn from_row(tup: Self::RowType) -> Self {
        Clip { id: tup.0, slug: tup.1, data: tup.2, interview_id: tup.3 }
    }
}

impl Deletable<ClipRepository> for Clip {
    const SQL: &'static str = 
        "DELETE FROM clips WHERE id = ?";
    fn to_params(&self) -> ::mysql::Params {
        (&self.id,).into()
    }
}

And which I can use like:

let mut conn = ::db::DB.get()?;
let clip: Clip = Repository::find(&mut *conn, id)?;

What I like about this

  • This is boilerplate, but a manageable amount, and it moves the SQL/database logic out of my data models
  • It keeps the query SQL and the ToValue/FromValue data together. Most of the Rust database adapters provide ways to look up row values by column name (e.g. row.get("id"), but these typically come with a lookup penalty (admittedly very small). This allows me to use the select order without too much danger of mixing up fields.

Questions

This seems like a useful abstraction, but I haven't seen anything like it in my (certainly limited) exposure to Rust code. Am I missing some major flaw? Or is it just that orphan rules make packaging this as a crate way less powerful/useful?

Are there better ways to abstract the repository pattern? I kind of felt like I was making up the signatures as I went along.

\$\endgroup\$
2
  • 3
    \$\begingroup\$ Repositories should deal with domain-model objects, not database entities. Domain-model objects can be aggregates of several database entities. For example, an Invoice in the context of a domain model, can actually be composed of two database entities, a Invoice "header" and several InvoiceLine entities. I'm researching a similar approach where so far, I have a Repository like you do, except it holds domain model objects, and then several DAO's that deal with persistence or more precisely, constitute the DAL (Data Access Layer) Service. \$\endgroup\$ Commented Sep 10, 2018 at 14:34
  • \$\begingroup\$ Main problem I can think of is that you have to define impl Insertable in same crate as entity Clip, also it's not possible to define different queries for different RDBMS \$\endgroup\$ Commented Jun 8, 2020 at 0:41

1 Answer 1

1
\$\begingroup\$

I implemented the Repository Pattern as follow. There is a lot of room for improvement, and I will be glad to improve it more.

use sqlx::{pool::PoolConnection, Postgres, Error};

use crate::models::application::model::Model;

#[async_trait::async_trait]
pub trait Repository<TEntity>
where
    TEntity: Model,
{
    /// get all entities
    async fn get_all(&self, connection: PoolConnection<Postgres>) -> Result<Vec<TEntity>, Error>;

    /// get a single entity by id
    async fn get_by_id(&self, connection: PoolConnection<Postgres>, id: &String) -> Result<TEntity, String>;

    /// add an entity to the database
    async fn add(&mut self, connection: PoolConnection<Postgres>, entity: &TEntity) -> Result<(), String>;

    /// update an entity
    async fn update(&mut self, connection: PoolConnection<Postgres>, entity: &TEntity) -> Result<(), String>;

    /// delete an entity by its id
    async fn delete(&mut self, connection: PoolConnection<Postgres>, id: &String) -> Result<(), String>;
}

Now Implementation

pub use sqlx::{
    pool::PoolConnection,
    postgres::{PgArguments, PgPoolOptions, PgRow},
    Arguments, Error, PgPool, Postgres, Row,
};

use crate::handlers::db_repository::Repository;
use crate::models::application::model::Model;
use crate::models::application::roles::users::User;

pub struct UserRepository { }

#[async_trait::async_trait]
impl Repository<User> for UserRepository {
    async fn get_all(&self, mut connection: PoolConnection<Postgres>) -> Result<Vec<User>, Error> {
        let result = sqlx::query("SELECT \"UserId\", \"Username\", \"DisplayName\", \"Language\", \"Password\", \"Salt\", \"StatusId\" FROM \"Role\".\"User\"")
            .map(|row: PgRow| User::from_row(&row))
            .fetch_all(&mut *connection)
            .await?;

        Ok(result)
    }

    async fn get_by_id(
        &self,
        mut connection: PoolConnection<Postgres>,
        id: &String,
    ) -> Result<User, String> {
        let mut args = PgArguments::default();
        args.add(id);

        let result = sqlx::query_with(
            "SELECT \"UserId\", \"Username\", \"DisplayName\", \"Language\", \"Password\", \"Salt\", \"StatusId\" FROM \"Role\".\"User\" WHERE \"UserId\"='$1'",
            args,
        )
        .map(|row: sqlx::postgres::PgRow| User::from_row(&row))
        .fetch_one(&mut *connection)
        .await;

        Ok(result.unwrap())
    }

    async fn add(
        &mut self,
        mut connection: PoolConnection<Postgres>,
        entity: &User,
    ) -> Result<(), String> {
        let mut args = PgArguments::default();
        args.add(entity.user_id.clone());
        args.add(entity.username.clone());
        args.add(entity.display_name.clone());
        args.add(entity.language.clone());
        args.add(entity.password.clone());
        args.add(entity.salt.clone());
        args.add(entity.status_id.clone());

        let result = sqlx::query_with("INSERT INTO \"User\" (\"UserId\", \"Username\", \"DisplayName\", \"Language\", \"Password\", \"Salt\", \"StatusId\") VALUES ($1, $2, $3, $4, $5, $6, $7);", args)
        .execute(&mut *connection)
        .await;

        match result {
            Ok(_) => Ok(()),
            Err(err) => Err(err.to_string()),
        }
    }

    async fn update(
        &mut self,
        mut connection: PoolConnection<Postgres>,
        entity: &User,
    ) -> Result<(), String> {
        let mut args = PgArguments::default();
        args.add(entity.user_id.clone());
        args.add(entity.username.clone());
        args.add(entity.display_name.clone());
        args.add(entity.language.clone());
        args.add(entity.password.clone());
        args.add(entity.salt.clone());
        args.add(entity.status_id.clone());

        let result = sqlx::query_with("UPDATE \"User\" SET \"Username\" = $2, \"DisplayName\" = $3, \"Language\" = $4, \"Password\" = $5, \"Salt\" = $6, \"StatusId\" = $7 WHERE \"UserId\" = $1;", args)
        .execute(&mut *connection)
        .await;

        match result {
            Ok(_) => Ok(()),
            Err(err) => Err(err.to_string()),
        }
    }

    async fn delete(
        &mut self,
        mut connection: PoolConnection<Postgres>,
        id: &String,
    ) -> Result<(), String> {
        let mut args = PgArguments::default();
        args.add(id);

        let result = sqlx::query_with("DELETE FROM \"User\" WHERE \"UserId\" = $1;", args)
            .execute(&mut *connection)
            .await;

        match result {
            Ok(_) => Ok(()),
            Err(err) => Err(err.to_string()),
        }
    }
}
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.