MSSQL #

Microsoft SQL Server is a very common enterprise database in corporate environments and Microsoft-stack applications. Integrating it with TypeScript using the mssql library gives you access to SQL Server features like stored procedures, table-valued parameters, and output parameters — all with solid type safety. There’s an important difference in how mssql works compared to mysql2: parameters don’t use ? but named parameters with the @ prefix, and column data types must be declared explicitly when using Request. Understanding these differences from the start will save you a lot of confusion when first migrating from MySQL or PostgreSQL to SQL Server.

Installation and Setup #

The main library used is mssql, along with its type definitions which are already included in the same package.

# Install the package
npm install mssql

# Type definitions are already included in mssql
# No separate @types/mssql needed

// tsconfig.json
{
  "compilerOptions": {
    "target": "ES2020",
    "module": "commonjs",
    "lib": ["ES2020"],
    "strict": true,
    "esModuleInterop": true,
    "resolveJsonModule": true,
    "outDir": "./dist",
    "rootDir": "./src"
  }
}

The recommended project structure:

src/
  ├── db/
  │   ├── connection.ts      -- pool configuration and singleton
  │   └── types.ts           -- custom SQL Server types
  ├── models/
  │   ├── user.model.ts
  │   └── produk.model.ts
  ├── repositories/
  │   ├── base.repository.ts
  │   └── user.repository.ts
  └── index.ts

Connection Configuration and Connection Pool #

mssql manages connections through ConnectionPool. Unlike mysql2, the pool in mssql needs to be explicitly connected before it can be used.

import sql, { config as SqlConfig, ConnectionPool } from 'mssql';

// Connection configuration
const dbConfig: SqlConfig = {
  server: process.env.DB_HOST ?? 'localhost',
  port: Number(process.env.DB_PORT ?? 1433),
  database: process.env.DB_NAME ?? 'toko_online',
  user: process.env.DB_USER ?? 'sa',
  password: process.env.DB_PASSWORD ?? '',
  options: {
    encrypt: true,              // required for Azure SQL
    trustServerCertificate: true, // for local development
    enableArithAbort: true,     // recommended for SQL Server 2017+
  },
  pool: {
    max: 10,                    // maximum simultaneous connections
    min: 0,                     // minimum idle connections
    idleTimeoutMillis: 30000,   // close idle connections after 30 seconds
  },
  requestTimeout: 30000,        // per-query timeout in ms
  connectionTimeout: 15000,     // connection opening timeout
};

// Singleton pool
let poolInstance: ConnectionPool | null = null;

export async function getPool(): Promise<ConnectionPool> {
  if (!poolInstance) {
    poolInstance = new sql.ConnectionPool(dbConfig);
    await poolInstance.connect();

    // Handle pool errors
    poolInstance.on('error', (err) => {
      console.error('SQL Pool error:', err);
      poolInstance = null;  // reset so the connection can be recreated
    });
  }
  return poolInstance;
}

export async function closePool(): Promise<void> {
  if (poolInstance) {
    await poolInstance.close();
    poolInstance = null;
  }
}

// Graceful shutdown
process.on('SIGTERM', closePool);
process.on('SIGINT', closePool);

flowchart TD
    A[App starts] --> B[new ConnectionPool\ncreate config]
    B --> C[pool.connect]
    C --> D{Connection\nsuccessful?}
    D -- Yes --> E[Pool ready\nto accept requests]
    D -- No --> F[Throw error\nconnection failed]
    E --> G[Request comes in]
    G --> H[pool.request]
    H --> I[Execute query]
    I --> J[Connection returns\nto the pool]
    J --> E

mssql requires an explicit pool.connect() call before the pool can be used. If you call pool.request() directly without connect(), you’ll get a "Connection not yet open" error. The singleton pattern that calls connect() on first initialization is the safest way to avoid this problem.

Defining Types for Database Rows #

Unlike mysql2 which uses RowDataPacket, result types in mssql are simpler — you just define regular interfaces and use them as type parameters.

import sql from 'mssql';

