@infinitaslearning/systemic-mssql

@infinitaslearning/systemic-mssql

Systemic mssql is a systemic component for the MS SQL. Its goal is to help you connect to a MS SQL database.

This library:

• includes straightforward configuration to connect to the database
• exposes query interface that prefers prepared statements for automatic sanitation to prevent sql injection
• includes easy to use sql-tag helper
• includes async iterable streaming query function to query large data sets without memory exhaustion issues
• includes transaction helpers
• exposed full connectionPool Request object for advanced scenarios
• allows setting up error handler to listen to internal connection pool errors

We've created this library as an alternative to the existing systemic-mssql library, which is very opiniated, and doesn't meet our needs.

Add Mssql to Systemic System

import System from 'systemic'
import initDb from '@infinitaslearning/systemic-mssql'

new System()
  .configure({
    mssql: {
      connection: 'my-connection-string',
    },
  })
  .add('mssql', initDb())
  .dependsOn('config')
  .start((err, components) => {
    // Do stuff with components.mssql
  })

Connection in the configuration can either be a mssql connection string or a full mssql ConnectionPool config.

Usage

Query

import { Database } from '@infinitaslearning/systemic-mssql'

interface Book {
  id: string
  title: string
}

const initBookStore = (database: Database) => ({
  getBook: (id: string) => database.query<Book>`
  SELECT *
  FROM Books
  WHERE id = ${id}`,
})

or

import { Database } from '@infinitaslearning/systemic-mssql'
import { bookQuery } from './queries'

const initBookStore = (database: Database) => ({
  getBook: (id: string) => database.query(bookQuery(id)),
})

All query functions use mssql tagged template literals to prevent sql injection

Re-usable queries

import { sql } from '@infinitaslearning/systemic-mssql'

interface Book {
  id: string
  title: string
}

export bookQuery = (id: string) => sql`
  SELECT *
  FROM Books
  WHERE id = ${id}`

Query big datasets

If you plan to work with large amount of rows, you should always use streaming to prevent memory exhaustion issues. The streamingQuery function wraps the mssql streaming capability and exposes it as an easy to use async iterable.

import { Database, sql } from '@infinitaslearning/systemic-mssql'

const initBookStore = (database: Database) => ({
  getBooks: () => database.streamingQuery(sql`SELECT * FROM Books`, { size: 500 }),
})

The second argument to the streamingQuery function is optional can be used to set the maximum size of the buffer (in number of records). When the buffer is full the request is automatically paused until all retreived records have been read.

Here's an example of using the result of a streamingQuery:

import { BookStore } from './stores'

const initBooksDomain = (store: BookStore) => ({
  doSomething: async () => {
    for await (const book of store.getBooks()) {
      // do something with the book
    }
  },
})

Transactions

The withTransaction function allows you to write clean code that's bundled in a single transaction that's automatically commited on success. By default the entire transaction is rolled back on error, but that behaviour can be overriden by providing and onTransactionError callback.

import { Database } from '@infinitaslearning/systemic-mssql'

const initStore = (database: Database) => ({
  doSomething: () => {
    database.withTransaction((transaction) => {
      const request = transaction.request()
      // ... execute mulitple request within same transaction and/or include other related logic
    })
  },
})

WithTransaction throws if an error occures while connecting to the database or starting the transaction, therefore in the error callback it's safe to assume that there's an active database connection.

import { Database } from '@infinitaslearning/systemic-mssql'
import { ISOLATION_LEVEL } from 'mssql'

const initStore = (database: Database) => ({
  doSomething: () => {
    database.withTransaction(
      (transaction) => {
        // normal transaction flow
      },
      {
        isolationLevel: ISOLATION_LEVEL.READ_UNCOMMITTED,
        onTransactionError: (error, transaction) => {
          // mitigating actions
        },
      },
    )
  },
})

Error handling

The onError function can be used to attach an error callback to the connection pool.

import { system } from './system'

system.start((err, components) => {
  const { mssql } = components
  mssql.onError((error) => {
    // ... error handler
  })
})

Advanced scenarios

For advanced scenarios that are not supported by any of the functions, the raw mssql Request is also available from this component:

import { Database } from '@infinitaslearning/systemic-mssql'

const initBookStore = (database: Database) => ({
  doAdvancedStuff: () => {
    const request = database.request()
    // do whatever you want with this request
  },
})