skip to Main Content

The simplest example I can give, is a User that can create multiple Posts. A one-to-many relationship where multiple posts can be tied to a single user.

But what if I want the User to only be able to have a max of 10 Posts? Ideally there’d be some kind of query I can run when creating a new Post, and if the limit has been reached, to reject creating that Post (or possibly replace a Post).

I’m kind of stumped on this. And I’m not sure if there is a way I can model this to create the desired outcome.

Otherwise, the only real solution I see is to fetch all Posts for a User, and count them before trying to create a new Post. But that would require two calls to the db instead of one which is the problem I am trying to avoid.

2

Answers


  1. Have you considered a database trigger? Below example is taken from this StackExhange post:

    CREATE OR REPLACE FUNCTION check_number_of_row()
    RETURNS TRIGGER AS
    $body$
    BEGIN
        IF (SELECT count(*) FROM your_table) > 10
        THEN 
            RAISE EXCEPTION 'INSERT statement exceeding maximum number of rows for this table' 
        END IF;
    END;
    $body$
    LANGUAGE plpgsql;
    
    CREATE TRIGGER tr_check_number_of_row 
    BEFORE INSERT ON your_table
    FOR EACH ROW EXECUTE PROCEDURE check_number_of_row();
    

    Unfortunately triggers don’t seem to be supported in Prisma yet, so you will have to define it in SQL: https://github.com/prisma/prisma/discussions/2382

    Login or Signup to reply.
  2. You can achieve it with interactive transaction, here’s my example code:

    const createPost = async (post, userId) => {
      return prisma.$transaction(async (prisma) => {
        // 1. Count current total user posts
        const currentPostCount = await prisma.posts.count({
          where: {
            user_id: userId,
          },
        })
    
        // 2. Check if user can create posts
        if (currentPostCount >= 10) {
          throw new Error(`User ${userId} has reached maximum posts}`)
        }
    
        // TODO
        // 3. Create your posts here
        await prisma.posts.create({
          data: post
        })
      })
    }
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search