Feature Request: acquire single connections.

[igalklebanov]

Hey 👋

Thanks for the awesome work people! 🙏

We're currently exploring adding mssql support @ https://github.com/kysely-org/kysely, and I have found your library very appealing.
One of Kysely's API requirements is acquiring a single connection and allowing to perform multiple queries on it before releasing it back to the pool.
I have read that everything is a connection pool in node-mssql, and you can only use the same connection in transactions and prepared statements. While playing around, I couldn't find a way that would allow it without doing some ugly hacks.
This is a blocker, and I'm sadly switching to the low-level tedious and some resource pooling library in the dialect implementation for now. This adds more complexity (pool management, result streaming implementation, js to tedious data types mapping, etc) on our end, that would've otherwise been great to consume from node-mssql, where it fits best.

Sorry if this is a duplicate, I have scanned all issues related to "connection" and could not find anything requesting this.

Expected behavior:

A method on the connection pool that reserves a single connection and returns it. The connection allows to execute many queries and can be manually released back to the pool.

Actual behavior:

Everything is a pool in node-mssql and you can't acquire a single connection.

Configuration:

irrelevant.

Software versions

• NodeJS: v16/18/20
• node-mssql: v9.1.1
• SQL Server: any

[dhensby]

Being able to acquire a single connection from the pool and release it at will is something that's been on my mind for a while and has been raised before.

I can see that it would be useful to either have a callback that could be used:

const pool = await mssql.connect(config);
const res = await pool.withConnection((conn) => {
  // business logic with connection here
});

Or even just a way to acquire a connection:

const pool = await mssql.connect(config);
const conn = await pool.acquireConnection();

The problem really is just to make that feature complete is quite tricky as there is currently no concept of a single connection at the moment and implementing that properly is going to be a pretty major refactor to the library. Whilst it's a nice feature to have in theory, the demand for it is quite low - I think this is only the second request I can recall for such a feature (the last one in April 2022).

[mccolljr]

There are certain tasks that can't be reliably completed with a pool. For example - we have a relatively large table stored in a data lake, but we cache "hot" portions in an Azure SQL database. The job that does this caching may end up needing to persist hundreds of millions of rows into the Azure SQL database, so for performance we write to a #tempTable first, and then periodically flush the contents of this table into the target database. In order to do this, we have to scope things to a single connection object, as only that connection can see the #tempTable, and we need to know if that connection is lost too early so that we can re-create and re-populate the #tempTable. We use the mssql library for application code, but it looks like we're not going to be able to use it for this task since we cannot get single connections.

[pkt-zer0]

I'll add a different use case here, the reason I've been looking for this same feature. What I wanted to do is run multiple batches on the same connection, because apparently that's how SET SHOWPLAN_XML works. (Maybe there's a simpler solution to that specific issue, but that's neither here nor there).

What I was thinking of as a workaround was to implement a connection "pool" which is guaranteed to return the same connection on acquire/release from an underlying real pool. Kind of hacky, but probably the easiest change at the moment.

[pkt-zer0]

So here's what I have for the single-connection pool. Seems to work, but also looks kind of scary, so use at your own risk.

// Wrapper around a ConnectionPool that only acquires a single connection, releasing it when closed.
function monoPool(originalPool: ConnectionPool): ConnectionPool & AsyncDisposable {
    // Cast here to avoid typing complaints for dynamic access to internals
    const original: any = originalPool;
    const originalAcquire = original._acquire.bind(original);

    let cached: any = null;
    function wrappedAcquire() {
        if (!cached) {
            // const conn = await this._acquire().promise
            // TODO: If this rejects on the first try, we will never retry
            cached = originalAcquire();
        }
        return cached;
    }

    const wrapper = Object.create(original);
    wrapper._acquire = wrappedAcquire;
    wrapper.close = async function() {
        if (!cached) {
            return; // Maybe this should be an error?
        }

        const connection = await cached.promise;
        return original.release(connection);
    };
    wrapper[Symbol.asyncDispose] = wrapper.close;
    return wrapper;
}

And then the usage code looks something like this:

// Ensure this runs on a single connection
await using conn = monoPool(pool);
const req = new Request(conn);

// NOTE: SET SHOWPLAN_XML must be the only item in the batch.
await req.batch`SET SHOWPLAN_XML ON;`;
const result = await req.batch(query);
await req.batch`SET SHOWPLAN_XML OFF;`;