// Type for the users table
interface User {
  id: number;
  nama: string;
  email: string;
  passwordHash: string;
  role: 'admin' | 'user' | 'moderator';
  aktif: boolean;
  dibuatPada: Date;
  diperbarui_pada: Date | null;
}

interface Produk {
  id: number;
  nama: string;
  deskripsi: string | null;
  harga: number;         // DECIMAL in SQL Server — watch out for precision
  stok: number;
  kategoriId: number;
  gambarUrl: string | null;
  dibuatPada: Date;
}

// Mapping TypeScript types to SQL Server types
// Important for input parameters
const SQL_TYPES = {
  INT: sql.Int,
  BIGINT: sql.BigInt,
  VARCHAR: (n: number) => sql.VarChar(n),
  NVARCHAR: (n: number) => sql.NVarChar(n),
  BIT: sql.Bit,                  // boolean
  DECIMAL: (p: number, s: number) => sql.Decimal(p, s),
  FLOAT: sql.Float,
  DATETIME2: sql.DateTime2,
  UNIQUEIDENTIFIER: sql.UniqueIdentifier,
} as const;

SQL Server data types have TypeScript equivalents you need to watch out for:

SQL Server Type	TypeScript Type	Notes
`INT`, `SMALLINT`	`number`	32-bit integer
`BIGINT`	`string` \| `number`	Can overflow JS numbers
`VARCHAR(n)`, `NVARCHAR(n)`	`string`	The N prefix = Unicode
`BIT`	`boolean`	0/1 auto-converted
`DECIMAL(p,s)`, `NUMERIC`	`number`	Watch out for floating point precision
`DATETIME2`, `DATETIME`	`Date`	Auto-converted by the driver
`UNIQUEIDENTIFIER`	`string`	GUID as a string
`VARBINARY`	`Buffer`	Binary data

Basic Queries — SELECT #

How parameterized queries work in mssql differs significantly from MySQL. Parameters use the named syntax @namaParam and must be declared with an explicit SQL data type.

import { getPool } from './db/connection';
import sql from 'mssql';

// ── Simple SELECT without parameters
async function semuaUser(): Promise<User[]> {
  const pool = await getPool();
  const result = await pool.request()
    .query<User>('SELECT id, nama, email, role FROM users WHERE aktif = 1');
  return result.recordset;  // array of rows
}

// ── SELECT with parameters — MUST declare the type!
async function cariUserById(id: number): Promise<User | null> {
  const pool = await getPool();
  const result = await pool.request()
    .input('id', sql.Int, id)          // name, SQL type, value
    .query<User>('SELECT * FROM users WHERE id = @id AND aktif = 1');
  return result.recordset[0] ?? null;
}

// ── SELECT with multiple parameters
async function produkByKategoriDanHarga(
  kategoriId: number,
  hargaMin: number,
  hargaMax: number
): Promise<Produk[]> {
  const pool = await getPool();
  const result = await pool.request()
    .input('kategoriId', sql.Int, kategoriId)
    .input('hargaMin', sql.Decimal(18, 2), hargaMin)
    .input('hargaMax', sql.Decimal(18, 2), hargaMax)
    .query<Produk>(`
      SELECT *
      FROM produk
      WHERE kategori_id = @kategoriId
        AND harga BETWEEN @hargaMin AND @hargaMax
        AND stok > 0
      ORDER BY harga ASC
    `);
  return result.recordset;
}

