How to return the serial primary key from a db insert to use for another db insert

Question

I'm using the express generated template with postgresql and I have 2 rest route methods for creating consignments and tracking. However i want tracking to be updated on each consignment insert, but i require the serial primary key to do it. So from the createCon function i require it to return the id after the insert, to use for the cid field in the createConTracking.

routes/index.js file

var db = require('../queries');
router.post('/api/cons', db.createCon);
router.post('/api/cons/:id/tracking', db.createConTracking);

queries.js

var promise = require('bluebird');
var options = {
  promiseLib: promise
};
var pgp = require('pg-promise')(options);
var db = pgp(connectionString);
function createCon(req, res, next) {
  var conid = parseInt(req.body.conid);
  db.none('insert into consignments(conid, payterm,........)'+
    'values($1, $2, ......)',
    [conid, req.body.payterm,........])
    .then(function () {
      res.status(200)
        .json({
          status: 'success',
          message: 'Inserted one con'
        });
    })
    .catch(function (err) {
      return next(err);
    });
}

function createConTracking(req, res, next) {
  var cid = parseInt(req.params.id);
  var userid = req.user.email;
  var conid = parseInt(req.body.conid);
  db.none('insert into tracking(status, remarks, depot, userid, date, cid, conid)'+
    'values($1, $2, $3, $4,$5, $6, $7)',
    [req.body.status, req.body.remarks, req.body.depot, userid, req.body.date, cid, conid])
    .then(function (data) {
      res.status(200)
        .json({
          data:   data,
          status: 'success',
          message: 'Updated Tracking'
        });
    })
    .catch(function (err) {
      return next(err);
    });
}

DB

CREATE TABLE consignments (
  ID SERIAL PRIMARY KEY,
  conId INTEGER,
  payTerm VARCHAR,

CREATE TABLE tracking (
  ID SERIAL PRIMARY KEY,
  status VARCHAR,
  remarks VARCHAR,
  cid INTEGER
  );

Answer 1

I'm the author of pg-promise.

---

You should execute multiple queries within a task (method task) when not changing data, or transaction (method tx) when changing data. And in case of making two changes to the database, like in your example, it should be a transaction.

You would append RETURNING id to your first insert query and then use method one to indicate that you expect one row back.

function myRequestHandler(req, res, next) {
    db.tx(async t => {
        const id = await t.one('INSERT INTO consignments(...) VALUES(...) RETURNING id', [param1, etc], c => +c.id);
        return t.none('INSERT INTO tracking(...) VALUES(...)', [id, etc]);
    })
        .then(() => {
            res.status(200)
                .json({
                    status: 'success',
                    message: 'Inserted a consignment + tracking'
                });
        })
        .catch(error => {
            return next(error);
        });
}

In the example above we execute the two queries inside a transaction. And for the first query we use the third parameter for an easy return value transformation, plus conversion (in case it is a 64-bit like BIGSERIAL).