From Node.js to SQL Server: the mssql Package, Connection Pools, Parameterized Queries and Transactions

Version note: The examples in this post were run with Node.js 26.13.1 (LTS), Express 5.3.0, mssql 12.7.14 (tedious 20.3.9), TypeScript 7.0.2 and SQL Server 2025.

Guess First

In Bookstore, the online bookstore used as the shared example across this site's series, prices live in a decimal(10,2) column in SQL Server, while the API has kept money as whole numbers of kuruş (cents for EUR) since post 12. Hakan added an edition priced at 19.99 EUR. Emre, who writes the repository, reads the price from the database, multiplies it by 100 and drops the fraction:

import sql from "mssql";

const pool = await new sql.ConnectionPool(process.env.DB_CONNECTION_STRING!).connect();

// A EUR edition priced 19.99 in a decimal(10,2) column; how many cents does the API compute?
const { recordset } = await pool.request().query("SELECT CAST(19.99 AS decimal(10, 2)) AS Price");
const price = recordset[0].Price;
console.log(typeof price, price);
console.log(Math.trunc(price * 100));

await pool.close();

The database holds exactly 19.99. What do the two lines print?

node --env-file=.env labs/13/guess.ts

number 19.99
1998

The mssql package turns a decimal column into a JavaScript number; 19.99 has no exact binary floating point representation, 19.99 * 100 comes out as 1998.9999999999998 and Math.trunc deletes a cent. With Dapper the same column lands in a C# decimal and the problem never appears. In this post we move the Bookstore API from memory to BookstoreDb in SQL Server: a connection pool, parameterized queries, a stored procedure, a transaction that writes the order and the stock together, and the export from post 09 streamed straight from SQL Server. We will also see the right way to convert: let SQL Server's own decimal arithmetic compute the kuruş.

mssql and the Connection Pool

The usual way to reach SQL Server from Node.js is the mssql package. It is a wrapper: the actual driver that speaks TDS, SQL Server's wire protocol, is tedious, written in pure JavaScript. With no native dependency it needs no extra installation on Windows, in a Linux container or in CI. The package brings the connection pool (tarn), parameters, transactions and streams together behind one API.

npm install mssql@12.7.14
npm install -D @types/mssql@12.3.0

Connection details come from an environment variable as a connection string, much like the ConnectionStrings section in .NET. mssql understands the format used by .NET's SqlConnection:

# .env (never committed); use a login with only the rights the API needs
DB_CONNECTION_STRING=Server=127.0.0.1,1433;Database=BookstoreDb;User Id=bookstore_app;Password=<password>;Encrypt=true;TrustServerCertificate=true

TrustServerCertificate=true is only for the self-signed certificate on a development machine; in production the server's real certificate must be validated. We open the pool once at startup and every repository shares it:

import sql from "mssql";
import { log } from "../diagnostics/log.ts";

// One pool per process, opened at startup and shared by every repository
export async function openPool(connectionString: string): Promise<sql.ConnectionPool> {
    const config = sql.ConnectionPool.parseConnectionString(connectionString);
    const pool = new sql.ConnectionPool({
        ...config,
        pool: { max: 10, min: 0, idleTimeoutMillis: 30_000 },
    });
    // Errors on idle connections arrive here; without a listener they would crash the process
    pool.on("error", (err: Error) => log("error", "sql pool error", { error: err.message }));
    await pool.connect();
    return pool;
}

The pool settings match mssql's defaults (at most 10 connections, a connection idle for 30 seconds is closed); we wrote them down on purpose so that anyone reading the code sees the numbers. With SqlConnection the pool is managed in the background, driven by the connection string; here the pool is an explicit object and you carry it around. server.ts opens the pool with top-level await, so the API does not start listening before the first connection succeeds:

// Top-level await: the API starts listening only after the first SQL connection succeeded
const pool = await openPool(connectionString);
const app = createApp(createServices(pool));

If the database cannot be reached, the process fails at startup with exit code 1, which beats an API that runs halfway:

ConnectionError: Failed to connect to 127.0.0.1:14399 - Could not connect (sequence)
    at /home/user/bookstore-api/node_modules/mssql/lib/tedious/connection-pool.js:86:17

createServices now takes the pool and wires the SQL Server implementations in place of the in-memory repositories. Services and handlers did not change: in post 12 we made the repository interfaces async on purpose. A measurement shows why we share the pool. The same small query 100 times; first with a new connection every time, then with a shared pool:

