Repository pattern in Rust

Question

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.

Answer 1

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()),
        }
    }
}