node.js - How to insert data in Azure SQL using tedious.js after verifying that the data doesn't exists in the database?

Question

This is the first time I'm using tedious.js, I still don't understand it a lot. I'm mostly going over their documentation here.

Following is the logic in the POST method.

• Check if the tag already exists for the given statement id
• If the tag exists, return the message to the client
• If the tag doesn't exist, insert the tag in the database.

This is my post method in express.js to add "tag"

 // POST add tags
 router.post('/tag', function(req, res){

     // get the body
     const data = req.body;    

     // sql query to check if the tag already exists for the given id or not. 
     let sql = `SELECT * FROM tags WHERE corporate_statement_link_id = @corporate_statement_link_id and name = @name FOR JSON PATH`;
     
      // sql query to insert the tag in the database
      let query = `INSERT INTO tags (corporate_statement_link_id, name) VALUES(@corporate_statement_link_id, @name)`;

     console.log("Getting tags");

     try {
         console.log("Checking if the tag '", data.name, "' exists for ", data.corporate_statement_link_id,".");

//This is where I'm using the tedious to make connection to Azure database
         var conn = sqldb.createConnection();

         var request = sqldb.createRequest(sql, conn);
         request.addParameter('corporate_statement_link_id', TYPES.Int, data.corporate_statement_link_id);
         request.addParameter('name', TYPES.VarChar, data.name);

         //=========>>>
         // This is where I'm not so sure what's happening and if I can call it this way. 
         // The reason I'm trying this is because var "request" is always undefined since it's done async?. so, even though the tag exists in the database, the code below to add the tag still runs. 
         var result = sqldb.runQuery(request, conn, res, '{}').then(d => {
             console.log(d);
         });
        //  console.log("Result: ", result);
         


         if(!request){
             console.log('the tag already exists')
            res.status(300).send("Tag already exist.");
         } else {
            console.log("Tag ",data.name, "doesn't exist.")
            console.log("Creating tag");

            // This is to add the tag to the database

            conn = sqldb.createConnection();
            request = sqldb.createRequest(query, conn);
            request.addParameter('corporate_statement_link_id', TYPES.Int, data.corporate_statement_link_id);
            request.addParameter('name', TYPES.VarChar, data.name);

            result = sqldb.runQuery(request, conn, res, '{}');
            console.log("Result: ", result);
            
            return result;  
         }


     } catch(err){
        res.send(err);
     }

Azure Database Connection

'use strict'

// function to create connection to the SQL database
function createConnection(){

    var AzureConfig = {
        authentication: {
          options: {
            userName: "username", 
            password: "password" 
          },
          type: "default"
        },
        server: "server", 
        options: {
          database: "database", 
          encrypt: true, 
          validateBulkLoadParameters: true
        }
    };

    // create connection to the database 
    var Connection = require('tedious').Connection;
    var connection = new Connection(AzureConfig);
 
    return connection;
    
}// end create connection


// create query request
function createRequest(query, connection){

    var Request = require('tedious').Request;

    console.log("Creating new request");

    var req = new Request(query, function(err, rowCount) {
        if(err){
            console.log(err);
            output.write(err);
        }

        connection && connection.close();
    });

    console.log("Request created..");

    return req;
}


// Running the query
function runQuery(query, connection, output, defaultContent){
    var request = query;
    
    if(typeof query == 'string'){
        request = createRequest(query, connection);
    }

    console.log("It's not a query but a request..");
    var empty = true;

    request.on('row', function(columns){
        console.log("Checking and running the row..");
        if(empty){
            console.log("Response fetched from SQL Database.");
            empty = false;
        
        }
        console.log("Columns found: ", columns[0].value);
        // console.log(output);
        output.write(columns[0].value);
        // console.log("returning data");
        // data.push(columns[0].value);
    });

    request.on('done', function(rowCount, more, rows){
        console.log("The request is done..");
        console.log(rowCount, "rows returned");
        // output.end();
        _OnDone(empty, defaultContent, output);
    });

    request.on('doneProc', function(rowCount, more, rows){
        console.log("The request procedure is done..");
        _OnDone(empty, defaultContent, output);
        console.log(rowCount, "rows returned");
        // output.end();
    });

    console.log("Executing request");
    executeRequest(request, connection);

}

function _OnDone(empty, defaultContent, output){
    if(empty){
        output.write(defaultContent);
        console.log('No results from database - default content is returned.');

    }

    try{
        
        console.log('Closing Http Response output.');
        // console.log("Output: ", output);
        output.end();
        
        
    } catch(err){
        // output.write(err);
        console.error(err);
    }
}

// execute request to pull the data
function executeRequest(request, connection){

    console.log("checking the connection..");
    
    connection.on('connect', function(err){
        if(err){
            console.log(err);
            //output.write(err);
        }
        console.log("request executing...");
        console.log("Request: ", request);
        connection.execSql(request);
    });
}

module.exports.createConnection = createConnection;
module.exports.createRequest = createRequest;
module.exports.executeRequest = executeRequest;
module.exports.runQuery = runQuery;

Error message and output

Checking and running the row..
Response fetched from SQL Database.
Columns found:  [{"corporate_statement_link_id":3,"name":"black-lives-matter","id":11},{"corporate_statement_link_id":3,"name":"edi","id":12},{"corporate_statement_link_id":3,"name":"test","id":13},{"corporate_statement_link_id":3,"name":"test2","id":14},{"corporate_statement_link_id":3,"name":"test3","id":23},{"corporate_statement_link_id":3,"name":"test","id":24}]
The request procedure is done..
Closing Http Response output.
undefined rows returned
GET /api/v1/statements/tags/3 200 353.227 ms - -
Getting tags
Checking if the tag ' test4 ' exists for  3 .
Creating new request
Request created..
It's not a query but a request..
Executing request
checking the connection..
POST /api/v1/statements/tag 200 27.657 ms - 2
request executing...
Request:  Request {
  _events: [Object: null prototype] {
    row: [Function],
    done: [Function],
    doneProc: [Function]
  },
  _eventsCount: 3,
  _maxListeners: undefined,
  sqlTextOrProcedure: 'SELECT * FROM tags WHERE corporate_statement_link_id = @corporate_statement_link_id and name = @name FOR JSON PATH',
  parameters: [
    {
      type: [Object],
      name: 'corporate_statement_link_id',
      value: 3,
      output: false,
      length: undefined,
      precision: undefined,
      scale: undefined
    },
    {
      type: [Object],
      name: 'name',
      value: 'test4',
      output: false,
      length: undefined,
      precision: undefined,
      scale: undefined
    }
  ],
  parametersByName: {
    corporate_statement_link_id: {
      type: [Object],
      name: 'corporate_statement_link_id',
      value: 3,
      output: false,
      length: undefined,
      precision: undefined,
      scale: undefined
    },
    name: {
      type: [Object],
      name: 'name',
      value: 'test4',
      output: false,
      length: undefined,
      precision: undefined,
      scale: undefined
    }
  },
  originalParameters: [],
  preparing: false,
  canceled: false,
  paused: false,
  userCallback: [Function],
  handle: undefined,
  error: undefined,
  connection: undefined,
  timeout: undefined,
  rows: undefined,
  rst: undefined,
  rowCount: undefined,
  callback: [Function],
  [Symbol(kCapture)]: false
}
The request procedure is done..
No results from database - default content is returned.
Closing Http Response output.
undefined rows returned
events.js:292
      throw er; // Unhandled 'error' event
      ^

Error [ERR_STREAM_WRITE_AFTER_END]: write after end
    at write_ (_http_outgoing.js:629:17)
    at ServerResponse.write (_http_outgoing.js:621:15)
    at _OnDone (C:Usersshresthassource
eposstatement-rater-app
outesazure-db.js:99:16)
    at Request.<anonymous> (C:Usersshresthassource
eposstatement-rater-app
outesazure-db.js:87:9)
    at Request.emit (events.js:315:20)
    at Request.emit (C:Usersshresthassource
eposstatement-rater-app
ode_modulesediouslib
equest.js:154:18)
    at Parser.<anonymous> (C:Usersshresthassource
eposstatement-rater-app
ode_modulesediouslibconnection.js:1323:19)
    at Parser.emit (events.js:315:20)
    at Parser.<anonymous> (C:Usersshresthassource
eposstatement-rater-app
ode_modulesediouslibokenoken-stream-parser.js:35:14)
    at Parser.emit (events.js:315:20)
Emitted 'error' event on ServerResponse instance at:
    at writeAfterEndNT (_http_outgoing.js:684:7)
    at processTicksAndRejections (internal/process/task_queues.js:85:21) {
  code: 'ERR_STREAM_WRITE_AFTER_END'
}

How can I resolve such that when the tag is found, it returns to the client bypassing the insert statement, but if the tag doesn't exist, I want to insert it and then send the tag to the client?

question from:https://stackoverflow.com/questions/65648013/how-to-insert-data-in-azure-sql-using-tedious-js-after-verifying-that-the-data-d

Answer 1

Instead of running two separate queries, one for the SELECT and one of the INSERT, which would actually require an explicit transaction to make sure that everything will always be consistent, you can execute everything with just one query, that will make also the code much simpler:

with cte as
(
    select * from ( values (4, 'tag6') ) as t(csid, [name])
)
insert into dbo.[tags_65648013] (corporate_statement_link_id, [name])
output inserted.*
select * from cte 
where not exists ( 
    select * from dbo.[tags_65648013] where corporate_statement_link_id = cte.csid  and [name] = cte.[name]
)

you can test the code using something like that:

drop table if exists dbo.tags_65648013;
create table dbo.tags_65648013
(
    corporate_statement_link_id int not null,
    [name] nvarchar(50) not null,
)
go

create clustered index ixc on dbo.tags_65648013(corporate_statement_link_id);
go

insert into dbo.[tags_65648013] values
(1, 'tag1'),
(1, 'tag2'),
(1, 'tag3'),
(2, 'tag1'),
(2, 'tag4'),
(3, 'tag2'),
(3, 'tag5')
go

select * from dbo.[tags_65648013]
go

Update: since OUTPUT do no support FOR JSON, you need to send the result into a @temp table and the query from there:

declare @result as table (id int, [name] nvarchar(50));
with cte as
(
    select * from ( values (4, 'tag66') ) as t(csid, [name])
)
insert into @result 
select * from (
    insert into dbo.[tags_65648013] (corporate_statement_link_id, [name])
    output inserted.* 
    select * from cte 
    where not exists ( 
        select * from dbo.[tags_65648013] where corporate_statement_link_id = cte.csid  and [name] = cte.[name]
)) r
select * from @result for json auto;