node --env-file=.env labs/13/pool-vs-connect.ts

new connection per request: 16619 ms
shared pool:                164 ms

A new connection means a TCP connection, a TLS handshake and a login every time; here that is 166 ms per query against 1.6 ms with the pool. In my measurement SQL Server ran in a container on the same machine; across a network the gap grows.

Schema and Catalog: Loading with a Stored Procedure

The schema lives in sql/schema.sql and can be run more than once. The tables follow the plan: Authors, Books, the Inventory module's StockItems for stock, Orders and OrderItems. Order numbers come from a SEQUENCE:

IF OBJECT_ID(N'dbo.Books', N'U') IS NULL
CREATE TABLE dbo.Books (
    Isbn char(13) NOT NULL CONSTRAINT PK_Books PRIMARY KEY,
    Title nvarchar(200) NOT NULL,
    AuthorId int NOT NULL CONSTRAINT FK_Books_Authors REFERENCES dbo.Authors (Id),
    Price decimal(10, 2) NOT NULL CONSTRAINT CK_Books_Price CHECK (Price > 0),
    Currency char(3) NOT NULL CONSTRAINT CK_Books_Currency CHECK (Currency IN ('TRY', 'EUR'))
);
GO
IF OBJECT_ID(N'dbo.StockItems', N'U') IS NULL
CREATE TABLE dbo.StockItems (
    Isbn char(13) NOT NULL CONSTRAINT PK_StockItems PRIMARY KEY CONSTRAINT FK_StockItems_Books REFERENCES dbo.Books (Isbn),
    Quantity int NOT NULL CONSTRAINT CK_StockItems_Quantity CHECK (Quantity >= 0)
);
GO
IF OBJECT_ID(N'dbo.OrderNumbers', N'SO') IS NULL
CREATE SEQUENCE dbo.OrderNumbers AS int START WITH 1001;

GO is not T-SQL; sqlcmd and SSMS split a script into pieces at those lines. mssql does nothing of the sort, so scripts/db-setup.ts first creates the database through master if it is missing, then splits the script at the GO lines and sends each piece with request().batch():

// GO is a sqlcmd/SSMS separator, not T-SQL: split the script into batches ourselves
const pool = await new sql.ConnectionPool(config).connect();
const script = await readFile(new URL("../sql/schema.sql", import.meta.url), "utf8");
const batches = script.split(/^\s*GO\s*$/m).filter((b) => b.trim() !== "");
for (const batch of batches) {
    await pool.request().batch(batch);
}

npm run db:setup

database BookstoreDb is ready (7 batches)

To load the catalog we wrote a stored procedure: dbo.UpsertBook finds or inserts the author, updates the book, inserts it with its initial stock when it is new, and reports whether it was inserted through an OUTPUT parameter.

CREATE OR ALTER PROCEDURE dbo.UpsertBook
    @Isbn char(13),
    @Title nvarchar(200),
    @Author nvarchar(200),
    @Price decimal(10, 2),
    @Currency char(3),
    @InitialStock int,
    @Inserted bit OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @AuthorId int = (SELECT Id FROM dbo.Authors WHERE Name = @Author);
    IF @AuthorId IS NULL
    BEGIN
        INSERT dbo.Authors (Name) VALUES (@Author);
        SET @AuthorId = SCOPE_IDENTITY();
    END;

    UPDATE dbo.Books SET Title = @Title, AuthorId = @AuthorId, Price = @Price, Currency = @Currency WHERE Isbn = @Isbn;
    SET @Inserted = IIF(@@ROWCOUNT = 0, 1, 0);
    IF @Inserted = 1
    BEGIN
        INSERT dbo.Books (Isbn, Title, AuthorId, Price, Currency) VALUES (@Isbn, @Title, @AuthorId, @Price, @Currency);
        INSERT dbo.StockItems (Isbn, Quantity) VALUES (@Isbn, @InitialStock);
    END;
END;

scripts/load-catalog.ts sends the books in a JSON file to this procedure inside a single transaction. A stored procedure is called with execute; a parameter declared with output() is read from result.output:

