Closing Connections

[feverfunk]

Looking at the coffee script, it appears that when a connection is closed and pooling is enabled, the entire pool is cleared. (Not coffee script literate)

What's the recommended use of connections with pooling enabled? Should i

1. connect once and share amongst modules
2. connect, execute query and then close for each db request
3. connect, execute query and not close for each db request.

thanks for a great module

[patriksimek]

Best practice is to open one sql.Connection and share it (or use global connection way). Once you execute a query, a new TCP connection is acquired from the pool internally. Once the query is complete, connection is released back to the pool automatically. You don't have to close anything. Maximum number of internal concurrent connections is configurable (see docs). Idle TCP connection are closed automatically after some time (also configurable).

[patriksimek]

Closing due to inactivity.

[mcdado]

Sorry to comment on such an old issue but I'm banging my head against this library. Incidentally I tried to setup a singleton intermediary class that would start the start the connection and then share that connection via static methods. My problem is that I'm having difficulties to setup things so that the connection is already open when it's time to run queries, but without having to reopen it again. Since the opening of the connection is of course async, I cannot just put everything in the opening callback, because that happens totally somewhere else at another time... the only thing I can do is to share sql.Connection, which is "connecting".

How can I open the connection and move on to prepare statements and run queries knowing that the connection is open?

[mcdado]

My problem is that whenever the code reaches connection.connect() from the second time on, it will hit the error EALREADYCONNECTING, because the connection is still being opened.

I thought about doing some sort of Promise pool of queries to be resolved once connect itself get resolved via a Promise, but right now my brain is very confused!

let mssql = require('mssql');
let fs = require('fs');

class Database
{

  static connect(username, password, server, database)
  {
    if (Database.connection !== null) {
      return Database.connection;
    }

    let storedUsername = null;
    let storedPassword = null;
    let storedServer = null;
    let storedDatabase = null;

    try {
      fs.accessSync(__dirname + '/../../config.json');

      let data = fs.readFileSync(__dirname + '/../../config.json')
      data = JSON.parse(data);
      storedUsername = data.sql.username;
      storedPassword = data.sql.password;
      storedServer = data.sql.server;
      storedDatabase = data.sql.database;

    } catch (e) {
      // Do nothing
    }

    var config = {
      user: username || storedUsername || '',
      password: password || storedPassword || '',
      server: server || storedServer || 'localhost',
      database: database || storedDatabase || '',
    }

    Database.connection = new mssql.Connection(config);

    return Database.connection;
  }

  static getConnection()
  {
    if (Database.connection === null) {
      try {
        Database.connect();
      } catch (e) {
        throw new Error('Database.getConnection: Database not connected.');
      }
    }

    return Database.connection;
  }

  static getInstance()
  {
    return mssql;
  }

  static query(query, fields)
  {
    if (typeof query !== 'string' || typeof fields !== 'object') {
      throw new Error("Invalid parameters");
    }

    let db = Database.getInstance();
    let connection = Database.getConnection();
    let ps = new db.PreparedStatement(connection);
    let values = {};

    fields.forEach(function(current, index) {
      ps.input(current.name, current.type);
      values[current.name] = current.value;
    });

    connection.connect(function(err) {
      if (err) {
        throw err;
      }

      ps.prepare(query, function(err) {
        if (err) {
          throw new Error(err);
        }

        ps.execute(values, function(err, recordset, affected) {
          if (err) {
            ps.unprepare(function(err) {
              if (err) {
                throw new Error(err);
              }
            });
            throw new Error(err);
          }

          ps.unprepare(function(err) {
            if (err) {
              throw new Error(err);
            }
          });
        });
      });
    });
  }
}

Database.connection = null;

module.exports = Database;

[mcdado]

For reference: http://stackoverflow.com/q/41686882/1092853