FILESTREAM

SQL Server FILESTREAM allows storing large binary data (BLOBs) directly on the file system while maintaining transactional consistency.

Windows Only

FILESTREAM is only available on Windows with the Microsoft OLE DB Driver 19 for SQL Server installed.

Check Availability

const ffi = mssql.getFfi();
if (ffi.filestreamAvailable()) {
  console.log("FILESTREAM is available");
}

Open a Handle

await using cn = await mssql.connect("Server=localhost;...");

// Must be in a transaction
await using tx = await cn.beginTransaction();

// Get the path_name and transaction context
const row = await cn.querySingle<{ path: string; ctx: Uint8Array }>(
  "SELECT file_data.PathName() AS path, GET_FILESTREAM_TRANSACTION_CONTEXT() AS ctx FROM Documents WHERE id = @id",
  { id: docId },
  { transaction: tx }
);

// Open the FILESTREAM handle
const handle = await cn.openFilestream(row.path, row.ctx, "read");

Read Data

const chunk = await handle.read(65536); // Read up to 64KB
if (chunk) {
  // chunk is a Uint8Array
}

Write Data

const data = new Uint8Array([/* ... */]);
const written = await handle.write(data);

Close

handle.close();
await tx.commit();