Mark Angelo Lim Mark Angelo Lim - 1 month ago 9
reST (reStructuredText) Question

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

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

Simply add a RETURNING clause to your INSERT statement. This clause allows you to return data concerning the actual values in the inserted record.

insert into consignments(conid, payterm,........)
values($1, $2, ......)
returning id;
Comments