// ── SELECT with LIKE
async function cariProdukByNama(keyword: string): Promise<Produk[]> {
  const pool = await getPool();
  // Escape SQL Server's special LIKE characters: %, _, [, ^
  const safeKeyword = keyword.replace(/[%_\[\^]/g, '[$&]');
  const result = await pool.request()
    .input('keyword', sql.NVarChar(200), `%${safeKeyword}%`)
    .query<Produk>(`
      SELECT TOP 20 * FROM produk
      WHERE nama LIKE @keyword
      ORDER BY nama
    `);
  return result.recordset;
}

// ANTI-PATTERN: direct string concatenation
async function cariUserTidakAman(email: string): Promise<User[]> {
  const pool = await getPool();
  // Vulnerable to SQL injection!
  const result = await pool.request()
    .query<User>(`SELECT * FROM users WHERE email = '${email}'`);
  return result.recordset;
}

// CORRECT: always declare parameters with .input()
async function cariUserAman(email: string): Promise<User | null> {
  const pool = await getPool();
  const result = await pool.request()
    .input('email', sql.NVarChar(255), email)
    .query<User>('SELECT * FROM users WHERE email = @email');
  return result.recordset[0] ?? null;
}

INSERT Operations #

SQL Server provides the OUTPUT clause, which is very useful for getting back column values from newly inserted rows — including auto-increment IDs and columns with default values.

import sql from 'mssql';
import { getPool } from './db/connection';

interface InputUser {
  nama: string;
  email: string;
  passwordHash: string;
  role?: 'admin' | 'user' | 'moderator';
}

// ── INSERT with OUTPUT to get the ID
async function buatUser(input: InputUser): Promise<number> {
  const pool = await getPool();
  const result = await pool.request()
    .input('nama', sql.NVarChar(100), input.nama)
    .input('email', sql.NVarChar(255), input.email)
    .input('passwordHash', sql.VarChar(255), input.passwordHash)
    .input('role', sql.VarChar(20), input.role ?? 'user')
    .query<{ id: number }>(`
      INSERT INTO users (nama, email, password_hash, role, aktif, dibuat_pada)
      OUTPUT INSERTED.id
      VALUES (@nama, @email, @passwordHash, @role, 1, GETDATE())
    `);
  return result.recordset[0].id;
}

// ── INSERT and return the entire newly created row
async function buatProduk(
  input: Omit<Produk, 'id' | 'dibuatPada'>
): Promise<Produk> {
  const pool = await getPool();
  const result = await pool.request()
    .input('nama', sql.NVarChar(200), input.nama)
    .input('deskripsi', sql.NVarChar(sql.MAX), input.deskripsi)
    .input('harga', sql.Decimal(18, 2), input.harga)
    .input('stok', sql.Int, input.stok)
    .input('kategoriId', sql.Int, input.kategoriId)
    .query<Produk>(`
      INSERT INTO produk (nama, deskripsi, harga, stok, kategori_id, dibuat_pada)
      OUTPUT INSERTED.*
      VALUES (@nama, @deskripsi, @harga, @stok, @kategoriId, GETDATE())
    `);
  return result.recordset[0];
}

// ── Batch INSERT with Table-Valued Parameters (TVP)
// TVP is a SQL Server feature for inserting many rows at once efficiently
async function buatBanyakProduk(
  produkList: Array<{ nama: string; harga: number; stok: number; kategoriId: number }>
): Promise<number> {
  const pool = await getPool();

  // Build the TVP table
  const tvp = new sql.Table();
  tvp.columns.add('nama', sql.NVarChar(200), { nullable: false });
  tvp.columns.add('harga', sql.Decimal(18, 2), { nullable: false });
  tvp.columns.add('stok', sql.Int, { nullable: false });
  tvp.columns.add('kategori_id', sql.Int, { nullable: false });

  for (const p of produkList) {
    tvp.rows.add(p.nama, p.harga, p.stok, p.kategoriId);
  }

  const result = await pool.request()
    .input('produkList', tvp)
    .execute('sp_InsertBanyakProduk');  // stored procedure accepting a TVP

  return result.rowsAffected[0];
}

sequenceDiagram
    participant App
    participant Pool
    participant SqlServer

    App->>Pool: request().input('nama', type, val)
    Note over Pool: Parameters added to the request
    App->>Pool: .query(INSERT...OUTPUT INSERTED.*)
    Pool->>SqlServer: Send the query with parameters
    SqlServer-->>Pool: recordset with the inserted rows
    Pool-->>App: result.recordset[0]
    Note over App: Gets the whole new row's data

UPDATE and DELETE Operations #

UPDATE in SQL Server can also leverage OUTPUT to get values before and after the change — useful for audit logs.

import sql from 'mssql';
import { getPool } from './db/connection';

// ── UPDATE with OUTPUT — know the before and after values
async function updateStokProduk(
  id: number,
  deltaStok: number
): Promise<{ stokLama: number; stokBaru: number } | null> {
  const pool = await getPool();
  const result = await pool.request()
    .input('id', sql.Int, id)
    .input('delta', sql.Int, deltaStok)
    .query<{ stokLama: number; stokBaru: number }>(`
      UPDATE produk
      SET stok = stok + @delta
      OUTPUT DELETED.stok AS stokLama,
             INSERTED.stok AS stokBaru
      WHERE id = @id AND (stok + @delta) >= 0
    `);

  return result.recordset[0] ?? null;  // null if stock is insufficient
}

// ── UPDATE specific fields
async function updateUser(
  id: number,
  data: Partial<Pick<User, 'nama' | 'email'>>
): Promise<boolean> {
  const entries = Object.entries(data).filter(([, v]) => v !== undefined);
  if (entries.length === 0) return false;

  // Whitelist of updatable fields
  const allowedFields: Record<string, [string, unknown]> = {
    nama: ['nama', sql.NVarChar(100)],
    email: ['email', sql.NVarChar(255)],
  };

  const pool = await getPool();
  const request = pool.request().input('id', sql.Int, id);
  const setClauses: string[] = [];

  for (const [key, value] of entries) {
    if (!allowedFields[key]) continue;
    const [sqlType] = allowedFields[key];
    request.input(`p_${key}`, sqlType as sql.ISqlType, value);
    setClauses.push(`${key} = @p_${key}`);
  }

  if (setClauses.length === 0) return false;

  const result = await request.query(`
    UPDATE users
    SET ${setClauses.join(', ')}, diperbarui_pada = GETDATE()
    WHERE id = @id
  `);

  return result.rowsAffected[0] > 0;
}

// ── DELETE — hard delete
async function hapusProduk(id: number): Promise<boolean> {
  const pool = await getPool();
  const result = await pool.request()
    .input('id', sql.Int, id)
    .query('DELETE FROM produk WHERE id = @id');
  return result.rowsAffected[0] > 0;
}

// ── Soft delete
async function softDeleteUser(id: number): Promise<boolean> {
  const pool = await getPool();
  const result = await pool.request()
    .input('id', sql.Int, id)
    .query(`
      UPDATE users
      SET aktif = 0, dihapus_pada = GETDATE()
      WHERE id = @id AND aktif = 1
    `);
  return result.rowsAffected[0] > 0;
}

Transactions #

Transactions in mssql use a Transaction object that must be committed or rolled back explicitly. Every Request inside a transaction must be created from the Transaction object, not directly from the pool.

import sql, { Transaction, Request } from 'mssql';
import { getPool } from './db/connection';

interface ItemPesanan {
  produkId: number;
  jumlah: number;
  hargaSatuan: number;
}

async function buatPesanan(
  userId: number,
  items: ItemPesanan[]
): Promise<number> {
  const pool = await getPool();
  const transaction = new sql.Transaction(pool);

  try {
    await transaction.begin();

    const totalHarga = items.reduce(
      (sum, item) => sum + item.hargaSatuan * item.jumlah, 0
    );

    // 1. Create the order record
    const r1 = await new sql.Request(transaction)
      .input('userId', sql.Int, userId)
      .input('total', sql.Decimal(18, 2), totalHarga)
      .query<{ id: number }>(`
        INSERT INTO pesanan (user_id, total_harga, status, dibuat_pada)
        OUTPUT INSERTED.id
        VALUES (@userId, @total, 'pending', GETDATE())
      `);

    const pesananId = r1.recordset[0].id;

    // 2. Process each item
    for (const item of items) {
      // Insert the order item
      await new sql.Request(transaction)
        .input('pesananId', sql.Int, pesananId)
        .input('produkId', sql.Int, item.produkId)
        .input('jumlah', sql.Int, item.jumlah)
        .input('harga', sql.Decimal(18, 2), item.hargaSatuan)
        .query(`
          INSERT INTO pesanan_item
            (pesanan_id, produk_id, jumlah, harga_satuan)
          VALUES (@pesananId, @produkId, @jumlah, @harga)
        `);

      // Decrease stock — UPDATE fails if stock is insufficient
      const rStok = await new sql.Request(transaction)
        .input('jumlah', sql.Int, item.jumlah)
        .input('produkId', sql.Int, item.produkId)
        .query(`
          UPDATE produk
          SET stok = stok - @jumlah
          WHERE id = @produkId AND stok >= @jumlah
        `);

      if (rStok.rowsAffected[0] === 0) {
        await transaction.rollback();
        throw new Error(`Stok tidak cukup untuk produk ID ${item.produkId}`);
      }
    }

    await transaction.commit();
    return pesananId;

  } catch (error) {
    // Rollback if not already rolled back (e.g. unexpected error)
    try {
      await transaction.rollback();
    } catch {
      // Ignore the rollback error if the transaction was already rolled back
    }
    throw error;
  }
}

Helper to simplify the transaction pattern:

async function withTransaction<T>(
  callback: (transaction: Transaction) => Promise<T>
): Promise<T> {
  const pool = await getPool();
  const transaction = new sql.Transaction(pool);
  await transaction.begin();
  try {
    const result = await callback(transaction);
    await transaction.commit();
    return result;
  } catch (error) {
    try { await transaction.rollback(); } catch { /* already rolled back */ }
    throw error;
  }
}

// Usage
const pesananId = await withTransaction(async (trx) => {
  const r1 = await new sql.Request(trx)
    .input('userId', sql.Int, userId)
    .query<{ id: number }>(`
      INSERT INTO pesanan (user_id) OUTPUT INSERTED.id VALUES (@userId)
    `);
  return r1.recordset[0].id;
});

flowchart TD
    A[new sql.Transaction pool] --> B[transaction.begin]
    B --> C[new sql.Request transaction]
    C --> D[Execute query]
    D --> E{Error?}
    E -- No --> F{More\noperations?}
    F -- Yes --> C
    F -- No --> G[transaction.commit]
    E -- Yes --> H[transaction.rollback]
    G --> I[Successfully done]
    H --> J[Re-throw the error]

Stored Procedures #

SQL Server makes heavy use of stored procedures. mssql supports calling SPs with input, output, and return value parameters.

import sql from 'mssql';
import { getPool } from './db/connection';

// ── Calling a simple stored procedure
async function spCariUser(email: string): Promise<User | null> {
  const pool = await getPool();
  const result = await pool.request()
    .input('email', sql.NVarChar(255), email)
    .execute<User>('sp_CariUserByEmail');
  return result.recordset[0] ?? null;
}

// ── SP with OUTPUT parameters
async function spDaftarUser(
  input: InputUser
): Promise<{ userId: number; pesan: string }> {
  const pool = await getPool();
  const result = await pool.request()
    .input('nama', sql.NVarChar(100), input.nama)
    .input('email', sql.NVarChar(255), input.email)
    .input('passwordHash', sql.VarChar(255), input.passwordHash)
    .output('userId', sql.Int)           // declare the OUTPUT
    .output('pesan', sql.NVarChar(500))
    .execute('sp_DaftarUser');

  return {
    userId: result.output.userId as number,
    pesan: result.output.pesan as string,
  };
}

// ── SP with a return value (RETURN statement in the SP)
async function spCekStok(produkId: number): Promise<number> {
  const pool = await getPool();
  const result = await pool.request()
    .input('produkId', sql.Int, produkId)
    .execute('sp_CekStok');

  // The return value is in result.returnValue
  return result.returnValue as number;
}

Error Handling #

SQL Server has different error codes than MySQL. It’s important to map specific errors to meaningful domain errors.

import sql from 'mssql';

// SQL Server error codes you'll often encounter
const MSSQL_ERRORS = {
  UNIQUE_VIOLATION: 2627,        // UNIQUE constraint violation
  PK_VIOLATION: 2627,            // PRIMARY KEY violation (same as unique)
  FK_VIOLATION: 547,             // FOREIGN KEY constraint violation
  NULL_VIOLATION: 515,           // Cannot insert NULL
  VALUE_TOO_LONG: 8152,          // String or binary data would be truncated
  DEADLOCK: 1205,                // Transaction deadlock
  TIMEOUT: -2,                   // Query timeout
  LOGIN_FAILED: 18456,           // Login failed
  OBJECT_NOT_FOUND: 208,         // Invalid object name (table doesn't exist)
} as const;

class DatabaseError extends Error {
  constructor(
    message: string,
    public readonly code?: number
  ) {
    super(message);
    this.name = 'DatabaseError';
  }
}

class DuplicateEntryError extends DatabaseError {
  constructor(detail?: string) {
    super(`Duplikat data${detail ? `: ${detail}` : ''}`);
    this.name = 'DuplicateEntryError';
  }
}

class ForeignKeyError extends DatabaseError {
  constructor(message = 'Referensi data tidak valid') {
    super(message);
    this.name = 'ForeignKeyError';
  }
}

class DeadlockError extends DatabaseError {
  constructor() {
    super('Deadlock terdeteksi — coba ulangi operasi');
    this.name = 'DeadlockError';
  }
}

function tanganiMssqlError(error: unknown): never {
  if (error instanceof sql.RequestError) {
    switch (error.number) {
      case MSSQL_ERRORS.UNIQUE_VIOLATION:
      case MSSQL_ERRORS.PK_VIOLATION:
        throw new DuplicateEntryError(error.message);
      case MSSQL_ERRORS.FK_VIOLATION:
        throw new ForeignKeyError();
      case MSSQL_ERRORS.NULL_VIOLATION:
        throw new DatabaseError('Field wajib tidak boleh kosong', error.number);
      case MSSQL_ERRORS.VALUE_TOO_LONG:
        throw new DatabaseError('Data melebihi panjang maksimum kolom', error.number);
      case MSSQL_ERRORS.DEADLOCK:
        throw new DeadlockError();
    }
  }
  throw new DatabaseError(`Error database: ${String(error)}`);
}

// Retry decorator for deadlocks — deadlocks can be retried
async function withDeadlockRetry<T>(
  fn: () => Promise<T>,
  maxRetry = 3,
  delayMs = 100
): Promise<T> {
  for (let attempt = 1; attempt <= maxRetry; attempt++) {
    try {
      return await fn();
    } catch (error) {
      if (error instanceof DeadlockError && attempt < maxRetry) {
        await new Promise(res => setTimeout(res, delayMs * attempt));
        continue;
      }
      throw error;
    }
  }
  throw new Error('Unreachable');
}

// Usage in a repository
async function buatUserAman(input: InputUser): Promise<number> {
  try {
    return await buatUser(input);
  } catch (error) {
    tanganiMssqlError(error);
  }
}

Pagination with SQL Server #

SQL Server uses the OFFSET ... FETCH NEXT syntax available since SQL Server 2012. For older versions, use ROW_NUMBER().

import sql from 'mssql';
import { getPool } from './db/connection';

type UserSortField = 'nama' | 'email' | 'dibuat_pada';
type SortOrder = 'ASC' | 'DESC';

const ALLOWED_SORT: Set<string> = new Set<UserSortField>([
  'nama', 'email', 'dibuat_pada'
]);

interface PaginasiResult<T> {
  data: T[];
  total: number;
  halaman: number;
  perHalaman: number;
  totalHalaman: number;
}

async function getUserPaginasi(
  halaman: number,
  perHalaman: number,
  sortBy: UserSortField = 'dibuat_pada',
  sortOrder: SortOrder = 'DESC'
): Promise<PaginasiResult<User>> {
  // Whitelist validation — REQUIRED for interpolated columns
  const safeSort = ALLOWED_SORT.has(sortBy) ? sortBy : 'dibuat_pada';
  const safeOrder: SortOrder = sortOrder === 'ASC' ? 'ASC' : 'DESC';
  const offset = (halaman - 1) * perHalaman;

  const pool = await getPool();
  const result = await pool.request()
    .input('offset', sql.Int, offset)
    .input('fetch', sql.Int, perHalaman)
    .query<User & { totalRows: number }>(`
      SELECT
        id, nama, email, role, aktif, dibuat_pada,
        COUNT(*) OVER() AS totalRows
      FROM users
      WHERE aktif = 1
      ORDER BY ${safeSort} ${safeOrder}
      OFFSET @offset ROWS
      FETCH NEXT @fetch ROWS ONLY
    `);

  const total = result.recordset[0]?.totalRows ?? 0;

  return {
    data: result.recordset.map(({ totalRows: _, ...user }) => user as User),
    total,
    halaman,
    perHalaman,
    totalHalaman: Math.ceil(total / perHalaman),
  };
}

ORDER BY is required when using OFFSET ... FETCH NEXT in SQL Server. A query without ORDER BY produces a "ORDER BY clause is required" error. This differs from MySQL which doesn’t require ORDER BY for LIMIT/OFFSET.

When to Switch to Another Approach #

Keep using mssql directly if:
  ✓ Enterprise applications making heavy use of stored procedures
  ✓ Complex queries needing full control over T-SQL
  ✓ Need SQL Server-specific features: TVP, OUTPUT, MERGE
  ✓ The team is familiar with T-SQL and doesn't need ORM abstraction
  ✓ Critical performance with DBA-optimized queries

Consider an ORM / Query Builder if:
  ✗ Many models with complex relationships — TypeORM with the SQL Server driver
  ✗ Multi-database (MSSQL + PostgreSQL) — TypeORM or Knex.js
  ✗ Rapid prototyping — Prisma (supports SQL Server since version 2.10)
  ✗ The team is less familiar with T-SQL — ORMs help but can hide problems
  ✗ Need automatic migrations — TypeORM migrations or Prisma migrate

Aspect	`mssql`	TypeORM	Prisma
SQL control	Full	Partial	Limited
Type safety	Manual	Decorators	Automatic from schema
Stored procedures	✓ Native	✓	Limited
OUTPUT clause	✓	Indirect	No
Migrations	Manual	Automatic	Automatic
Learning curve	Low	Medium	Low

Summary #

pool.connect() must be called before any request — unlike mysql2, the mssql pool needs to be connected explicitly; use a singleton pattern with connect() on first initialization.
Parameters use @namaParam, not ? — declare every parameter with .input('nama', sql.TipeSQL, nilai) before calling .query() or .execute().
SQL types must be declared explicitly — sql.Int, sql.NVarChar(255), sql.Decimal(18,2); without this the driver can mis-map types and cause truncation or data type errors.
Use OUTPUT INSERTED.* to get the newly inserted row’s data — more idiomatic in SQL Server than a separate SELECT SCOPE_IDENTITY().
OUTPUT DELETED.* and INSERTED.* are available in UPDATE for audit logs — a unique SQL Server feature not present in MySQL.
new sql.Request(transaction) for queries inside a transaction — requests aren’t created directly from the pool; always create them from the Transaction object.
ORDER BY is required for OFFSET...FETCH NEXT — SQL Server errors if there’s no ORDER BY, unlike MySQL which is tolerant.
Validate dynamic column names with a whitelist — column names can’t be parameterized and must be validated from a Set before interpolating into a query.
Retry for deadlocks — deadlocks (error.number === 1205) are recoverable conditions; implement retry logic with backoff to improve application resilience.
sql.NVarChar for Unicode text — use NVarChar (not VarChar) for strings that might contain non-ASCII characters like Arabic names or other scripts.

← Previous: MySQL Next: Oracle →