How to correctly execute this promise based SQL Query?

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)
  })
}

Answer

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)
    }
}