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

Why not simply use a serial type, or an identity column in postgres? Then you don't need to make multiple db requests at all. — Bergi
– Bergi, Commented Feb 23, 2020 at 21:28
Hmm, I tried that, but I get this error: ERROR: duplicate key value violates unique constraint "users_pkey" DETAIL: Key (id)=(12) already exists. SQL state: 23505 — Joon Joonam
– Joon Joonam, Commented Feb 23, 2020 at 21:32
Okay so if I keep on doing it, eventually, the ID works it way up to the empty row. Why is it not starting at the free one at the start? — Joon Joonam
– Joon Joonam, Commented Feb 23, 2020 at 21:33
If you use a sequence, you need to leave the id column out of the INSERT statement so that it uses the generated value as the default — Bergi
– Bergi, Commented Feb 23, 2020 at 21:35
Or probably you created the sequence when you already had data in the table, but still had it start at 0? — Bergi
– Bergi, Commented Feb 23, 2020 at 21:37

vitaly-t · Accepted Answer · 2020-02-27 20:35:18Z

2

Since you are using more than one query at a time, you should use a task:

await db.task('add-new-user', async t => {
    const count = await t.one('SELECT count(*) FROM users', [], a => +a.count);
    newUser.id = count + 1;
    return t.none('INSERT INTO users(${this:name}) VALUES(${this:csv})', newUser);
});

P.S. You should use type serial instead, and then you can avoid all this.

edited Feb 27, 2020 at 20:35

answered Feb 23, 2020 at 21:39

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

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

1 Comment

Joon Joonam Over a year ago

Thank you, yes, definitely a better solution is to use serial.

Bergi · Accepted Answer · 2020-02-23 21:41:48Z

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

Collectives™ on Stack Overflow

Multiple queries in one request

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related