query_exec.js

const libs = `${process.cwd()}/libs`;

const log = require(`${libs}/log`)(module);

const conn = require(`${libs}/db/monet`);

 // Promise that executes an SQL query with optional parameters
 // ```
 // Examples:
 //   Query with no parameters:
 //     execSqlQuery('SELECT * FROM people');
 //   Query with one parameter:
 //     execSqlQuery('SELECT name, age FROM people WHERE id = ?', [1]);
 //   Query with more than one parameter:
 //     execSqlQuery('SELECT name, age FROM people WHERE city = ? AND age > ?', ['São Paulo', 35]);
 // ```
function execSqlQuery(sqlQuery, sqlQueryParams = []) {
    log.debug(`Executing SQL query '${sqlQuery}' with params '${sqlQueryParams}'`);
    return new Promise((resolve, reject) => {
        // Prepare statement
        conn.prepare(sqlQuery, true).then((dbQuery) => {
            // Execute query
            dbQuery.exec(sqlQueryParams).then((dbResult) => {
                // release resources allocated for the prepared statement
                dbQuery.release();
                resolve(dbResult.data);
            }).catch((queryError) => {
                log.error(`SQL query execution error: ${queryError.message}`);
                log.error(`SQL query: ${sqlQuery} with params: ${sqlQueryParams}`);
                // release resources allocated for the prepared statement
                dbQuery.release();
                reject(new Error(queryError.message));
            });
        }).catch((prepError) => {
            log.error(`SQL prepared statement error: ${prepError.message}`);
            log.error(`SQL query: ${sqlQuery} with params: ${sqlQueryParams}`);
            reject(new Error(prepError.message));
        });
    });
}

function execMultiQuery(querySet = []) {
    // Issue all queries concurrently to the database, for every query object in the iterable
    // NOTE: Array.map() returns a copy of the original array with each object 'mapped'.
    return querySet.map((qry) => { return execSqlQuery(qry.toString()); });
}

module.exports = {execSqlQuery, execMultiQuery};