Using SQL Server with Node on MacOS and Windows

We’ve had a few projects at Echobind where we needed to create a script that runs on a Windows server, accesses a Microsoft SQL Server database, and does some stuff with that data. All of our devs work on Macs and most of us know TS really well. So, we’d prefer to write complex scripts in TS and develop on MacOS.

Easy enough. Reach for the mssql library and start working. That’ll go just fine during development. You might have a Microsoft SQL Server database running in Docker on your Mac, developing against some dummy data, and everything goes smoothly. But that innocent joy, that childlike season of wonder where you blow on the dandelions of summer and laugh as little seeds of code float effortlessly into the sunny blue sky of familiarity, will die when you start testing your script on a Windows machine and you will cry the salty tears of interoperability.

You can’t just import mssql from 'mssql' and go on using mssql on Windows. Windows has a special version of mssql that you need to import, mssql/msnodesqlv8.js. And does mssql/msnodesqlv8.js work on MacOS? No, of course it doesn’t! You can’t even import mssql/msnodesqlv8.js on MacOS! Why would you even ask that?

At this point, you’re left with a situation where you need to import one library on MacOS and another on Windows, and the one you import on Windows, you must never, under any circumstance, import on MacOS. And the weeping commences.

Well, dry those salty tears. We have cried them for you. Just copy/paste the snippet below:

import mssql from 'mssql';

type MSSql = typeof mssql;

let _mssql: MSSql;

async function getMssql() {
  if (!_mssql) {
    // Windows
    if (isProduction()) {
      // The normal mssql lib doesn't work on Windows and even importing this
      // mssql/msnodesqlv8 library on MacOS will fail. So we have to conditionally
      // import it.
      _mssql = (await import('mssql/msnodesqlv8.js').then((m) => m.default)) as MSSql;
    }
    // MacOS (or whatever)
    else {
      _mssql = mssql;
    }
  }

 return _mssql;
}

This will import mssql on MacOS and Windows (that’s fine), but will only import mssql/msnodesqlv8.js in production (on Windows). The getMssql function will decide which version of mssql you need, cache it (so it doesn’t need to reimport on every call), and return it.

Thankfully, the rest of your application doesn’t need to know that this witchcraft occurred; mssql and mssql/msnodesqlv8.js have the same interface. getMssql is the scapegoat of your codebase. It suffers so the rest of your application can live on in peaceful ignorance of its sacrifice.

You can read the mssql library docs for this, but here’s a short example of how you’d use getMssql:

const mssql = await getMssql();

await mssql.connect(config);

const result = await mssql.query`select * from users where id = ${id}`;

const users = (results.recordset || []);