this.parent.acquire is not a function on prepared statements

Question

I use the mssql (https://www.npmjs.com/package/mssql) module for my database. Normally I use postgres databases which lead to pg (https://www.npmjs.com/package/pg).

I want to setup prepared statements for the mssql database. When using the pg module it's quite easy.

This is how I do it with pg:

I setup my databaseManager

const { Pool } = require('pg');
const db = require('../config/database.js');
const pool = new Pool(db);

function queryResponse(result, err) {
  return {
    result,
    err
  };
}

module.exports = async (text, values) => {
  try {
    const result = await pool.query(text, values);
    return queryResponse(result.rows, null);
  } catch (err) {
    return queryResponse(null, err);
  }
};

and whenever I want to query the database I can call this module and pass in my statement and values. An example for todo apps would be

todos.js (query file)

const db = require('../databaseManager.js');

module.exports = {
  getAllTodosFromUser: values => {
    const text = `
        SELECT
            id,
            name,
            is_completed AS "isCompleted"
        FROM
            todo
        WHERE
            owner_id = $1;
    `;

    return db(text, values);
  }
};

I wanted to create an mssql equivalent. From the docs I see that the module differs from the pg module.

I changed my databaseManager to

const sql = require('mssql');
const config = require('../config/database.js');
const pool = new sql.ConnectionPool(config).connect();

module.exports = async (queryBuilder) => {
    try {
      const preparedStatement = await new sql.PreparedStatement(pool);

      return queryBuilder(sql, preparedStatement, async (query, values) => {
        await preparedStatement.prepare(query);
        const result = await preparedStatement.execute(values);
        await preparedStatement.unprepare();

        return {
            result: result.rows,
            err: null
        };
      });
    } catch (err) {
        return {
            result: null,
            err
        }
    }
};

and my query file would pass in the required parameters for the preparedStatement object

const db = require('../databaseManager.js');

module.exports = {
  getUserByName: username => db((dataTypes, statementConfigurator, processor) => {
    statementConfigurator.input('username', dataTypes.VarChar);

    const query = `
        SELECT
          *
        FROM
          person
        WHERE
          username = @username;
    `;

    return processor(query, { username });
  })
};

I was hoping that this approach would return the desired result but I get the error

this.parent.acquire is not a function

and don't know if my code is wrong. If it is, how can I setup my prepared statements correctly?

Edit:

I just found out that the error comes from this line of code

await preparedStatement.prepare(query);

but I think I took it correctly from the docs

https://tediousjs.github.io/node-mssql/#prepared-statement

Does this question help? https://stackoverflow.com/questions/30356148/how-can-i-use-a-single-mssql-connection-pool-across-several-routes-in-an-express — MOHRE, Apr 23 '19 at 06:31
I guesse is problem when u re trying to Acuire connection ! U re passing some object to another functions, somehow when event fires it tryes to call function of object but simply didnt finded! 1.First for all advice U to make simple example like in documentation https://github.com/tediousjs/node-mssql Without wrapping the layers! 2.After that try correctly wrap U're layer! 3.If this didnt work Add issue to bugreport of mssql libs u use ! — Diaskhan, Apr 23 '19 at 17:56

PrivateOmega · Accepted Answer · 2019-05-29T10:35:07.257

I thought this question deserved a little bit more explanation than the answer what OP gave. The solution is no different than what OP already answered.

The issue remains same, pool mustn't have resolved from its promise pending state. So it just has to be awaited.

module.exports = async queryBuilder => {
  try {
    await pool; // Waiting for pool resolve from promise pending state.
    const preparedStatement = await new sql.PreparedStatement(pool);
    // ..
  } catch (err) {
    // ..
  }
};

When you try to build a prepared statement, you pass the pool as an argument to its constructor. In its constructor is the below line

this.parent = parent || globalConnection

After which when you prepare the statement the flow leads to this line which would cause the issue since at that time this.parent's value was still a promise which was yet to be resolved.

this.parent.acquire(this, (err, connection, config) => {

this.parent.acquire is not a function on prepared statements

1 Answers1