Show EchoJS: SQL Server orchestration with Node.js

ms-sqlcmd

This package makes it easier to execute sqlcmd scripts from node.js. It will use an mssql url string as a connection string. The path should be /INSTANCENAME/DATABASENAME or simply /DATABASENAME . The fields should be encoded via encodeURIComponent in order to facilitate special characters, especially in passphrases that might otherwise interfere or have special characters.

As the mssql and underlying tedious packages favor TCP connections, this library will default to matching constraints. You should of course make certain that your SQL Server installation is setup to listen to TCP connection requests.

Script file(s) should be encoded in UTF-8, they will be written in a temporary location for execution, and cleaned up (via UTF-16LE / Unicode in sqlcmd).

Usage

const { sqlcmd } = require('ms-sqlcmd');
...
// mssql url formatted connection string - for now, no querystring options will be parsed
// for connection/execution options, see Query String section below.
const connectionString = 'mssql://sa:password@server:port/database';

// path to the script to run, should prefer absolute paths, or relative to the current working directory.
// see Connection String below
const scripts = [
  path.join(__dirname, '../sql-scripts/somescript.sql'),
];

// Optional, script variables, for scripts using `:setvar` and `$(VarName)`
const scriptVars = {
  "DatabaseName": "foo"
};

// Optional, additional options, defaults below
const options = {
  echo: true, // echo sqlcmd output to stdout/stderr while running
};

// call sqlcmd with the parameters, if you want to pass options, without scriptVars, use null for scriptVars.
// returns a promise, you can await on it directly, or listen for specific events.
try {
  // if sqlcmd returns with a non-zero exit code, an error will be thrown
  const output = await sqlcmd(connectionString, scripts, scriptVars, options);
} catch(error) {
  // standard properties set on error object other error properties may also be set
  //   if a code of "INVALID_CONNECTION_STRING" is used, there will be an innerError property
  const { message, code, output, stdout, stderr } = error;
}

Errors

The error should have a code property. This will be a string you can match on, or the exit code from sqlcmd directly.

• ### - the exit code from sqlcmd executable.
• SQLCMD_NOT_FOUND - the sqlcmd executable could not be found, either in the PATH environment, or in known install locations.

  PATH
  sqlcmd
  

• INVALID_CONNECTION_STRING - the connectionString argument is not a valid URL, or does not contain all the necessary parameters.
• UNEXPECTED_ERROR - Any other errors will be wrapped in this, with an innerError property containing the original error.

Connection String

The main portions of the connectionString are as follows: (See Query String section below)

Protocol://Username:Passphrase@ServerName:Port/InstanceName/DatabaseName?Query

Each section should be URI Component Path encoded (encodeURIComponent).

• Protocol: The connection protocol to use
  • mssql: will use TCP for local sqlcmd or default for docker runs.
  • mssql+tcp - TCP, default for non-docker requests
  • msssql+lpc: - shared memory
  • mssql+np: - named pipes
  • mssql+docker - will run a default connection INSIDE a named container
• Username: Required for TCP connections, if unspecified will use a Trusted Connection option.
• Passphrase: Required if Username is specified.
• ServerName: The name of the server to connect to.
  • localhost for local connections (lpc, np, tcp)
    • For docker users, always use localhost
  • The dns or ip address for the server for remote connections (tcp)
  • docker for execution inside a docker sql server instance.
• Port: (optional) the port to connect to the server on, TCP Only
  • Docker users, use the listen port, this will allow the command to attempt match a running container.
• InstanceName: (optional)
  • Named Instance (tcp, lcp, np)
  • Container ID or Name (docker)
  • If unspecifed, will use the default instance
• DatabaseName: The name of the database to connect to.
• Query: See below

Docker

If the Protocol is "mssql+docker" , then a locally installed Docker server is expected with the INSTANCE specified to either the Container ID or Name. The script will be copied into the container as /tmp/sqlscript.sql . Inside the container, /opt/mssql-tools/bin/sqlcmd will be executed against the script.

For Docker runs, the ServerName and Port will be ignored as the default instance inside the container will be used. In practice, you should use either localhost or docker . The Username and Passphrase fields are also optional for Docker runs.

Query String

The following query string parameters may also be specified as additional parameters passed to sqlcmd . Boolean values may be a literal true , t , y or 1 as a boolean or string for a true value, false , f , n , or 1 for a false value, all other values will use the default/unset value.

ReadOnly

Other Details

Install SQL Server command line tools on Linux

While the Microsoft instructions should work, I noticed on at least Pop!_OS/Ubuntu/Debian in 19.10+ that the instructions fail in practice. For ubuntu, you should first isntall unixodbc first, then install mssql-tools .

Setup:

sudo apt update
sudo apt install unixodbc
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt update
sudo apt install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile

Related Projects

Some knowledge for this project was learned from reading the source for @quorum/sqlcmd-runner .

License

MIT License