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.

• Paul-Sebastian Manole: 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.
• Bohdan Mart: 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

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