Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Prisma Many to Many relationship with at least one value

I use Prisma and Postgresql for a project to store and manage recipes.

I am struggling to implement a Many to Many relationship where I can constrain one side to require at least one value for the relation field.

Recipes can have one or more aromas. Aromas can be included in zero or more recipes.

I use an explicit relation table because I need to store additional data in it (the quantity for each aroma in a recipe).

The Recipe, Aroma and relation models are the following:

model Recipe {
  id          Int              @id @default(autoincrement())
  name        String
  base        String?          @default("50/50")
  description String?          @default("aucune description")
  rating      Int?             @default(1)
  aromas      RecipeToAromas[]
}

model Aroma {
  id      Int              @id @default(autoincrement())
  name    String
  brand   Brand?           @relation(fields: [brandId], references: [id])
  brandId Int?             @default(1)
  recipes RecipeToAromas[]

  @@unique([name, brandId], name: "aromaIdentifier")
}

model RecipeToAromas {
  id         Int    @id @default(autoincrement())
  recipeId   Int
  aromaId    Int
  quantityMl Int
  recipe     Recipe @relation(fields: [recipeId], references: [id])
  aroma      Aroma  @relation(fields: [aromaId], references: [id])
}

I want to constrain recipes to have at least one aroma.

By definition Many to Many defines zero to many relationship.

I thought about solving the problem with adding an additional One to Many relationship between Recipe and Aroma.

That would imply adding an additional aroma field in Recipe to store the one aroma that is required (and rename aromas field to additionalAromas to avoid confusion) :

model Recipe {
  id          Int              @id @default(autoincrement())
  name        String
  base        String?          @default("50/50")
  description String?          @default("aucune description")
  rating      Int?             @default(1)
  aromas      RecipeToAromas[]
  aroma       Aroma            @relation(fields: [aromaId], references: [id])
  aromaId     Int
}

And adding a recipe field in Aroma as it required to establish the relation :

model Aroma {
  id      Int              @id @default(autoincrement())
  name    String
  brand   Brand?           @relation(fields: [brandId], references: [id])
  brandId Int?             @default(1)
  recipes RecipeToAromas[]
  recipe  Recipe[]

  @@unique([name, brandId], name: "aromaIdentifier")
}

But that feels wrong as I will have duplicates : recipes and recipe fields in Aroma would store identical data.

** Edit **
I tried to solve the problem using this solution, it creats a second problem :
Each aroma in a recipe has to be unique in this recipe (this is reflected by the compound @unique in the relational database).

If I add the One to Many relationship between Recipe and Aroma, then an aroma can be stored more than once in a recipe :

await prisma.recipe.create({
    data: {
      name: "First recipe",
      aromaId: 1,
      aromas: {
        create: [
          { aromaId: 1, quantityMl: 2 },
          { aromaId: 2, quantityMl: 2 },
          { aromaId: 3, quantityMl: 2 },
        ],
      },
    },
  });

I could of course workaround the problem by just relying on validation in mutation functions and user input. And probably try to add a layer of safety with types as I am using typescript.
But I feel like it would make the database brittle and is prone to error especially if I have to collaborate with other devs, or even use the database in a different projet.

I could not find any resource covering a similar situation, and of course I have spend a lot of time searching and re-reading the documentation.

