Node mssql: return id after SQL insertion

Question

I am working on an integration between Mongodb and SQL Server, inserting and updating registers from MongoDb to SQL Server database with a scheduled process, using the mssql package to achieve this.

Each time a new register is inserted, I want the new row id to be retrieved back, because I need it to perform another insertions of subregisters.

I tried the following, expecting to have some information retrieved in the result of the query. The query actually inserts the row, but result is returned as undefined:

var sql = require('mssql');
var connectionConfig = 'xxxxxxxxxxxx';
var insertionQuery =   'xxxxxxxxxxxx';

sql.connect(connectionConfig).then(pool => {
    pool
      .request()
      .query(insertionQuery);    

}).then(result => {
        //I expect 'result' to have information of the inserted row, but it comes undefined instead
        cosole.log(result);

}).catch(err => {
        console.err(err);
});

I think using stored procedures may work around this, but I just want to avoid using a stored procedure for simple inserts.

Does anyone knows how to have the new row id back after insertion?

EDIT

I did include OUTPUT SCOPE_IDENTITY() in my SQL statement - my query now looks like:

INSERT INTO TABLE (columns...) 
OUTPUT SCOPE_IDENTITY() 
VALUES (values...)

If I run the query in my SQL Server Management Studio, it works just fine and I got my new row id returned, but when running the query through the mssql package's query method, result is returned like this:

[ { '': null } ]

Answer 1

I quote from node-mssql insert returning undefined recordset:

INSERT statement doesn't return a recordset so recordset is undefined. Please see this section of the docs to learn more about how to get number of affected rows.

An option to solve this is to add a SELECT statement after your INSERT and ask for SCOPE_IDENTITY() or @@IDENTITY. That will get you the ID of the last inserted objects, i.e.:INSERT INTO table (...) VALUES (...); SELECT SCOPE_IDENTITY() AS id;

Answer 2

If anyone comes having problems with GUID, since you cannot set it as an Identity column, you must do something like:

const result = await pool
    .request()
    .input('orgName', sql.VarChar, orgName)
    .input('tier', sql.Int, tier)
    .input('status', sql.Int, status)
    .input('createDate', sql.Date, new Date())
    .input('statusChangeDate', sql.Date, new Date())
    .query(`INSERT INTO app.Organizations (OrgName, 
Tier, Status, CreateDate, StatusChangeDate)
 OUTPUT inserted.OrgID
 VALUES (@orgName, @tier, @status, @createDate, @statusChangeDate);
`)

Where the orgID is the auto generated GUID. Also don't follow my new Date() example as it inserts the date as tomorrow... I need to fix that.