Node js SQL Transactions REST

Question

I have implemented a REST API with express.js. I use it to connect to my database. The database has to tables. One is the table Person and the other is called Pet.

app.post('/persons', (req, res, next) => {

let firstname = req.body.firstname;
let lastname = req.body.lastname;
let petname = req.body.petname;

if (!firstname) {
    return res.status(400).send({ error: true, message: 'Please provide first name' });
}else if (!lastname) {
    return res.status(400).send({ error: true, message: 'Please provide last name' });
}else if (!petname) {
    return res.status(400).send({ error: true, message: 'Please provide pet name' });
}

When i call this post method i want to check if a certain petname already exists in the database. If so, then get the petID and insert it with the first name and the last name in the table Person, so that a person is linked with a pet. If this petname does not exist, then create a new pet with this name and a new id in the table Pet. Then again save the id and the first name and last name to the table Person. So every petname should only exist once.

I know how to write the Person into the database:

Conn.query("INSERT INTO Person SET ? ", { FirstName: firstname, LastName: lastname, PetID: petid }, function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'New person has been created successfully.' });
    });

But now i still need the petID, if available, and if not create a new pet in the DB and return the id. How do i do that?

score 0 · Answer 1 · answered Nov 26 '19 at 10:52

I think checking before creating transaction is your option.

If pet exists you will get it's id, and if not - create new pet and also get the id.

I wrote a small sql script for psql:

WITH pet AS (
WITH new_row AS (
INSERT INTO Pets (id)
SELECT '{id}' WHERE NOT EXISTS (SELECT * FROM Pets WHERE id = '{id}')
RETURNING *
)

SELECT * FROM new_row
UNION
SELECT * FROM Pets WHERE id = '{id}' LIMIT 1
RETURNING *
)

INSERT INTO Person SET pet_id=pet.id;

SOURCE FROM RELATED QUESTION

Next step is creating transaction for person. Now you already have your pet id.

score 0 · Answer 2 · answered Nov 26 '19 at 11:53

So i tried this but it does not work.

Conn.beginTransaction(function(error){
    if (error) throw error;
    Conn.query('Select PetID FROM Pet WHERE Petname = ' + mysql.escape(petname), function (error, results, fields){
        if (error) { 
            connection.rollback(function() {
                throw error;
            });
        }
        else {
            if (!results) {
                Conn.query('INSERT INTO Pet SET ? ', { Petname: petname }, function (error, results, fields){
                    if (error) { 
                        connection.rollback(function() {
                          throw err;
                        });
                    }
                    return res.send({ error: false, data: results, message: 'New pet has been created successfully.' });
                });
            }
            let petid = results.insertId;

            Conn.query('INSERT INTO Person SET ? ', { FirstName: firstname, LastName: lastname, PetID: petid}, function (error, results, fields){
                if (error) { 
                    connection.rollback(function() {
                      throw error;
                    });
                  }
                Conn.commit;  
                return res.send({ error: false, data: results, message: 'New person has been created successfully.' });    
            });
        }
    });
});

What is wrong with this transaction?

Node js SQL Transactions REST

2 Answers2