I am new to prisma (started yesterday) and I dont have too much experience with RDBMS, so it feels like I am missing something.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I finally found a clear answer : it has to be implemented using input check / validation.

    Check the answer provided by @Ianis that solves the problem by using Triggers (another concept I didnt know about SQL db ^^') and make the db more robust.

    I think my answer is still useful to beginners using Prisma like me to better understand Prisma and relations in SQL dbs.

    My misunderstanding actually came from my lack of knowledge about RDBMS :

    In a One-to-Many relationship, the information is actually stored only on one side. A Many-to-Many relationship is implemented by storing 2 One-to-Many relationships as foreign keys in a relation table.

    My case as an example

    There is a Many-To-Many relationship between recipes and aromas. Hence the Recipe table structure looks like this :

    Recipe Table

    You can notice there is no information about the relation.

    In the same way the Aromas table looks like this : enter image description here

    The relation information is stored in the relation table (aptly named ^^') as foreign keys : enter image description here

    So using Prisma, the only solution to make a recipe have at least one aroma (instead of zero or more) is to check / validate the input when creating a recipe entry.

    This is due to the fact that under the hood, when an aroma argument is specified in the call to the create function, Prisma actually makes a query to create a Recipe row AND to create a row in the relation table.

    I got confused because in the Prisma model the relationship appears on both side, which is not the case in the underlying database.

    model Recipe {
  id          Int              @id @default(autoincrement())
  name        String
  base        String?          @default("50/50")
  description String?          @default("aucune description")
  rating      Int?             @default(1)
  aromas      RecipeToAromas[] //the relation to the relation table appears here
}

model Aroma {
  id      Int              @id @default(autoincrement())
  name    String
  brand   Brand?           @relation(fields: [brandId], references: [id])
  brandId Int?             @default(1)
  recipes RecipeToAromas[] //and here

  @@unique([name, brandId], name: "aromaIdentifier")
}

    It is actually stated in Prisma documentation :

    Note The relation field does not "manifest" in the underlying database schema. On the other side of the relation, the annotated relation field and its relation scalar field represent the side of the relation that stores the foreign key in the underlying database.

    I think the conclusion is : if you are a beginner like me and decide to use an ORM, you have to make sure that you actually understand database structures and concepts (and ideally query language) that the ORM abstracts.


  2. 0

    So your problem can be solved in 2 ways (in raw SQL), one better than the other. Also I’m going to use PostgreSQL syntax as I’m not familiar with prisma but I’m sure this can be translated to the ORM model at best or inserted as raw SQL statements in postgres at worst

    Using Triggers

    Triggers are associated with specific tables and will be run when a specific action is done in that table, for our case we would want to run this after the Recipe gets a new element. The syntax would be as follows (also feel free to read the documentation here as this is quite a complex topic that can’t be summarized in a few sentences)

    CREATE OR REPLACE FUNCTION consistency_check_recipe() RETURNS TRIGGER AS $$
  BEGIN
    IF NOT EXISTS (SELECT * FROM RecipeToAromas WHERE recipeId = NEW.ID) THEN
      DELETE FROM Recipe WHERE Recipe.id = NEW.id;
      RAISE EXCEPTION 'Must have at least 1 Aroma';
    END IF;
    RETURN NULL; 
  END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER recipe_aroma_check 
AFTER INSERT OR UPDATE ON Recipe 
INITIALLY DEFERRED
FOR EACH ROW EXECUTE FUNCTION consistency_check_recipe();

    So to summarize what the above function/trigger does, once a new Recipe entry is created the trigger waits until the last moment of the transaction and then checks if there is a RecipeToAromas entry with the Recipe.id. This also means that if you wish to create a new Recipe you have to also add a aroma in the same transaction, for example this would work

    BEGIN;

INSERT INTO Recipe 
VALUES (1, 'Borsh', 'Tasty Water', 'Food Nothing more', 100);

INSERT INTO RecipeToAromas 
VALUES (DEFAULT, 1, 3, 4);

COMMIT;

    But this wouldn’t

    INSERT INTO Recipe 
VALUES (1, 'Borsh', 'Tasty Water', 'Food Nothing more', 100);

INSERT INTO RecipeToAromas 
VALUES (DEFAULT, 1, 3, 4);

    Since each of those statements are considered seperate transactions so the trigger will get called after Recipe gets an INSERT but before RecipeToAromas gets it’s INSERT

    Using Check constrains

    You can but shouldn’t use sub queries in check constrains.

    There is a lot more information to be added here and ill update my answer in a short while, but right now I’m out of time and I have to go somewhere.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search