0

Ok, I have looked but didn't find anything that worked for me. If you can point me to anything or help my find the solution it would be great.

Let's say I have a "users" table (columns: id, username and name) and a "users_items" table (columns: id_item, id_user, both Foreign Keys)

What I want to do: I want to insert a new user and assign him a default item that everyone should have. For that, I want to create a transaction where everything or nothing is saved. So if (for any reason) I can't give him that item, I want the user creation to fail.

How I do it:

const pgp = require('pg-promise')(
    {
        capSQL: true // generate capitalized SQL 
    });

const db = pgp(configuration);

saveUser = (username, name) =>
    db.tx (t =>
        t.one('INSERT INTO users (username, name) VALUES $1, $2 RETURNING *', [username, name]).then(user =>
            t.none ('INSERT INTO users_items (id_user, id_item) VALUES $1, 1', [user.id]).then(()=>
                console.log('Everything\'s alright :)');
        )
    );

What I expect: everything runs perfectly and we are all happy :).

What actually happens: The first instruction is OK, and returns correctly the user with the ID. However, the second one tells me that the constraint fk_id_user is being violated, and perform a ROLLBACK.

The part of it not commiting the first insert is working properly. However, shouldn't the second insert work too? Am I understanding something extremely wrong or is pg-promise not working as expected? Or maybe I need to do something different.. Any help would be appreciated.

dquijada
  • 1,697
  • 3
  • 14
  • 19
  • `However, shouldn't the second insert work too?` - work how? It is violating the constraint, as the error tells you. So what were you expecting? – vitaly-t Apr 12 '19 at 11:59
  • @vitaly-t the second instruction shouldn't be executed after the first one? In that case, the constraint shouldn't be violated.. – dquijada Apr 15 '19 at 09:04
  • According to your own explanation, the first query works fine, gives you the Id, and the second one produces `constraint violation` error. So why the second instruction shouldn't be executed after the first one? – vitaly-t Apr 15 '19 at 13:05
  • @vitaly-t exactly what I'm asking. If the first one is creating the users entry, why the second one produces a `constraint violation` like it didn't – dquijada Apr 16 '19 at 13:45
  • The second one creates a record in a separate table, is where it runs into the problem. You did not provide any details about keys and restrictions there, so I do not know what's exactly causing the issue there. The issue you are having is with your data structure and integrity, and not with pg-promise. – vitaly-t Apr 16 '19 at 14:10

0 Answers0