SQL Server

SQL Server 2022 Express
npm i @pi-r/mssql

Interface

import type { ConnectionConfiguration } from 'tedious';
import type { DataType, ParameterData } from "tedious/lib/data-type";
import type { PoolConfig } from "@e-mc/mssql/types/pool"; // From "@types/tedious-connection-pool"

interface MSSQLDataSource extends DbDataSource {
    source: "mssql";
    credential: string | MSSQLCredential;
    query?: string;
    params?: MSSQLRequestParameters | MSSQLRequestWithOutputParameters;
    storedProc?: boolean;
    usePool?: boolean | string | PoolConfig; // External pool provider configurable options
}

interface MSSQLCredential extends ServerAuth, ConnectionConfiguration {/* Empty */}

interface MSSQLRequestWithOutputParameters {
    input?: MSSQLRequestParameters;
    output?: MSSQLRequestParameters;
}

interface MSSQLRequestParameterValue {
    name?: string;
    value?: unknown;
    type?: DataType;
    options?: ParameterData;
}

type MSSQLRequestParameters = MSSQLRequestParameterValue[] | ObjectMap<MSSQLRequestParameterValue>;

Pool

import type { PoolConfig } from "@e-mc/mssql/types/pool";

interface PoolConfig {
    min?: number; // min
    max?: number; // max
    idle?: number; // idleTimeout
    queue_idle?: number; // acquireTimeout
}

Authentication

Connection

{
  "dataSource": {
    "uri": "<username>:<password>@localhost:1433/<database>", // authentication.options@server:options
    /* OR */
    "credential": "main", // squared.db.json
    /* OR */
    "credential": {
      "server": "localhost",
      "options": {
        "port": 1433,
        "database": "example",
        "trustServerCertificate": true, // Local development

        /* Azure */
        "encrypt": true,
        "enableArithAbort": true,
        "connectionIsolationLevel": 2 // ISOLATION_LEVEL.READ_UNCOMMITTED
      },
      "authentication": {
        "type": "default",
        "options": {
          "userName": "**********",
          "password": "**********"
        }
      }
    }
  }
}

Example usage

{
  "selector": "img",
  "type": "attribute",
  "dataSource": {
    "source": "mssql",
    "credential": {/* Authentication */},

    "query": "SELECT * FROM table WHERE id = @name AND value = @value", // db.execSql
    "query": "./path/to/statement.sql", // Extension ".sql" (settings.directory.sql + users/username/?)
    /* OR */
    "query": "uspGetItems", // db.callProcedure
    "storedProc": true,

    "params": { "a": { "value": "1", "type": "VarChar", "options": { "length": 50 } }, "b": 2 /* Implicit: Int */ }, // MSSQLRequestParameters
    "params": [{ "name": "c", "type": "Decimal", "value": 12.345, "options": { "precision": 10, "scale": 2 } }],
    "params": [{ "name": "d", "type": "TVP", "value": { "columns": [/* MSSQLRequestParameters[] */], "rows": [/* unknown[][] */] }],
    "params": {
      "input": {/* MSSQLRequestParameters */}, // Two keys only for object detection (required)
      "output": {/* MSSQLRequestParameters */} // Last row i{/* MSSQLRequestParameters */}n result (data["__returnvalue__"] = true)
    },

    /* Result: { "item_src": "mssql.png", "item_alt": "SQL Server" } */
    "value": {
      "src": "item_src",
      "alt": "item_alt"
    },

    "usePool": true,
    "usePool": { // tedious-connection-pool2
      "min": 0,
      "max": 10
    }
  }
}