How do I do sql queries with nodejs in AWS lambda?

Question

exports.handler = async (event) => {
    const sql = require('mssql');
    
    let config = {
        user: "MyUsername",
        password: "MyPassword",
        server: "MyServer",
        port: 1433,
        options: {
            database: 'MyDatabase',
            encrypt: false,
        }
    };


    sql.connect(config, (err) => {
        if (err) { console.log(err);}
        else {
            console.log('connected');

            let request = new sql.Request();
            const theQuery = "SELECT * FROM MyTable";

            request.query(theQuery, (err, data)=> {
                if(err) { console.log(err); }
                else { console.log(data); }
            })
       }
    });
};

I am trying to do a simple SELECT query with this, and it seems to work fine when I run it locally, but when I try it on Lambda, it times out. I have the lambda on the same VPC as the SQL Server so it is not a connection issue. Any Ideas?

2022-07-17T03:10:02.912Z    a4775342-517a-4511-920e-46269ee07627    INFO    ConnectionError: Failed to connect to ********:1433 in 15000ms
    at /opt/nodejs/node_modules/mssql/lib/tedious/connection-pool.js:70:17
    at Connection.onConnect (/opt/nodejs/node_modules/tedious/lib/connection.js:1051:9)
    at Object.onceWrapper (node:events:642:26)
    at Connection.emit (node:events:527:28)
    at Connection.emit (node:domain:475:12)
    at Connection.emit (/opt/nodejs/node_modules/tedious/lib/connection.js:1079:18)
    at Connection.connectTimeout (/opt/nodejs/node_modules/tedious/lib/connection.js:1284:10)
    at Timeout._onTimeout (/opt/nodejs/node_modules/tedious/lib/connection.js:1229:12)
    at listOnTimeout (node:internal/timers:559:17)
    at processTimers (node:internal/timers:502:7) {
  code: 'ETIMEOUT',
  originalError: ConnectionError: Failed to connect to ********:1433 in 15000ms
      at Connection.connectTimeout (/opt/nodejs/node_modules/tedious/lib/connection.js:1284:26)
      at Timeout._onTimeout (/opt/nodejs/node_modules/tedious/lib/connection.js:1229:12)
      at listOnTimeout (node:internal/timers:559:17)
      at processTimers (node:internal/timers:502:7) {
    code: 'ETIMEOUT',
    isTransient: undefined
  }
}

Have you checked routing and security groups?

Mark Sailes
– Mark Sailes

2022-07-17 07:09:30 +00:00
Commented Jul 17, 2022 at 7:09 — Mark Sailes
– Mark Sailes, Commented Jul 17, 2022 at 7:09

Pa3k.m · Accepted Answer · 2022-07-18 16:00:56Z

2

As it turns out, the script is simply terminating before finishing. To fix this, I had to return a promise

exports.handler = async (event) => {

    const promise = new Promise(function(resolve, reject) {
        
        let sql = require('mssql');
        
        sql.connect(config, (err) => {
            if (err) {
                reject(err);
            }
            
            else {
                const request = new sql.Request();
                
                const query = "SELECT * FROM MyTable";
                request.query(query, (err, result) => {
                    if(err) {
                        reject(err);
                    }
                    
                    else {
                        resolve(result);
                    }
                });
            }
        });
    });
        
    return promise;
};

answered Jul 18, 2022 at 16:00

Pa3k.m

1,1862 gold badges13 silver badges26 bronze badges

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

1 Comment

Brant Over a year ago

Do you need to close the MSSQL connection before returning a response from the lambda?

Collectives™ on Stack Overflow

How do I do sql queries with nodejs in AWS lambda?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related