@fujitsusweden/mssql-handler

    0.1.2 • Public • Published

    mssql-handler

    What is it

    A library for interacting with MS SQL Server, implemented as a convenience wrapper around mssql.

    How to use it

    This module provides no classes. You don't need to keep track of connection pools or instances. Just call the module functions directly with what you need. Connection pools are created and cached behind the scenes.

    mssqlHandler.init({ log })

    Initialize the module. Example:

    const mssqlHandler = require("@fujitsusweden/mssql-handler");
    mssqlHandler.init({ log });

    The log is an object holding the following log functions: debug, info, warn, error and critical. Each log function should be an async function taking arguments data and req.

    mssqlHandler.beforeExit()

    Close all connection pools.

    In scripts that should terminate, call await mssqlHandler.beforeExit() at the end. Otherwise, asynchronous background jobs can prevent the process from terminating indefinitely.

    mssqlHandler.runDbQuery({ config, query, types, params, req })

    Execute an SQL query and return its result. Example:

    const results = await mssqlHandler.runDbQuery({
      config: config.db_sql,
      query: "SELECT * FROM people WHERE Name = @name;",
      params: { name: "Joe" },
      req,
    });

    Option details:

    • config: The SQL configuration to connect with.

    • query: The SQL query to execute. Use @ to denote parameters.

    • types: Optional. Probably never needed. A map from (some of) the parameter names to the name of their type. For the parameters present in types, this will override the library's attempt at figuring out a suitable type.

    • params: Optional. An object mapping parameter names to values.

    • req: The req object used for logging.

    mssqlHandler.runDbQueryAG({ config, query, types, params, req })

    Asynchronous generator variant of mssqlHandler.runDbQuery, with the same option details. Example:

    for await (const record of mssqlHandler.runDbQueryAG({
      config: config.db_sql,
      query: "SELECT * FROM people WHERE Name = @name;",
      params: { name: "Joe" },
      req,
    })) {
      console.log(record);
    }

    mssqlHandler.runFuncAsTransaction({ config, func, req, ...context })

    Execute a function in the context of an SQL transaction. If the function throws any error, the transaction will be rolled back. Example:

    async function transfer({ runDbQuery, fromAccount, toAccount, amount }) {
      await runDbQuery({
        query: `
          UPDATE accounts
          SET balance = balance - @amount
          WHERE holder = @fromAccount;`,
        params: { amount, fromAccount },
      });
      await runDbQuery({
        query: `
          UPDATE accounts
          SET balance = balance + @amount
          WHERE holder = @toAccount;`,
        params: { amount, toAccount },
      });
      const fromAccountAfterwards = await runDbQuery({
        query: `
          SELECT *
          FROM accounts
          WHERE holder = @fromAccount;`,
        params: { fromAccount },
      });
      // Rollback to prevent overdraft
      assert(0 <= fromAccountAfterwards[0].balance);
    }
    
    await mssqlHandler.runFuncAsTransaction({
      config: config.db_sql,
      func: transfer,
      req,
      fromAccount: "Alice",
      toAccount: "Bob",
      amount: 700,
    });

    Option details:

    • config: The SQL configuration to connect with.

    • func: The function to run within a transaction. It should be asynchronous and take a context argument, an object containing:

      • req

      • runDbQuery: Just like mssqlHandler.runDbQuery except it does not accept a config option and works within the transaction.

      • runDbQueryAG: Just like mssqlHandler.runDbQueryAG except it does not accept a config option and works within the transaction.

      • transaction: The native transaction object. You probably don't need that.

      • ...context: Any other options passed to mssqlHandler.runFuncAsTransaction.

    • req: The req object used for logging.

    • ...context: Optional. Any number of options you want to pass along to func.

    mssqlHandler.escId(id)

    Take the name of an identifier (for e.g. a table, column or index) and return it escaped, ready to be used in an SQL statement. It might be a good convention to use a q_ prefix for variables holding quoted identifiers. Example:

    const tableName = "Table123";
    const q_tableName = mssqlHandler.escId(tableName);
    await mssqlHandler.runDbQuery({
      config,
      req,
      query: `UPDATE ${q_tableName} SET a = 0;`,
    });

    (There is no function for escaping values. Use the params option for that.)

    mssqlHandler.unEscId(q_id)

    The inverse of mssqlHandler.escId.

    mssqlHandler.mssql

    The mssql module, in case you need to by-pass the wrapper.

    Development

    Run ./script without arguments for help.

    Keywords

    none

    Install

    npm i @fujitsusweden/mssql-handler

    DownloadsWeekly Downloads

    2

    Version

    0.1.2

    License

    MIT

    Unpacked Size

    23.6 kB

    Total Files

    13

    Last publish

    Collaborators

    • mrhoffmann
    • tobiasolofsson1988
    • raeven
    • axelsvensson