General Advice: Relational database model in Rust?


I am writing a program which will largely be a front-end to a relational database (in this case, SQLite using the rusqlite crate). I'm wondering if anyone may have some advice for me as to how I can lay out my structs, methods, etc in a way that won't get super confusing super fast, will be easier to maintain in the long run, etc. Apologies for the open-ended nature of the question—I haven't had any formal education on databases yet so I'm learning as I go.

Here is what I have so far. My two basic objects are Recipes and Ingredients, which share a many-to-many relationship. A recipe will usually have multiple ingredients, and an ingredient may be used in more than one recipe. So, three tables: recipes, ingredients, and a third to relate the two:

Screen Shot 2021-04-09 at 2.36.44 PM

In the future, I would like to add some more objects which will themselves have relationships with recipe objects and ingredient objects, which is why I would like to keep the two separate.

I have started to create some structs to represent these. I thought to myself, there will be stages while the program is running where a user is creating a recipe/ingredient before it is saved to the database, and at this point it'd be convenient to have a representation of these entities which is explicitly not in the database. I called them UnregisteredRecipe and UnregisteredIngredient respectively, and their fields are pub to facilitate easy editing:

pub struct UnregisteredRecipe {
    // omit the ID, as this is provided by the
    // database upon registration
    pub title: Option<String>,
    pub description: Option<String>,
    /* ... */
    pub ingredients: Vec<UnregisteredIngredient>

with a similar definition for UnregisteredIngredient.

To represent the recipes and ingredients that already exist in the database, I use similar structs called RegisteredRecipe and RegisteredIngredient but with their fields private, providing getter methods to view the fields and setter methods (which must be called in conjunction with a reference to the Database struct to persist the change) to change the fields. Additionally instead of storing RegisteredIngredients in the registered recipe, I have a third struct RecipeIngredient to represent the row in the many-to-many table, which itself stores info such as the amount (as shown in the diagram).

Then, in my Database struct (which at the moment simply exists to hold the rusqlite::Connection for the life of the program), I wrote a method to register the recipe so that it'd be persisted in the database, which itself calls methods to match/create the recipe's ingredients in the database as needed:

pub fn register_recipe(&mut self, recipe: UnregisteredRecipe)
    -> rusqlite::Result<RegisteredRecipe> { /* ... */ }

All of this, as I was coming up with it, was truly making me feel like a database whiz. However, the more work I put into actually implementing all these struct impls, methods, and sub-helper methods, the more this feeling creeps on me that my whole approach is flawed. I don't know if this feeling is really substantiated, or if databases are simply difficult to do and the way I am feeling is natural. In either case, I figured I really could benefit from the advice of those more experienced in Rust programming (or really, any programming) for my situation. Is there a standard (...idiomatic?) approach for modelling database entities in Rust code, which takes advantage of the borrow checker and all the lovely things that make Rust great? Maybe there's a crate which does all this and I'm spinning my wheels for no reason? :stuck_out_tongue: Or, maybe as I said, this is all kinda to be expected when dealing with databases. This is why I am asking you.

Any and all advice, links to articles, pointers, or criticism would be highly, highly appreciated. This post is long, and so I'm very grateful even if you went to the trouble to read it at all. I thank you sincerely for your time. <3

1 Like

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.