// One stored procedure call per book, all inside one transaction: the catalog loads completely or not at all
for (const book of books) {
    const result = await new sql.Request(tx)
        .input("Isbn", sql.Char(13), book.isbn)
        .input("Title", sql.NVarChar(200), book.title)
        .input("Author", sql.NVarChar(200), book.author)
        .input("Price", sql.Decimal(10, 2), toAmount(book.priceMinor))
        .input("Currency", sql.Char(3), book.currency)
        .input("InitialStock", sql.Int, Number(stockText))
        .output("Inserted", sql.Bit)
        .execute("dbo.UpsertBook");
    inserted += result.output.Inserted ? 1 : 0;
}

First the 12-book seed catalog, then the data/catalog.json produced by the CSV import from post 08:

npm run db:load -- src/db/books.json 20
node scripts/import-catalog.ts data/catalog-sample.csv
npm run db:load -- data/catalog.json

loaded 12 books from src/db/books.json: 12 inserted, 0 updated
skipped line 8: invalid ISBN-13 9780141439519
skipped line 9: invalid price abc
imported 6 books into data/catalog.json, 2 rows skipped
loaded 6 books from data/catalog.json: 1 inserted, 5 updated

Five of the six valid CSV rows were already in the catalog and got updated; Çalıkuşu was inserted. nvarchar columns carry Turkish characters without trouble; with varchar, depending on the collation, "ş" and "ı" could have been mangled.

Parameterized Queries and SQL Injection

Pasting text from a search box into SQL is the classic road to SQL injection. The Web Security (OWASP Top 10) series covered injection in general; here we look at its Node counterpart:

import sql from "mssql";

const pool = await new sql.ConnectionPool(process.env.DB_CONNECTION_STRING!).connect();
const search = "x' OR 1=1 --";

// WRONG: user input glued into the SQL text
const glued = await pool.request().query(`SELECT Title FROM dbo.Books WHERE Title LIKE '%${search}%'`);
console.log(`string concatenation: ${glued.recordset.length} rows`);

// Right: the value travels as a parameter, SQL Server never parses it as SQL
const param = await pool.request().input("search", sql.NVarChar(102), `%${search}%`).query("SELECT Title FROM dbo.Books WHERE Title LIKE @search");
console.log(`parameter:            ${param.recordset.length} rows`);

// Tagged template: the same parameter, written as ${search}
const tagged = await pool.request().query`SELECT Title FROM dbo.Books WHERE Title LIKE ${"%" + search + "%"}`;
console.log(`tagged template:      ${tagged.recordset.length} rows`);

// A parameter is still a LIKE pattern: "%%" matches every title unless % and _ are escaped
const wildcard = await pool.request().input("search", sql.NVarChar(102), "%%%").query("SELECT Title FROM dbo.Books WHERE Title LIKE @search");
console.log(`unescaped "%%":       ${wildcard.recordset.length} rows`);

await pool.close();

string concatenation: 13 rows
parameter:            0 rows
tagged template:      0 rows
unescaped "%%":       13 rows

