npm.io
1.0.3 • Published 8 years ago

mssql-query-builder

Licence
MIT
Version
1.0.3
Deps
1
Size
51 kB
Vulns
0
Weekly
0
Stars
4
SummaryDependencyVersions

mssql-query-builder

Dynamically build Microsoft SQL Server queries using JavaScript.

package build downloads minified size minzipped size

Install

  • npm install mssql-query-builder --save or
  • yarn add mssql-query-builder

Use

Note: All methods can be chained together. Each method call returns the original QueryBuilder instance.

import QueryBuilder from 'mssql-query-builder';

// Provide an optional MSSQL_CONFIG parameter to include server, user, password, database, etc.
// If you do not include one, it will default to environment variables.
const query = new QueryBuilder(MSSQL_CONFIG);

// Seed the query builder with some literal SQL.
query.query('SELECT a WHERE b = c');

// Conditionally write part of the query.
query.from(
  process.env.flag ?
    'table_x' :
    'table_y'
);

if (today === 'Monday') {
  query.whereIn('birthDay', [ 1, 2, 3 ]);
}

// Skip the first 100 rows, then fetch the next 25.
// Useful for pagination.
query.offset(100).fetch(25);

// Output the query thus far as a string (for debugging).
console.log(query.buildQuery());

// Execute the query (returns a Promise).
query.execute().then(
  (result) => {
    console.log(result);
  }
);

Methods

  • all(all?: boolean)

    Specifies that duplicate rows can appear in the result set.

    This is the opposite of distinct(boolean).

  • distinct(distinct?: boolean)

    Specifies that only unique rows can appear in the result set.

    Null values are considered equal for the purposes of the DISTINCT keyword.

  • distinct(selectItem: string)

    Returns all distinct values of the single select item.

  • distinct(selectList: string[])

    Returns all distinct values of the select list.

  • execute()

    Executes the query as built thus far. Returns a Promise of a result.

  • execute(query: string)

    Executes the query string provided. Returns a Promise of a result.

  • fetch(n: number)

    Specifies the number of rows to return, after processing the OFFSET clause.

    The argument for the FETCH clause can be an integer or expression that is greater than or equal to one.

  • from(table: string)

    Specifies the tables from which to retrieve rows.

  • groupBy(column: string)

    Specifies the groups (equivalence classes) that output rows are to be placed in.

    If aggregate functions are included in the SELECT clause's select list, the GROUP BY clause calculates a summary value for each group.

  • having(...having: string[])

    Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used with a GROUP BY clause. When GROUP BY is not used, there is an implicit single, aggregated group.

  • input(value: Input)

    Inputs a SQL variable and returns its generated variable name.

const myVar = query.input('Bob');
query.where('name = ' + myVar); // WHERE name = @__QB_INPUT_1__
query
  .input(1, 'myNumber', Int)
  .input('password', 'userPass', NVarChar)

Supported Input types and their respective ISqlTypeFactoryWithNoParams types are are boolean (Bit), number (Int), string (NVarChar), Buffer (VarBinary), and Date (DateTime).

Omitting the ISqlTypeFactoryWithNoParams type will result in the QueryBuilder determining the type on its own.

query.orderBy('column_name');
query.orderBy('column_name ASC');
query.orderBy({ by: 'column_name', order: 'ASC' });
query.orderBy([
  'column_name',
  { by: 'column2_name', order: 'DESC' }
]);
  • query(q: string)

    Convert a string into a QueryBuilder object.

    Sometimes it's easier to start your query building process with an incomplete SQL query instead of building it from scratch.

const query1 = new QueryBuilder().select('a').where('b = 1');
const query2 = new QueryBuilder().query('SELECT a WHERE b = 1');
query1.from('c');
query2.from('d');
const MY_VALUE = 1;
new QueryBuilder()
  .input(MY_VALUE, 'value')
  .select('a')
  .from('b')
  .where('a = @value')
  .rowCount()
  .then(
    (rows) => {
      console.log(`There are ${rows} rows where a equals ${MY_VALUE}.`);
  );
  • select(...s: Array<Aliases | string>)

    Specifies the columns to be returned by the query.

    As strings, query.select('column_name') or query.select('column1_name', 'column2_name').

    To use aliases, pass an object where the object keys are column aliases and values are their expressions.

query.select({
  column: 'column',                                        // no alias
  expression: 'MAX(column1 + column2, column3 / column4)', // alias is `expression`
  Two: '1 + 1'
});
  • time()

    Time it took to execute the query.

    Returns 0 if the query has no begun executing.

    Returns negative if the query is still executing.

// Zero because the query has not begun to execute:
console.log(query.time());

query
  .select('a')
  .from('b')
  .execute()
  .then(
    (result) => {

      // Time it took the execute:
      console.log(query.time());
    }
  );

// Negative, because the query is still executing asynchronously:
console.log(query.time());
// Select all users who are 3 or 4 feet tall.
query.select('username').from('users').whereIn('FLOOR(height / 12)', [ 3, 4 ]);

// Select all users who are named Bob or Tim.
query.select('username').from('users').whereIn('name', [ 'Bob', 'Tim' ]);

Keywords

nodenodejssqlsqlserver