@firstfleet/ffsql

FFSQL

This is our libary that wraps database calls for both Seriate (task-based) and MSSQL (promise-based).

This is a functional library that wraps the cross-platofrm node to sql library Seriate. It relies on three npm packages.

• data.task - used to create a task oriented work flow
• data.either - used to check on nulls
• seriate - used to make calls to a sql database from node
• mssql - used to make promise-based calls

Installation

npm install @firstfleet/ffsql --save

Seriate (Tasks)

Each of the exposed module methods return Task from data.task. You need to fork the task in order to get you Task.rejected(error) or your Task.of(data)

Require the module
cosnt sql = require('@firstfleet/ffsql')

ReturnSqlResults

sql.ReturnSqlResults(procedure, params)

Takes in a prodcedure name {string} and params {Object}. Returns a Task.rejected(error) || Task.of(data)

Example

sql.ReturnSqlResults("Database.dbo.getSqlRecords", {user: userId, option: 1})

ExecSql

sql.ExecSql(procedure, params)

Takes in a procedure name {string} and a params {Object}. Returns either a Task.rejected(error) || Task.of('Success') Used to execute a sql procedure when not expecting any data to return.

ExecDynamicSql

sql.ExecDynamicSql(procdedure || sql string, params)

Takes in either a sql procedure, or a string of sql text like "select * from table". Returns either a Task.rejected(error) || Task.of(data)

MSSQL (Promises)

ExecMsProc (procecure, params, options)

Inputs

• procedure: name of SQL stored procedure
• params: object containing key/value options that must match the parameters of the procedure
• options
  • firstOnly - if set to true, the method will only return the first record found (instead of an array)

Special notes

• If your query result is a single value, it will automatically be unpacked to a simple scaler value (instead of an array)

Examples:

const sql = require('@firstfleet/ffsql')
sql.ExecMsProc('ESig_GetFieldsToSave', {DocID: docId, SignSequence: signSequence}, {firstOnly: true})
sql.ExecMsProc('ESig_GetEmployeeDocs', {empId: empId})
sql.ExecMsProc('ESig_GetDocsMissingPDFData');

ExecMsQuery (queryText, params, options)

Inputs

• queryText: Any SQL text. Param placeholders should be in the string prefixed with @ followed by an incrementing integer (similar to .NET PetaPoco syntax)
• params: an array of parameters that will slide into the queryText placeholders
• options
  • firstOnly - if set to true, the method will only return the first record found (instead of an array)

Special notes

• If your query result is a single value, it will automatically be unpacked to a simple scaler value (instead of an array)

Examples:

 const sql = require('@firstfleet/ffsql')
 sql.ExecMsQuery(`select count(*) from ESigEmpDocs where EmpId = @0`, [empId], {firstOnly: true});
 sql.ExecMsQuery('update ESigEmpDocs set SignDate = getdate(), SignStatus = @0 where Id = @1', [signStatus, empDocId])