In the glued version ' OR 1=1 -- changed the query's condition and all 13 books came back. In the parameterized version the same text is just a value; no title matches. The tagged template form hides the subtlest trap: written as query`... ${x}`, every ${} becomes a parameter (@param1), but write the same line with parentheses, query(`... ${x}`), and it is an ordinary template literal that glues the value into the SQL text. They look almost the same and behave in opposite ways. When a code review shows query(`, question every ${} inside it.

The last line is a separate issue: a parameter prevents injection but not LIKE's wildcards. A user who types %% gets the whole catalog; on a big table that is an expensive scan that cannot use an index. The repository escapes %, _ and [:

// LIKE treats % and _ as wildcards even inside a parameter, so escape them
function likePattern(text: string): string {
    return `%${text.replace(/[\\%_[]/g, (ch) => `\\${ch}`)}%`;
}

The one thing that cannot be a parameter is a column or table name. In post 12 we made sort an enum; on the SQL side that enum maps to fixed SQL fragments, and user input is never written into ORDER BY:

// Never a column name from user input: the API's sort values map to fixed SQL fragments
const ORDER_BY: Record<BookSort, string> = {
    title: "b.Title",
    price: "b.Price, b.Title",
    "-price": "b.Price DESC, b.Title",
};

The Repository: Hand-Written Mapping, Dapper Style

SqlBookRepository implements post 12's BookRepository interface with SQL. If you have used Dapper, the shape is familiar: you write the SQL, a function turns rows into objects.

// Decimal to integer happens in SQL, where decimal arithmetic is exact
const BOOK_COLUMNS = "b.Isbn, b.Title, a.Name AS Author, CAST(b.Price * 100 AS int) AS PriceMinor, b.Currency";
const FROM_BOOKS = "FROM dbo.Books AS b JOIN dbo.Authors AS a ON a.Id = b.AuthorId";

// Hand-written mapping, as with Dapper: one place that knows the column names
function toBook(row: BookRow): Book {
    return { isbn: row.Isbn, title: row.Title, author: row.Author, priceMinor: row.PriceMinor, currency: row.Currency };
}

async list({ search, author, sort, offset, limit }: BookQuery): Promise<{ items: Book[]; totalCount: number }> {
    const result = await this.#pool
        .request()
        .input("search", sql.NVarChar(102), search ? likePattern(search) : null)
        .input("author", sql.NVarChar(200), author ?? null)
        .input("offset", sql.Int, offset)
        .input("limit", sql.Int, limit)
        .query<BookRow & { TotalCount: number }>(`SELECT ${BOOK_COLUMNS}, COUNT(*) OVER () AS TotalCount ${FROM_BOOKS}
            WHERE (@search IS NULL OR b.Title LIKE @search ESCAPE '\\')
              AND (@author IS NULL OR a.Name = @author)
            ORDER BY ${ORDER_BY[sort]}
            OFFSET @offset ROWS FETCH NEXT @limit ROWS ONLY`);
    const rows = result.recordset;
    // COUNT(*) OVER () repeats the total on every row; an empty page needs a separate count
    const totalCount = rows.length > 0 ? rows[0].TotalCount : offset > 0 ? await this.#count(search, author) : 0;
    return { items: rows.map(toBook), totalCount };
}

A template literal is safe here because every ${} is one of the code's own constants; every value that comes from the user becomes a parameter through input(). Spelling out parameter types (sql.NVarChar(102)) does two jobs: it avoids conversions where SQL Server guesses the type and cannot use the index, and it keeps the query as a single plan in the plan cache. COUNT(*) OVER () fetches the page and the total in one query. The list of ISBNs in an order needs a single parameter too: we send the list as JSON and open it in SQL Server with OPENJSON (WHERE b.Isbn IN (SELECT value FROM OPENJSON(@isbns))). From the outside the API did not change:

curl -s "http://127.0.0.1:3000/api/books?author=Jane%20Austen"

{"items":[{"isbn":"9780141439587","title":"Emma","price":{"amount":245,"currency":"TRY"}},{"isbn":"9780141439686","title":"Persuasion","price":{"amount":199,"currency":"TRY"}},{"isbn":"9780141439518","title":"Pride and Prejudice","price":{"amount":12.99,"currency":"EUR"}}],"page":1,"pageSize":10,"totalCount":3,"totalPages":1,"hasNextPage":false}

Type Conversions: decimal, datetime2 and UTC

The problem in Guess First is not the only one. The bigger version of the same lab, in a process running on Turkish time:

TZ=Europe/Istanbul node --env-file=.env labs/13/types.ts

{
  Price: 19.99,
  PriceMinor: 1999,
  Huge: 12345678901234568,
  HugeText: '12345678901234567.89'
}
{
  AsDatetime: 2026-11-14T10:00:00.007Z,
  AsDatetime2: 2026-11-14T10:00:00.005Z
}
useUTC=true: 2026-11-14T10:00:00.000Z
useUTC=false: 2026-11-14T07:00:00.000Z

Line by line: CAST(Price * 100 AS int) in SQL gives the right result (1999) because the multiplication uses decimal arithmetic. In a wide column such as decimal(19,2), once the value passes Number.MAX_SAFE_INTEGER, about 9 quadrillion, the number loses its last digits; read such values as text (cast to varchar). The old datetime type rounds to 1/300 of a second: 005 milliseconds come back as 007; datetime2(3) keeps the value as it is. The last two lines are the sneakiest: mssql's useUTC option defaults to true and treats dates without an offset as UTC. Set it to false and the same value is taken as the process's local time, shifting by three hours in Istanbul. Bookstore's rule is simple: the column is called CreatedAtUtc, its type is datetime2(3), useUTC stays at its default; conversion to local time happens only on screen. If you need to store the time zone itself, use datetimeoffset.

Writing needs care too: we pass toAmount(34990), that is 349.9, to a sql.Decimal(10, 2) parameter; tedious rounds the value to the scale, so it is stored as 349.90. When reading, we always leave the conversion to SQL.

Transactions: the Order and the Stock Together

In post 08 we said the stock update needs a transaction. When the order lines are written the stock must go down; if one line does not fit the stock, nothing may be written. SqlOrderRepository.add:

// The order, its lines and the stock change commit together or not at all
async add(order: Order): Promise<void> {
    const tx = new sql.Transaction(this.#pool);
    await tx.begin(sql.ISOLATION_LEVEL.READ_COMMITTED);
    let rolledBack = false;
    // SQL Server may roll the transaction back itself (deadlock victim, XACT_ABORT)
    tx.on("rollback", () => {
        rolledBack = true;
    });
    try {
        await new sql.Request(tx)
            .input("id", sql.Int, fromOrderId(order.id))
            .input("customerId", sql.NVarChar(64), order.customerId)
            .input("status", sql.VarChar(20), order.status)
            .input("currency", sql.Char(3), order.currency)
            .input("createdAtUtc", sql.DateTime2(3), new Date(order.createdAtUtc))
            .query("INSERT dbo.Orders (Id, CustomerId, Status, Currency, CreatedAtUtc) VALUES (@id, @customerId, @status, @currency, @createdAtUtc)");

        const missing: string[] = [];
        // One request at a time: a transaction is a single connection
        for (const [index, item] of order.items.entries()) {
            const result = await new sql.Request(tx)
                .input("orderId", sql.Int, fromOrderId(order.id))
                .input("lineNumber", sql.Int, index + 1)
                .input("isbn", sql.Char(13), item.isbn)
                .input("quantity", sql.Int, item.quantity)
                .input("unitPrice", sql.Decimal(10, 2), toAmount(item.unitPriceMinor))
                .query(`UPDATE dbo.StockItems SET Quantity = Quantity - @quantity WHERE Isbn = @isbn AND Quantity >= @quantity;
                        INSERT dbo.OrderItems (OrderId, LineNumber, Isbn, Quantity, UnitPrice) VALUES (@orderId, @lineNumber, @isbn, @quantity, @unitPrice);`);
            // rowsAffected has one entry per statement: [UPDATE, INSERT]
            if (result.rowsAffected[0] === 0) {
                missing.push(item.isbn);
            }
        }
        if (missing.length > 0) {
            throw new OutOfStockError(missing);
        }
        await tx.commit();
    } catch (err) {
        if (!rolledBack) {
            await tx.rollback();
        }
        throw err;
    }
}

What matters is the UPDATE's condition: Quantity >= @quantity. If the stock were read first and decreased afterwards ("check-then-act"), two concurrent orders could both see the same last copy and both succeed. The conditional UPDATE checks and writes in one atomic step; zero affected rows means the stock was not enough. The CHECK (Quantity >= 0) constraint stays as the last line of defence. OutOfStockError is a new domain error; the error middleware maps it to 409 Conflict. Let's try: Ada's order, then Alan asking for more Dune than is left:

HTTP/1.1 201 Created
x-request-id: req-13a
Location: /api/orders/ORD-1001
Content-Type: application/json; charset=utf-8

{"id":"ORD-1001","customerId":"ada","status":"Pending","createdAtUtc":"2026-11-14T19:39:04.807Z","items":[{"isbn":"9780441172719","quantity":3,"unitPrice":{"amount":349.9,"currency":"TRY"},"lineTotal":{"amount":1049.7,"currency":"TRY"}},{"isbn":"9780547928227","quantity":1,"unitPrice":{"amount":289.5,"currency":"TRY"},"lineTotal":{"amount":289.5,"currency":"TRY"}}],"total":{"amount":1339.2,"currency":"TRY"}}
{"type":"https://tools.ietf.org/html/rfc9110#section-15.5.10","title":"Conflict","status":409,"detail":"not enough stock for ISBN: 9780441172719","instance":"/api/orders","traceId":"req-13b"}

Alan's order had a The Hobbit line that was in stock, yet the database shows no trace of it: The Hobbit's stock stayed at 19, and Orders holds a single row. The rollback worked. One side effect: number 1002, reserved for Alan, does not come back, because a SEQUENCE (just like IDENTITY) is not part of the transaction. Gaps in order numbers are normal; if accounting needs consecutive numbers, invoice numbers are generated separately. Now we lower Çalıkuşu's stock to 1 and let Grace and Linus buy the last copy at the same moment:

linus 201
grace 409

One won, the other got 409; the stock stayed at 0 and never went negative. There is no lock on the Node side; SQL Server's row lock does the work.

The for loop in the code is sequential on purpose. A transaction is bound to one connection, and a connection runs one request at a time. That is why parallelizing with Promise.all fails:

{ Books: 13 }
EREQINPROG: Can't acquire connection for the request. There is another request in progress.

My first version of this lab used Promise.all followed straight away by rollback(): Promise.all rejected at the first error while the other request was still running, so the rollback failed too with Can't rollback transaction. There is a request in progress., and the process never exited because the transaction stayed open. The C# habit of running independent queries in parallel with Task.WhenAll does not hold inside a transaction; it is the Node counterpart of Entity Framework Core not allowing concurrent operations on the same DbContext.

Streaming the Export from SQL Server

In post 09 we fed the 5.3-million-row export from a generator that made up rows, and promised to move to the database in post 13. For testing I added 500 thousand orders and 1 million order lines to the tables with a set-based INSERT:

node --env-file=.env labs/13/seed-orders.ts 500000

{ Orders: 500002, OrderItems: 1000004 } 12887 ms

mssql's toReadableStream() turns a request into an object-mode Readable and supports backpressure: when the stream's buffer is full the request pauses and no new rows are pulled from SQL Server. Let's measure the difference first; the same query, as a whole recordset and as a stream:

node --env-file=.env labs/13/export-memory.ts all
node --env-file=.env labs/13/export-memory.ts stream

all: 1000004 rows in 2005 ms, peak RSS 650 MB
stream: 1000004 rows in 1992 ms, peak RSS 139 MB

The time is the same, but RSS is 650 MB against 139 MB. Taking the recordset in one go means holding a million JavaScript objects in the heap at once; the number grows with the table. With a stream, memory does not depend on the row count. The repository's export method:

// Rows flow from SQL Server with backpressure: the request pauses while the stream's buffer is full
exportRows(): Readable {
    const request = new sql.Request(this.#pool);
    const rows = request.toReadableStream({ highWaterMark: 500 });
    request.query(`
        SELECT o.Id, o.CreatedAtUtc, o.CustomerId, i.Isbn, b.Title, i.Quantity,
               CAST(i.UnitPrice * 100 AS int) AS UnitPriceMinor, o.Currency, o.Status
        FROM dbo.Orders AS o
        JOIN dbo.OrderItems AS i ON i.OrderId = o.Id
        JOIN dbo.Books AS b ON b.Isbn = i.Isbn
        ORDER BY o.Id, i.LineNumber`);
    // A destroyed stream does not stop the query by itself (client gone, gzip failed...)
    rows.on("close", () => {
        if (!rows.readableEnded) {
            request.cancel();
        }
    });
    const toExportRow = new Transform({
        objectMode: true,
        transform(row, encoding, callback) {
            const exportRow: OrderExportRow = {
                orderId: toOrderId(row.Id),
                createdAtUtc: (row.CreatedAtUtc as Date).toISOString(),
                customerId: row.CustomerId,
                isbn: row.Isbn,
                title: row.Title,
                quantity: row.Quantity,
                unitPriceMinor: row.UnitPriceMinor,
                currency: row.Currency,
                status: row.Status,
            };
            callback(null, exportRow);
        },
    });
    // pipeline, not pipe: destroying the returned stream also destroys the SQL stream
    pipeline(rows, toExportRow, () => {});
    return toExportRow;
}

The request.cancel() line is not decoration. The mssql documentation says it plainly: even when the stream is destroyed, the request runs to the end and the remaining rows are discarded. Two runs that stop reading after the first 1000 rows of a million-row table:

keep: stopped reading at 259 ms, request ended with "done" at 743 ms
cancel: stopped reading at 96 ms, request ended with "ECANCEL" at 110 ms

Without cancel() the query sent all million rows and held the connection until it finished; with 10 connections in the pool, a few abandoned downloads can lock the pool up. With cancel() SQL Server receives a cancel signal and the connection was back in the pool within 14 ms. The handler did not change; the pipeline(orders.exportRows(), csvTransform(), createGzip(), res) chain from post 09 is now fed by SQL Server:

curl -s -o orders.csv -w "plain: %{http_code} %{size_download} bytes %{time_total}s\n" http://127.0.0.1:3000/api/orders/export
curl -s -H "accept-encoding: gzip" -o orders.csv.gz -w "gzip: %{http_code} %{size_download} bytes %{time_total}s\n" http://127.0.0.1:3000/api/orders/export

plain: 200 97734419 bytes 3.200417s
gzip: 200 7029159 bytes 2.900884s

1,000,004 rows (plus the header), 98 MB plain and 7 MB gzipped CSV. The API process's RSS was 141 MB idle and peaked at 210 MB during the two downloads. The idle figure is higher than in post 09 (85 MB) because mssql and tedious are loaded and the pool is open; what matters is that the peak does not grow with the size of the export.

An ORM or Hand-Written SQL?

This series runs parallel to the Dapper series: we write the SQL and do the mapping by hand. Node has ORMs too, and their SQL Server support varies in maturity. According to the npm registry and the projects' own documentation at the time of writing: Prisma 7 connects to SQL Server through @prisma/adapter-mssql, which uses the mssql package (Prisma 8 is still a release candidate). Drizzle's stable 0.45 has no SQL Server; support arrives in the 1.0 release candidate line. Kysely is a query builder with a SQL Server dialect in its core. I did not run any of them for this post. A short rule for the decision: with a large domain model, many relations and migrations you want to manage from code, take Prisma or Drizzle; when most queries are reports, bulk operations and stored procedures, hand-written SQL with mssql brings fewer surprises. Kysely sits in between for those who want type safety without giving up SQL. If you use PostgreSQL, the Node counterpart is the pg package; the parameter syntax is $1, and the pool and transaction logic is the same. The details are in the PostgreSQL series.

In Production

Open the pool once and close it on shutdown. A new pool per request was the difference between 16 seconds and 0.16 seconds over 100 queries. The shutdown order (first the HTTP server, then the pool) is the subject of the next post.

Size the pool for SQL Server. Each API instance opens at most 10 connections; 8 replicas mean 80 connections. Long exports and streams that are never cancelled hold connections; do not forget request.cancel().

Narrow the application login's rights. Instead of sa, use a login that can only read and write the Bookstore tables and execute procedures; run db:setup, which changes the schema, under a separate, privileged identity.

Know the timeouts. mssql defaults to 15 seconds for connecting and 15 seconds per query. Bulk work (like the seed script) needs a longer requestTimeout; API queries should finish long before that.

Convert money in SQL. Read kuruş with CAST(Price * 100 AS int); never multiply a decimal that has already become a number in JavaScript.

Common Pitfalls

A new connection per request. sql.connect() returns a global pool; calling it in every handler, or opening a new ConnectionPool every time, defeats the pool.

Building SQL by string concatenation. The difference between parenthesized query(`...${x}`) and tagged query`...${x}` is one character, but one of them is an injection hole. Values always go through input() or a tagged template.

Putting SQL fragments into a tagged template. The reverse is a trap too: write query`SELECT ... ${ORDER_BY[sort]}` and the ordering expression also becomes a parameter, and SQL Server reports a syntax error. Use an ordinary template literal to join fixed SQL fragments, parameters for values.

Promise.all inside a transaction. One connection, one request: EREQINPROG.

Not listening to pool.on("error"). When an idle connection drops, the pool emits an error event; as we saw in post 07, an unhandled error event crashes the process.

datetime and useUTC: false. New tables use datetime2, times are UTC, conversion happens only on screen.

A Small Experiment

★☆☆ Replace Math.trunc(price * 100) in labs/13/guess.ts with Math.round. Does it fix 19.99? Why is Math.round still not enough for a four-decimal column such as decimal(10,4)?

★★☆ GET /api/orders/:id fetches two result sets in a single round trip (result.recordsets). Do the same with two separate queries inside Promise.all and see that it does not fail because it runs on the pool. How does it differ from the situation inside a transaction?

★★★ Add order cancellation: POST /api/orders/:id/cancel sets the order's status to Cancelled and puts the stock back. If the status is not Pending, return 409. Prove with a conditional UPDATE ... WHERE Status = 'Pending' that two concurrent cancel requests do not return the stock twice.

The data now lives in SQL Server, but the API still behaves like a script on a development machine: logs are console.log, configuration is scattered, and when SIGTERM arrives open requests are cut off. In the next post we get the API ready for production: typed configuration, structured logging with pino, health checks and graceful shutdown.