mssql dialect always sends string params/literals as NVARCHAR, causing implicit-conversion index scans on VARCHAR columns

Issue

For the mssql dialect, every JS string value is escaped/bound as Unicode (N'...' literals, and TYPES.NVarChar for bound parameters), regardless of the actual column's SQL type. This happens unconditionally, with no way to opt out per-model or per-column.

This causes a well-known SQL Server performance problem: when a VARCHAR column is compared against an NVARCHAR literal/parameter, SQL Server must implicitly convert the column (not just the parameter) to match type precedence rules. That conversion is applied per-row, which prevents the query optimizer from using an index seek on that column — it falls back to a full index/table scan. See Microsoft's own guidance on this: "Implicit conversion may affect cardinality estimates in the query plan".

Many production schemas — including ours, and presumably many others migrated from older systems or designed for ASCII-only data (codes, IDs, statuses) — use plain VARCHAR columns. For these, Sequelize's default behavior silently causes significant query slowdowns that are very hard to diagnose unless you already know to look for N' in the generated SQL.

Root cause

1. lib/sql-string.js, in escape():

   case "string":
     prependN = dialect === "mssql";
     break;
   

This unconditionally prepends N' to every escaped string literal for mssql, with no awareness of the target column's actual type.

lib/dialects/mssql/query.js, in getSQLTypeFromJsType():

getSQLTypeFromJsType(value, TYPES) { const paramType = { type: TYPES.NVarChar, typeOptions: {}, value }; ... This defaults every string bind parameter to TYPES.NVarChar (tedious), again independent of the column's declared type. Reproduction

const { Sequelize, DataTypes } = require('sequelize'); const db = new Sequelize('db', 'user', 'pass', { dialect: 'mssql', logging: false }); const M = db.define('Example', { code: DataTypes.STRING }, { timestamps: false });

const sql = db.getQueryInterface().queryGenerator.selectQuery( 'Examples', { where: { code: 'ABC-123' } }, M );

console.log(sql); // SELECT * FROM [Examples] AS [Example] WHERE [Example].[code] = N'ABC-123'; If the underlying code column is VARCHAR, this query cannot use an index seek on code.

Expected behavior Ideally, Sequelize would derive the correct literal/parameter type (VARCHAR vs NVARCHAR) from the model attribute's actual column type (DataTypes.STRING vs a distinct "ASCII string"/VARCHAR type), rather than hardcoding Unicode for the entire dialect. At minimum, this behavior should be configurable at the connection or model level (e.g. a dialect option like useUnicodeStrings: false), since currently there's no supported way to avoid it short of patching the library.

Workaround We're currently using patch-package to force VarChar literals/params by default:

--- a/node_modules/sequelize/lib/sql-string.js +++ b/node_modules/sequelize/lib/sql-string.js @@ -31,7 +31,7 @@ function escape(val, timeZone, dialect, format2) { case "bigint": return val.toString(); case "string":

•  prependN = dialect === "mssql";
  

•  prependN = false;
   break;
  

  }

--- a/node_modules/sequelize/lib/dialects/mssql/query.js +++ b/node_modules/sequelize/lib/dialects/mssql/query.js @@ -20,7 +20,7 @@ class Query extends AbstractQuery { return "id"; } getSQLTypeFromJsType(value, TYPES) {

• const paramType = { type: TYPES.NVarChar, typeOptions: {}, value };

• const paramType = { type: TYPES.VarChar, typeOptions: {}, value }; This works for us since our schema is ASCII-only, but it's a blunt, app-wide override and isn't safe for anyone storing genuine Unicode data in VARCHAR-mapped columns. A first-class solution in Sequelize itself would be much safer than every mssql user needing to discover and patch this individually.

Environment sequelize: 6.37.7 tedious: 18.6.1 SQL Server (mssql dialect)