wuno wuno - 14 days ago 6
Node.js Question

Handling Postgres Error Message To Perform Correct Query

I am using

pg
and
node.js
. When a user logs in using the
auth0
widget I am passing the email it returns and checking my database to see if the user exist. If the user does not exist I am inserting them into the database. I have managed to get this working with a hack job of a function but I would appreciate some help ironing it out.

Problems I am facing.


  1. When checking the database for the email it is not checking it for the whole email address.

    var emailCheck = "SELECT id from public.user WHERE email=" + req.body.email;


    req.body.email;
    is actually
    myemail@example.com



Returns this error,

column "myemail" does not exist


Even though

myemail@example.com


does exist.


  1. regardless of the error it throws it moves on to insert the email address. If it does not exist it inserts it. Since there is a Unique Key on the email it throws an error

    duplicate key value violates unique constraint "uk_user_email"



So to fix this my question is why is it not checking for after the @ sign? And what logic should I follow to change this function to run the first query and only run the second if the first query does not find the email address in question?

checkRegister: function(req, res) {
pool.connect(function(err, client, done) {
if (err) {
return console.error('error fetching client from pool', err);
} connection
var emailCheck = "SELECT id from public.user WHERE email=" + req.body.email;
var emailInsert = "insert into public.user (user_auth_level,email,account_locked,contract) " +
"values ('1','" + req.body.email + "','false','false')"
client.query(emailCheck, function(err, result) {
if (err) {
return console.error(err.message);
}

});
client.query(emailInsert, function(err, result) {
if (err) {
return console.error(err.message);
}

});
done();
});
pool.on('error', function(err, client) {
console.error('idle client error', err.message, err.stack)
});
}

Answer

You need to wrap your value with ' to make it string. Without string wrap, it will be come compare between column. It should be:

var yourQuery = "SELECT id from public.user WHERE email=" + req.body.email; // SELECT id from public.user WHERE email=myemail@example.com
var correntQuery = "SELECT id from public.user WHERE email='" + req.body.email + "'"; // SELECT id from public.user WHERE email='myemail@example.com'

Nodejs is synchronous, your need to use callback or promise to chain your code like below:

checkRegister: function (req, res) {
    pool.connect(function (err, client, done) {
        if (err) {
            console.error(err);
            // should return response error like 
            return res.status(500).send();
        }
        var emailCheck = "SELECT id from public.user WHERE email='" + req.body.email + "'";
        client.query(emailCheck, function (err, result) {
            if (err) {
                console.error(err);
                res.status(500).send();
                return done(); // always close connection
            }
            if (result.rowCount > 0) {
                let user = result.rows[0]
                // return your user
                return done(); // always close connection
            } else {
                var emailInsert = "insert into public.user (user_auth_level, email, account_locked, contract) " +
                    "values ('1','" + req.body.email + "','false','false') RETURNING *"
                client.query(emailInsert, function (err, result) {
                    if (err) {
                        console.error(err);
                        res.status(500).send();
                        return done(); // always close connection
                    } else {
                        if (result.rowCount > 0) {
                            let user = result.rows[0]
                            // return your user
                            return done(); // always close connection
                        }
                    }

                });
            }
        })
    })
    pool.on('error', function (err, client) {
        console.error('idle client error', err.message, err.stack)
    });
}