How to write a function that returns data from a database?

Question

Node.js

You need to write a function that takes a SQL query string and returns an array of data received from the database. I only achieved that the data was output to the console inside the function, but I can’t figure out how to make the function return data. The code:

const Connection = require('tedious').Connection;
const Request = require('tedious').Request;
const { request } = require('http');
const config = require('./config_db');

const connection = new Connection(config);
connection.on('connect', (err) => {err ? console.log(err) : select(query)});

const select = (query) => {
    request = new Request(query);
    request.on('row', (columns) => {
        columns.map(column => {
            console.log(column.value)
        })
    })
    connection.execSql(request)
    return request
};

module.exports = select;

But this function does not return anything.

I made sure that the return was, but a very big crutch, like this:

const Connection = require('tedious').Connection;
const Request = require('tedious').Request;
const { request } = require('http');
const config = require('./config_db');
const addState = require('./state').addState; // функция делает push в state


const connection = new Connection(config);
connection.on('connect', (err) => {err ? console.log(err) : select(query)});

const select = (query) => {
    request = new Request(query);
    request.on('row', (columns) => {
        columns.map(column => {
            addState(column.value)
        })
    })
    connection.execSql(request)
    const state = require('./state').state // импортирую именно сдесь, потому что если импортировать раньше вернется пустой state
    return state
};

module.exports = select;

So it still works only the second time. :)
How to make the function return data?

Answer 1

const select = (query) => new Promise((resolve, reject) => {
  const request = new Request(query);
  request.once('error', reject);
  request.once('done', (rowCount, more, rows) => {
    resolve(rows.map(
      columns => columns.map(
        column => column.value
      )
    ));
  });
  connection.execSql(request);
});