Insert a null array of objects in postgres node

Question

I have an angular app and the users can have multiple roles.

In the client when I remove or add a role to the user I send it to nodejs as an array of objects.

"roles": [
        {
            "role_id": 1,
            "role_name": "Admin"
        },
        {
            "role_id": 2,
            "role_name": "Moderator"
        }
    ],

my query :

for (i in roles) {
    db.query(`
    INSERT INTO user_roles (role_id, user_id)
    VALUES($1, $2) 
    ON CONFLICT (role_id, user_id) 
    DO UPDATE
    SET 
    role_id = $1,
    user_id = $2`, 
    [roles[i].role_id, user_id], (err, data) => {
        if (err) {
            console.log(err)
        } else {
            console.log('inserted/deleted role')
        }
    })
}

Now, there are two issue:

1- when a user is created, they start with no roles and a null array of objects is being passed roles: [ null ] which will break the query because role.role_id is null.

2- If I want to remove a role, I am passing the below, but how do I add in the query that I want to remove the roles that don't exist in the array of objects?:

"roles": [
        {
            "role_id": 1,
            "role_name": "Admin"
        },
          //the role moderator is removed from client so it's not being passed

    ],

Pass an empty array instead of an array containing a NULL value. They are not the same thing. — Gordon Linoff
– Gordon Linoff, Commented Nov 2, 2020 at 14:03

Mike Organek · Accepted Answer · 2020-11-03 09:27:49Z

1

I see you went with the many-to-many approach :-)

Just as PostgreSQL can make your query results into json for you, it can work with json you pass into the database. Again, I would ask the database to take care of this for you.

Instead of looping in your javascript, try something like this. It's a little verbose, but it should be easier to follow once you understand the individual parts.

db.query(`
    WITH invars AS ( -- pass your parameter values only once
      SELECT $2::INT as user_id, $1::JSONB as jdata
    ), newroles as ( -- expand to all valid role_id values, if none, then role_id will be null
      SELECT user_id, (el->>'role_id')::INT as role_id
        FROM invars
             CROSS JOIN LATERAL JSONB_ARRAY_ELEMENTS(jdata) AS j(el)
    ), do_delete AS ( -- delete any existing user_roles that should not exist
      DELETE FROM user_roles
       USING invars
       WHERE invars.user_id = user_roles.user_id
         AND NOT EXISTS (SELECT 1 
                           FROM newroles
                          WHERE role_id = user_roles.role_id)
    ) -- finally insert user_roles that do not exist
    INSERT INTO user_roles (role_id, user_id)
    SELECT role_id, user_id 
      FROM newroles n
     WHERE role_id IS NOT NULL 
       AND NOT EXISTS (SELECT 1 
                         FROM user_roles
                        WHERE (role_id, user_id) = (n.role_id, n.user_id))`, 
    [JSON.stringify(roles), user_id], (err, data) => {
        if (err) {
            console.log(err)
        } else {
            console.log('inserted/deleted role')
        }
    })

edited Nov 3, 2020 at 9:27

answered Nov 2, 2020 at 15:59

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MG91 Over a year ago

Oh hey Mike! you caught me :-). Yes, I actually thought about what you said about flexibility and went with your way. Thanks I think it works now but I have added: AND role_id IS NOT NULL. Because I am getting a null value from Angular at index 0 and that caused some issue (not null constraint). I really appreciate your help, Mike :-)

Mike Organek Over a year ago

@MG91 Thank you for catching my error. I have updated my answer. FYI: You can make edits to other people's answers. The author is notified and given the option to accept the edit. Good luck with your project!

Collectives™ on Stack Overflow

Insert a null array of objects in postgres node

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related