How to correctly execute this promise based SQL Query?

Question

I'm using MySQL2 to connect my Node.js app with a MySQL Database. Unfortunately trying to perform some promise based prepared statements I just can't get a proper function setup that either returns successfully after entering the record or to throw an error whenever something goes wrong.

Any ideas on how to fix the code below?


// Connection Settings
const connection = mysql.createConnection({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_DATABASE,
  port: process.env.DB_PORT
})

// Promise based SQL Prepared Statement
db.pps = ({ query, variables, error }) => {
  return new Promise((resolve, reject) => {
    connection.execute(query, variables, (err, results) => {
      if (err) {
        console.log(`Error: ${error} \n ${err.sqlMessage}`)
        return reject(err)
      }
      return resolve(results)
    })
  })
}

// Sign Up
auth.signup = (req, res) => {
  const query = `
    INSERT INTO User (Id, Email, Password)
    VALUES (UUID_TO_BIN(UUID()), ?, ?)
  `
  const variables = [req.query.email, req.query.password]

  db.promise({ query, variables }, (err, result) => {
    if (err) {
      res.status(400)
    }
    res.status(200)
  })
}

Please tell what error comes ?

Mahesh Bhatnagar
– Mahesh Bhatnagar

2020-01-06 09:58:14 +00:00
Commented Jan 6, 2020 at 9:58 — Mahesh Bhatnagar
– Mahesh Bhatnagar, Commented Jan 6, 2020 at 9:58

Sohail Ashraf · Accepted Answer · 2020-01-06 10:04:10Z

1

you can use the prepared statement query function like below.

If you are not using this inside a function

auth.signup = (req, res) => {
    const query = `
      INSERT INTO User (Id, Email, Password)
      VALUES (UUID_TO_BIN(UUID()), ?, ?)
    `
    const variables = [req.query.email, req.query.password]
    db.pps({ query, variables })
    .then(result => {
        res.status(200)
    })
    .catch( error => {
        res.status(400)
    }); 
  }

Or use async await

auth.signup = async (req, res) => {
    const query = `
                    INSERT INTO User (Id, Email, Password)
                    VALUES (UUID_TO_BIN(UUID()), ?, ?)
                    `
    const variables = [req.query.email, req.query.password]
    try {
        await db.pps({ query, variables });
        res.status(200)
    } catch (err) {
        res.status(400)
    }
}

edited Jan 6, 2020 at 10:04

answered Jan 6, 2020 at 9:58

Sohail Ashraf

10.7k3 gold badges31 silver badges43 bronze badges

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

Comments

Subburaj · Accepted Answer · 2020-01-06 10:01:56Z

1

You have to do 2 changes:

1.Remove error param

db.pps = ({ query, variables}) => {

2. Change the SignUp code as below

auth.signup = (req, res) => {
  const query = `
    INSERT INTO User (Id, Email, Password)
    VALUES (UUID_TO_BIN(UUID()), ?, ?)
  `
  const variables = [req.query.email, req.query.password]

  db.pps({ query, variables }).then(response => {
    // Do stuff with users
  })
  .catch(err => {
    // handle errors
  })

answered Jan 6, 2020 at 10:01

Subburaj

5,21211 gold badges48 silver badges95 bronze badges

Comments

Surojit Paul · Accepted Answer · 2020-01-06 10:34:50Z

1

function pps( query, variables){
  return new Promise((resolve, reject) => {
    connection.execute(query, variables, (err, results) => {
      if (err) {
        console.log(`Error: ${error} \n ${err.sqlMessage}`)
        reject(err)
      }
      resolve(results)
    })
  })
}

auth.signup = (req, res) => {
    const query = `
      INSERT INTO User (Id, Email, Password)
      VALUES (UUID_TO_BIN(UUID()), ?, ?)
    `
    const variables = [req.query.email, req.query.password]
    pps(query, variables)
    .then(result => {
        res.status(200)
    })
    .catch( error => {
        res.status(400)
    }); 
  }

answered Jan 6, 2020 at 10:34

Surojit Paul

1,28211 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to correctly execute this promise based SQL Query?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related