npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

@infinitaslearning/systemic-mssql

v1.0.6

Published

Systemic component to connect to Mssql

Downloads

4

Readme

@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
  },
})