Integrating sqlx::FromRow

help
1

I am currently building a web framework and CMS using sqlx and sea_query to handle database stuff. I want to provide a simple API (as little boilerplate code as possible) for items that can be stored in the database.

I already have implemented a DatabaseSql trait with methods that return the appropriate sea_query::query::*Statements and a DatabaseItem trait to automatically provide methods for database operations for types that implement DatabaseSql + some trait for constructing that type from database rows.

sqlx provides such a trait (sqlx::FromRow), however there are some issues I have with it:

  • Ideally I'd like to provide the option to not deal with sqlx directly
  • It's a generic trait that doesn't ensure implementation for every database backend enabled
  • The error type is restricted to sqlx:Error
  • There's a proc macro however it can't handle more complex types (requiring writing weird boilerplate code) and i prefer to avoid them when it's not obvious what they do internally

I've tried various ways of abstracting over it to no avail, sqlx::Row isn't dyn compatible among other things

Ultimately this is sort of what i want the API to look like (i might even combine these into one trait):

use webcrust::{
	assets::{Asset, AssetInstance},
	database::{Database, DatabaseItem, DatabaseError}
};

#[derive(Clone)]
struct Cat {
	name: String,
	meows: u16
}

impl Cat {
	fn meow(&mut self) {
		println!("meow!");
		self.meows += 1;
	}
}

// allows it to be used in AssetInstance, will include more things later on
impl Asset for Cat {}

impl CustomFromRow for Cat {
	fn db_from_row(row: CustomRow) -> Result<Self, DatabaseError> {
		Ok(Self {
			name: row.get("name")?,
			meows: row.get("meows")?
		})
	}
}

// Assuming all of the above are implemented, some other traits
// get automatically implemented which the methods in AssetInstance
// rely on:

let db: Database = /* ... */;
let user: User = /* ... */;
let group: Group = /* ... */;

let mut cat = AssetInstance::<Cat>::create(
	db.clone(),
	Cat {
		name: String::from("Beau"),
		meows: 21
	}
	&user,
	&group
)?;

cat.edit(|cat| { cat.meows += 21; });

cat.save(db)?;

(even this is a lot of boilerplate stuff, i plan on having at least a few core Asset types, plus User and Group and whatever else goes into the CMS along with whatever plugins)

Is this just generally a bad approach? What should I do instead?

  • Is there a way to 'wrap' FromRow to provide the correct error type?
  • Is there a different database crate that is easier to work with?
    • I would like to support mysql, sqlite and postgres
    • Or should I just support one database?
  • Should I write my own proc macros?
Publishing crates without GitHub? (and some criticism)
2

Where does CustomFromRow come from?

This looks wrong:

	fn db_from_row(row: CustomRow) -> Result<Self, DatabaseError> {
		Self {
			name: row.get("name")?,
			meows: row.get("meows")?
		}
	}

I think it should read

	fn db_from_row(row: CustomRow) -> Result<Self, DatabaseError> {
		Ok(Self {
			name: row.get("name")?,
			meows: row.get("meows")?
		})
	}

I don't understand your question about a presumably correct error type. What's wrong about the current error types?

Regarding other frameworks. What about diesel?

3

Oops, forgot the Ok()! I guess it wasn't clear, the CustomFromRow trait and CustomRow types do not exist—I have not been able to do so successfully.

My idea was to use it in place of sqlx::FromRow which is less ergonomic (at least without using macros), and since the error type is restricted would not allow for using validation methods that return other error types. An implementation for Cat would look like:

impl<R: sqlx::Row> sqlx::FromRow<'_, R> for Cat {
	fn from_row(row: &R) -> sqlx::Result<Self> {
		Ok(Self {
			name: row.try_get("name")?,
			meows: row.try_get("meows")?
		})
	}
}

Regarding diesel I did consider it however it is more complicated/over-featured, less transparent and I'm not sure how it would work in the context of a library.

4

Given that Diesel came up here let me at least for other summarize how it could or could not address the points raised in the OP.

Diesel allows you to build query results as list of tuples, so you could load it into a tuple and then map the tuple to your struct. Which would only leave your actual database code with the need to deal with diesel.

(In fact that's what diesels #[derive(Queryable)] internally does. It gets the result as tuple and maps it to your struct. See the documentation

Diesel still requires this knownledge for actually loading the data into a tuple as the set of supported types differs between database backends. As written before you can just move the loading into tuples part in your actual database code where you already know the backend type so it shouldn't be a problem.

The relevant diesel method returns a Result<T, Box<dyn Error>> so you can return whatever error you want.

Manual mapping or some "boilerplate" code to define the mapping once is still required for diesel. The "boilerplate" code would essentially be implementing FromSql + deriving FromSqlRow. The former can be done in terms of existing type mappings so depending on your usecase it might be something like 2 or 3 lines of code or 100, which depends on how complex the actual mapping from data to type is.

As a more general note: For multibackend applications diesel also has a #[derive(MutliConnection)] which essentially takes care of all this mapping stuff (and many more details) for different backends at once.

2 Likes
5

As of this time I am having my own go at building a database library, however should that turn out to be a complete disaster I will investigate this as a potential solution