Getting null from Azure SQL Database in recordset When running SELECT query

Question

I am connecting to my Azure SQL database from nodejs. When I run a simple SELECT * query I get nothing in the result even though the query runs. I have inspected the result. My result looks like the following whenever I query it:
"Result Object: { recordsets: null, recordset: null, output: {}, rowsAffected: [ 6 ] }"

I am using mssql package in nodejs to connect to the database. Following is my code:

const sql = require('mssql');

const config = {
    user: '******', // better stored in an app setting such as process.env.DB_USER
    password: '*******', // better stored in an app setting such as process.env.DB_PASSWORD
    server: 'myfreeoeedbserver.database.windows.net', // better stored in an app setting such as process.env.DB_SERVER
    port: 1433, // optional, defaults to 1433, better stored in an app setting such as process.env.DB_PORT
    database: 'myfreeoeedb', // better stored in an app setting such as process.env.DB_NAME
    authentication: {
        type: 'default'
    },
    options: {
        encrypt: true
    },
    stream:true
}

try {
        // Establish connection
        let pool = await sql.connect(config);
        
        // Execute a simple query
        let result = await pool.request().query('SELECT TOP 10 * FROM [dbo].[Bystronic12K_shifts]');
        
        // Log the structure of the result object
        console.log("Result Keys:", Object.keys(result)); // Should output: [ 'recordsets', 'recordset', 'output', 'rowsAffected' ]
        
        // Log the entire result object for debugging
        console.log("Result Object:", result);

        // Check if the recordset is not null and print results
        if (result.recordset) {
            console.log("Recordset Found:");
            result.recordset.forEach(row => {
                console.log(row); // Prints each row in the recordset
            });
        } else {
            console.log('No records found.');
        }
        
        // Close the connection
        await pool.close();
    } catch (err) {
        console.error('SQL error', err);
    }

The recordset shows as null even though there are rows affected. The table is not empty and I can see the correct output when I run the same query in the query editor

Answer 1

The issue where the recordset is null while rows are affected in your Azure SQL database query using Node.js and the mssql package could be due to several reasons, such as misconfigurations in the connection setup, outdated mssql package or Node.js version, or potential multiple recordsets being returned with the desired data not in the first set. It could also be caused by unexpected behavior in the SQL query or issues with the table itself.

Try the following :

const sql = require('mssql');
const config = {
    user: process.env.DB_USER || '******',
    password: process.env.DB_PASSWORD || '*******',
    server: process.env.DB_SERVER || 'myfreeoeedbserver.database.windows.net',
    port: process.env.DB_PORT || 1433,
    database: process.env.DB_NAME || 'myfreeoeedb',
    authentication: {
        type: 'default'
    },
    options: {
        encrypt: true,
        enableArithAbort: true,
        debug: {
            packet: true,
            token: true,
            data: true,
            payload: true,
        }
    }
};
(async () => {
    try {
        let pool = await sql.connect(config);
        let result = await pool.request().query('SELECT TOP 10 * FROM [dbo].[Bystronic12K_shifts]');
        console.log("Result Keys:", Object.keys(result));
        console.log("Result Object:", result);
        if (result.recordset) {
            console.log("Recordset Found:");
            result.recordset.forEach(row => {
                console.log(row);
            });
        } else {
            console.log('No records found.');
        }
        await pool.close();
    } catch (err) {
        console.error('SQL error', err);
    }
})();