Multiple queries in one request

Question

I'm trying to add new users to the database and have the id automatically iterated. The way I'm trying to do this is run a query first to check the total number of rows and then adding 1 to it to assign as the ID of the user I want to add.

One of the problems I'm having is that in the first query the assignment of newUser.id is block scoped, and I cant access that value outside of it. The id for newUser stays at null, or undefined depending on how I move things around

/add user to DB
router.post("/", (req, res) => {

var newID;

const newUser = {
    id: null,
    name: req.body.name,
    email: req.body.email,
    active: true
};

 db.result("SELECT COUNT(*) FROM users")
    .then(data => {
        newID = parseInt(data.rows[0].count) + 1;
        newUser.id = newID;

     //IF I CONSOLE.LOG(newUser) here then the value for id is 14

    });

    //IF I CONSOLE.LOG(newUser) here then the value for id is NULL

  db.none(
    "INSERT INTO users(id, name, email, active) VALUES ($1, $2, $3, $4)",
    [newUser.id, newUser.name, newUser.email, newUser.active]
  )
    .then(data => {
      res.status(200).json({ msg: "new user was added" });
    })
    .catch(error => {
      console.log("ERROR", error);
    });

Answer 1

the assignment of newUser.id is block scoped, and I cant access that value outside of it.

You will need to use promise chaining for that:

db.result("SELECT …")
.then(data => {
    newUser.id = parseInt(data.rows[0].count) + 1;
    return db.none("INSERT …", newUser);
}).then(data => {
    res.status(200).json({ msg: "new user was added" });
}, error => {
    console.log("ERROR", error);
});

I'm trying to add new users to the database and have the id automatically iterated

Don't make two queries to achieve that. Use an identity column or sequence in the database for that.

Or at least join the two queries into a single statement to run at once:

db.none(
  "INSERT INTO users(id, name, email, active) VALUES ((SELECT COUNT(*)+1 FROM users), $1, $2, $3)",
  [newUser.name, newUser.email, newUser.active]
)
.then(data => {
  res.status(200).json({ msg: "new user was added" });
}, error => {
  console.log("ERROR", error);
});